Re: Tomcat JDBC Connection Pool interfaces to PostgreSQL...

Thanks Dave for your quick response...

You are cheating a little bit since you are using the DBSimpleDataSource...
(which according to the Doc "Simple DataSource which does not perform
connection pooling").

As I explained in my email I am using the

org.apache.tomcat.jdbc.pool.DataSource

and this one (again according to the Doc "The *JDBC Connection
Pool org.apache.tomcat.jdbc.pool* is a replacement or an alternative
to the Apache
Commons DBCP <http://commons.apache.org/dbcp/> connection pool").

This is from this one that I do not seem to be able to access the
PGConnection object...

So does your answer is some sort of "It is on the Tomcat side"?

Granted that the issue sits either on the PostgreSQL, Tomcat, or my side.
I am just surprised that it is so difficult to make this work properly,
given that I use Tomcat (a fairly widely used Application Server),
PostgreSQL (a fairly widely used DB engine), the combination of the two
(which I also suspect is fairly widely used) and that I seem to be the only
one having this issue! It is like saying that printf does not work on this
particular C compiler... I must not be the only one trying to use the
combination of Tomcat Connection pooling with a PostgreSQL DB engine; this
simply cannot be the case!

Strangely, when using the Netbeans debugger to examine the Connection that
is returned by the Tomcat Connection Pooling engine, I see:

poolConn = (com.sun.proxy.$Proxy77)
ProxyConnection[PooledConnection[org(dot)postgresql(dot)jdbc(dot)PgConnection(at)1b712b5e]]

So the PgConnection is there buried somewhere underneath the
ProxyConnection/PooledConnection objects. If I could find a way to access
it, my problems would be solved, since according to the Doc, it implements
all the Interfaces I need to have access to (PGConnection & BaseConnection):

- java.lang.Object
<http://docs.oracle.com/javase/8/docs/api/java/lang/Object.html?is-external=true>
-
- org.postgresql.jdbc.PgConnection

- All Implemented Interfaces:AutoCloseable
<http://docs.oracle.com/javase/8/docs/api/java/lang/AutoCloseable.html?is-external=true>
, Connection
<http://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html?is-external=true>
, Wrapper
<http://docs.oracle.com/javase/8/docs/api/java/sql/Wrapper.html?is-external=true>
, BaseConnection
<https://jdbc.postgresql.org/development/privateapi/org/postgresql/core/BaseConnection.html>
, PGConnection
<https://jdbc.postgresql.org/development/privateapi/org/postgresql/PGConnection.html>
I just cannot get access to it!Anyway, thanks for you time & quick
response!
Eric

On Tue, May 17, 2016 at 4:17 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> 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(at)postgresintl(dot)com
> www.postgresintl.com
>
> On 17 May 2016 at 14:40, Eric Neron <eneron(at)e-djuster(dot)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?
>>
>>
>>
>>
>>
>>
>>
>