You are viewing a plain text version of this content. The canonical link for it is here.
Posted to j-users@xalan.apache.org by la...@swedbank.se on 2012/12/20 09:02:03 UTC
Sql extension - parameterized query
Hi all.
I posted this message at dev@xalan.apache.org<ma...@xalan.apache.org> 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 far, 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 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
Sub-department Team zOS and Common Functions in Release & Quality Management (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.
Re: SV: Sql extension - parameterized query
Posted by Gary Gregory <GG...@rocketsoftware.com>.
Can you try the current version 2.7.1?
Gary
On Dec 20, 2012, at 7:48, "lars.bjerges@swedbank.se<ma...@swedbank.se>" <la...@swedbank.se>> wrote:
Well, I’m sorry to say that it does not work for me, I get:
file:///C:/xalan-j_2_7_0/samples/extensions/sql/pquery/dbtest.xsl; Line #41; Column #70; java.sql.SQLException: At least one parameter to the current statement is uninitialized.
file:///C:/xalan-j_2_7_0/samples/extensions/sql/pquery/dbtest.xsl; Line #51; Column #25; Error in Query
When fiddling around however it seems that there is something amiss in the use of:
<!-- Build a CSV list of parameter types -->
<xsl:param name="q1type" select="int" />
Whenever I try to make a list, e.g. “int,string” (which would be a proper CSV list to my understanding) I get:
javax.xml.transform.TransformerException: javax.xml.transform.TransformerException: Ytterligare otillåtna tecken: ',', 'string'
which is OK so I try to do an alternate pquery invocation:
<xsl:variable name="table" select="sql:pquery($db, $q1, 'int,string')"/> , I get:
file:///C:/xalan-j_2_7_0/samples/extensions/sql/pquery/dbtest.xsl; Line #51; Column #25; Error in Query
When moving the type definitions into the Query structure (attribute “type”) and removing the third parameter from the “sql:pquery” statement it works all the time regardless of the type values I enter (e.g. string instead of int).
All tests above done with the Derby database. I will try some more on z/OS DB2 after New Year.
So, till then: Happy Holidays…….
Lars
Från: Christoffer Bruun [mailto:cdbruun@flyingpigs.dk]
Skickat: den 20 december 2012 09:17
Till: j-users@xalan.apache.org<ma...@xalan.apache.org>
Ämne: Re: Sql extension - parameterized query
Hi,
I have no direct expericence with the Sql extension, but I think you should use
<xsl:variable name="qparam" select="//QUERY/*"/>
- otherwise it may assume that you want to add only 1 parameter (the 1 QUERY element) with a text value of SPI010000004016
Best regards
Christoffer Bruun
Den 20-12-2012 09:02, lars.bjerges@swedbank.se<ma...@swedbank.se> skrev:
...
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)"/>
Re: Sql extension - parameterized query
Posted by Michael Ludwig <mi...@gmx.de>.
Christoffer Bruun schrieb am 20.12.2012 um 13:49 (+0100):
> Den 20-12-2012 13:47, lars.bjerges@swedbank.se skrev:
> ><xsl:param name="q1type" select="int" />
> >
> That won't work since select will try to evaluate int as an xpath query
> You should use:
> <xsl:param name="q1type">int</xsl:param>
Yes, but that would build an RTF (result tree fragment in XSL lingo),
and it's more efficient to use a simple string (note the quotes):
<xsl:param name="q1type" select="'int'" />
Michael
Re: Sql extension - parameterized query
Posted by Christoffer Bruun <cd...@flyingpigs.dk>.
Den 20-12-2012 13:47, lars.bjerges@swedbank.se skrev:
>
> Well, I'm sorry to say that it does not work for me, I get:
>
> file:///C:/xalan-j_2_7_0/samples/extensions/sql/pquery/dbtest.xsl;
> Line #41; Column #70; java.sql.SQLException: At least one parameter to
> the current statement is uninitialized.
>
> file:///C:/xalan-j_2_7_0/samples/extensions/sql/pquery/dbtest.xsl;
> Line #51; Column #25; Error in Query
>
> When fiddling around however it seems that there is something amiss in
> the use of:
>
> <!-- Build a CSV list of parameter types -->
>
> <xsl:param name="q1type" select="int" />
>
That won't work since select will try to evaluate int as an xpath query
You should use:
<xsl:param name="q1type">int</xsl:param>
> Whenever I try to make a list, e.g. "int,string" (which would be a
> proper CSV list to my understanding) I get:
>
> javax.xml.transform.TransformerException:
> javax.xml.transform.TransformerException: Ytterligare otillåtna
> tecken: ',', 'string'
>
> which is OK so I try to do an alternate pquery invocation:
>
> <xsl:variable name="table" select="sql:pquery($db, $q1,
> 'int,string')"/> , I get:
>
SV: Sql extension - parameterized query
Posted by la...@swedbank.se.
Well, I'm sorry to say that it does not work for me, I get:
file:///C:/xalan-j_2_7_0/samples/extensions/sql/pquery/dbtest.xsl; Line #41; Column #70; java.sql.SQLException: At least one parameter to the current statement is uninitialized.
file:///C:/xalan-j_2_7_0/samples/extensions/sql/pquery/dbtest.xsl; Line #51; Column #25; Error in Query
When fiddling around however it seems that there is something amiss in the use of:
<!-- Build a CSV list of parameter types -->
<xsl:param name="q1type" select="int" />
Whenever I try to make a list, e.g. "int,string" (which would be a proper CSV list to my understanding) I get:
javax.xml.transform.TransformerException: javax.xml.transform.TransformerException: Ytterligare otillåtna tecken: ',', 'string'
which is OK so I try to do an alternate pquery invocation:
<xsl:variable name="table" select="sql:pquery($db, $q1, 'int,string')"/> , I get:
file:///C:/xalan-j_2_7_0/samples/extensions/sql/pquery/dbtest.xsl; Line #51; Column #25; Error in Query
When moving the type definitions into the Query structure (attribute "type") and removing the third parameter from the "sql:pquery" statement it works all the time regardless of the type values I enter (e.g. string instead of int).
All tests above done with the Derby database. I will try some more on z/OS DB2 after New Year.
So, till then: Happy Holidays.......
Lars
Från: Christoffer Bruun [mailto:cdbruun@flyingpigs.dk]
Skickat: den 20 december 2012 09:17
Till: j-users@xalan.apache.org
Ämne: Re: Sql extension - parameterized query
Hi,
I have no direct expericence with the Sql extension, but I think you should use
<xsl:variable name="qparam" select="//QUERY/*"/>
- otherwise it may assume that you want to add only 1 parameter (the 1 QUERY element) with a text value of SPI010000004016
Best regards
Christoffer Bruun
Den 20-12-2012 09:02, lars.bjerges@swedbank.se<ma...@swedbank.se> skrev:
...
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)"/>
Re: Sql extension - parameterized query
Posted by Christoffer Bruun <cd...@flyingpigs.dk>.
Hi,
I have no direct expericence with the Sql extension, but I think you
should use
<xsl:variable name="qparam" select="//QUERY*/**"/>
- otherwise it may assume that you want to add only 1 parameter (the 1
QUERY element) with a text value of SPI010000004016
Best regards
Christoffer Bruun
Den 20-12-2012 09:02, lars.bjerges@swedbank.se skrev:
> ...
> 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)"/>
>