SQL Anywhere database - Global var @@error not resetting to 0 on ASA 9.02

Author

Global var @@error not resetting to 0 on ASA 9.02

Wytze

2005-08-19, 9:24 am

Hello,

While testing our application on ASA 9.02, we run into the
following problem.
Under certain circumstances the global var @@error gets
stuck on a value and will not reset to 0 when a new
statement is executed.
It seems to happen when a select is executed after a
raiserror.
I was able to reproduce the problem with a simple case.
I created a new database with Sybase Central with everything
on default and put 2 procedures on them:

create procedure test1()
as
begin
raiserror 60000
end

create procedure test2()
as
begin
exec test1
select @@error
end

After executing test2, @@error will not reset to 0. I can
execute the statement "select @@error" as many times as I
want, it will return the value 60000.

I tested this on a XP SP2 machine with the ASA builds 3044,
3137 and 3169.

Is this a known issue? And is there a workaround for the
problem?

Regards,
Wytze.

Oliver Schulze L.

2005-08-25, 3:31 am

Try running this:

exec test1;
select @@error;
select 1 from dummy;
select @@error;

Maybe the @@error should be reset after a successfull command.

Oliver

Wytze wrote:
> Hello,
>
> While testing our application on ASA 9.02, we run into the
> following problem.
> Under certain circumstances the global var @@error gets
> stuck on a value and will not reset to 0 when a new
> statement is executed.
> It seems to happen when a select is executed after a
> raiserror.
> I was able to reproduce the problem with a simple case.
> I created a new database with Sybase Central with everything
> on default and put 2 procedures on them:
>
> create procedure test1()
> as
> begin
> raiserror 60000
> end
>
> create procedure test2()
> as
> begin
> exec test1
> select @@error
> end
>
> After executing test2, @@error will not reset to 0. I can
> execute the statement "select @@error" as many times as I
> want, it will return the value 60000.
>
> I tested this on a XP SP2 machine with the ASA builds 3044,
> 3137 and 3169.
>
> Is this a known issue? And is there a workaround for the
> problem?
>
> Regards,
> Wytze.

Wytze

2005-08-25, 7:23 am

Hello Oliver,

In your example there is no resultset after a raiserror
within a procedure or nested procedures. And that is what
seems to be the problem.
Maybe though you meant execution of test2 instead of test1.
But then execution of another statement does not change the
value of @@error.

We have done some more testing. We also could reproduce the
problem with ASA 6 (the version we are currently using).
When procedure test2 is executed and there is no
resume-command executed when the resultset is shown, @@error
will get stuck on 60000 too. Only when a resume-command is
executed, @@error will return to 0, when a select @@error is
executed for the second time.
Now, when running ASA 9 and using the DBISQL 9-client it is
not possible to execute a resume-command anymore. When
connecting tot ASA 9 with the DBISQL 6-client, it is
possible to execute a resume-command, but that makes no
difference. @@error will get stuck anyway. I don't know if
this has anything to do with the cause of the problem, but I
thought it was worth mentioning.

To deal with the problem, we are thinking about changing the
errorhandling to a construction like this:

create procedure test1
as
begin
select 60000
raiserror 60000
return 60000
end

create procedure test2
as
begin
declare @error int
declare @status int
execute @status = test1
select @error = @@error
if @status <> 0
return @status
select @error
return @error
end

When a raiserror is executed in test1 the number of
resultsets will not match the expected number, but that will
not be a problem for us.

Wytze.
[color=darkred]
> Try running this:
>
> exec test1;
> select @@error;
> select 1 from dummy;
> select @@error;
>
> Maybe the @@error should be reset after a successfull
> command.
>
> Oliver
>
> Wytze wrote:

Oliver Schulze L.

2005-08-25, 8:23 pm

Hi Wytze,
thanks for the comments.
some last notes:
- I think a user should use 90000 and up for raiserror
- why you don't use: raiserror 90001 'Error in my sp';
- I not that familiar with the @@error variable, HTH

Oliver

Wytze

2005-08-26, 7:23 am

Hello Oliver,

Why should only 90000 and up be used for raiserror? In the
docs I can only find, that values should be higher than
17000 and values between 17001 and 19999 are treated
differently than higher values.
By the way, using values of 90000 and up doesn't change
anything on the situation.

Wytze.

> Hi Wytze,
> thanks for the comments.
> some last notes:
> - I think a user should use 90000 and up for raiserror
> - why you don't use: raiserror 90001 'Error in my sp';
> - I not that familiar with the @@error variable, HTH
>
> Oliver

Breck Carter [TeamSybase]

2005-08-28, 11:23 am

Check out the options CONTINUE_AFTER_RAISE
RROR and ON_TSQL_RESUME.

Breck

On 19 Aug 2005 01:44:01 -0700, Wytze wrote:

>Hello,
>
>While testing our application on ASA 9.02, we run into the
>following problem.
>Under certain circumstances the global var @@error gets
>stuck on a value and will not reset to 0 when a new
>statement is executed.
>It seems to happen when a select is executed after a
>raiserror.
>I was able to reproduce the problem with a simple case.
>I created a new database with Sybase Central with everything
>on default and put 2 procedures on them:
>
>create procedure test1()
>as
>begin
> raiserror 60000
>end
>
>create procedure test2()
>as
>begin
> exec test1
> select @@error
>end
>
>After executing test2, @@error will not reset to 0. I can
>execute the statement "select @@error" as many times as I
>want, it will return the value 60000.
>
>I tested this on a XP SP2 machine with the ASA builds 3044,
>3137 and 3169.
>
>Is this a known issue? And is there a workaround for the
>problem?
>
>Regards,
>Wytze.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links