Problems with JBoss 3.0.5 connection pooling using Informix

[hveit]

Hello,

we have experienced a problem using JBoss connection pooling with Informix JDBC driver JDBC 2.21 JC3. When using non-TX connections from two or more parallel clients, we occasionally get SQLException "Cursor not open." while next'ing through a result set. What we do is retrieving a connection from the pool via

javax.sql.DataSource ds = (javax.sql.DataSource)new InitialContext().lookup("myDS");
connection = ds.getConnection();

from a stateful session bean, where we store a reference to it, so that all successive calls to that session bean use this connection until it gets removed via ejbRemove, where we close the connection.
With this connection we do a SQL SELECT and also store the retrieved ResultSet object in the same stateful session bean. Each successive call to that stateful session bean then gets the next n rows from the stored result set until all rows are retrieved and the result set and connection get released again.
If we do not use JBoss connection pooling (by retieving a connection directly from the driver) everything works fine, but slower.
Here is what we define in our "informix-service.xml":

<?xml version="1.0" encoding="UTF-8"?>


<depends optional-attribute-name="ManagedConnectionFactoryName">

jdbc/HyperdocEJBDSTX


<config-property name="ConnectionURL" type="java.lang.String">jdbc:informix-sqli://139.2.146.22:1526/hyp431:INFORMIXSERVER=INF_Bender</config-property>
<config-property name="DriverClass" type="java.lang.String">com.informix.jdbc.IfxDriver</config-property>
<config-property name="UserName" type="java.lang.String">informix</config-property>
<config-property name="Password" type="java.lang.String">informix</config-property>


<depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper


<depends optional-attribute-name="ManagedConnectionPool">

0
50
5000
15
ByContainer


<depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager
<depends optional-attribute-name="JaasSecurityManagerService">jboss.security:service=JaasSecurityManager
java:/TransactionManager
jboss.jca:service=RARDeployer



<!-- NO TX -->



<depends optional-attribute-name="ManagedConnectionFactoryName">

jdbc/HyperdocEJBDS


<config-property name="ConnectionURL" type="java.lang.String">jdbc:informix-sqli://139.2.146.22:1526/hyp431:INFORMIXSERVER=INF_Bender</config-property>
<config-property name="DriverClass" type="java.lang.String">com.informix.jdbc.IfxDriver</config-property>
<config-property name="UserName" type="java.lang.String">informix</config-property>
<config-property name="Password" type="java.lang.String">informix</config-property>


<depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper


<depends optional-attribute-name="ManagedConnectionPool">

0
50
5000
15
ByContainer


<depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager
<depends optional-attribute-name="JaasSecurityManagerService">jboss.security:service=JaasSecurityManager
jboss.jca:service=RARDeployer



In addition to this, if we use JBoss with Oracle DBMS, its also no problem. We already tried setting different ISOLATION LEVELS, but with not much success.
When we try to set AutoCommit on our non-TX connection (what we normally not do, but only for tests)it gets worse: If we set AutoCommit to false after retrieving the connection from the pool and test its AutoCommit state everytime we use that connection in our stateful session bean, we can see that it gets reset to true, just before the SQLException "Cursor not open" comes up. What does that mean?
Maybe the connections get mixed up somehow?
Can someone please help.

Regards,

Holger Veit

[zsoltvincze]

Sorry, don't have the answer for you but would like to learn the details how you could manage to use oracle with jboss.

[davidjencks]

I don't recommend caching Connection objects over method calls. I don't think caching result sets is guaranteed to work, and I think it's a terrible idea.

If you do hold a connection between method calls, it may not be connected to the same physical connection on each call. This is likely to cause the problems with the result set you are seeing. To understand why this happens, consider that with container managed security, the db user may be different on each ejb call, thus requiring different physical connections.

You may be able to make this work by marking the datasource non-sharable in the ejb-jar. This may keep the same connection associated until you close the handle. I still don't recommend it.

[hveit]

Hello David,

thanks for your reply. If I understand you correctly, you say that caching the connection even in a stateful session bean is a bad idea.
Why we did this is because we need to handle very large result sets, but with an acceptable answering time for the client. Do you have a better idea to achieve this than to cache the result set?

Thanks in advance

Holger Veit

[hveit]

Hello,

to get JBoss 3.0.5 to work with Oracle you need to have a file oracle-service.xml file in your /deploy directory instead of the informix-service.xml file. Its content looks very similiar to that of the informix-service.xml I have posted, except that the driver class is that of your Oracle driver and the connection URL is suitable for your Oracle instance. You also have to put the Oracle driver in your classpath. We do that in our JBoss run.bat file.

Regards

Holger Veit

[hveit]

Hello David,

in your answer you talk about setting the datasource to non-sharable in the ejb-jar to get around the problem. I wanted to give it a try and have searched for a while but did not find how to do this. Can you please give me a hint.

Thanks in advance

Holger Veit