Re: paremetized insert statement in sql extension

John Gentilin 24 Sep 2004 18:47:41 -0000

Jiyang,

If you turn this into a Query, i.e. Select EmpNo, ENAME from EMP where EmpNo= ? and EName = ? Do you still get the null pointer exception ?? Also can you tell if the Error you are getting is the error from the JDBC pre process code or is the Update actually making it to the DB and the Error is being generated from the Server. I am not sure how you can tell, I know both MySql and MSSql have trace logging abilities. If the query is making it to the DB, I would be interested in seeing what SQL was actually created.

Also 'short' equates to the java.sql.Types.SMALLINT type which is probably a 
rarely used
type. I would also type it as Integer. The mappings are listed in 
QueryParameter.java class.

-JG

Jiyang Hu wrote:

John,

Per your suggestion, I tried addParameterWithType() instead of
addParameter() for the following snapshot,

<xsl:param name="q1" select="'INSERT INTO EMP(EMPNO, ENAME) VALUES(?, ?)'"/>
<xsl:variable name="value1" select="'114'"/>
<xsl:variable name="value2" select="'hu'"/>
<xsl:value-of select="sql:clearParameters()"/>
<xsl:value-of select="sql:addParameterWithType($db, $value1, 'short')"/>
<xsl:value-of select="sql:addParameterWithType($db, $value2, 'string')"/>
<xsl:variable name="table" select='sql:pquery($db, $q1)'/>

Then I caught an SQLException, "Missing IN or OUT parameter at index:: 1". I
digged into code and found it was from method
populateStatement(PreparedStatement stmt, ExpressionContext ctx) under
SQLQueryParser class. There is an invocation for setObject() over there and
this generated the above SQLException. For my case, if I used setShort() and
setString() to replace setObject(), I found new record in database
successfully for insert query, otherwise there was always an error. I don't
know how this happened, from either JDBC or Oracle side. I am using Xalan
2.6.2, JDK 1.4.2 and Oracle 9.2.

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

Re: paremetized insert statement in sql extension

Reply via email to