3 Replies Latest reply on Nov 8, 2013 11:21 AM by wdfink

    Oracle JDBC Extensions, problem

    altar

      Hi,

       

      I have a problem using JDBC Oracle Extensions :

       

      I need to use them because I need to work with complex Data Object like Types, CLOBs, etc...

       

      The problem is that each service call consumes a new connection from the pool, it appears that it creates a new connection every time I invoke a service, doesn't reuse or doesn't close the connections

       

      However if I use only JDBC Standard all works fine, the connections are closed and reused from the pool, but of course I need to use extra code to deal with complex data

       

      What is causing this ?! why when I use OracleConnection and other oracle extensions doesn't reuse connections from the pool and creates new ones instead ?! any idea ?

       

      thanks in advance

        • 1. Re: Oracle JDBC Extensions, problem
          wdfink

          You can use the normal lookup for a datasource and get the underlying connection. But you need to close the original retrieved one otherwise JBoss can not handle the connection correctly.

           

          Please share your code that we can understand what you are doing and what the issue is.

          1 of 1 people found this helpful
          • 2. Re: Oracle JDBC Extensions, problem
            altar

            Yes you right and that was my problem, after a long research I have found the solution

             

            I was closing the underlying connection and never the native connection which was always active, meaning that the pool was exhausted very quickly

             

            to help people with the same problem

             

            add this method to retrieve the underlying OracleConnection :

             

             

                import java.lang.reflect.Method;

             

                public static OracleConnection getOracleConnection(Connection conFromPool) throws SQLException {

                    try {

                        Class[] parms = null;

             

                        Method method = (conFromPool.getClass()).getMethod("getUnderlyingConnection", parms);

             

                        return (OracleConnection) method.invoke(conFromPool, parms);

             

                    } catch (InvocationTargetException ite) {

             

                        throw new SQLException(ite.getMessage());

             

                    } catch (Exception e) {

                        throw new SQLException(e.getMessage());

             

                    }

             

                }

             

             

            in the services that needs to use Oracle Data Objects :

             

                    Connection conn = null;

                    OracleConnection oraConn=null;

             

                    OracleResultSet rset = null;

                    OracleCallableStatement stmt = null;

             

                    conn = this.getConnection(ctx);

                    oraConn = getOracleConnection(conn);

             

            // do your work using oraConn

             

            // close the native connection

                conn.close();

                 conn=null;

            • 3. Re: Oracle JDBC Extensions, problem
              wdfink

              That's correct, from a performance perspective you might not use reflection but cast to the JBoss connection wrapper.

               

              Also you should mark the helpful/correct posts and set the thread to answered, it makes it more easy for others to find the solution