Thread: Minor Feature Request
Hi, I am trying to figure out if I can get the line number, when executing a "CREATE FUNCTION" statement which fails. The line number of where the error occurs. For example, if I execute: CREATE OR REPLACE FUNCTION "public"."new_func" (xid, xid) RETURNS bool AS ' BEGIN IFsss $2 IS NULL THEN return ''NULL''; ELSE return ''NOT_NULL''; END IF; END ' LANGUAGE 'plpgsql' I would like to get back the line number 4, which is the line in where the syntax error occurs. Could this be placed in the SQLException.getMessage() text? If so, then I can parse out the line number for my use. Otherwise, I can't find the line number anywhere. Example: [ERROR: Line 4: syntax error at or near "ELSE"] In PGAdmin you get the line number in the text message, but I assume it is using the native API. In a Java Query tool which uses JDBC the line number would be nice. thanks -Niels
On Tue, 5 Apr 2005, NielsG wrote: > I am trying to figure out if I can get the line number, when executing a > "CREATE FUNCTION" statement which fails. The line number of where the > error occurs. > > I would like to get back the line number 4, which is the line in where > the syntax error occurs. Could this be placed in the > SQLException.getMessage() text? If so, then I can parse out the line > number for my use. Otherwise, I can't find the line number anywhere. > > Example: [ERROR: Line 4: syntax error at or near "ELSE"] > There are many parts to a backend error message, see http://www.postgresql.org/docs/8.0/static/protocol-error-fields.html Putting all of these in the SQLException.getMessage() result would be overload. The driver currently only puts in severity and message by default, but will include more info based on the driver's loglevel. Is position alone enough info to include? It really kind of depends on what you are executing what info you want back. Position isn't relevent for something like a duplicate key error. Also what if a function syntax error isn't picked up until runtime? You'd also want "where". I can see people also wanting detail and hint for certain messages. I see where you're coming from, but I'm unsure what should really go into an error message. Kris Jurka
Kris Jurka wrote: > > On Tue, 5 Apr 2005, NielsG wrote: > > >>I am trying to figure out if I can get the line number, when executing a >>"CREATE FUNCTION" statement which fails. The line number of where the >>error occurs. [...] > I can see > people also wanting detail and hint for certain messages. I see where > you're coming from, but I'm unsure what should really go into an error > message. Perhaps we should put accessors for the various message parts on PSQLException, but leave the SQLException message as it currently is. -O
On Wed, 6 Apr 2005, Oliver Jowett wrote: > Perhaps we should put accessors for the various message parts on > PSQLException, but leave the SQLException message as it currently is. > This could work for a pg specific admin tool or client, but not a generic/portable java client. Kris Jurka
Kris Jurka wrote: > > On Wed, 6 Apr 2005, Oliver Jowett wrote: > > >>Perhaps we should put accessors for the various message parts on >>PSQLException, but leave the SQLException message as it currently is. >> > > > This could work for a pg specific admin tool or client, but not a > generic/portable java client. Right, but aren't we talking about a client that knows about postgresql internals anyway? There's no concept of line numbers, hints, etc. associated with an exception in standard JDBC anyway, and as you said it's not obvious what is useful to have in the error message for standard clients. Niels was talking about putting the line number into the message, then parsing the message to get this info. That seems pretty horrible since then you have lots of extra work in the app, and the app is still postgresql-specific.. It seems much cleaner if it can just do something like: try { // ... } catch (PSQLException e) { if (e instanceof PSQLException) linenumber = ((PSQLException)e).getLineNumber(); else linenumber = -1; // handle exception } -O
Oliver Jowett wrote: > } catch (PSQLException e) { Oops, SQLException of course. -O
Hi,
There are two issues that I am trying to solve. One is for my SQL Query tool which just executes a user defined statement and prints out the SQLException.getMessage() text. This currently works well, although a Line number in the message for CREATE FUNCTION errors would be nice (Users have asked for this, so they can get the same helpfull message as PGAdmin). The second issue is that I am building a Procedure/Function editor which displays the error message after a CREATE FUNCTION in a Grid, where the user can double click on the error message and my tool will automatically highlight the line which has the error. All I really need is the line number, although if it gives me the character position I can easily calculate the line number.
It doesn't really matter to me how I get the line number, whether a "Line X" in the message or a PGException specific class. The reason is because my tool has specific code for all databases that it supports. For example, the procedure editor that I am working on gets the line number for Oracle by quering the all_errors table, it gets the line number for SQL Server by parsing the getMessage() text which has the Line number at the beginning of the text, it gets the line number for Sybase Anywhere from the getMessage() text which is at the end of the text and for Sybase it gets it from a SybSQLException class specific with Sybase errors.
So, it doesn't really matter how the line number is presented to me, I already have code to parse it. And it doesn't matter if it is PostgreSQL specific because I have alot of PG specific code already.
thanks
-Niels
Oliver Jowett wrote:
There are two issues that I am trying to solve. One is for my SQL Query tool which just executes a user defined statement and prints out the SQLException.getMessage() text. This currently works well, although a Line number in the message for CREATE FUNCTION errors would be nice (Users have asked for this, so they can get the same helpfull message as PGAdmin). The second issue is that I am building a Procedure/Function editor which displays the error message after a CREATE FUNCTION in a Grid, where the user can double click on the error message and my tool will automatically highlight the line which has the error. All I really need is the line number, although if it gives me the character position I can easily calculate the line number.
It doesn't really matter to me how I get the line number, whether a "Line X" in the message or a PGException specific class. The reason is because my tool has specific code for all databases that it supports. For example, the procedure editor that I am working on gets the line number for Oracle by quering the all_errors table, it gets the line number for SQL Server by parsing the getMessage() text which has the Line number at the beginning of the text, it gets the line number for Sybase Anywhere from the getMessage() text which is at the end of the text and for Sybase it gets it from a SybSQLException class specific with Sybase errors.
So, it doesn't really matter how the line number is presented to me, I already have code to parse it. And it doesn't matter if it is PostgreSQL specific because I have alot of PG specific code already.
thanks
-Niels
Oliver Jowett wrote:
Kris Jurka wrote:On Wed, 6 Apr 2005, Oliver Jowett wrote:Perhaps we should put accessors for the various message parts on PSQLException, but leave the SQLException message as it currently is.This could work for a pg specific admin tool or client, but not a generic/portable java client.Right, but aren't we talking about a client that knows about postgresql internals anyway? There's no concept of line numbers, hints, etc. associated with an exception in standard JDBC anyway, and as you said it's not obvious what is useful to have in the error message for standard clients. Niels was talking about putting the line number into the message, then parsing the message to get this info. That seems pretty horrible since then you have lots of extra work in the app, and the app is still postgresql-specific.. It seems much cleaner if it can just do something like: try { // ... } catch (PSQLException e) { if (e instanceof PSQLException) linenumber = ((PSQLException)e).getLineNumber(); else linenumber = -1; // handle exception } -O
On Wed, 6 Apr 2005, Oliver Jowett wrote: > Perhaps we should put accessors for the various message parts on > PSQLException, but leave the SQLException message as it currently is. > I've made ServerErrorMessage and its members available in the newly released 8.1dev-400. int errorPosition = 0; try { ... } catch (PSQLException e) { ServerErrorMessage m = e.getServerErrorMessage(); if (m != null) { errorPosition = m.getPosition(); } } Kris Jurka