11 Replies Latest reply on Jan 30, 2002 11:40 AM by johnnycannuk

    PreparedStatement & cache

    jmoliere

      Hi all,
      using Jboss 2.4.4 & Oracle 8i (8.1.7.3) via the thin driver (not the OCI one) I guess a clever problem with the preparedstatement caching policy ( I saw such a feature in the ClientConnection code I think).
      The problem is : using 3 preparedstatments in a code block ,
      only the first one is succesfully created because the 2 others are created using the same parameters (e.g FORWARD_ONLY ).

      Does someone encountered such problem ? Any trick or solution?

      Thanks
      Jerome

        • 1. Re: PreparedStatement & cache
          johnnycannuk

          This is a bug in the 2.4.4 implementation of the XAClientDatasource.java. Calling prepareStatement always returns a forward_only resultset, no matter what the parameters passed in. This is the fix:


          I submitted a "patch" consisting of a 1 minute cut-paste-edit compile to the XAClientConnection.java in the jbosspool part of the source.(see http://sourceforge.net/tracker/index.php?func=detail&aid=506673&group_id=22866&atid=376687)

          The change is this for those who need to duplicate it/fix their install -

          Fix for #506549 bug scrollable rs prob
          Here is the patch that fixes this:

          The old call to con.prepareStatement(sql,int,int)

          was implemented like this:

          public PreparedStatement prepareStatement(String sql,
          int resultSetType, int resultSetConcurrency) throws
          SQLException {
          return this.prepareStatement(sql);

          }


          it should be like so:

          public PreparedStatement prepareStatement(String sql,
          int resultSetType, int resultSetConcurrency) throws
          SQLException {
          if(con == null) throw new SQLException(CLOSED);
          try {
          return con.prepareStatement(sql,
          resultSetType, resultSetConcurrency);
          } catch(SQLException e) {
          setError(e);
          throw e;
          }

          }


          I have tested this out (although not with the
          testsuite) and it works well - seems like a simple
          oversight considering the implementations of
          prepareStatment() and prepareCall() around it were
          done correctly.

          Try it and let me know if it fixes your problem.
          Mike

          • 2. Re: PreparedStatement & cache
            johnnycannuk

            BTW, You will need to download the 2.4.4 source, make the change in XAClientDataSource.java and rebuild. You can then drop the new jbosspool.jar and jbosspool_jdbc.rar into any jboss-2.4.4/lib/ext and /deploy/lib/ directory, respectively, and fix the problem. Eg:

            jbosspool.jar -> <jboss-install>/lib/ext

            jbosspool_jdbc.rar -> <jboss-install>/deploy/lib

            This works for 2.4.4 but not for 2.4.3. You would need to repoeat this process with the 2.4.3 sources.


            Also, WHY THE HELL ISN"T PREVIEW WORKING? It took me 1/2 an hour to write this post because my stuff kept disappearing..but I digress

            Mike

            • 3. Re: PreparedStatement & cache
              jmoliere

              Yes I watched this class source, you 're right!!
              thanks for your answer, I will test when my download will be over (I have a very poor transfer rate today on sourceforge)
              cheers
              Jerome

              • 4. Re: PreparedStatement & cache
                jmoliere

                Hi mike (& all others)
                I patched jboss, recompiled, deployed OK!!!
                Now my prepared statements are quite good but each parameter bound to my query is not seen by Oracle driver (not all varaiables bound exception ). I've seen a post dealing with this problem but it gave me no clues !!!
                I must say that my query is very simple (something like:
                select field1 from my_table where field2=?

                field2 is a varchar2 (32) field , I try to make a pstmt.setString(1,my_new_value)...

                Any clue welcome
                Cheers
                Jerome

                • 5. Re: PreparedStatement & cache
                  johnnycannuk

                  Post your PreparedStatement query string and your setters...I've run into this problem with Oracle 8.1.7 when I have an extra '?' and don't assign a value to it in the setters.


                  Mike

                  • 6. Re: PreparedStatement & cache
                    jmoliere

                    > Post your PreparedStatement query string and your
                    > setters...I've run into this problem with Oracle
                    > 8.1.7 when I have an extra '?' and don't assign a
                    > value to it in the setters.
                    >

                    String qry = "SELECT OBJECT_PROPERTY FROM LEXICON_OBJECT_TBL WHERE OBJECT_ID =?";
                    PreparedStatement stmt = dataSourceConnection.prepareStatement( qry,
                    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

                    stmt.setString(1,aLexiconId);
                    ResultSet rset = stmt.executeQuery(qry);


                    This is quite a hello statement but it's very usefull for me!!! I can't see what's wrong with this query (it works well if my connection is fetched directly from the thin Oracle driver).
                    Jerome

                    • 7. Re: PreparedStatement & cache
                      johnnycannuk

                      What does the stack trace look like? Post the Error mesages...

                      We'll keep trying.

                      I use a very similar query and it works great....

                      Mike

                      • 8. Re: PreparedStatement & cache
                        jmoliere

                        Hi all (mike!!),
                        these are my traces still the first call from my code:
                        the problem must be something like : how to get the statements from the Jboss spool / what is the main difference with a direct call to the Oracle driver:

                        java.sql.SQLException: ORA-01008: not all variables bound
                        [12:22:05,536,Default] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
                        [12:22:05,537,Default]
                        [12:22:05,537,Default] at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
                        [12:22:05,538,Default]
                        [12:22:05,538,Default] at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
                        [12:22:05,539,Default]
                        [12:22:05,539,Default] at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
                        [12:22:05,540,Default]
                        [12:22:05,540,Default] at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
                        [12:22:05,541,Default]
                        [12:22:05,541,Default] at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1819)
                        [12:22:05,542,Default]
                        [12:22:05,542,Default] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2015)
                        [12:22:05,543,Default]
                        [12:22:05,543,Default] at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:571)
                        [12:22:05,544,Default]
                        [12:22:05,544,Default] at com.viveo.lmt.server.web.servlets.shared.MergedLexicon.init(MergedLexicon.java:62)

                        String qry = "SELECT OBJECT_PROPERTY FROM LEXICON_OBJECT_TBL WHERE OBJECT_ID =?";
                        PreparedStatement stmt = dataSourceConnection.prepareStatement( qry,
                        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                        System.err.println("Lexicon id size ( PreparedStatement)= " + aLexiconId.length());
                        stmt.setString(1,aLexiconId);
                        ResultSet rset = stmt.executeQuery(qry);//this is the line appearing in the bottom of the stack trace....

                        Cheers
                        Jerome

                        • 9. Re: PreparedStatement & cache
                          johnnycannuk

                          Strange...What is the value of aLexiconId?

                          try commenting out

                          stmt.setString(1,aLexiconId);

                          and hardcoding the value into the string qry and running it. (still as a prepared statement and all). See if you get a result back. You may also whant to restart jboss. Your code here should work, but I have found with jboss that if you hot deploy a new bean without undeploying or re-starting, your implementation changes aren't seen by the system because the old ones are still in memory, even if the old jar has been replaced.

                          Any way if the above works, change it back to the way you have it now and restart and see if it works.

                          Mike

                          • 10. Re: PreparedStatement & cache
                            jmoliere

                            > Strange...What is the value of aLexiconId?
                            >
                            this is one instance of the PK of the reflected table,
                            so this is a 32 chars string (I'm using an UUid Session Bean for generation of the PKs...)
                            this is the value used for a query:
                            b329d153c0a802680076cbf724ef83cc
                            as you can see there 's nothing wrong (I hope !!), just letters & digital chars, no special chars as (,' and so on..
                            > try commenting out
                            >
                            > stmt.setString(1,aLexiconId);
                            >

                            > and hardcoding the value into the string qry and
                            > running it. (still as a prepared statement and all).
                            yes I 've already attempted it, it works fine... (I've just changed my code to go back to this state & it still works).
                            But there 's no sense using prepared statements with fixed sql queries....

                            > See if you get a result back. You may also whant to
                            > restart jboss. Your code here should work, but I have
                            > found with jboss that if you hot deploy a new bean
                            > without undeploying or re-starting, your
                            > implementation changes aren't seen by the system
                            > because the old ones are still in memory, even if the
                            > old jar has been replaced.
                            >
                            I 've already attempted to start down then up several times...
                            > Any way if the above works, change it back to the way
                            > you have it now and restart and see if it works.

                            Thanks
                            Jerome

                            • 11. Re: PreparedStatement & cache
                              johnnycannuk

                              Sorry buddy, I'm stumped...I've never run into this the way you describe...as I stated, I have always had an extra '?' in the query to cause this, making it easy to fix...

                              Good luck

                              Mike