Thread: Re: JDBC Connection State Management with SQL Exceptions (esp Postgresql)

John Moore wrote:
> 
> HELP!
> 
> I am converting an app from Oracle to Postgresql and running into a
> significant difference in the behavior of a connection after an SQLException
> has been asserted. I am looking for the "correct" way to deal with the
> issue.
> 
> From a number of experiments, it appears that the only way I can re-use a
> connection after it has asserted an SQLException is to issue a rollback()
> call on the connection.
> 
> I am doing transactional work, with multiple statements and then a commit().
> I am also doing my own connection pooling, so it is important that I be able
> to reliably re-use connections.

Hi. There is a lot of state that can be left with a connection, and a good
pooling system should do a bunch of cleanup on the connection when it is
returned to the pool, so it will be ready for the next user. This would include
closing all statements and result sets that the previous user may have created
but not closed. This is crucial because you don't want retained references
to these objects to allow a 'previous user' to affect anything the next user
does. You should clear theconnection warnings that accrue. You should
roll back any hanging transactional context, by doing a rollback if
autoCommit() is false, and you should then reset the connection to autoCommit(true),
which is the standard condition for a new JDBC connection.
Joe

> 
> My questions:
>    What is the best way (in Postgressql, or even better, in a portable
> manner) to deal with SQLExceptions in a pooled connection environment?
> 
>    If I pull a connection out of my pool, is there any way I can tell if it
> will work?  Should I always do a rollback on it just in case? Will that have
> a performance impact?
> 
> In the case of Postgresql, I cannot find a way to tell if the connection is
> in the state of having had an SQL Exception exerted and no rollback called,
> other than keeping track of it myself! Is there any way to determine that
> connection state other than by doing a test query?
> 
> A non-working trace (that I think should work but doesn't) is below. Note
> that a "Done" means the SQL operation did NOT produce an SQLException
> ------------------------------------ cut
> here --------------------------------------
> 
> ...Drop Table Testtable
> SQL Error (Allowed):java.sql.SQLException: ERROR:  table "testtable" does
> not exist
> 
> ......commit()
> ...Select from TestTable after drop
> SQL Error (Allowed):No results were returned by the query.
> Result set:null
> 
> ...Create Table Testtable
> ......Done
> ...Insert into Testtable
> ......Done
> ...Insert into Testtable
> ......Done
> ......commit()
> ...Insert into Testtable
> SQL Error (Allowed):java.sql.SQLException: ERROR:  Relation 'testtable' does
> not
>  exist
> 
> ......commit()
> ...Select from Testtable
> SQL Error (Allowed):No results were returned by the query.
> Result set:null
> 
> ......commit()
> 
> A working trace (added rollbacks) is here:
> ------------------------------------ cut
> here --------------------------------------
> ...Drop Table Testtable
> ......Done
> ......commit()
> ...Select from TestTable after drop
> SQL Error (Allowed):java.sql.SQLException: ERROR:  Relation 'testtable' does
> not
>  exist
> 
> ......Rollback
> Result set:null
> 
> ...Create Table Testtable
> ......Done
> ...Insert into Testtable
> ......Done
> ...Insert into Testtable
> ......Done
> ......commit()
> ...Insert into BOGUSTABLE
> SQL Error (Allowed):java.sql.SQLException: ERROR:  Relation 'bogustable'
> does no
> t exist
> 
> ......Rollback
> ......commit()
> ...Insert into Testtable
> ......Done
> ......commit()
> ...Select from Testtable
> ......done
> Result set:org.postgresql.jdbc2.ResultSet@653108
> 
> ......commit()
> 
> Thanks in advance
> 
> John Moore
> NOSPAMjohn@NOSPAMtinyvital.com

-- 

PS: Folks: BEA WebLogic is expanding rapidly, with both entry and advanced positions
for people who want to work with Java, XML, SOAP and E-Commerce infrastructure products.
We have jobs at Nashua NH, Liberty Corner NJ, San Francisco and San Jose CA.
Send resumes to joe@bea.com

"Joseph Weinstein" <joe@bea.com> wrote in message
news:3B3277C6.4C9BCA9@bea.com...
>
>
> John Moore wrote:
.....
> > I am doing transactional work, with multiple statements and then a
commit().
> > I am also doing my own connection pooling, so it is important that I be
able
> > to reliably re-use connections.
>
> Hi. There is a lot of state that can be left with a connection, and a good
> pooling system should do a bunch of cleanup on the connection when it is
> returned to the pool, so it will be ready for the next user. This would
include
> closing all statements and result sets that the previous user may have
created
> but not closed.

What about PreparedConnection pooling?
What is your oppinion on the following  code
[design] for such caching within a connection :
( getUsedPstmts() is imaginary method of imaginary
MyConnection interface )

public void returnConnection (Connection con) {  Connection local_con = con;  con = null; PreparedStatement []
used_pstmt= (MyConnection) local_con.getUsedPstmts() for (int i =0 ; i < used_con.length ; i++) {
PreparedStatementnew_pstmt = used_con[i];       used_con[i] = null;      cached_pstmt_HashMap.put( new_pstmt.getSql(),
new_pstmt); }
 
... some other cleaning steps....
...set connection as available...
}

AlexV

> This is crucial because you don't want retained references
> to these objects to allow a 'previous user' to affect anything the next
user
> does. ......

AV wrote:
> 
> "Joseph Weinstein" <joe@bea.com> wrote in message
> news:3B3277C6.4C9BCA9@bea.com...
> >
> > John Moore wrote:
> .....
> > > I am doing transactional work, with multiple statements and then a
> commit().
> > > I am also doing my own connection pooling, so it is important that I be
> able
> > > to reliably re-use connections.
> >
> > Hi. There is a lot of state that can be left with a connection, and a good
> > pooling system should do a bunch of cleanup on the connection when it is
> > returned to the pool, so it will be ready for the next user. This would
> include
> > closing all statements and result sets that the previous user may have
> created
> > but not closed.
> 
> What about PreparedConnection pooling?
> What is your oppinion on the following  code
> [design] for such caching within a connection :
> ( getUsedPstmts() is imaginary method of imaginary
> MyConnection interface )
> 
> public void returnConnection (Connection con) {
>    Connection local_con = con;
>    con = null;
>   PreparedStatement [] used_pstmt = (MyConnection) local_con.getUsedPstmts()
>   for (int i =0 ; i < used_con.length ; i++) {
>         PreparedStatement new_pstmt = used_con[i];
>         used_con[i] = null;
>        cached_pstmt_HashMap.put( new_pstmt.getSql(),  new_pstmt );
>   }
> ... some other cleaning steps....
> ...set connection as available...
> }
> 
> AlexV

Hi Alex. I think I understand this... The basis of caching/re-using a PreparedStatment
is via the SQL used to create it, but I see no actual statement-level cleanup here.
You should be clearing any warnings the statement may have accrued. Another example
is that you should do something to cover the possibility some user code called setMaxRows(1)
on the statement. You don't want this condition to remain and silently truncate the results
of any subsequent user... This code also doesn't allow for multiple statements with the
same SQL. There will be some 'utility' statements that might be used at several levels
in a user's stack, and you want to allow for caching multiple identical statements *and*
making sure that no two methods in the same caller stack get the *same* statement,
even if it is the same SQL.

Joe


> 
> > This is crucial because you don't want retained references
> > to these objects to allow a 'previous user' to affect anything the next
> user
> > does. ......

-- 

PS: Folks: BEA WebLogic is expanding rapidly, with both entry and advanced positions
for people who want to work with Java, XML, SOAP and E-Commerce infrastructure products.
We have jobs at Nashua NH, Liberty Corner NJ, San Francisco and San Jose CA.
Send resumes to joe@bea.com

"Joseph Weinstein" <joe@bea.com> wrote in message
news:3B3277C6.4C9BCA9@bea.com...
> Hi. There is a lot of state that can be left with a connection, and a good
> pooling system should do a bunch of cleanup on the connection when it is
> returned to the pool, so it will be ready for the next user.

>This would include
> closing all statements and result sets that the previous user may have
created
> but not closed. This is crucial because you don't want retained references
> to these objects to allow a 'previous user' to affect anything the next
user
> does.

Argh... Does this mean that my connection pooler needs to keep track of all
statements and result
sets the user creates. I assume this means I also need to wrap the
statements so that I can
capture the returned result sets by overriding the execute method. Is this
correct?

Do you know of any source out there that implements connection pooling in a
portable manner so I could use it with both Oracle and Postgresql?

>You should clear theconnection warnings that accrue.

Okway
>You should
> roll back any hanging transactional context, by doing a rollback if
> autoCommit() is false, and you should then reset the connection to
autoCommit(true),
> which is the standard condition for a new JDBC connection.

It also appears that once a non-autoCommit transaction has sustained an
SQLException, it is
useless until a rollback is done - at least in PostgreSQL. Is this correct?

The following question is still outstanding...

> > In the case of Postgresql, I cannot find a way to tell if the connection
is
> > in the state of having had an SQL Exception exerted and no rollback
called,
> > other than keeping track of it myself! Is there any way to determine
that
> > connection state other than by doing a test query?

Thanks

John