Thread: Detailed Stored Proc Exception Message at JDBC Layer

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.

Say for example I have a main pl/pgSQL function for inserting/updating
data to multiple tables. This main function uses other specialized
functions for executing the data inserts/updates. For example one of
those specialized functions inside the main function encountered a
data constraint exception, the error will be thrown back to the Java
app with only the contraint error detail, and doesn't include which
particular specialized function inside the main function did the
exception occurred. The developer will have a difficult time tracing
which function (and code line) the error came from in the database.
Running the main function using psql can display a more detailed error
like this:

db=> select * from f_process_grn_record('BS1LEM-SN100013');
ERROR:  duplicate key violates unique constraint
"t_material_commodity_type_id_key"
CONTEXT:  SQL statement "INSERT INTO t_material ( id,
commodity_type_id, code, name, date_registered, supplier_id) values (
$1 ,  $2 ,  $3 ,  $4 , CAST(NOW() AS TIMESTAMP),  $5  )"
PL/pgSQL function "f_add_material" line 26 at SQL statement
SQL statement "SELECT  f_add_material( $1 ,  $2 ,  $3 ,  $4 ,  $5 )"
PL/pgSQL function "f_process_grn_record" line 96 at select into variables

At the Java app layer, I will only get the ERROR message. It would be
nice if there's a way I can display the CONTEXT message in the
application layer to provide a better way for tracing the problem.

Any ideas on how to pull this one off?

--
You can make a difference!
http://gawadkalinga.org
http://www.childrenshour.org.ph
http://www.handsonmla.org

http://groups.yahoo.com/group/pinoyjug
http://jojopaderes.blogsome.com
http://jojopaderes.multiply.com

"In preparing for battle I have always found that plans are useless,
but planning is indispensable." - Eisenhower

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

Hi Kris,

Adding the "?loglevel=1" to the connection url works well. The "W"
information is sufficient to help the Java developers debug/trace the
problem on the database functions. Thanks for pointing out this
solution! :-)

regards,

jojo

On 11/5/05, Kris Jurka <books@ejurka.com> wrote:
>
> 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
>