Re: paremetized insert statement in sql extension

[John Gentilin]

Jiyang,
I will look into addParameter, it is a function I rarely use. In the 
meantime, you can use
add ParameterWithType so you Stylesheet will change from

<xsl:value-of select="sql:addParameter($value1)"/>
<xsl:value-of select="sql:addParameter($value2)"/>
<xsl:variable name="table" select='sql:pquery($db, $q1,"short, string")'/>
to
<xsl:value-of select="sql:addParameterWithType($value1,'short')"/>
<xsl:value-of select="sql:addParameterWithType($value2,'string')"/>
<xsl:variable name="table" select='sql:pquery($db, $q1)'/>
Regards
John G
Jiyang Hu wrote:
John,
I noticed two items from SQL extension which doesn't work with following
snippet stylesheet,
<xsl:variable name="value1" select="'114'"/>
<xsl:variable name="value2" select="'hu'"/>
<xsl:value-of select="sql:addParameter($value1)"/>
<xsl:value-of select="sql:addParameter($value2)"/>
<xsl:variable name="table" select='sql:pquery($db, $q1,"short, string")'/>
1. Nullpointerexception happens when calling addParameter(String value)
under XConnection class. Because it will go addParameterWithType(String
value, String Type) and then initialize QueryParameter(value, Type). Error
is caused because Type is null when setTypeName(t) is invoked from there;
2. From debug I can find size for m_ParameterList in XConnection class is 2
after finishing 2 addParameter() action as above. But I got
ArrayOutOfBoundException when calling pquery($db, $q1,"short, string"). That
happens in addTypeToData(String typeInfo) method. One thing I can't figure
out is why size is 0 in that method for m_ParameterList.
That is my all current findings so far. Any input is welcome!
Jiyang
-----Original Message-----
From: John Gentilin [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 23, 2004 4:51 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; '[EMAIL PROTECTED]'
Subject: Re: paremetized insert statement in sql extension
Jiyang,
I posted this one back to the group to see if anyone else had run into
this problem. The general problem is that the SQL extension was never
really designed to perform insert/update operations. Depending on what
version of Xalan you are using, the pquery code either performed an
execute or executeQuery. Currently it is executeQuery, where we are
probably running into problem is that an Update does not return and
records or even metadata. I would do two things.
1) Using your exact XSL code, perform a Query. Make sure this is not just
a generic bug.
2) Search the list, I know some others were using the SQL extensions to
execute stored procedures which I think would have the same problems. There
was some example code in those threads that may be of some help.
3) Run Xalan in a debugger and look at the SQLDocument#execute method to see
where the problem is occurring. If there is a fix that won't adversely
affect
the query operations, we can add it to the code base.
Regards
John G
Jiyang Hu wrote:
 

Dear John,
I used your pquery example as reference to test my xsl stylesheet which
   

need
 

work for an insertion. My sample code is straightforward but I always
   

catch
 

a nullpoinerexception. I am wondering if you can provide me an example
   

code
 

which has an insert statement. I am using Oracle and I can execute select
statement without any trouble. Following is my code and your any comment
   

is
 

welcome, thanks!
Jiyang
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
version="1.0" xmlns:sql="org.apache.xalan.lib.sql.XConnection"
extension-element-prefixes="sql">
        <xsl:output method="xml" indent="yes"/>
        <xsl:variable name="value1" select="111"/>
        <xsl:variable name="value2" select="'hu'"/>
        <xsl:param name="q1" select="'insert into emp values(?, ?)'"/>
        <xsl:param name="cinfo" select="//DBINFO"/>
        <xsl:template match="/">
                <xsl:variable name="db" select="sql:new()"/>
                <xsl:if test="not(sql:connect($db, $cinfo))">
                        <xsl:message>Error Connecting to the 
Database</xsl:message>
                        <xsl:copy-of select="sql:getError($db)/ext-error"/>
                </xsl:if>
                <xsl:value-of select="sql:addParameter($db, $value1)"/>
                <xsl:value-of select="sql:addParameter($db, $value2)"/>
                <xsl:variable name="table" select='sql:pquery($db, $q1,"int, 
string")'/>
                <xsl:if test="not($table)">
                        <xsl:message>Error in Query</xsl:message>
                        <xsl:copy-of select="sql:getError($db)/ext-error"/>
                </xsl:if>
                <xsl:value-of select="sql:close($db)"/>
        </xsl:template>
</xsl:stylesheet>
   

 


--
--------------------------------------
John Gentilin
Eye Catching Solutions Inc.
18314 Carlwyn Drive
Castro Valley CA 94546
   Contact Info
[EMAIL PROTECTED]
Ca Office 1-510-881-4821
NJ Office 1-732-422-4917