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

"Joseph Weinstein" <joe(at)bea(dot)com> wrote in message
news:3B3277C6(dot)4C9BCA9(at)bea(dot)com(dot)(dot)(dot)
> 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