5 Replies Latest reply on Aug 24, 2002 6:26 PM by Frederic Soulier

    JBoss3.0.1, Informix 7.31, Prepared statement and Blob

    Frederic Soulier Newbie

      Hi,

      Configuration
      -------------
      JDK 1.3.1_04 (Linux or W2K)
      JBoss3.01
      Informix 7.31
      JDBC Driver (com.informix.jdbc.IfxDriver)

      We are getting this "No ManagedConnections Available" msg after our application runs for a while.
      I have traced the pbm down to a PreparedStatement which updates a Blob.
      I did source trace the DAO and could see the Prepared Statement and the connection being closed... Yet on the Informix Server Admin I can see the connection is still there with the last SQL statement executed being the one from the DAO. Everytime this method is invoked we end up with one more connection opened and not close for no obvious reason.
      The datasource has been configured using the informix-service.xml example in /docs/examples/jca and the managed connection pool is set to min=2 max=50

      Note: we do not have this problem using Sun j2ee 1.3.1_02


      Here is the method in our DAO:

      void saveBasket(BasketContext basketContext)
      throws DAOException
      {
      final String METHOD_NAME = "saveBasket";

      PreparedStatement pstmt = null;


      try
      {
      conn = getConnection();

      String sql = "update tb_basket set BASKET_CONTEXT = ?, " +
      "last_modified_by = '" + CLASS_NAME + "', " +
      "last_modified_timestamp = CURRENT " +
      "where basket_id = '" + basketContext.getBasketId().getId() + "'";


      pstmt = conn.prepareStatement(sql);


      ByteArrayOutputStream out = new ByteArrayOutputStream();
      ObjectOutputStream objStream = new ObjectOutputStream(out);

      objStream.writeObject(basketContext);

      ByteArrayInputStream in = new ByteArrayInputStream(out.toByteArray());
      objStream.flush();

      pstmt.setBinaryStream(1, in, in.available());

      try
      {
      pstmt.executeUpdate();
      objStream.close();
      out.close();
      }
      catch (SQLException e)
      {
      String err = "Failed to update table; nested exception is: \n" +e.getLocalizedMessage();
      throw new DAOException(err);
      }

      }
      catch (Exception e)
      {
      String err = "Unexpected error!; nested exception is: \n" +e.getLocalizedMessage();
      throw new DAOException(err);
      }
      finally
      {

      try
      {
      if (pstmt != null)
      {
      pstmt.close();
      pstmt = null;
      }

      if (conn != null)
      {
      conn.close();
      conn = null;
      }
      }
      catch (SQLException e)
      {
      String err = " Cannot close prepared statement or connection!; nested exception is: \n" +e.getLocalizedMessage();
      throw new DAOException(err);
      }
      }
      }

      Thanks for any help.
      /Fred

        • 1. Re: JBoss3.0.1, Informix 7.31, Prepared statement and Blob
          Frederic Soulier Newbie

          We've tried the following Connection URLs but still no success.

          <config-property name="ConnectionURL" type="java.lang.String">jdbc:informix-sqli://192.168.20.105:12100/ebasket:INFORMIXSERVER=lb_db1</config-property>
          <config-property name="DriverClass" type="java.lang.String">com.informix.jdbc.IfxDriver</config-property>

          <config-property name="ConnectionURL" type="java.lang.String">jdbc:informix-sqli://192.168.20.105:12100/ebasket:INFORMIXSERVER=lb_db1;IFX_AUTOFREE=true</config-property>
          <config-property name="DriverClass" type="java.lang.String">com.informix.jdbc.IfxDriver</config-property>

          • 2. Re: JBoss3.0.1, Informix 7.31, Prepared statement and Blob
            David Jencks Master

            Why do you think the problem comes from this blob use?

            I'd suspect that your security and pooling parameters are set up so that the pool and jca adapter don't recognize that your connections can be reused.

            What is the criteria attribute for your pool and how do you supply the user/pw?

            • 3. Re: JBoss3.0.1, Informix 7.31, Prepared statement and Blob
              Frederic Soulier Newbie

              Hi David,

              Well, we have several other DataSources setup to get connections from and we have no pbm with them, I mean we can get connections, statements, resultsets then close everything and all is fine. It's only on this particular combination of Prepared Statement and Blob that we get this connection which is never released. I cannot be sure it's the pbm but everything points at this particular sequence of actions.

              Again, we do not have this pbm using Sun j2ee 1.3.1_02.


              This is how the DataSource is defined in informix-service.xml. We have 3 other DataSources defined the same way and we do not have pbm we getting/releasing connections with these DataSources.

              <!-- eBasket Datasource #3 -->


              <!-- Include a login module configuration named InformixDbRealm.
              Update your login-conf.xml, here is an example for a
              ConfiguredIdentityLoginModule:

              <application-policy name = "InformixDbRealm">

              <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule" flag = "required">
              <module-option name = "principal">yourprincipal</module-option>
              <module-option name = "userName">yourusername</module-option>
              <module-option name = "password">yourpassword</module-option>
              <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=Informix_EBASKET_DS3</module-option>
              </login-module>

              </application-policy>

              NOTE: the application-policy name attribute must match SecurityDomainJndiName, and the
              module-option name = "managedConnectionFactoryName"
              must match the object name of the ConnectionManager you are configuring here.
              -->
              <!--uncomment out this line if you are using the Informix DbRealm above
              InformixDbRealm
              -->

              <depends optional-attribute-name="ManagedConnectionFactoryName">
              <!--embedded mbean-->


              jdbc/BasketDB



              <config-property name="ConnectionURL" type="java.lang.String">jdbc:informix-sqli://192.168.20.105:12100/ebasket:INFORMIXSERVER=lb_db1</config-property>
              <config-property name="DriverClass" type="java.lang.String">com.informix.jdbc.IfxDriver</config-property>
              <!--set these only if you want only default logins, not through JAAS -->
              <config-property name="UserName" type="java.lang.String">XXXXXX</config-property>
              <config-property name="Password" type="java.lang.String">YYYYYY</config-property>


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


              <depends optional-attribute-name="ManagedConnectionPool">
              <!--embedded mbean-->


              2
              50
              5000
              15
              <!--criteria indicates if Subject (from security domain) or app supplied
              parameters (such as from getConnection(user, pw)) are used to distinguish
              connections in the pool. Choices are
              ByContainerAndApplication (use both),
              ByContainer (use Subject),
              ByApplication (use app supplied params only),
              ByNothing (all connections are equivalent, usually if adapter supports
              reauthentication)-->
              ByContainer


              <depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager

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

              java:/TransactionManager
              <!--make the rar deploy! hack till better deployment-->
              jboss.jca:service=RARDeployer


              /Fred

              • 4. Re: JBoss3.0.1, Informix 7.31, Prepared statement and Blob
                David Jencks Master

                Well, that looks pretty convincing. Just for fun you might try

                ByNothing

                I don't think this should make any difference but it's worth a try.

                Are any exceptions getting thrown?

                If this doesn't work I'd try setting the max size to perhaps 1 or 2 and turning tracing in org.jboss.resource up to trace and seeing if you can see what is happening. Put some logging in your app so you can see the work happening and both before and after connection.close. If you send me the relevant part of the log I'll try to take a look.

                davidjencks@directvinternet.com

                • 5. Re: JBoss3.0.1, Informix 7.31, Prepared statement and Blob
                  Frederic Soulier Newbie

                  Thanks David.
                  Ultimately I traced it down to bad coding and a connection obtained in a constructor which was never closed...
                  It's all working beautifully now.
                  /Fred