xalan-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <lars.bjer...@swedbank.se>
Subject Sql extension - parameterized query
Date Thu, 13 Dec 2012 07:30:49 GMT
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<blocked::mailto:lars.bjerges@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.



Mime
View raw message