Return-Path: 
 <dev-return-38785-apmail-xalan-dev-archive=xalan.apache.org@xalan.apache.org>
X-Original-To: apmail-xalan-dev-archive@www.apache.org
Delivered-To: apmail-xalan-dev-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 9351FD6F2
	for <apmail-xalan-dev-archive@www.apache.org>;
 Thu, 13 Dec 2012 07:31:34 +0000 (UTC)
Received: (qmail 42984 invoked by uid 500); 13 Dec 2012 07:31:34 -0000
Delivered-To: apmail-xalan-dev-archive@xalan.apache.org
Received: (qmail 42614 invoked by uid 500); 13 Dec 2012 07:31:24 -0000
Mailing-List: contact dev-help@xalan.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:dev-help@xalan.apache.org>
List-Unsubscribe: <mailto:dev-unsubscribe@xalan.apache.org>
List-Post: <mailto:dev@xalan.apache.org>
List-Id: <dev.xalan.apache.org>
Reply-To: dev@xalan.apache.org
Delivered-To: mailing list dev@xalan.apache.org
Received: (qmail 42539 invoked by uid 99); 13 Dec 2012 07:31:22 -0000
Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 13 Dec 2012 07:31:22 +0000
X-ASF-Spam-Status: No, hits=-2.8 required=5.0
	tests=HTML_MESSAGE,RCVD_IN_DNSWL_HI,SPF_HELO_PASS,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (nike.apache.org: domain of lars.bjerges@swedbank.se
 designates 164.10.46.102 as permitted sender)
Received: from [164.10.46.102] (HELO mail2.swedbank.se) (164.10.46.102)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 13 Dec 2012 07:31:15 +0000
Received: from unknown (HELO amcmail01.unix.se) ([10.155.192.81])
  by mail2in.swedbank.se with SMTP; 13 Dec 2012 08:30:53 +0100
Received: from (fspas01ex100.fspa.myntet.se [10.190.87.22]) by
 amcmail01.unix.se with smtp
	 id 3e86_1c87_0691398c_44f7_11e2_a072_005056b77bbb;
	Thu, 13 Dec 2012 08:30:52 +0100
Received: from FSPAS01EV010.fspa.myntet.se ([fe80::8542:9a17:6e18:f730]) by
 FSPAS01EX100.fspa.myntet.se ([fe80::255f:1eaa:1ad7:df%13]) with mapi; Thu, 13
 Dec 2012 08:30:49 +0100
From: <lars.bjerges@swedbank.se>
To: <dev@xalan.apache.org>
Date: Thu, 13 Dec 2012 08:30:49 +0100
Subject: Sql extension - parameterized query
Thread-Topic: Sql extension - parameterized query
Thread-Index: Ac3ZA8Zyv14xlwqBSgiYEl71krgepg==
Message-ID: 
 <7AE554A17B4ECB449C6EA79DAA4DD488627F46B26A@FSPAS01EV010.fspa.myntet.se>
Accept-Language: sv-SE
Content-Language: sv-SE
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: sv-SE
Content-Type: multipart/alternative;
	boundary="_000_7AE554A17B4ECB449C6EA79DAA4DD488627F46B26AFSPAS01EV010f_"
MIME-Version: 1.0
X-Virus-Checked: Checked by ClamAV on apache.org

--_000_7AE554A17B4ECB449C6EA79DAA4DD488627F46B26AFSPAS01EV010f_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 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
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=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_7AE554A17B4ECB449C6EA79DAA4DD488627F46B26AFSPAS01EV010f_
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;}
code
	{mso-style-priority:99;
	font-family:"Courier New";}
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>Hi all.<o:p></o:p>=
</p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal><span la=
ng=3DEN-US>I have tried the sql extension somewhat as a IBM z/OS DB2 client=
 and, sofar, it has been working out allright.<o:p></o:p></span></p><p clas=
s=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p><p class=3DMso=
Normal><span lang=3DEN-US>One strange thing I&#8217;ve found however concer=
ns the parameterized queries which is possible to implement in a number of =
ways in xml-document and/or stylesheet.<o:p></o:p></span></p><p class=3DMso=
Normal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p><p class=3DMsoNormal>=
<span lang=3DEN-US>What I would like to do is to have =A0a xml fragment con=
taining the parameter values:<o:p></o:p></span></p><p class=3DMsoNormal><sp=
an lang=3DEN-US>&lt;QUERY&gt;<o:p></o:p></span></p><p class=3DMsoNormal><sp=
an lang=3DEN-US> &lt;KOD_TXT_GRP&gt;SPI01&lt;/KOD_TXT_GRP&gt;<o:p></o:p></s=
pan></p><p class=3DMsoNormal><span lang=3DEN-US> &lt;KODTXT&gt;0000004016&l=
t;/KODTXT&gt;<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>=
&lt;/QUERY&gt;<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US=
>And, in the stylesheet have the following code:<o:p></o:p></span></p><p cl=
ass=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p><p class=3DM=
soNormal><span lang=3DEN-US>&lt;xsl:param name=3D&quot;q1&quot; select=3D&q=
uot;'SELECT HANDAT,VALFRI_TEXT_RAD FROM DB2T.SPATTXT WHERE KOD_TXT_GRP =3D =
? AND KODTXT =3D ?'&quot;/&gt;<o:p></o:p></span></p><p class=3DMsoNormal><s=
pan lang=3DEN-US>&lt;xsl:variable name=3D&quot;qparam&quot; select=3D&quot;=
//QUERY&quot;/&gt;<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DE=
N-US>&lt;xsl:value-of select=3D&quot;sql:addParameterFromElement($db,$qpara=
m)&quot;/&gt;<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>=
&lt;xsl:param name=3D&quot;q1type&quot; select=3D&quot;'string,string'&quot=
;/&gt;<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>&lt;xsl=
:variable name=3D&quot;table&quot; select=3D&quot;sql:pquery($db,$q1,$q1typ=
e)&quot;/&gt;<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>=
<o:p>&nbsp;</o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>When do=
ing 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>. </span>ERRORCODE=3D-446=
1, SQLSTATE=3D42815 -4461<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o=
:p></p><p class=3DMsoNormal><span lang=3DEN-US>When just using one paramete=
r, after modifying whatever needs to be modified above, it executes allrigh=
t.<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;=
</o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>After spending som=
e 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>&lt;xsl:value-of=
 select=3D&quot;sql:addParameterWithType($db,'SPI01','string')&quot;/&gt;<o=
:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>&lt;xsl:value-o=
f select=3D&quot;sql:addParameterWithType($db,'0000004016','string')&quot;/=
&gt;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0 <o:p></o:p></span></p><p class=3DMsoNormal><span lang=
=3DEN-US>&lt;xsl:variable name=3D&quot;table&quot; select=3D&quot;sql:pquer=
y($db, $q1)&quot;/&gt;<o:p></o:p></span></p><p class=3DMsoNormal><span lang=
=3DEN-US><o:p>&nbsp;</o:p></span></p><p class=3DMsoNormal><span lang=3DEN-U=
S>And after doing:<o:p></o:p></span></p><p class=3DMsoNormal><span lang=3DE=
N-US>&lt;xsl:variable name=3D&quot;p1&quot;&gt;SPI01&lt;/xsl:variable&gt;<o=
:p></o:p></span></p><p class=3DMsoNormal><span lang=3DEN-US>&lt;xsl:variabl=
e name=3D&quot;p2&quot;&gt;0000004016&lt;/xsl:variable&gt;<o:p></o:p></span=
></p><p class=3DMsoNormal><span lang=3DEN-US>&lt;xsl:value-of select=3D&quo=
t;sql:addParameterWithType($db,$p1,'string')&quot;/&gt;<o:p></o:p></span></=
p><p class=3DMsoNormal><span lang=3DEN-US>&lt;xsl:value-of select=3D&quot;s=
ql:addParameterWithType($db,$p2,'string')&quot;/&gt;<o:p></o:p></span></p><=
p class=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p><p class=
=3DMsoNormal><span lang=3DEN-US>It actually works so the long and the short=
 of it is that there is a workaround. The original problem is still there h=
owever so my question is:<o:p></o:p></span></p><p class=3DMsoNormal><span l=
ang=3DEN-US><o:p>&nbsp;</o:p></span></p><p class=3DMsoNormal><span lang=3DE=
N-US>Has anybody done the original pattern with more than one parameter, if=
 so, what have I done wrong?<o:p></o:p></span></p><p class=3DMsoNormal><spa=
n lang=3DEN-US><o:p>&nbsp;</o:p></span></p><p class=3DMsoNormal><span lang=
=3DEN-US>Regards</span><o:p></o:p></p><p class=3DMsoNormal><span style=3D'f=
ont-size:10.0pt'>&nbsp;<o:p></o:p></span></p><p class=3DMsoNormal><b><span =
style=3D'font-size:10.0pt;font-family:"Arial","sans-serif";color:#999999'>L=
ars Bjerges</span></b><span style=3D'font-size:10.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'>D=
QS, Mainframes and DW (Sweden)<o:p></o:p></span></p><p class=3DMsoNormal><s=
pan style=3D'font-size:8.0pt;font-family:"Arial","sans-serif";color:#999999=
'><o:p>&nbsp;</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=
-family:"Arial","sans-serif";color:#999999'>105 34 Stockholm</span><span st=
yle=3D'font-family:FSpaCaslon;color:gray'><o:p></o:p></span></p><p class=3D=
MsoNormal><span style=3D'font-size:8.0pt;font-family:"Arial","sans-serif";c=
olor:#999999'>Telefon: +46 (0)8&nbsp;58 59 43 45</span><span style=3D'font-=
family:FSpaCaslon;color:gray'><o:p></o:p></span></p><p class=3DMsoNormal><s=
pan style=3D'font-size:8.0pt;font-family:"Arial","sans-serif";color:#999999=
'>Mobil: +46 (0)70&nbsp;95 27 774</span><span style=3D'font-family:FSpaCasl=
on;color:gray'><o:p></o:p></span></p><p class=3DMsoNormal><span style=3D'fo=
nt-size:8.0pt;font-family:"Arial","sans-serif";color:#999999'>Email:<a href=
=3D"blocked::mailto:lars.bjerges@swedbank.se" title=3D"mailto:lars.bjerges@=
swedbank.se"><span style=3D'color:blue'>lars.bjerges@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-size:8.0pt;font-family:"Verdan=
a","sans-serif";color:#CC6633'><a href=3D"blocked::http://www.swedbank.se/"=
 title=3D"http://www.swedbank.se/"><span style=3D'color:#B55100'>www.swedba=
nk.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:FSpaCas=
lon;color:gray'>&nbsp;<o:p></o:p></span></p><p class=3DMsoNormal><span styl=
e=3D'font-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 konfid=
entiell information. Om du felaktigt blivit mottagare av detta meddelande b=
er vi dig informera avs=E4ndaren om felet genom att anv=E4nda svara-funktio=
nen. Vi ber dig ocks=E5 att radera e-postmeddelandet utan att skicka det vi=
dare eller kopiera det. Trots att vi intygar att e-postmeddelandet och even=
tuella bilagor inte inneh=E5ller virus och andra fel som kan p=E5verka dato=
rn eller IT-systemet d=E4r det mottages och l=E4ses, =F6ppnas det p=E5 mott=
agarens eget ansvar. Vi tar inte p=E5 oss n=E5got ansvar f=F6r f=F6rlust el=
ler skada, som har uppst=E5tt i samband med att e-postmeddelandet mottagits=
 och anv=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-si=
ze:8.0pt;font-family:"Arial","sans-serif";color:#999999'>__________________=
___________________________________________________________________________=
________________________________________________________________</span><spa=
n lang=3DEN-GB style=3D'font-family:FSpaCaslon;color:gray'><o:p></o:p></spa=
n></p><p class=3DMsoNormal><span lang=3DEN-GB style=3D'font-family:FSpaCasl=
on;color:gray'>&nbsp;<o:p></o:p></span></p><p class=3DMsoNormal><span lang=
=3DEN-GB style=3D'font-size:8.0pt;font-family:"Arial","sans-serif";color:#9=
99999'>Please note that this message may contain confidential information. =
If you have received this message by mistake, please&nbsp;inform the sender=
 of the mistake by sending a reply, then delete the message from your syste=
m without making, distributing or retaining any copies of it.&nbsp;Although=
 we believe that the message and any attachment are free from viruses and o=
ther errors that might affect the computer or IT system where it is receive=
d and read, the recipient opens the message at his or her own risk. We assu=
me no responsibility for any loss or damage arising from the receipt or use=
 of this message.</span><span lang=3DEN-GB><o:p></o:p></span></p><p class=
=3DMsoNormal><span lang=3DEN-GB><o:p>&nbsp;</o:p></span></p><p class=3DMsoN=
ormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p></div></body></html>=

--_000_7AE554A17B4ECB449C6EA79DAA4DD488627F46B26AFSPAS01EV010f_--