1 Reply Latest reply on Nov 11, 2006 10:57 AM by scott schenkein

    Problems with JBoss 3.0.5 connection pooling using Informix

    Holger Veit Newbie

      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