Sybase close problem

[seanx]

I am using JBoss 3.0 with Tomcat 403. Here is snippet of my servlet code:
DataSource aSource = (DataSource) aJNDIContext.lookup( "java:/Sybase
DS") ;
// Get JDBC Connection, create statement and get the result to retur
n
Connection aConnection = aSource.getConnection();
Statement aStatement = aConnection.createStatement();
String aSql = "SELECT contact from download";
out.println( "<p><h5>Sql Statement: " + aSql+"</h5></p>");
ResultSet aResult = aStatement.executeQuery( aSql );
while( aResult.next() )
{
out.println("<li>contact: "+ aResult.getString(1) + "</li>\n");
}
aStatement.close();
aConnection.close();


When my servlet tries to close a connection obtained from DataSource, I get the following error message in the log.
Can someone shed some light?
thanks,

15:51:04,044 ERROR [LocalTxConnectionManager$LocalConnectionEventListener] Resou
rceException while closing connection handle!
javax.resource.ResourceException: Could not cleanup: com.sybase.jdbc2.jdbc.SybSQ
LException: SET CHAINED command not allowed within multi-statement transaction.

at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.cleanup(
LocalManagedConnection.java:198)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.unregiste
rAssociation(BaseConnectionManager2.java:666)
at org.jboss.resource.connectionmanager.LocalTxConnectionManager$LocalCo
nnectionEventListener.connectionClosed(LocalTxConnectionManager.java:381)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.closeHan
dle(LocalManagedConnection.java:338)
at org.jboss.resource.adapter.jdbc.local.LocalConnection.close(LocalConn
ection.java:97)
at testsybase.servi
ce(testsybase.java:64)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

[seanx]

I did a quick search on Internet and found something regarding this message. It is with a different product but it may apply to JBoss too.

In fact, the way Expresso deals with the autocommit mode is not compliant
with Sybase :

when it releases a connection, DBConnectionPool first tests the
autocommit and then put it backs to true if it is false. This doesn't
work because with Sybase CHAINED mode, calling getAutommitMode starts a
new transaction. So you cannot call setAutoCommit(true) without
rollbacking/comitting before (it throws a SQLException with the message
"SET CHAINED command not allowed within multi-statement transaction").
This only way to work properly with Sybase is to call rollback (to be
sure there is no more transaction) and then setAutocommit(true). This is
what Poolman and Jakarta DBCP do and it is OK,
there is an other problem : DBObject delete method implicitly tries to
start a new transaction before deleting detail record. This cannot work
if you are already inside an explicit transaction. The same SQL
Exception is thrown ...

Are these problem solve by Expresso new release ?

[davidjencks]

This is fixed in more recent versions of jboss. I'm not sure about 3.0.1 but both cvs versions for sure. I recommend 3.1, ds config is much simpler.