Hello,

I migrate an app from hibernate 3.0 to 3.1 and get a pb with the jdbc connection closing.

I use the session.connection() in a jboss JTA manage transaction.

I read in documentation that connection opened by session.connection() should be close by user.
So i modify my code to close the session manualy like this :
Connection cnx = session.connection();
... do some jdbc work
cnx.close();

But now i get the following error on transaction commit :
2006-03-28 16:02:57,761 WARN [org.jboss.tm.TransactionImpl] XAException: tx=TransactionImpl:XidImpl[FormatId=257, GlobalId=zorglub/43, BranchQual=, localId=43] errorCode=XA_UNKNOWN(0)
org.jboss.resource.connectionmanager.JBossLocalXAException: could not commit local tx; - nested throwable: (org.jboss.resource.JBossResourceException: SQLException; - nested throwable: (org.postgresql.util.PSQLException: Connection is closed. Operation is not permitted.))

So how and when should i close my connection ?

I also try to put the hibernate.connection.release_mode to "on_close" but it seems have no effect on connection closing for connection open with session.connection()


Thanks in advance for your reply

Nicolas PERIDONT

It ised to be the case in some circumstances for 3.0.5, 2.x just returned currect connection and there was no need to close it manually, 3.1 line seens to go back to 2.x design and simply return the current connection:

public Connection borrowConnection() {
if ( isClosed ) {
throw new HibernateException( "connection manager has been closed" );
}
if ( isSuppliedConnection() ) {
return connection;
}
else {
if ( borrowedConnection == null ) {
borrowedConnection = BorrowedConnectionProxy.generateProxy( this );
}
return borrowedConnection;
}
}


The bottom line: do not close the connection :)

Thanks for the reply but it does not work better without the close() call

If i remove the close()
The connection is not realase in the connectionPool, it remain in inUse state

i checked the AvailableConnectionCount in ManagedConnectionPool Mbean and it decrease by one each time a call my code.

Here is hibenate log with connection.close() call
2006-03-28 23:39:26,625 DEBUG [org.hibernate.jdbc.JDBCContext] opening user JDBC connection, application must close it
2006-03-28 23:39:26,707 DEBUG [org.hibernate.jdbc.JDBCContext] before transaction completion
2006-03-28 23:39:26,707 DEBUG [org.hibernate.impl.SessionImpl] before transaction completion
2006-03-28 23:39:26,740 WARN [org.jboss.tm.TransactionImpl] XAException: tx=TransactionImpl:XidImpl[FormatId=257, GlobalId=zorglub/33, BranchQual=, localId=33] errorCode=XA_UNKNOWN(0)
org.jboss.resource.connectionmanager.JBossLocalXAException: could not commit local tx; - nested throwable: (org.jboss.resource.JBossResourceException: SQLException; - nested throwable: (org.postgresql.util.PSQLException: Connection is closed. Operation is not permitted.))
at org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource.commit(TxConnectionManager.java:947)




And the log without close()
2006-03-28 23:22:49,603 DEBUG [org.hibernate.jdbc.JDBCContext] opening user JDBC connection, application must close it
2006-03-28 23:22:49,677 DEBUG [org.hibernate.jdbc.JDBCContext] before transaction completion
2006-03-28 23:22:49,677 DEBUG [org.hibernate.impl.SessionImpl] before transaction completion
2006-03-28 23:22:49,678 DEBUG [org.hibernate.transaction.CacheSynchronization] transaction after completion callback, status: 3
2006-03-28 23:22:49,678 DEBUG [org.hibernate.jdbc.JDBCContext] after transaction completion
2006-03-28 23:22:49,678 DEBUG [org.hibernate.impl.SessionImpl] after transaction completion
2006-03-28 23:22:49,678 DEBUG [org.hibernate.transaction.CacheSynchronization] automatically closing session
2006-03-28 23:22:49,678 DEBUG [org.hibernate.impl.SessionImpl] automatically closing session
2006-03-28 23:22:49,678 DEBUG [org.hibernate.impl.SessionImpl] closing session
2006-03-28 23:22:49,678 DEBUG [org.hibernate.jdbc.ConnectionManager] connection already null in cleanup : no action
2006-03-28 23:22:49,678 DEBUG [org.hibernate.impl.SessionImpl] closing session
2006-03-28 23:22:49,678 DEBUG [org.hibernate.jdbc.ConnectionManager] connection already null in cleanup : no action
2006-03-28 23:23:20,548 DEBUG [org.hibernate.jdbc.ConnectionManager] running Session.finalize()


If i well understand what happened i should call the ConnectionProvider.closeConnection(cnx) instead of cnx.close()
But i see no way to get a ref to the connectionProvider instance from the Hibernate session instance

Thanks in advance for your help

It is strange because I did quick test in 3.1.3 and in 3.2-trunk and connection() returns the same object and does not exhaust my pool of 1(one) connection.

thanks moving from 3.1rc2 (the one ship with jboss4.0.3SP1) to 3.1.3 solve the closing pb.

But i get a new problem
The job i do with my native jdbc connection is postgres specific sql to read a postgres largeObject.

Now when i make session.connection() i get a ConnectionManager proxy instance of instead of a jboss WrappedConnection instance as in version 3.0

Is there a way i can cast this proxy to PGConnection (postgres jdbc connection class) like calling the jboss WrappedConnection.getUnderlyingConnection() ?

Regards,
Nicolas PERIDONT

Finally i patch the ConnectionManager class from the hibernate src code

Changing the folowing method

public Connection borrowConnection() {
return getConnection();
}

returning the connection directly instead of returning a BorrowedConnectionProxy of it

No idea if i will get side effect from this modification but it looks to work like i want

Nicolas PERIDONT

At least with hibernate 3.2 RC2 without jboss I could use:

getCurrentSession().connection().getMetaData().getConnection();

I don't know if this works with jboss.

Since you are an expert in patching:
is it possible that you integrated the copy patch into the newest postgresq jdbc driver release?

best wishes
ido