DO NOT REPLY [Bug 30874] New: - SQLTransformer throws exception with stored procs on Sybase

[bugzilla]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG 
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://issues.apache.org/bugzilla/show_bug.cgi?id=30874>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND 
INSERTED IN THE BUG DATABASE.

http://issues.apache.org/bugzilla/show_bug.cgi?id=30874

SQLTransformer throws exception with stored procs on Sybase

           Summary: SQLTransformer throws exception with stored procs on
                    Sybase
           Product: Cocoon 2
           Version: 2.1.5
          Platform: PC
        OS/Version: Windows NT/2K
            Status: NEW
          Severity: Major
          Priority: Other
         Component: blocks
        AssignedTo: [EMAIL PROTECTED]
        ReportedBy: [EMAIL PROTECTED]


I'm wanting to use the SQLTransformer to call a stored procedure on a Sybase
database, using their jConnect JDBC driver.  My query definition is

<?xml version="1.0"?>
<page xmlns:sql="http://apache.org/cocoon/SQL/2.0";>
        <accountdetails>
                <execute-query xmlns="http://apache.org/cocoon/SQL/2.0";>
                        <query isstoredprocedure="true">
{call spu_Valuation_Report '<sql:substitute-value sql:name="portfolio"/>',
'<sql:substitute-value sql:name="login_id"/>'}
                        </query>
                </execute-query>
        </accountdetails>
</page>

However, when this runs I get no data returned (running the same call in ISQL
returns half a dozen rows) and the sitemap.log contains the following exception:

WARN    (2004-08-26) 18:05.20:171   [sitemap.transformer.sql]
(/imuk/generated/pages/imuk/secure/reports/portfoliovaluation-en-UK.jsp)
http7081-Processor8/SQLTransformer: SQLTransformer: Could not close JDBC connection
java.sql.SQLException: JZ0S2: Statement object has already been closed.
        at com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(ErrorMessage.java:436)
        at com.sybase.jdbc2.jdbc.SybStatement.checkDead(SybStatement.java:1654)
        at com.sybase.jdbc2.jdbc.SybStatement.close(SybStatement.java:420)
        at
org.apache.commons.dbcp.DelegatingCallableStatement.close(DelegatingCallableStatement.java:199)
        at
org.apache.cocoon.transformation.SQLTransformer$Query.close(SQLTransformer.java:1193)
        at
org.apache.cocoon.transformation.SQLTransformer.executeQuery(SQLTransformer.java:368)
        at
org.apache.cocoon.transformation.SQLTransformer.endExecuteQueryElement(SQLTransformer.java:478)
...

The problem is that in the execute() method the SQLTransformer has (lines 1075-1087)

                    if ( oldDriver ) {
                        cst = conn.prepareCall( query );
                    } else {
                        cst = conn.prepareCall( query,
                                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY );
                    }
                    registerOutParameters( cst );
                    pst = cst;
                }

                registerInParameters( pst );
                boolean result = pst.execute();

Note the "pst = cst" line - this is fine it itself, but has the side-effect that
the close() method (lines 1189-1194)

                if ( pst != null )
                    pst.close();
                pst = null;        // Prevent using pst again.
                if ( cst != null )
                    cst.close();
                cst = null;        // Prevent using cst again.

is therefore closing the statement twice, once through pst and again through
cst.  Presumably, other DBMS' drivers don't mind this, but jConnect does...