COPY FROM and C3PO connection pool in Postgres

Question

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
}

Nightgown

The complete stack trace of the error is? Why are you initializing a static field from a constructor? And what makes you think that connections returned by C3P0 are instances of BaseConnection (whatever this class might be?) – Titter

What do you mean by doesn't work? Is any exception of any type thrown? Does it hang? – Epigrammatist

@AHungerArtist: "Doesn't work" as in the data in the file is not copied to the table. There is no exception thrown, nor does it hang; it just continues as if the code were executed. – Nightgown

@JB Nizet: A stack trace was not thrown, I initialized it from the constructor so i only have one instance of the pool, and I know C3P0 doesn't return instances of BaseConnection; that's why i was trying to cast it? Is this my error? – Nightgown

I don't know. Execute the code in a debugger to see what it does. We don't know enough of your code to do. I suspect you're catching the exception thrown. Also, casting an object to a class will only work if the object is an instance of this class. – Titter

Answer 1

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.

Robespierre

Thanks for pointing me in the right direction. The raw connection is what I needed to make it work. I'll update my original post with the code I used that got it to work. – Nightgown

Answer 2

CopyManager cm = new CopyManager(cpds.getConnection().unwrap(PgConnection.class))

works for me.

Corinthian