cancel
Showing results for 
Search instead for 
Did you mean: 

What does an EXCEPTION for SQLCODE = 0 mean?

Breck_Carter
Participant
14,346

I have an application using SQL Anywhere 11.0.1.2276 where almost every block of SQL has an EXCEPTION handler based on the template shown below.

Quite often I see exceptions being thrown where SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = [empty]. By "quite often", I mean a few dozen per day, from a wide variety of code blocks, when these blocks of code are being executed many million times per day. I have seen this behavior for years (literally), and have added code in many EXCEPTION handlers to ignore it, but I'm ( finally 🙂 getting annoyed.

What does EXCEPTION for SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = [empty] mean? ...besides (no message) I mean 🙂

What is the cause and/or explanation for such a pointless exception?

BEGIN

DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );

-- other code

EXCEPTION

WHEN OTHERS THEN

SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;

-- code to handle and/or record exception

END;


Added 2011-12-17...

Apparently, if you have an HTML service procedure like this...

CREATE SERVICE demo_schedule 
   TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL demo_schedule();
...
CREATE PROCEDURE demo_schedule()
RESULT ( html_string LONG VARCHAR )
BEGIN

which generates HTML code containing a button which can re-launch the service like this

function submitF ( val ) { 
   document.f.action = "demo_schedule?val=" + val; 
   document.f.submit(); 
   return false; 
} 
...
<form name="f" method="POST"> 
...
<input onclick="return submitF ( 'Save' );" type="SUBMIT" value="Save">

then you can see lots of these exceptions if you pound on the "Save" button repeatedly; some of them are the SQLCODE = 0 case, and some are the true SQLCODE = -299:

DIAG 2011-12-17 11:48:50.042 demo_schedule called...
DIAG 2011-12-17 11:48:50.266 demo_schedule called...
DIAG 2011-12-17 11:48:50.323 input val = "Save"
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.338: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.357: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.375: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.394: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.408: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.419: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.430: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_copy_day_to_period_schedule at 2011-12-17 11:48:50.437: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.448: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.456: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.465: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.474: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.484: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.494: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.502: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_copy_day_to_period_schedule at 2011-12-17 11:48:50.512: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.520: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.528: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
DIAG 2011-12-17 11:48:50.530 input val = "Save"
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.537: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.555: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.565: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.575: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.583: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_copy_day_to_period_schedule at 2011-12-17 11:48:50.590: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
EXCEPTION in demo_schedule at 2011-12-17 11:48:50.598: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
EXCEPTION in rroad_copy_day_to_period_schedule at 2011-12-17 11:48:50.728: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
DIAG 2011-12-17 11:48:50.734 demo_schedule called...
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.737: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.755: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.764: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.773: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.781: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.790: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.800: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_copy_day_to_period_schedule at 2011-12-17 11:48:50.810: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.818: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.828: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.835: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.843: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.853: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.863: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_store_HTTP_schedule_day_string at 2011-12-17 11:48:50.870: SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() = 
EXCEPTION in rroad_copy_day_to_period_schedule at 2011-12-17 11:48:50.878: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
EXCEPTION in demo_schedule at 2011-12-17 11:48:50.886: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user

...which leads me to wonder "how does one prevent this kind of insanity"? (never mind the fix to display -299 instead of zero).

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

This issue should be fixed as QTS 685149. The fix will be available in build 12.0.1.3476 or higher. Note that although the SQLCODE was reported incorrectly, the correct handler for the actual SQLCODE was invoked.

Breck_Carter
Participant

Since I have no intention of upgrading any time soon, please give me some more information about this issue.

Is it one single SQLCODE being incorrectly reported, and if so, which one?

If it is multiple SQLCODEs being incorrectly reported, is there some common underlying reason?

Thanks!

Former Member
0 Kudos

It's the SQLCODE -299 that is not reported correctly, and the error message is "Statement interrupted by user". As far as I can see, this is the only such SQLCODE.

VolkerBarth
Contributor
0 Kudos

@Elmi: So this is the exact error situation Ivan has discovered (see his answer). Or are there other cases where this might occur?

Former Member

This is the exact error situation Ivan has discovered, and I do not see any other error code that could be hidden in the same way.

Answers (2)

Answers (2)

Former Member

I have found an example where this SQLCODE=0 exception is detected. If a web service is executing and the browser connection terminates (for example, the user presses the Cancel or Stop button on the browser), then the procedural code that is executing will be cancelled with SQLCODE=0.

I have registered this as a possible bug.

For example, create the following web service, access http://localhost.../zero_error and then cancel the browser request.

create or replace procedure dba.P_zero_error()
begin
    declare @x  long varchar = repeat('x',1024*1024);
    message 'starting loop: ',now();
    loop
        set @x = replace(@x,'x','x');
    end loop;

exception when others then
message 'Error: SQLCODE=',SQLCODE,' errormsg=[',errormsg(),'] traceback=[',traceback(),'] ',now();  
end;

create service [zero_error]
type 'raw'
authorization off
user dba
as call dba.P_zero_error();

The server console window shows the following text for me:

starting loop: 2011-09-22T04:40:10.052000Z
Error: SQLCODE=0 errormsg=[] traceback=[loop
  set @x = replace(@x,'x','x')
end loop
] 2011-09-22T04:40:28.993000Z
VolkerBarth
Contributor
0 Kudos

What would be the expected error code for such cases?

Former Member

I was expecting to see -299 INTERRUPTED "Statement interrupted by user". It is not my area of the product, though, so I'm not certain if the code 0 is expected in this case (in which case the documentation will be updated).

MCMartin
Participant
0 Kudos

Maybe it is the result of a Resignal. If an Exception is thrown and handled and then rethrown, the documentations says that the SQLSTATE is reset to zero if the Exception handler contains more code than just the Resignal statement. So the Exception handler handling the resignaled exception is not getting any error details anymore.

See "Using exception handlers in procedures and triggers" in the documentation.

Breck_Carter
Participant
0 Kudos

I don't think so. SQLSTATE being set to zero is what happens if the EXCEPTION handler does NOT execute a RESIGNAL, as shown in the example here http://dcx.sybase.com/index.html#1101en/dbusage_en11/pteweh.html ...but you had me going, I have a LOT of RESIGNAL statements ...if RESIGNAL set the SQLSTATE to zero, why would it be called "RESIGNAL"? 🙂

Breck_Carter
Participant
0 Kudos

PS... the example in the Help is poorly coded, the RESIGNAL is not indented properly. It is executed only by the WHEN OTHERS, which is NOT the case for the example output.

MCMartin
Participant

Maybe you can include a SELECT TRACEBACK(); into your logging to narrow down from where the unexpected exception arises?

reimer_pods
Participant

@Martin: that's a good one, I didn't know something like that existed. Always leaning something here, great thing!

Breck_Carter
Participant
0 Kudos

@Martin: In the application described here, every block has an EXCEPTION handler, so I know exactly where the exception is coming from (within a few statements)... a traceback is of no additional help. I don't need to know where the exception is coming from, it is a meaningless exception... I need to know WHY it is disrupting flow of control.