SPL exception handling - best practices
Brock Sides
I'm just getting started writing stored procedures in SPL, and I'm
looking for advice on "best practices" regarding exception handling,
particularly how best to indicate to a client application that an
error has occurred. I'm thinking I could do one of two things:
(a) Return some "out of range" value(s) to the application, such as
NULL:
on exception
rollback work;
return NULL;
end exception
The obvious disadvantage here is that the application has no idea what
went wrong, and can't pass a useful error message to the user.
(b) Add two or three extra return values to the procedure, to indicate
SQL error code, ISAM error code, and perhaps the error message:
on exception
set sqlerr, isamerr, errmsg;
rollback work;
return NULL, sqlerr, isamerr, errmsg;
end exception
If I did this, how long would I need to make errmsg to be able to hold
the error message. I saw someone's code that did a "define errmsg
char(78)". Is that the maximum length of an error message?
Second question:
Is there a way to return meaningful column names in the return value
of a stored procedure? E.g. Let's say I've got a procedure foo() as
follows:
create procedure foo() returning int, int;
return 23, 42;
end procedure;
Is there any way to label the columns it returns, e.g. so it returns
foo bar
23 42
instead of
(expression) (expression)
23 42
?
Jonathan Leffler
> First question:
>
> I'm just getting started writing stored procedures in SPL, and I'm
> looking for advice on "best practices" regarding exception handling,
> particularly how best to indicate to a client application that an
> error has occurred. I'm thinking I could do one of two things:
>
> (a) Return some "out of range" value(s) to the application, such as
> NULL:
>
> on exception
> rollback work;
> return NULL;
> end exception
>
> The obvious disadvantage here is that the application has no idea what
> went wrong, and can't pass a useful error message to the user.
>
> (b) Add two or three extra return values to the procedure, to indicate
> SQL error code, ISAM error code, and perhaps the error message:
>
> on exception
> set sqlerr, isamerr, errmsg;
> rollback work;
> return NULL, sqlerr, isamerr, errmsg;
> end exception
>
> If I did this, how long would I need to make errmsg to be able to hold
> the error message. I saw someone's code that did a "define errmsg
> char(78)". Is that the maximum length of an error message?
Why not simply raise an exception? As in
RAISE EXCEPTION -746, -201, "RTFM? - Guide to SQL: Syntax, Volume 2";
> Second question:
>
> Is there a way to return meaningful column names in the return value
> of a stored procedure? E.g. Let's say I've got a procedure foo() as
> follows:
>
> create procedure foo() returning int, int;
> return 23, 42;
> end procedure;
>
> Is there any way to label the columns it returns, e.g. so it returns
>
> foo bar
> 23 42
>
> instead of
>
> (expression) (expression)
> 23 42
>
> ?
Not yet - we're working on it.
--
Jonathan Leffler (jlef...@earthlink.net, jlef...@us.ibm.com)
Guardian of DBD::Informix 1.00.PC2 -- see http://dbi.perl.org/
#include <disclaimer.h>
Paul Watson
>
> First question:
>
> I'm just getting started writing stored procedures in SPL, and I'm
> looking for advice on "best practices" regarding exception handling,
> particularly how best to indicate to a client application that an
> error has occurred. I'm thinking I could do one of two things:
>
> (a) Return some "out of range" value(s) to the application, such as
> NULL:
>
> on exception
> rollback work;
> return NULL;
> end exception
>
> The obvious disadvantage here is that the application has no idea what
> went wrong, and can't pass a useful error message to the user.
>
> (b) Add two or three extra return values to the procedure, to indicate
> SQL error code, ISAM error code, and perhaps the error message:
I tend to have the first return value as the error indicator (sqlerr)
and in the exception block insert the SPLname, datetime, sqlerr,
isamerr and errmsg into a error table.
The SPL calling code then works out what do if it gets a none zero
first return variable.
I also tend not to do the transaction control in the SPL as it
becomes a pain to handle if you are calling a number of them.
>
> on exception
> set sqlerr, isamerr, errmsg;
> rollback work;
> return NULL, sqlerr, isamerr, errmsg;
> end exception
>
> If I did this, how long would I need to make errmsg to be able to hold
> the error message. I saw someone's code that did a "define errmsg
> char(78)". Is that the maximum length of an error message?
No, it's bigger than that, I'd guess at 256 but I've never tested it
>
> Second question:
>
> Is there a way to return meaningful column names in the return value
> of a stored procedure? E.g. Let's say I've got a procedure foo() as
> follows:
>
> create procedure foo() returning int, int;
> return 23, 42;
> end procedure;
>
> Is there any way to label the columns it returns, e.g. so it returns
>
> foo bar
> 23 42
>
> instead of
>
> (expression) (expression)
> 23 42
>
Not really
--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
www.oninit.com #