Sql extension - parameterized query

[la...@swedbank.se]

Hi all.

I have tried the sql extension somewhat as a IBM z/OS DB2 client and, sofar, it has been working out allright.

One strange thing I've found however concerns the parameterized queries which is possible to implement in a number of ways in xml-document and/or stylesheet.

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="q1" select="'SELECT HANDAT,VALFRI_TEXT_RAD FROM DB2T.SPATTXT WHERE KOD_TXT_GRP = ? AND KODTXT = ?'"/>
<xsl:variable name="qparam" select="//QUERY"/>
<xsl:value-of select="sql:addParameterFromElement($db,$qparam)"/>
<xsl:param name="q1type" select="'string,string'"/>
<xsl:variable name="table" select="sql:pquery($db,$q1,$q1type)"/>

When doing this with more than one parameter (questionmark in the sql) I get:
[jcc][10143][10845][4.9.80] Invalid parameter 1: The parameter is not specified, or registered. ERRORCODE=-4461, SQLSTATE=42815 -4461

When just using one parameter, after modifying whatever needs to be modified above, it executes allright.

After spending some time rearranging the code I found that the following set-up will work:
<xsl:value-of select="sql:addParameterWithType($db,'SPI01','string')"/>
<xsl:value-of select="sql:addParameterWithType($db,'0000004016','string')"/>
<xsl:variable name="table" select="sql:pquery($db, $q1)"/>

And after doing:
<xsl:variable name="p1">SPI01</xsl:variable>
<xsl:variable name="p2">0000004016</xsl:variable>
<xsl:value-of select="sql:addParameterWithType($db,$p1,'string')"/>
<xsl:value-of select="sql:addParameterWithType($db,$p2,'string')"/>

It actually works so the long and the short of it is that there is a workaround. 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
DQS, Mainframes and DW (Sweden)

Swedbank AB (publ)
105 34 Stockholm
Telefon: +46 (0)8 58 59 43 45
Mobil: +46 (0)70 95 27 774
Email:lars.bjerges@swedbank.se<bl...@swedbank.se>
www.swedbank.se<blocked::http://www.swedbank.se/>

Vi ber dig lägga märke till att detta e-postmeddelande kan innehålla konfidentiell information. Om du felaktigt blivit mottagare av detta meddelande ber vi dig informera avsändaren om felet genom att använda svara-funktionen. Vi ber dig också att radera e-postmeddelandet utan att skicka det vidare eller kopiera det. Trots att vi intygar att e-postmeddelandet och eventuella bilagor inte innehåller virus och andra fel som kan påverka datorn eller IT-systemet där det mottages och läses, öppnas det på mottagarens eget ansvar. Vi tar inte på oss något ansvar för förlust eller skada, som har uppstått i samband med att e-postmeddelandet mottagits och använts.
_____________________________________________________________________________________________________________________________________________________________

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 without making, 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 recipient opens the message at his or her own risk. We assume no responsibility for any loss or damage arising from the receipt or use of this message.