Re: 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:

>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