5 Replies Latest reply on Aug 25, 2003 10:30 AM by rgk

    How do get Oracle Callable Statement?

    joshlam

      Hi,

      I am a newbie with JBoss 3.2.1 and I am using it to connect to Oracle 9.2. I have the classess12.jar in the /server/default/lib. I am using Windows XP.

      My basic configuration looks ok as I am able to execute regular SQL statements. However, I am having some problems with OracleCallableStatement.

      The way I initialize and get a connection is as follow:

      InitialContext naming = new InitialContext();
      DataSource dataSource = (DataSource)naming.lookup("java:/OracleDS");
      Connection dbConn = dataSource.getConnection();

      Now I want to get a use a CallableStatement:

      CallableStatement stmt = dbConn.prepareCall(GET_STUFF);
      stmt.setString(2, "51294691");
      stmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
      stmt.executeQuery();

      In trying to get the Oracle cursor back, I got a ClassCastException with the following statement:

      ResultSet rset = ((oracle.jdbc.driver.OracleCallableStatement) stmt).getCursor(1); // ** getting ClassCast Exception

      I later wrote some code to see what the class stmt is and found out that its: org.jboss.resource.adapter.jdbc.WrappedCallableStatement

      What do I need to do so that JBoss would use the org.jboss.resource.adapter.jdbc.WrappedCallableStatement instead of its WrappedCallableStatement?

      Thanks.

      --
      Josh

        • 1. Re: How do get Oracle Callable Statement?
          jonlee

          WrappedCallableStatement does not allow access to the underlying CallableStatement object. You would need to get the underlying Oracle connection from the WrappedConnection object - there is a recent post on getting the Oracle specific connection. You would then use this OracleConnection object to create your OracleCallableStatement. There may be transactional ramifications from doing this. YMMV. Use with care.

          • 2. Re: How do get Oracle Callable Statement?
            joshlam

            Thanks. That did the trick.

            Where can I find a discussion regarding the WrappedConnection and the transactional ramifications of using the underlying connection?

            I bought the jBoss 3.2.1 book but could not find it there.

            • 3. Re: How do get Oracle Callable Statement?
              jonlee

              There is no specific discussion on the use of the underlying connection. The wrapped connection is used since EJBs, particularly entity beans work with datasource connections and are themselves transactional.

              Say for example you have an EJB call another EJB and both work with writing datasource data. The actions they perform are designed intentionally to be atomic - if either one fails, nothing is committed. If a bean method fails in this EJB transactional chain, the datasource transactions spread throughout that chain are rolled back. The wrapped connection and the wrapped statements, et al perform underlying work to ensure the atomicity.

              By using the underlying connection directly, you bypass these controls.

              • 4. Re: How do get Oracle Callable Statement?
                rgk

                Interesting.
                We implemented this same solution when upgrading from Jboss 2.4 to 3.1 - but have been worried about the use of the underlying connection.

                Is there a recommend way to call getCursor without using the underlying connection in jboss 3?

                • 5. Re: How do get Oracle Callable Statement?
                  rgk