Re: Tomcat JDBC Connection Pool interfaces to PostgreSQL... - Mailing list pgsql-jdbc

it would appear that tomcat is not returning the PgConnection object.

I just wrote this as a test:

public void testConnectionUnwrapPGDataSource() throws SQLException {
  PGSimpleDataSource dataSource = new PGSimpleDataSource();
  assertNotNull(dataSource);
  dataSource.setDatabaseName("test");
  dataSource.setServerName("localhost");
  dataSource.setPortNumber(5432);
  Connection connection = dataSource.getConnection("test","test");
  assertNotNull(connection);
  Object v=connection.unwrap(PGConnection.class);
  assertNotNull(v);
  assertTrue(v instanceof PGConnection);

}

and it works fine.

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.ca> wrote:

I am currently working using the Tomcat JDBC Connection Pool (http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html) which we plan to use in our Java Web application in order to streamline server resources usage associated to supporting our PostgreSQL connections (i.e., minimize the number or connections required).

I am using the following jar files to support my work:

tomcat-jdbc.jar

postgresql-9.4.1208.jre7.jar

Although most of it seems to be working fine so far, I am hitting issues when I try to "cast" the connection the pool is returning to me (a ProxyConnection) into either a PGConnection or BaseConnection.  I need those in order to access either of the following PostgreSQL "advanced/internal" methods:

- addDataType () (PGConnection)

- CopyManager () (BaseConnection).

The Tomcat documentation suggests to extract the native connection by using the following method:

Connection pgConnection = ((javax.sql.PooledConnection) conn).getConnection ( );

Although this does not fail, using a debugger, I can see that pgConnection is really an

org.postgresql.jdbc.PgConnection (not an org.postgresql.PGConnection), and there is not way it seems that I can bridge between the two... even by using:

pgConnection.unwrap ( PGConnection.class );

I am just wondering if this issue is caused by the fact that I use the 

org.apache.tomcat.jdbc.pool.DataSource to instantiate my Connection Pool.

Browsing through the list of choice given to me to determine where the DataSource class is imported in my Java code, I can see:

javax.sql.DataSource (which I have seen can give one access to PGConnection from one of my research on the Web - http://stackoverflow.com/questions/27898632/how-to-cast-jdbc4connection-to-pgconnection), though if I select it, it tells me that this is an abstract Class that I cannot instantiate from!

Similarly, another StackOverlflow page (http://stackoverflow.com/questions/36986653/cast-java-sql-connection-to-pgconnection) suggests using the following code:

PGConnection pgConnection = dataSource.getConnection().unwrap(PGConnection.class);

but again, if I try this, I get the following SQLException: "Not a wrapper of org.postgresql.PGConnection"

(in fact, it turns out that this is exactly the same solution as the "Tomcat solution" I explained above).

I am running out of alternatives...  Any ideas?