Problem with JDBC connection from stateless SessionBean

[ignac]

Hi,

I've got problem executing queries to the Hypersonic database from my stateless session EJB.
Below there are pieces of code and of jboss console output.
I've tried on 3.2.2 and 3.2.1 versions of JBoss.
Please, help!!!
Thanks in advance.

The code looks like that:
-------------------------------------
Context c = new InitialContext();
ds = (DataSource) c.lookup("java:/DefaultDS");
conn = ds.getConnection();
getUser = conn.prepareStatement(GET_USER);
getUser.setString(1, login);
System.err.println(" %%%%%%%%%%%% Executing Query ... ");
System.err.flush();
ResultSet rs = getUser.executeQuery();
System.err.println(" %%%%%%%%%%%% Executing Query ... DONE ! ");
System.err.flush();
if(rs.next()) {
....
------------------------------
and the console says:
------------------------------
14:10:51,718 ERROR [STDERR] %%%%%%%%%%%% Executing Query ...
14:10:51,725 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping
14:10:51,729 INFO [TxConnectionManager$TxConnectionEventListener] throwable from unregister connection
java.lang.IllegalStateException: Trying to return an unknown connection1! org.jboss.resource.adapter.jdbc.WrappedConnection@11cf0b
at org.jboss.resource.connectionmanager.CachedConnectionManager.unregisterConnection(CachedConnectionManager.java:264)
at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener.connectionClosed(TxConnectionManager.java:550)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.closeHandle(BaseWrapperManagedConnection.java:287)
at org.jboss.resource.adapter.jdbc.WrappedConnection.close(WrappedConnection.java:127)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.resource.connectionmanager.CachedConnectionManager.closeAll(CachedConnectionManager.java:375)
at org.jboss.resource.connectionmanager.CachedConnectionManager.popMetaAwareObject(CachedConnectionManager.java:199)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:190)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:72)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:128)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:118)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:331)
at org.jboss.ejb.Container.invoke(Container.java:700)
at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:353)
at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:83)
at $Proxy52.getUser(Unknown Source)
at org.sourcery.mim.facades.userauthorization.UserAuthorizationEJBBean.checkPassword(UserAuthorizationEJBBean.java:40)

[adrian.brock]

You should close all connections and statements after
you have finished with them. This returns them to the pool.

Connection c = ds.getConnection();
try
{
}
finally
{
c.close();
}

And similar for statements.

Regards,
Adrian

[zielen2]

hi, this is the second developer in the team.

We would like to avoid closeing these connections. We have DAO object which is a stateless session bean. On create it opens a connection, and preperes the statements. On destroy, it closes them.

This way, the statements are compiled once, and the only thing you have to do on method invocation, is fill in the parameters, and execute the query.

Isn't there a possibility to do it this way with jboss?

michal

[adrian.brock]

The connections are not actually closed, they are
returned to the pool.
If you are really seeing a problem with the prepared
statement information being discarded, I would suggest
you get a better jdbc driver that understands connection
pooling.

Alternatively, you can enable "spec compliance" in the
cached connection manager (transaction-service.xml)
but this does not scale if you have many such queries.
The idea being you can keep a connection specifically
for the prepared statement.

Regards,
Adrian

[zielen2]

ok. thanks for help. the change in transaction-service.xml, solved the problem.

Now I've got a question. As I understood from what you wrote, the EJB developer, should not worry about pooling the connections, because jboss, does it for him (unless you change the thing in transaction-service.xml). Is there any mechanism for pooling prepared statements?
Or is it : you either assign a group of prepared statements to a connection, and you pool the bundle like we do, using stateless session beans, or you just pool connections, using the jboss mechanism?

michal

[adrian.brock]

We don't pool statements.
A statement belongs to the connection.
You may not use the same connection on each request
when pooling.

The caching of prepared statement info is left to the quality of
implementation of the underlying db or driver support.

Regards,
Adrian