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.

Some form of coherent exception/error handling for ASE???

6 posts in General Discussion Last posting was on 2011-07-08 06:57:13.0Z
Simon Scott Posted on 2011-07-07 01:48:34.0Z
Sender: 5b7b.4e150f1f.1804289383@sybase.com
From: Simon Scott
Newsgroups: sybase.public.ase.general
Subject: Some form of coherent exception/error handling for ASE???
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e151072.5bd4.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 6 Jul 2011 18:48:34 -0700
X-Trace: forums-1-dub 1310003314 10.22.241.41 (6 Jul 2011 18:48:34 -0700)
X-Original-Trace: 6 Jul 2011 18:48:34 -0700, 10.22.241.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30320
Article PK: 72499

Hi all

Has anyone come up with a neat way of handling
errors/exceptions in t/sql?

We need to strengthen our error handling in some important
procedures, but all we can come up with is a bunch of code
handling @@error after every frickin statement, which just
cannot be a legitimate approach....

How do you effectively handle errors in your code?


Rob V [ Sybase ] Posted on 2011-07-07 07:17:42.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Some form of coherent exception/error handling for ASE???
References: <4e151072.5bd4.1681692777@sybase.com>
In-Reply-To: <4e151072.5bd4.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e155d96@forums-1-dub>
Date: 7 Jul 2011 00:17:42 -0700
X-Trace: forums-1-dub 1310023062 10.22.241.152 (7 Jul 2011 00:17:42 -0700)
X-Original-Trace: 7 Jul 2011 00:17:42 -0700, vip152.sybase.com
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30321
Article PK: 72500


On 07-Jul-2011 03:48, Simon Scott wrote:
> Hi all
>
> Has anyone come up with a neat way of handling
> errors/exceptions in t/sql?
>
> We need to strengthen our error handling in some important
> procedures, but all we can come up with is a bunch of code
> handling @@error after every frickin statement, which just
> cannot be a legitimate approach....
>
> How do you effectively handle errors in your code?

What you describe is the right way of doing it. It may not be fun, but
for good production code I would expect to see a significant part of the
number of code lines to be spent on error checking.

Apart from checking @@error, you should sometimes also check @@rowcount,
for example when you expect to have update exactly 1 row (what if you
actually upadated 0 or 2?) In addition it is a good idea to add some
checking on your transaction handling: at a point in your code where you
are starting a transaction and you do not expect any transaction to be
active, explicitly check @@trancount to be 0 (this can save you from
painful problems if somehow a commit is forgotten somewhere).

Don't forget to copy @@error and @@rowcount into local variables first
before testing them.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


"Mark A. Parsons" <iron_horse Posted on 2011-07-07 19:33:05.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Some form of coherent exception/error handling for ASE???
References: <4e151072.5bd4.1681692777@sybase.com>
In-Reply-To: <4e151072.5bd4.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e1609f1$1@forums-1-dub>
Date: 7 Jul 2011 12:33:05 -0700
X-Trace: forums-1-dub 1310067185 10.22.241.152 (7 Jul 2011 12:33:05 -0700)
X-Original-Trace: 7 Jul 2011 12:33:05 -0700, vip152.sybase.com
Lines: 163
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30331
Article PK: 72510

Unfortunately T-SQL doesn't have the ability to define an exceptions handler like in PL-SQL.

And regardless of what design you come up with ... it only works if it's part of an enforced coding standard. ("Duh,
Mark!" ?)

I prefer a 'modular' approach similar to the following:

=====================================
create proc ...
as

declare @error int,
@rowcount int,
@ret_cd int,
@my_error int,
@my_err_msg varchar(100),
@my_ret_cd int,
@trancount int,
@abort_fl char(1)

select @my_ret_cd = 0,
@abort_fl = 'N'

...

begin tran
save tran my_local_tran

select @trancount = @@trancount

insert/update/delete/select

-- after each query requiring error handling ...

select @error = @@error, @rowcount = @@rowcount

if @errror != 0
-- or @rowcount = 0
begin
-- set some @variables
select @my_error = 23456,
@my_err_msg = 'Error: had a problem with update of #result. @@error = ' +
convert(varchar,@error) + ' ; @@rowcount = '+
convert(varchar,@rowcount),
@my_ret_cd = 135 -- assuming =0 ==> SUCCESS, !=0 ==> FAILURE
-- can use different values to indicate what parent
-- process should do

-- and jump to a common set of code for error handling

goto rollback_now
end

...

-- after each proc call requiring error handling ...

exec @ret_cd = some_proc @param1, @param2

select @error = @@error

if @ret_cd != 0 -- assumes local standard of @ret_cd!=0 == FAILURE
--or @@error != 0
begin
-- set some variables
select @my_error = 23488,
@my_err_msg = 'Error: problem with some_proc call. @ret_cd = ' +
convert(varchar,@ret_cd),
@my_ret_cd = 142

-- and jump to a common set of code for error handling

goto rollback_now
end

...

-- finished all normal work, now for some checks-n-balances

-- address sub-proc/triggers that a) leave open a txn or b)
-- perform a 'rollback tran' that rolls back our local txn

if @trancount != @@trancount
begin
select @abort_fl = 'Y',
@my_error = 45999,
@my_err_msg = 'Error: txn management screwed up. @trancount = '+
convert(varchar,@trancount) + ', @@trancount = '+
convert(varchar,@@trancount)
goto rollback_now
end

-- if we got this far we had no errors so skip over error handling

goto commit_now

-- our 'error handler' section; what to do about txn management?
-- issue raiserror

rollback_now:

raiserror @my_error @my_err_msg

if @abort_fl = 'Y' -- what is your local policy if sub-proc
-- or trigger screws up txn management?
begin
rollback tran -- rollback all parent txn's?
end
else
rollback tran my_local_tran -- rollback local savepoint

commit_now:

commit tran -- close out local tran

-- send back our desired return code

return @my_ret_cd
=====================================

Granted, you still need a block of code after each query/proc of interest.

I prefer a structure like this primarily because it allows me to have one entry point (beginning of proc) and one exit
point (single 'return' at end of proc), as well as one place (at end of proc) to deal with transaction management issues.

Some folks don't like the 'goto' command, but as long as you don't use it to jump *back* in the code you should be fine.
I've had a few 'goto' bigots change their mind once they got into the issue of having to deal with a) error handling
after each query, b) error message generation after each query, c) transaction management after each query and d)
'return' and return code management after each query.

I've seen some shops where junior T-SQL developers will put all the error handling/txn management/return processing
after each query ... then occasionally forget to close out a transaction properly before issuing the 'return' ...
combined with not checking for errors/bad-return codes after sub-proc/trigger processing ... with the net result being
incomplete transactions making their way into the database. *messy* *messy* *messy*



Other items to keep in mind:

- use error numbers that are unique within your code base so that given an error number you can easily figure out which
proc/line generated the number (hint: many front-end applications mask a good bit of the useful info in a
dataserver-generated error message, so any additional details you can provide the better)

- consider adding key @values (eg, key search parameters for T-SQL queries, input/output params for procs) to your error
message to allow for easier troubleshooting

- obviously this method assumes all errors are cause for aborting the proc, ie, there's no easy way to 'go back' and
pick up where you left off after completing your error handling [though a parent process could decide on further actions
based on the error number and return code you return to said parent process]

On 07/06/2011 21:48, Simon Scott wrote:
> Hi all
>
> Has anyone come up with a neat way of handling
> errors/exceptions in t/sql?
>
> We need to strengthen our error handling in some important
> procedures, but all we can come up with is a bunch of code
> handling @@error after every frickin statement, which just
> cannot be a legitimate approach....
>
> How do you effectively handle errors in your code?


Simon Scott Posted on 2011-07-08 01:56:02.0Z
Sender: 5b7b.4e150f1f.1804289383@sybase.com
From: Simon Scott
Newsgroups: sybase.public.ase.general
Subject: Re: Some form of coherent exception/error handling for ASE???
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e1663b2.feb.1681692777@sybase.com>
References: <4e1609f1$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Jul 2011 18:56:02 -0700
X-Trace: forums-1-dub 1310090162 10.22.241.41 (7 Jul 2011 18:56:02 -0700)
X-Original-Trace: 7 Jul 2011 18:56:02 -0700, 10.22.241.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30334
Article PK: 72514

> I prefer a 'modular' approach similar to the following:

Thanks for that, its very similar to what we had in mind.
The unfortunate thing is, our system is very DML heavy, and
doing this after every insert/update/delete just isnt
practical at this point. The procedures would be absolutely
massive, and the testing effort would be astronomical.

I think we'll be moving to SQL Server soon anyway, which has
try/catch built in.


Robert Densmore Posted on 2011-07-08 02:58:32.0Z
From: Robert Densmore <bdensmore@austin.rr.ignore.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Some form of coherent exception/error handling for ASE???
Message-ID: <cdsc17latbh3pmp246uphu0top01nn8kvc@4ax.com>
References: <4e1609f1$1@forums-1-dub> <4e1663b2.feb.1681692777@sybase.com>
X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 7 Jul 2011 19:58:32 -0700
X-Trace: forums-1-dub 1310093912 10.22.241.152 (7 Jul 2011 19:58:32 -0700)
X-Original-Trace: 7 Jul 2011 19:58:32 -0700, vip152.sybase.com
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30335
Article PK: 72513

And don;t forget that alot of the errors that may be thrown will abort
the proc/trigger at the DML statement and throw the error to the
client app. So, you have to have good error handling in both the
procs/triggers (for permission errors/dup key errors/etc) and on the
client side for all other fatal errors.

Bob

On 7 Jul 2011 18:56:02 -0700, Simon Scott wrote:

>
>> I prefer a 'modular' approach similar to the following:
>
>Thanks for that, its very similar to what we had in mind.
>The unfortunate thing is, our system is very DML heavy, and
>doing this after every insert/update/delete just isnt
>practical at this point. The procedures would be absolutely
>massive, and the testing effort would be astronomical.
>
>I think we'll be moving to SQL Server soon anyway, which has
>try/catch built in.


Rob V [ Sybase ] Posted on 2011-07-08 06:57:13.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Some form of coherent exception/error handling for ASE???
References: <4e1609f1$1@forums-1-dub> <4e1663b2.feb.1681692777@sybase.com>
In-Reply-To: <4e1663b2.feb.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e16aa49@forums-1-dub>
Date: 7 Jul 2011 23:57:13 -0700
X-Trace: forums-1-dub 1310108233 10.22.241.152 (7 Jul 2011 23:57:13 -0700)
X-Original-Trace: 7 Jul 2011 23:57:13 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30336
Article PK: 72522


On 08-Jul-2011 03:56, Simon Scott wrote:
>> I prefer a 'modular' approach similar to the following:
> Thanks for that, its very similar to what we had in mind.
> The unfortunate thing is, our system is very DML heavy, and
> doing this after every insert/update/delete just isnt
> practical at this point. The procedures would be absolutely
> massive, and the testing effort would be astronomical.
>
> I think we'll be moving to SQL Server soon anyway, which has
> try/catch built in.

The testing effort would not be fundamentally different on SQL Server
since the number of cases to test (all those DML statements) would
essentially be identical.

Note that you do not necessarily have to expand the full error testing
after every statement.
You could do something like this and wrap the error checkinto a proc
which you pass the conditions to check for as paramters:

...insert/update/delete stmt...

select @err = @@error, @rc = @@rowcount
exec @ret = p_status_check @err, @rc, 'insert-stmt-xyz', @expected_rc=1
if @ret > 0 goto rollback

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------