Problems with JBoss 3.0.5 connection pooling using Informix
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
