Jiyang,
I asked you to test the query because I wanted to see if there was some base line bug in the SQL extension of some compatibility issue with Oracle and the usage of the setObject method in their JDBC libraries. If I have read your email correctly, the setObject method works fine as long as the SQL being created is a Query statement but fails if it is an Update/Insert.
I find this a bit bizarre, I tried to do a search on the error code but the results were slim. There was something about it being a language issue, but the link was broken when I tried to dig further.
I suspect that the JDBC library is generating bad SQL code with the setObject method. Have you
thrown this back to Oracle to see why a prepared statement, if it is an Insert statement, can't
utilize the setObject method ??
Can you replicate this problem with some simple java code ??
-JG
Jiyang Hu wrote:
John,
As you mentioned, SQL extension works mainly for select query, and in my case for select query test it works fine too.
The error message I got from SQLException returned me a null for sql statement and error code 17041(I am using Oracle 9.2). So far, I don't know which side the error comes from, JDBC or Server. I used the following 2 lines code to replace setObject() in populateStatement() of SQLQueryParser class, and I did see new record in database.
if (indx==0) stmt.setShort(1,Short.valueOf(value).shortValue()); if (indx==1) stmt.setString(2,value);
Jiyang
-----Original Message----- From: John Gentilin [mailto:[EMAIL PROTECTED] Sent: Friday, September 24, 2004 12:49 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: paremetized insert statement in sql extension
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>
