5 Replies Latest reply on Jun 1, 2012 5:24 AM by matt__b

    Problem with prepared-statement-cache-size datasource config

    matt__b

      Hi,

       

      We are facing an issue similar to this old unanswered one:

      https://community.jboss.org/message/192271

       

      We are using jboss eap 5.1.1 and jdbc oracle driver 11.1.0.7

       

      When we try to activate prepared statement cache by setting the prepared-statement-cache-size parameter to 100 as follows :

      <local-tx-datasource>

                          <jndi-name>jdbc/****</jndi-name>

                          <use-java-context>false</use-java-context>

                          <connection-url>jdbc:oracle:thin:@****</connection-url>

                          <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>

                          <user-name>****</user-name>

                          <password>****</password>

                          <min-pool-size>2</min-pool-size>

                          <max-pool-size>30</max-pool-size>

                          <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>

                          <prepared-statement-cache-size>100</prepared-statement-cache-size>

              <query-timeout>20</query-timeout>

              <metadata>

                                    <type-mapping>Oracle9i</type-mapping>

                          </metadata>

      </local-tx-datasource>

       

      And we are getting this SQL exception :

      java.sql.SQLException : Instruction fermée

                at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)

                at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)

                at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)

                at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)

                at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)

                at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)

                at oracle.jdbc.driver.OracleClosedStatement.setString(OracleClosedStatement.java:479)

                at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:278)

       

      If we reset the prepared-statement-cache-size param to its default value (0) everything works fine again...

       

      Could anyone shed some lights on this issue and how to solve/debug it ?

       

      Many thanks in advance,

       

      --Matt

        • 1. Re: Problem with prepared-statement-cache-size datasource config
          wdfink

          Can you a bit more verbose?

          When did it happen?

          Do you use SQL direct or using CMP or BMP entities?

          Also post a bit around the Exception, I can't understand 'Instruction fermée' (sounds like connection closed) but maybe it helps if you retest with english messages it might be a confusing translation.

          • 2. Re: Problem with prepared-statement-cache-size datasource config
            matt__b

            Thanks for your answer.

             

            The exception occurs when calling the setString method on an OracleCallableStatement from a connexion accessed through an EntityManager.

            Here is the code :

             

            OracleCallableStatement statement = JDBCUtil.getOracleStatement(connexion.prepareCall(storedProc.toString()));

                      try {

                          statement.registerOutParameter(1, Types.NUMERIC);

                          statement.registerOutParameter(2, Types.VARCHAR);

                          statement.setString(3, userData.getApplicationCode()); --> raise the following exception (...in english this time ! ;-)

             

             

            java.sql.SQLException : Closed Statement

                      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)

                      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)

                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)

                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)

                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)

                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)

                      at oracle.jdbc.driver.OracleClosedStatement.setString(OracleClosedStatement.java:479)

                      at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:278)

             

            While debugging the code, the first request with the statement works well but the following ones (with, I guess, the statement retrieved from the cache) fail with the exception.

            • 3. Re: Problem with prepared-statement-cache-size datasource config
              wdfink

              I have no idea

              As the statement is accessible a line before ...

              But how do you close the connection in the first run? With the access to the OracleStatement? Maybe this will close the connection and give it back to pool....

              • 4. Re: Problem with prepared-statement-cache-size datasource config
                matt__b

                I have been doing some more tests and it seems indeed related to the way the connection is handled.

                In this case the connection is retrieved from an EntityManager with a transaction type of RESOURCE_LOCAL, I guess that the connection is closed when we close the entityManager.

                I will need to get a better understanding of the relationship between the connection and preparedStatement caching to find the source of the problem.

                The information I am struggling to find is what are the condition/restriction to use the prepared-statement-cache-size configuration parameter in the datasource...

                 

                I will post the details if I find something...

                 

                Cheers

                • 5. Re: Problem with prepared-statement-cache-size datasource config
                  matt__b

                  Ok, I eventually got the solution so I post it here in case it can help someone :

                  The issue was that we were closing the underlying OracleCallableStatement and not the jboss WrappedCallableStatement itself ! ...D'oh !