Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Error Handling

3 posts in Product Futures Discussion Last posting was on 2003-11-26 12:24:45.0Z
Penny Heeren Posted on 2003-11-25 21:59:33.0Z
From: "Penny Heeren" <pheeren@newtonmfg.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Error Handling
Lines: 50
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Original-NNTP-Posting-Host: smtp.newtonmfg.com
Message-ID: <3fc3d23c$1@forums-2-dub>
X-Original-Trace: 25 Nov 2003 14:05:48 -0800, smtp.newtonmfg.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 25 Nov 2003 13:49:14 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 25 Nov 2003 13:59:33 -0800
X-Trace: forums-1-dub 1069797573 10.22.108.75 (25 Nov 2003 13:59:33 -0800)
X-Original-Trace: 25 Nov 2003 13:59:33 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1456
Article PK: 96463

I'd like to open a discussion to gather information before putting in an
enhancement request. Currently there is very limited 'error handling'
available within stored procedures on Sybase. Currently, the only way to
determine if there is an error is to check the value of @@error after every
statement is ran and using it to decide the next action. Fatal errors
terminate the process completely, with no ability by the developer to
perform any further checks or logging with the process. If a non-fatal
error occurs, as far as Sybase is concerned, it returns the error (handled
or not), even if it continues processing successfully.

By non-fatal error, I reference the following as an example:
"Msg 2601, Level 14, State 1:
Server 'SYBASE1', Procedure 'a1Update', Line 31:
Attempt to insert duplicate key row in object 'a1' with unique index
'a1_testindex'" type of error.

Coming from an Oracle background, I specifically miss functionality provided
by the following features:

* On Error GoTo <<LABEL>> statements that enable you to route errors to
different locations to determine processing, with return or On Error
Continue abilities.

* Only returning errors that are 'unhandled' or are of specific
significance, with the ability to NOT return error messages on errors that
are either 'handled' - i.e., corrected via error handler or errors that
should be ignored.

* The ability to 'generate' errors that should get returned even if there
were no errors generated by the SQL statements, but the logic within the
stored procedure dictates that it should abort and return an error to the
application calling the procedure.

I'm not sure, but is there an ability to 'fairly easily' generate error logs
from the stored procedures? If so, I'd appreciate some feedback on how to
set up a process where I could create a log file within the stored
procedure, with a name like 'a1Update112503.log' that would be created the
first time the stored procedure was ran with an error occuring that day, and
be appended to every other time an error occurs within that stored
procedure.

What other 'error handling' features should I include in my write up for an
enhancement request?

Penny Heeren
DBA/Programmer Analyst
Newton Manufacturing Company
pheeren@newtonmfg.com


A Cornell Posted on 2003-11-25 23:29:33.0Z
From: "A Cornell" <agcornell@msn.com>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3fc3d23c$1@forums-2-dub>
Subject: Re: Error Handling
Lines: 63
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 0-1pool122-217.nas46.thornton1.co.us.da.qwest.net
X-Original-NNTP-Posting-Host: 0-1pool122-217.nas46.thornton1.co.us.da.qwest.net
Message-ID: <3fc3e5dd@forums-1-dub>
Date: 25 Nov 2003 15:29:33 -0800
X-Trace: forums-1-dub 1069802973 67.4.122.217 (25 Nov 2003 15:29:33 -0800)
X-Original-Trace: 25 Nov 2003 15:29:33 -0800, 0-1pool122-217.nas46.thornton1.co.us.da.qwest.net
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1457
Article PK: 96462

You can use
if @@error != 0 goto label

"Penny Heeren" <pheeren@newtonmfg.com> wrote in message
news:3fc3d23c$1@forums-2-dub...
> I'd like to open a discussion to gather information before putting in an
> enhancement request. Currently there is very limited 'error handling'
> available within stored procedures on Sybase. Currently, the only way to
> determine if there is an error is to check the value of @@error after
every
> statement is ran and using it to decide the next action. Fatal errors
> terminate the process completely, with no ability by the developer to
> perform any further checks or logging with the process. If a non-fatal
> error occurs, as far as Sybase is concerned, it returns the error (handled
> or not), even if it continues processing successfully.
>
> By non-fatal error, I reference the following as an example:
> "Msg 2601, Level 14, State 1:
> Server 'SYBASE1', Procedure 'a1Update', Line 31:
> Attempt to insert duplicate key row in object 'a1' with unique index
> 'a1_testindex'" type of error.
>
> Coming from an Oracle background, I specifically miss functionality
provided
> by the following features:
>
> * On Error GoTo <<LABEL>> statements that enable you to route errors to
> different locations to determine processing, with return or On Error
> Continue abilities.
>
> * Only returning errors that are 'unhandled' or are of specific
> significance, with the ability to NOT return error messages on errors that
> are either 'handled' - i.e., corrected via error handler or errors that
> should be ignored.
>
> * The ability to 'generate' errors that should get returned even if
there
> were no errors generated by the SQL statements, but the logic within the
> stored procedure dictates that it should abort and return an error to the
> application calling the procedure.
>
> I'm not sure, but is there an ability to 'fairly easily' generate error
logs
> from the stored procedures? If so, I'd appreciate some feedback on how to
> set up a process where I could create a log file within the stored
> procedure, with a name like 'a1Update112503.log' that would be created the
> first time the stored procedure was ran with an error occuring that day,
and
> be appended to every other time an error occurs within that stored
> procedure.
>
> What other 'error handling' features should I include in my write up for
an
> enhancement request?
>
> Penny Heeren
> DBA/Programmer Analyst
> Newton Manufacturing Company
> pheeren@newtonmfg.com
>
>


Carl Kayser Posted on 2003-11-26 12:24:45.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3fc3d23c$1@forums-2-dub>
Subject: Re: Error Handling
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: 146.142.33.192
Message-ID: <3fc49d01$1@forums-2-dub>
X-Original-Trace: 26 Nov 2003 04:30:57 -0800, 146.142.33.192
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 26 Nov 2003 04:14:18 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 26 Nov 2003 04:24:45 -0800
X-Trace: forums-1-dub 1069849485 10.22.108.75 (26 Nov 2003 04:24:45 -0800)
X-Original-Trace: 26 Nov 2003 04:24:45 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1458
Article PK: 96464

(SNIP)

>
> * The ability to 'generate' errors that should get returned even if
there
> were no errors generated by the SQL statements, but the logic within the
> stored procedure dictates that it should abort and return an error to the
> application calling the procedure.
>

It seems to me that this functionality should be handled by the application
code/SQL and not by the RDBMS. The raiserror command might be of use; I've
never used it.

> I'm not sure, but is there an ability to 'fairly easily' generate error
logs
> from the stored procedures? If so, I'd appreciate some feedback on how to
> set up a process where I could create a log file within the stored
> procedure, with a name like 'a1Update112503.log' that would be created the
> first time the stored procedure was ran with an error occuring that day,
and
> be appended to every other time an error occurs within that stored
> procedure.

It sounds like you might want to request to augment "dbcc printolog
(string)" to "dbcc printolog (string [, logname])" to do as you have
described in the preceding paragraph.

> What other 'error handling' features should I include in my write up for
an
> enhancement request?

I recommend that you not bundle a bunch of features into one request such as
was done in e01_257.

>
> Penny Heeren
> DBA/Programmer Analyst
> Newton Manufacturing Company
> pheeren@newtonmfg.com
>
>