Re: Detailed Stored Proc Exception Message at JDBC Layer

On Sat, 5 Nov 2005, Jojo Paderes wrote:

> I'm using PostgresSQL pl/pgSQL functions for executing data
> inserts/updates and uses Java JDBC for to execute these functions on the
> application layer. I'm having problems debugging my db functions due to
> the limited exception message thrown at the Java application layer. I
> would like to find a way wherein I can use the exception message on the
> Java app to trace where exactly the error occurred in the database
> function.

We recently added some more detail to the default exception message, but
unfortunately that does not cover the specific case you're interested in.
We've got a lot of fields to choose from and don't want to overwhelm a
user:

http://www.postgresql.org/docs/8.0/static/protocol-error-fields.html

To get the 'W' or "Where" information you need to increase the driver's
loglevel via a URL parameter. Try adding "?loglevel=1" to your connection
string. If your application is pg specific and you're using a recent
driver version you may test if a thrown SQLException is in fact a
PSQLException and then see if it has an available ServerErrorMessage
object available which will have all of the fields mentioned above. For
debugging functions you may also need the 'p' and 'q' fields (internal
query/position) which require going up to a loglevel of 2. Unfortunately
this spits out a whole bunch of other junk as the driver runs.

It seems we should consider adding W, p, and q messages to the default
error message or add a new URL parameter like verboseExceptions=true.
Thoughts?

Kris Jurka