Обсуждение: Using CopyManager with pooled JCA connection

I'm trying to use the CopyManager API inside a JEE container (Geronimo) with DB connections managed by JNDI.

CopyManager can be directly instantiated (new CopyManager(BaseConnection c)) or retrieved from a PGConnection instance.
However, the connection I'm given is a wrapper (org.tranql.connector.ManagedConnectionHandle) where the real PostgreSQL
connectionis wrapped several layers deep.  There doesn't seem to be an API with which to retrieve the a PGConnection or
BaseConnection,or something that can be cast to one of these. 

Anyone have a suggestion as to how to proceed?

> Try getPhysicalConnection()[1]. It's defined as returning an Object but internally it looks like it has the actual
connection(in this case a java.sql.Connection). You'll probably just have to cast it to a java.sql.Connection. 
>
> [1]:
http://grepcode.com/file/repo1.maven.org/maven2/org.tranql/tranql-connector/1.6/org/tranql/connector/ManagedConnectionHandle.java/#32

Thanks for the link. I tried this but unfortunately it returns a proxy object:

    import org.tranql.connector.jdbc.ConnectionHandle;
    ...
    private BaseConnection getBaseConnection(Connection con)
    {
        BaseConnection temp = null;
        if (con instanceof ConnectionHandle)
            temp = (BaseConnection) ((ConnectionHandle) con).getAssociation().getPhysicalConnection(); // << exception
here
        else
            temp = (BaseConnection) con;
        return temp;
    }


java.lang.ClassCastException: $Proxy46 cannot be cast to org.postgresql.core.BaseConnection

>From: Sehrope Sarkuni [mailto:sehrope@jackdb.com]
>Sent: Thursday, September 12, 2013 2:01 PM
>One other trick is that if you can get anything that implements java.sql.Connection you can usually get to the "raw"
connectionby first getting the DB meta data object and then getting the original connection from it.  
Spring has a decent explanation of in SimpleNativeJdbcExtractor[1]. Basically you do conn.getMetaData().getConnection()
>If that doesn't work try to programmatically dump the interfaces implemented by the value returned by
getPhysicalConnection()and see if any of those have an "unwrap" method on them. It's possible that the "physical
connection"is itself a wrapper of the actual connection. I know some connection pools do that. 

Looks like we're following the same path.  I almost had this working:

    private PGConnection getPGConnection(Connection con)
    {
        PGConnection temp = null;

        if (con instanceof ConnectionHandle)
        {
            Object pc = ((ConnectionHandle) con).getAssociation().getPhysicalConnection();
            temp = (PGConnection) pc;
        }
        else
            temp = (PGConnection) con;

        return temp;
    }

But unfortunately, the two connection objects (source and target databases) are loaded by different classloaders, so
thestatement 

    If (con instanceof ConnectionHandle)

Works for one but fails for the second.  I will try your getMetadata() suggestion

Tried

        DatabaseMetaData md = con.getMetaData();
        Connection       rc = md.getConnection();

 but that just returns the same ConnectionHandle object.  Looks like I'm at a dead end because for two different
connectionsI get instances of the same classes loaded by two different classloaders, which makes them completely
incompatible.Sigh... 

On Thu, 12 Sep 2013, Jim Garrison wrote:

> >From: Sehrope Sarkuni [mailto:sehrope@jackdb.com] Sent: Thursday,
> >September 12, 2013 2:01 PM One other trick is that if you can get
> >anything that implements java.sql.Connection you can usually get to the
> >"raw" connection by first getting the DB meta data object and then
> >getting the original connection from it.
>
> Spring has a decent explanation of in SimpleNativeJdbcExtractor[1].
> Basically you do conn.getMetaData().getConnection()
>

This seems like a dangerous thing to rely on.  Not proxying
DatabaseMetaData seems like a bug to me.  Yes, the PG driver doesn't do it
currently, but if it did and broke user code that relied on it not
proxying, I wouldn't have a whole lot of sympathy for the user.

Kris Jurka

I solved this by writing a wrapper around the Copy API and Connection that locates and invokes the Copy API via
reflection.

This allows me to retrieve the Copy API instance appropriate for both the source and destination database connections
whenthose connection objects came from different class loaders. 

This is fairly specific to my situation, but if anybody is interested I can share the code.