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

Hi Dave,

Strangely, when building my "bare metal" test case, the issue
disappears... I can send you the code if you wish (but as I said, it does
not demonstrate the issue that I am having since this issue has vanished).
I have to investigate which layer of my application is causing me this
issue...

But to comfort the other people looking over your shoulder, let me
just say that the following code (which demonstrates the gist of it)
appears to work fine:

// Set-up ConnectionPool...
dataSource = initTomcatConnectionPool ( );
// Get a connection from the Pool
// TODO: Put your breakpoint here to examine the connection that is
returned from the Tomcat pool...
connection = borrowConnection ( );
// TODO: This is where I am having problem casting the above variable into
a PgConnection or a BaseConnection
PgConnection pgConnection = connection.unwrap ( PgConnection.class );
CopyManager cm = new CopyManager ( (BaseConnection) pgConnection );
PGClassAndType pgClassAndType = new PGClassAndType ( );
pgConnection.addDataType( pgClassAndType.getType ( ), PGClassAndType.class
);

PreparedStatement stmt = pgConnection.prepareStatement ( "SELECT 1" );

FYI, this is the borrowConnection () code:

public static Connection borrowConnection ( ) throws SQLException,
InterruptedException {
Connection conn = ( dataSource == null ) ? null : dataSource.getConnection
( );
return conn;
}

and dataSource is defined as follows:

import org.apache.tomcat.jdbc.pool.DataSource;

private static DataSource dataSource = null;

Thanks anyway for making me delve into my own code... I think I have made
some progress at least telling me there should be a way to make this work!

Regards,

Eric

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

> Eric,
>
> Yes, a bit. Can you create a self contained test using DBCP and I'll try
> to find some time to look at it?
>
> Dave
>
> Dave Cramer
>
> davec(at)postgresintl(dot)com
> www.postgresintl.com
>
> On 17 May 2016 at 17:04, Eric Neron <eneron(at)e-djuster(dot)ca> wrote:
>
>> 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?
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>
>