title: COPY FROM and C3PO connection pool in Postgres
body: <p>I have the follow code in my JAVA program that allows me to copy data from a file into my Postgres database:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>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 <a href="http://www.mchange.com/projects/c3p0/" rel="noreferrer">C3P0</a>.</p>
  
  <pre><code>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();
  }
  }
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>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?</p>
  
  <p>----<em><strong>UPDATED</strong></em>----</p>
  
  <p>Thanks to a_horse_with_no_name the guidance on this. The following code worked for me</p>
  
  <pre><code>// 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
  }
  </code></pre>