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