Sql extension - parameterized query
<[email protected]> Thu, 20 Dec 2012 09:02:03 +0100
| Newsgroups | gmane.text.xml.xalan.java.user |
|---|---|
| Message-ID | <7AE554A17B4ECB449C6EA79DAA4DD4886281BD29D1@FSPAS01EV010.fspa.myntet.se> |
--_000_7AE554A17B4ECB449C6EA79DAA4DD4886281BD29D1FSPAS01EV010f_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all. I posted this message at [email protected]<mailto:[email protected]> = but found it to be more appropriate to use this forum instead so here goes.= ..... I have tried the sql extension somewhat as a IBM z/OS DB2 client and, so fa= r, it has been working out all right. This is Xalan 2.7.0 by the way. One strange thing I've found however concerns the parameterized queries whi= ch is possible to implement in a number of ways in xml-document and/or styl= esheet. What I would like to do is to have a xml fragment containing the parameter= values: <QUERY> <KOD_TXT_GRP>SPI01</KOD_TXT_GRP> <KODTXT>0000004016</KODTXT> </QUERY> And, in the stylesheet have the following code: <xsl:param name=3D"q1" select=3D"'SELECT HANDAT,VALFRI_TEXT_RAD FROM DB2T.S= PATTXT WHERE KOD_TXT_GRP =3D ? AND KODTXT =3D ?'"/> <xsl:variable name=3D"qparam" select=3D"//QUERY"/> <xsl:value-of select=3D"sql:addParameterFromElement($db,$qparam)"/> <xsl:param name=3D"q1type" select=3D"'string,string'"/> <xsl:variable name=3D"table" select=3D"sql:pquery($db,$q1,$q1type)"/> When doing this with more than one parameter (questionmark in the sql) I ge= t: [jcc][10143][10845][4.9.80] Invalid parameter 1: The parameter is not speci= fied, or registered. ERRORCODE=3D-4461, SQLSTATE=3D42815 -4461 When just using one parameter, after modifying whatever needs to be modifie= d above, it executes allright. After spending some time rearranging the code I found that the following se= t-up will work: <xsl:value-of select=3D"sql:addParameterWithType($db,'SPI01','string')"/> <xsl:value-of select=3D"sql:addParameterWithType($db,'0000004016','string')= "/> <xsl:variable name=3D"table" select=3D"sql:pquery($db, $q1)"/> And after doing: <xsl:variable name=3D"p1">SPI01</xsl:variable> <xsl:variable name=3D"p2">0000004016</xsl:variable> <xsl:value-of select=3D"sql:addParameterWithType($db,$p1,'string')"/> <xsl:value-of select=3D"sql:addParameterWithType($db,$p2,'string')"/> It actually works so the long and the short of it is that there is a workar= ound. The original problem is still there however so my question is: Has anybody done the original pattern with more than one parameter, if so, = what have I done wrong? Regards Lars Bjerges Sub-department Team zOS and Common Functions in Release & Quality Managemen= t (Sweden) Swedbank AB (publ) 105 34 Stockholm Telefon: +46 (0)8 58 59 43 45 Mobil: +46 (0)70 95 27 774 Email:[email protected]<blocked::mailto:[email protected]> www.swedbank.se<blocked::http://www.swedbank.se/> Vi ber dig l=E4gga m=E4rke till att detta e-postmeddelande kan inneh=E5lla = konfidentiell information. Om du felaktigt blivit mottagare av detta meddel= ande ber vi dig informera avs=E4ndaren om felet genom att anv=E4nda svara-f= unktionen. Vi ber dig ocks=E5 att radera e-postmeddelandet utan att skicka = det vidare eller kopiera det. Trots att vi intygar att e-postmeddelandet oc= h eventuella bilagor inte inneh=E5ller virus och andra fel som kan p=E5verk= a datorn eller IT-systemet d=E4r det mottages och l=E4ses, =F6ppnas det p= =E5 mottagarens eget ansvar. Vi tar inte p=E5 oss n=E5got ansvar f=F6r f=F6= rlust eller skada, som har uppst=E5tt i samband med att e-postmeddelandet m= ottagits och anv=E4nts. ___________________________________________________________________________= ___________________________________________________________________________= _______ Please note that this message may contain confidential information. If you = have received this message by mistake, please inform the sender of the mist= ake by sending a reply, then delete the message from your system without ma= king, distributing or retaining any copies of it. Although we believe that = the message and any attachment are free from viruses and other errors that = might affect the computer or IT system where it is received and read, the r= ecipient opens the message at his or her own risk. We assume no responsibil= ity for any loss or damage arising from the receipt or use of this message. --_000_7AE554A17B4ECB449C6EA79DAA4DD4886281BD29D1FSPAS01EV010f_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr= osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:= //www.w3.org/TR/REC-html40"><head><meta http-equiv=3DContent-Type content= =3D"text/html; charset=3Diso-8859-1"><meta name=3DGenerator content=3D"Micr= osoft Word 12 (filtered medium)"><style><!-- /* Font Definitions */ @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face {font-family:Verdana; panose-1:2 11 6 4 3 5 4 4 2 4;} @font-face {font-family:FSpaCaslon; panose-1:0 0 4 0 0 0 0 0 0 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri","sans-serif";} a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:purple; text-decoration:underline;} span.E-postmall17 {mso-style-type:personal-compose; font-family:"Calibri","sans-serif"; color:windowtext;} .MsoChpDefault {mso-style-type:export-only;} @page WordSection1 {size:612.0pt 792.0pt; margin:70.85pt 70.85pt 70.85pt 70.85pt;} div.WordSection1 {page:WordSection1;} --></style><!--[if gte mso 9]><xml> <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" /> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext=3D"edit"> <o:idmap v:ext=3D"edit" data=3D"1" /> </o:shapelayout></xml><![endif]--></head><body lang=3DSV link=3Dblue vlink= =3Dpurple><div class=3DWordSection1><p class=3DMsoNormal><span lang=3DEN-US= >Hi all.<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US><o:p>= </o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>I posted thi= s message at <a href=3D"mailto:[email protected]">[email protected]</= a> but found it to be more appropriate to use this forum instead so here go= es…… <o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN= -US><o:p> </o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>I h= ave tried the sql extension somewhat as a IBM z/OS DB2 client and, so far, = it has been working out all right. This is Xalan 2.7.0 by the way.<o:p></o:= p></span></p><p class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></spa= n></p><p class=3DMsoNormal><span lang=3DEN-US>One strange thing I’ve = found however concerns the parameterized queries which is possible to imple= ment in a number of ways in xml-document and/or stylesheet.<o:p></o:p></spa= n></p><p class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span></p><= p class=3DMsoNormal><span lang=3DEN-US>What I would like to do is to have &= nbsp;a xml fragment containing the parameter values:<o:p></o:p></span></p><= p class=3DMsoNormal><span lang=3DEN-US><QUERY><o:p></o:p></span></p><= p class=3DMsoNormal><span lang=3DEN-US><KOD_TXT_GRP>SPI01</KOD_TXT= _GRP><o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US><K= ODTXT>0000004016</KODTXT><o:p></o:p></span></p><p class=3DMsoNorma= l><span lang=3DEN-US></QUERY><o:p></o:p></span></p><p class=3DMsoNorm= al><span lang=3DEN-US>And, in the stylesheet have the following code:<o:p><= /o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></= span></p><p class=3DMsoNormal><span lang=3DEN-US><xsl:param name=3D"= ;q1" select=3D"'SELECT HANDAT,VALFRI_TEXT_RAD FROM DB2T.SPATTXT W= HERE KOD_TXT_GRP =3D ? AND KODTXT =3D ?'"/><o:p></o:p></span></p><p= class=3DMsoNormal><span lang=3DEN-US><xsl:variable name=3D"qparam&= quot; select=3D"//QUERY"/><o:p></o:p></span></p><p class=3DMso= Normal><span lang=3DEN-US><xsl:value-of select=3D"sql:addParameterF= romElement($db,$qparam)"/><o:p></o:p></span></p><p class=3DMsoNorma= l><span lang=3DEN-US><xsl:param name=3D"q1type" select=3D"= ;'string,string'"/><o:p></o:p></span></p><p class=3DMsoNormal><span= lang=3DEN-US><xsl:variable name=3D"table" select=3D"sql:= pquery($db,$q1,$q1type)"/><o:p></o:p></span></p><p class=3DMsoNorma= l><span lang=3DEN-US><o:p> </o:p></span></p><p class=3DMsoNormal><span= lang=3DEN-US>When doing this with more than one parameter (questionmark in= the sql) I get:<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-= US>[jcc][10143][10845][4.9.80] Invalid parameter 1: </span><span lang=3DEN>= The parameter is not specified, or registered</span><span lang=3DEN-US>. ER= RORCODE=3D-4461, SQLSTATE=3D42815 -4461<o:p></o:p></span></p><p class=3DMso= Normal><span lang=3DEN-US><o:p> </o:p></span></p><p class=3DMsoNormal>= <span lang=3DEN-US>When just using one parameter, after modifying whatever = needs to be modified above, it executes allright.<o:p></o:p></span></p><p c= lass=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span></p><p class=3D= MsoNormal><span lang=3DEN-US>After spending some time rearranging the code = I found that the following set-up will work:<o:p></o:p></span></p><p class= =3DMsoNormal><span lang=3DEN-US><xsl:value-of select=3D"sql:addPara= meterWithType($db,'SPI01','string')"/><o:p></o:p></span></p><p clas= s=3DMsoNormal><span lang=3DEN-US><xsl:value-of select=3D"sql:addPar= ameterWithType($db,'0000004016','string')"/>  = ; &n= bsp;  = ; &n= bsp;  = ; &n= bsp; &nbs= p; &= nbsp; &nbs= p; &= nbsp; &nbs= p; <o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US><xsl:va= riable name=3D"table" select=3D"sql:pquery($db, $q1)"/&= gt;<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US><o:p> = ;</o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>And after doing:<= o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US><xsl:variab= le name=3D"p1">SPI01</xsl:variable><o:p></o:p></span></p= ><p class=3DMsoNormal><span lang=3DEN-US><xsl:variable name=3D"p2&q= uot;>0000004016</xsl:variable><o:p></o:p></span></p><p class=3DMso= Normal><span lang=3DEN-US><xsl:value-of select=3D"sql:addParameterW= ithType($db,$p1,'string')"/><o:p></o:p></span></p><p class=3DMsoNor= mal><span lang=3DEN-US><xsl:value-of select=3D"sql:addParameterWith= Type($db,$p2,'string')"/><o:p></o:p></span></p><p class=3DMsoNormal= ><span lang=3DEN-US><o:p> </o:p></span></p><p class=3DMsoNormal><span = lang=3DEN-US>It actually works so the long and the short of it is that ther= e is a workaround. The original problem is still there however so my questi= on is:<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US><o:p>&n= bsp;</o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>Has anybody do= ne the original pattern with more than one parameter, if so, what have I do= ne wrong?<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US><o:p= > </o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>Regards<o:p= ></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p>= </span></p><p class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span>= </p><p class=3DMsoNormal><b><span lang=3DEN-GB style=3D'font-size:10.0pt;fo= nt-family:"Arial","sans-serif";color:#999999'>Lars Bjerges</span></b><span = lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Arial","sans-serif";col= or:#999999'><o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US s= tyle=3D'font-size:8.0pt;font-family:"Arial","sans-serif";color:#BFBFBF'>Sub= -department Team zOS and Common Functions in Release & Quality</span><s= pan lang=3DEN-US style=3D'font-size:8.0pt;font-family:"Arial","sans-serif";= color:#004080'> </span><span lang=3DEN-US style=3D'font-size:8.0pt;font-fam= ily:"Arial","sans-serif";color:#BFBFBF'>Management </span><span lang=3DEN-G= B style=3D'font-size:8.0pt;font-family:"Arial","sans-serif";color:#999999'>= =A0(Sweden)<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-GB st= yle=3D'font-size:8.0pt;font-family:"Arial","sans-serif";color:#999999'><o:p= > </o:p></span></p><p class=3DMsoNormal><span style=3D'font-size:8.0pt= ;font-family:"Arial","sans-serif";color:#999999'>Swedbank AB (publ)</span><= span style=3D'font-size:12.0pt;font-family:FSpaCaslon;color:gray'><o:p></o:= p></span></p><p class=3DMsoNormal><span style=3D'font-size:8.0pt;font-famil= y:"Arial","sans-serif";color:#999999'>105 34 Stockholm</span><span style=3D= 'font-family:FSpaCaslon;color:gray'><o:p></o:p></span></p><p class=3DMsoNor= mal><span style=3D'font-size:8.0pt;font-family:"Arial","sans-serif";color:#= 999999'>Telefon: +46 (0)8 58 59 43 45</span><span style=3D'font-family= :FSpaCaslon;color:gray'><o:p></o:p></span></p><p class=3DMsoNormal><span st= yle=3D'font-size:8.0pt;font-family:"Arial","sans-serif";color:#999999'>Mobi= l: +46 (0)70 95 27 774</span><span style=3D'font-family:FSpaCaslon;col= or:gray'><o:p></o:p></span></p><p class=3DMsoNormal><span style=3D'font-siz= e:8.0pt;font-family:"Arial","sans-serif";color:#999999'>Email:<a href=3D"bl= ocked::mailto:[email protected]" title=3D"mailto:lars.bjerges@swedba= nk.se"><span style=3D'color:blue'>[email protected]</span></a></span= ><span style=3D'font-family:FSpaCaslon;color:gray'><o:p></o:p></span></p><p= class=3DMsoNormal><span style=3D'font-size:8.0pt;font-family:"Verdana","sa= ns-serif";color:#CC6633'><a href=3D"blocked::http://www.swedbank.se/" title= =3D"http://www.swedbank.se/"><span style=3D'color:#B55100'>www.swedbank.se<= /span></a></span><span style=3D'font-family:FSpaCaslon;color:gray'><o:p></o= :p></span></p><p class=3DMsoNormal><span style=3D'font-family:FSpaCaslon;co= lor:gray'> <o:p></o:p></span></p><p class=3DMsoNormal><span style=3D'f= ont-size:8.0pt;font-family:"Arial","sans-serif";color:#999999'>Vi ber dig l= =E4gga m=E4rke till att detta e-postmeddelande kan inneh=E5lla konfidentiel= l information. Om du felaktigt blivit mottagare av detta meddelande ber vi = dig informera avs=E4ndaren om felet genom att anv=E4nda svara-funktionen. V= i ber dig ocks=E5 att radera e-postmeddelandet utan att skicka det vidare e= ller kopiera det. Trots att vi intygar att e-postmeddelandet och eventuella= bilagor inte inneh=E5ller virus och andra fel som kan p=E5verka datorn ell= er IT-systemet d=E4r det mottages och l=E4ses, =F6ppnas det p=E5 mottagaren= s eget ansvar. Vi tar inte p=E5 oss n=E5got ansvar f=F6r f=F6rlust eller sk= ada, som har uppst=E5tt i samband med att e-postmeddelandet mottagits och a= nv=E4nts.</span><span style=3D'font-family:FSpaCaslon;color:gray'><o:p></o:= p></span></p><p class=3DMsoNormal><span lang=3DEN-GB style=3D'font-size:8.0= pt;font-family:"Arial","sans-serif";color:#999999'>________________________= ___________________________________________________________________________= __________________________________________________________</span><span lang= =3DEN-GB style=3D'font-family:FSpaCaslon;color:gray'><o:p></o:p></span></p>= <p class=3DMsoNormal><span lang=3DEN-GB style=3D'font-family:FSpaCaslon;col= or:gray'> <o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-G= B style=3D'font-size:8.0pt;font-family:"Arial","sans-serif";color:#999999'>= Please note that this message may contain confidential information. If you = have received this message by mistake, please inform the sender of the= mistake by sending a reply, then delete the message from your system witho= ut making, distributing or retaining any copies of it. Although we bel= ieve that the message and any attachment are free from viruses and other er= rors that might affect the computer or IT system where it is received and r= ead, the recipient opens the message at his or her own risk. We assume no r= esponsibility for any loss or damage arising from the receipt or use of thi= s message.</span><span lang=3DEN-GB><o:p></o:p></span></p><p class=3DMsoNor= mal><span lang=3DEN-US><o:p> </o:p></span></p></div></body></html>= --_000_7AE554A17B4ECB449C6EA79DAA4DD4886281BD29D1FSPAS01EV010f_--