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

[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.

[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;

[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