Re: Cast java.sql.Connection to PGConnection

OK I found it!

FYI, it has to do with the presence of the postgresql library (jar file)
under both the Tomcat/lib and my application/lib. If I remove it from my
application everything works just fine! I found it by putting that library
into my "bare metal" app (which had none so far) and it started to
misbehave...

The reason it is (still) in my app/lib is that I need it there for my
Jasper reports to compile. I commented out that part in my build file,
removed the postgresql library, and then everything started to work!

So I just have to find a way to compile my Jasper reports code somehow and
problem solved!

Thanks again to all for your help... As I have come to find over the last
five years (and going to the Ottawa PgCon from times to times), you are a
wonderful team!

Keep on the good work, it is much appreciated from this end of the spectrum!

Eric

On Wed, May 18, 2016 at 10:49 AM, Eric Neron <eneron(at)e-djuster(dot)ca> wrote:

> Yes I am investigating that issue...
>
> It is a bit complicated since a few months ago, I had built my own "toy
> connection pooler" to serve a seldom used part of my application. At the
> time, I didn't want to take the risk of breaking the rest of my application
> by making use of a generic Connection Pool (like the Tomcat one), so I
> figured out (perhaps wrongly) that this was the safest and easiest way to
> bridge between my requirements for a (small) connection pool at the time
> and the full blown thing... Not very surprisingly, my "toy connection
> pooler" included a class called PooledConnection, that I though last night
> might interfere with a similarly named class in the Tomcat Connection Pool,
> and thus cause the issues I am having! (You may wonder why I didn't remove
> my "toy connection pooler" as the first step of this "implement the real
> thing (Tomcat Connection Pool)" project; the reason is that I wanted to do
> this critical change in an incremental fashion, making sure that at each
> step I had not broken anything. Keeping my "toy connection pooler" in
> there, while not using it anymore appeared to me as the safest route since
> it guaranteed at least that my project would still compile...)
>
> So this morning, my first attempt at cracking this issue was to include my
> "toy connection pool" classes in my "bare metal" test environment (without
> making use of it, but just to be sure they were "visible" in my
> environment), to check whether my hypothesis was right (I was convinced
> this was the issue)... Unfortunately, this didn't change anything: the
> "bare metal" app still works just fine!
>
> To confirm again that this was not the case in the full blown app, I
> renamed my PooledConnection class to PooledConnection1 and I still have the
> issues.
>
> So both of these experiments tend to confirm that my PooledConnection
> class is not the cause of the issues I am having...
>
> Next step was to see, as you suggested, that the libraries that I am
> including in my app are not in conflict with the ones that are in the
> Tomcat lib. Indeed, I have the two libraries I suspect might be the cause
> of these issues (tomee-jdbc-1.7.1.jar & postgresql-9.4.1208.jre.jar) in
> both places. Obviously, the first one (tomee-jdbc-1.7.1.jar) was in the
> Tomcat/lib by default since it is part of the install. I had to put the
> second one (postgresql-9.4.1208.jar) in the Tomcat/lib since I had found a
> few days ago that if it wasn't there, the Tomcat Connection pool would not
> be initialized/loaded... Again, having my "bare metal" app to check this
> environment seems to confirm that, since everything works in there and it
> does not have any of those two libraries bundled into it... In fact, it
> does not have any libraries at all... I just confirmed that the postgres
> library is required under the Tomcat/lib; if it is not there, when I try to
> instantiate the Tomcat Pool, I get the rather cryptic SQLException:
> "java.sql.SQLException: org.postgresql.Driver" and my "bar metal" app does
> not even compile - though this is not surprising, that library has to be
> somewhere!
>
> So I removed those two libraries from my app... well I tried but the
> Jasper report component wants to see the postgresql one in my app library
> to compile, so I had to put it back... and it is still broken! You will
> understand that my application is a bit more complicated than the few lines
> of code I sent you, among other things, it includes about 45 libraries...
> So I have to figure out how to try to dig into my issue: either adding more
> and more stuff to my "bare metal" app until it breaks, or remove more and
> more stuff from my app until it is repaired... I think I will take the
> first option, but obviously it will take me time to do...
>
> Thanks again for your help and comments. At least making me build a "bare
> metal" app confirmed to me that there is light at the end of the tunnel,
> although the tunnel may be longer than what I initially expected...
>
> Once I have succeeded cracking that nut, I will look at my CopyManager
> invocation to account for your comments and use the proper constructor...
>
> Thanks again!
>
> Eric
>
> On Wed, May 18, 2016 at 9:28 AM, Vitalii Tymchyshyn <vit(at)tym(dot)im> wrote:
>
>> I'd really suspect a classloading issue here. TS gets connection through
>> JNDI and then tries to unwrap/cast. By any chance, have you got two
>> postgresql driver jars in different classloaders (e.g. two webapps or
>> tomcat lib and webapp or tomcat common lib)?
>>
>> Best regards, Vitalii Tymchyshyn
>>
>> Вт, 17 трав. 2016 17:06 користувач David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)com> пише:
>>
>>> Except that didn't appear to work ... haven't confirmed the behavior
>>> myself.
>>>
>>> In any case an apparent second report of this just surfaced:
>>>
>>>
>>> http://www.postgresql.org/message-id/CANOVrEhYbmO=mJT==YA=JZ_ZYZHEuq_wra5eYOQuZ+xF1feiHA@mail.gmail.com
>>>
>>> David J.
>>>
>>> On Tue, May 3, 2016 at 3:18 AM, Philippe Marschall <
>>> Philippe(dot)Marschall(at)netcetera(dot)com> wrote:
>>>
>>>> Hi
>>>>
>>>> The following is the way to do it:
>>>>
>>>> PGConnection pgCon = conn.unwrap(PGConnection.class);
>>>>
>>>> Cheers
>>>> Philippe
>>>> ________________________________________
>>>> From: pgsql-jdbc-owner(at)postgresql(dot)org <pgsql-jdbc-owner(at)postgresql(dot)org>
>>>> on behalf of peter.penzov <peter(dot)penzov(at)gmail(dot)com>
>>>> Sent: Monday, May 2, 2016 20:29
>>>> To: pgsql-jdbc(at)postgresql(dot)org
>>>> Subject: [JDBC] Cast java.sql.Connection to PGConnection
>>>>
>>>> I want to upload a file in PostgreSQL using Tomcat:
>>>>
>>>> @Resource(name = "jdbc/DefaultDB")
>>>> private DataSource ds;
>>>> Connection conn = ds.getConnection();
>>>>
>>>> I tried to cast the conn object this way:
>>>>
>>>> PGConnection pgCon = ((org.apache.commons.dbcp.DelegatingConnection)
>>>> conn).getInnermostDelegate();
>>>>
>>>> I get
>>>>
>>>> Caused by: java.lang.ClassCastException:
>>>>
>>>> org.apache.tomcat.dbcp.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper
>>>> cannot be cast to org.postgresql.PGConnection
>>>>
>>>> I also tried:
>>>>
>>>> Connection unwrap = conn.unwrap(Connection.class);
>>>> connSec = (org.postgresql.PGConnection) unwrap;
>>>>
>>>> I get
>>>>
>>>> java.sql.SQLException: Cannot unwrap to org.postgresql.PGConnection
>>>>
>>>> LargeObjectManager pgCon = pgCon.getLargeObjectAPI();
>>>>
>>>> What is the proper way to implement the code?
>>>>
>>>> P.S
>>>>
>>>> I tried this:
>>>>
>>>> PGConnection pgConnection = null;
>>>>
>>>> if (conn.isWrapperFor(PGConnection.class))
>>>> {
>>>> pgConnection = conn.unwrap(PGConnection.class);
>>>> }
>>>>
>>>> LargeObjectManager lobj = pgConnection.getLargeObjectAPI();
>>>>
>>>> But I get NPE at this line LargeObjectManager lobj =
>>>> pgConnection.getLargeObjectAPI();
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> View this message in context:
>>>> http://postgresql.nabble.com/Cast-java-sql-Connection-to-PGConnection-tp5901531.html
>>>> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>>
>>>
>>>
>