4 Replies Latest reply on Oct 19, 2004 12:25 AM by Adrian Brock

    Implicit Statement caching

    dsldsl Newbie

      I have searched the forums and found some info, but not really an answer. I am trying to understand how to configure my system for Implicit Statement Caching for JDBC. I am using:

      Oracle 9.2.0.3
      newest Oracle JDBC drivers (ojdbc14.jar)
      JBoss 3.2.5
      JSDK 1.4.2_4

      I am using CallableStatements. (Is this possible? Should be since CallableStatement implements PreparedStatement)

      First of all, do I want to let JBoss do the caching or let the Oracle drivers do the caching? (i.e. handle it on the wrapped connection level or the underlying connection level.) I think I have tried both but to no avail. What I have tried:

      - oracle-ds.xml: <prepared-statement-cache-size>20</prepared-statement-cache-size>
      this i think is at the JBoss level. It seems to have no effect, but I am not positive.

      - Trying to set it on the underlying Oracle drivers:

      WrapperDataSource ds = (WrapperDataSource) ic.lookup(jndi);
      WrappedConnection conn = ds.getConnection();
      OracleConnection oconn = (OracleConnection)(((WrappedConnection)conn).getUnderlyingConnection());
      OracleCallableStatement cst = (OracleCallableStatement) oconn.prepareCall("SELECT 1 FROM DUAL");
      
      // This is FALSE
      System.out.println(oconn.getImplicitCachingEnabled());
      oconn.setImplicitCachingEnabled(true);
      // This is still FALSE
      System.out.println(oconn.getImplicitCachingEnabled());
      
      // This is -1
      System.out.println(oconn.getStatementCacheSize());
      
      // This is NEW
      System.out.println(cst.creationState());
      cst.close();
      cst = (OracleCallableStatement) oconn.prepareCall("SELECT 1 FROM DUAL");
      // This is still NEW
      System.out.println(cst.creationState());
      cst.close();
      



      No luck. Even with the oracle-ds.xml addition for cache size, the java code above says -1.

      Some direction would be much appreciated.


        • 1. Re: Implicit Statement caching
          Adrian Brock Master

          Callable statements are not cached. Perhaps you would care to provide a patch?

          • 2. Re: Implicit Statement caching
            dsldsl Newbie

            That could be fun. =) I'll take a look through CVS -- who do I talk to about that?

            But let me get back to my question first... obviously the JBoss caching implementation has to be independant of the DB driver caching implementation to work with all DBs... so JBoss must do its own caching separate from the DB driver caching.

            So if i know I am working with Oracle drivers - meaning OracleDataSource and OracleCallableStatement - is seems like it would be pretty efficient to use the Oracle driver's caching instead of doing it at the JBoss level.

            So how can I config my JBoss data source to allow me access to that? If you notice in my code I get the underlying OracleConnection and an OracleCallableStatement. How can I get an OracleDataSource?

            I'm sure I'll actually get my question answered as I look through the code, but if you can give me some conceptual guidance that would be great.

            • 3. Re: Implicit Statement caching
              dsldsl Newbie

              Also... I can track the caching of OracleCallableStatements with the .creationState() method, and I can call the methods .getImplicitCachingEnabled() and .getStatementCacheSize() on an OracleConnection to see that Oracle caching is not on.

              How can I check this on the Wrapped connections? Say I was using WrappedPreparedStatement objects... how would I do it then?

              • 4. Re: Implicit Statement caching
                Adrian Brock Master

                Using the OracleDataSource would make no sense since the pooling would not
                take into account the JCA considerations (transactions/security).

                There is no such exposure of those statistics, again provide a patch if you have an
                interest.
                The WrappedConnection doesn't even know about the cache since that it is a transient
                object that just delegates to the ManagedConnection.

                If you want to plugin vendor specific behaviour, it should be done via configuration
                on the ManagedConnectionFactory. e.g. the check valid connection processing

                Patches can be posted here:
                http://sourceforge.net/tracker/?atid=376687&group_id=22866&func=browse
                JCA Development is discussed here (but that does not include user help):
                http://www.jboss.org/index.html?module=bb&op=viewforum&f=165
                The patch should include tests in the testsuite.

                If the patch is any good you will get cvs r/w
                http://www.jboss.org/wiki/Wiki.jsp?page=HowDoIGetInvolvedInJBoss

                A word of advice. Don't try to impress, just keep the code simple and to the point.