COPY FROM and C3PO connection pool in Postgres

I have the follow code in my JAVA program that allows me to copy data from a file into my Postgres database:

Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:####/myDb", 
                                                   "myuser", "mypassword"); 
CopyManager cm = new CopyManager((BaseConnection) con);
cm.copyIn("COPY prices FROM STDIN WITH DELIMITER AS ','", 
             new BufferedReader(new FileReader(filepath)), buffersize);

This code works fine, but I would like to use a connection pool to manage my connections, as I have this code running for numerous files. So I used C3P0.

public static final ComboPooledDataSource cpds = new ComboPooledDataSource();

public class MyPooledConnection {
MyPooledConnection() throws PropertyVetoException {
    cpds.setDriverClass("org.postgresql.Driver"); 
    cpds.setJdbcUrl("jdbc:postgresql://localhost:5432/myStockDatabase"); 
    cpds.setUser("myUserName"); 
    cpds.setPassword("myPassword"); 
    cpds.setInitialPoolSize(4);
    cpds.setMinPoolSize(4);
    cpds.setMaxIdleTime(30);
    cpds.setMaxPoolSize(MAX_CONNECTIONS);
}

public static Connection getConnection() {
    return cpds.getConnection();
}
}

However, when i get a connection from the connection pool above and try to use it with CopyManager like in the example below, the code doesn’t work

Connection pooled_con = MyPooledConnection.getConnection();
CopyManager cm = new CopyManager((BaseConnection) pooled_con);
cm.copyIn("COPY prices FROM STDIN WITH DELIMITER AS ','", 
             new BufferedReader(new FileReader(filepath)), buffersize);

I’m guessing the issue is with the connection, but i can’t seem to figure out what about it is different. I’ve tried catching the error with SQLException and IOException, but it doesn’t catch either. Has anyone encountered this?

—-UPDATED—-

Thanks to a_horse_with_no_name the guidance on this. The following code worked for me

// Cast the connection as a proxy connection
C3P0ProxyConnection proxycon = (C3P0ProxyConnection)cpds.getConnection();
try {

    // Pass the getCopyAPI (from PGConnection) to a method
    Method m = PGConnection.class.getMethod("getCopyAPI", new Class[]{});
    Object[] arg = new Object[] {};

    // Call rawConnectionOperation, passing the method, the raw_connection, 
    // and method parameters, and then cast as CopyManager
    CopyManager cm = (CopyManager) proxycon.rawConnectionOperation(m,
                                         C3P0ProxyConnection.RAW_CONNECTION,arg);
    cm.copyIn("COPY prices FROM STDIN WITH DELIMITER AS ','", new BufferedReader(new 
                                                FileReader(filepath)), buffersize);
} catch (NoSuchMethodException | IllegalAccessException 
                        | IllegalArgumentException | InvocationTargetException e) {
    // Deal with errors here
}

Answer

The pool does not give you the “native” connection, it always hands out a proxy object:

From the manual:

C3P0 wraps these Objects behind a proxies, so you cannot cast C3P0-returned Connections or Statements to the vendor-specific implementation classes

You probably can’t use the CopyManager using C3P0. I’m not sure, but maybe you can use the workarounds described here: http://www.mchange.com/projects/c3p0/#raw_connection_ops

If that does not work you might want to use a different connection pool (e.g. the new Tomcat 7 JDBC-Pool) that gives you access to the underlying native connection.

Advertisement

Answer