Thread: Expressiveness of SQLException
Hello dear developers, I wrote a function for the server, that rises this error when called (called and displayed in PGAdmin III): ERROR: value too long for type character varying(255) SQL Status:22001 Kontext:PL/pgSQL function "fkt_calculateshoppinglistitems" line 26 at assignment PL/pgSQL function "fkt_calculateallshoppinglistitems" line 16 at FOR over SELECT rows When this function is called by JDBC, the only information stored in the exception is: ERROR: value too long for type character varying(255) Which makes debugging from server logfiles much harder. I expected to have some more exceptions encapsulated (retrievable with getNextException on PSQLException), but found nothing. Where do I have to look for the missing information? With best regards, Daniel Migowski
Daniel Migowski wrote: > When this function is called by JDBC, the only information stored in the > exception is: > > ERROR: value too long for type character varying(255) > > Which makes debugging from server logfiles much harder. I expected to > have some more exceptions encapsulated (retrievable with > getNextException on PSQLException), but found nothing. http://java.sun.com/j2se/1.4.2/docs/api/java/sql/SQLException.html#getErrorCode() http://java.sun.com/j2se/1.4.2/docs/api/java/sql/SQLException.html#getSQLState() -- Craig Ringer
Daniel Migowski wrote: > Where do I have to look for the missing information? The driver extension org.postgresql.util.PSQLException.getServerErrorMessage() lets you get at all the fields of a server-generated exception. -o
Thank you for your responses. Sadly, they don't help me (with PG 8.3 and the JDBC-3-Driver). This is my SQL: CREATE OR REPLACE FUNCTION fail() RETURNS int4 AS $BODY$ BEGIN RAISE exception 'FAIL'; return null; END; $BODY$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION fail2()RETURNS int4 AS $BODY$ BEGIN select fail(); return null; END; $BODY$ LANGUAGE 'plpgsql'; select fail2(); And this the result: ERROR: FAIL SQL Status:P0001 Kontext:SQL statement "select fail()" PL/pgSQL function "fail2" line 1 at SQL statement Now when called from Java: try { ResultSet r = stmt.executeQuery("select fail2();"); JdbcUtils.dumpResultSet(r); } catch (SQLException e) { e.printStackTrace(); PSQLException ex = (PSQLException)e; System.err.println(ex.getServerErrorMessage()); System.err.println(ex.getSQLState()); System.err.println(ex.getErrorCode()); System.err.println(ex.getNextException()); System.err.println(ex.getCause()); } I get this result: org.postgresql.util.PSQLException: ERROR: function fail2() does not exist at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1559) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:235) at de.ikoffice.jdbc.JdbcExceptionsTest.main(JdbcExceptionsTest.java:24) ERROR: function fail2() does not exist 42883 0 null null where is no more information from the functions you proposed. Is this a bug? With best regards, Daniel Migowski
Daniel Migowski wrote: > System.err.println(ex.getServerErrorMessage()); getServerErrorMessage() returns an object you can query for further details. -O
Hallo Oliver, Oliver Jowett schrieb: > getServerErrorMessage() returns an object you can query for further > details. Oh, sorry, I totally overlooked that... this will solve it. Thank you very much! Daniel Migowski