What does an EXCEPTION for SQLCODE = 0 mean?

[Breck_Carter]

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).

[VolkerBarth]

My suggestion, as 0 is often used to flag successful execution: It means "Exceptionally successful" - well, if I would get that message a few times a day, I wouldn't bother:)

[Breck_Carter]

@Volker: The point is this: at some point in the "other code" part of the template, the code STOPPED EXECUTING and immediately branched to the EXCEPTION handler. That's how exceptions work, at least AFAIK. It sure doesn't sound like "successful execution" to me 🙂

[VolkerBarth]

@Breck: I'm sure you got it - but for other readers: Beware - My comment above is free of any serious contents.

[Breck_Carter]

@Volker: I did not get it... lack of coffee, perhaps, or maybe my Astuteness Module needs servicing 🙂

[VolkerBarth]

@Breck: Wow, "astuteness" - another word to enter my (passive) vocabulary - Learning English via SQLA!

[Former Member]

Hi guys, I'm still lost on this one... Doesn't the "other code" stopped executing on the exception? What happened when SQLCODE = 0 on the exception???

[Breck_Carter]

@elriba: The WHEN OTHERS THEN takes complete control of handling the exception. If it is empty, for example, NOTHING FURTHER happens... the outer block terminates with no message. So, in one respect, yes, the outer block stops and exits. In another respect, there's no error message or other consequence UNLESS it is coded in the EXCEPTION handler. My question is "WHY does SQLCODE 0 raise an EXCEPTION at all?"

[Former Member]

Do you have a self-contained repro, Breck?

[Breck_Carter]

@Glenn: It is not a reproducible case, it occurs maybe once in every few million block executions... or billions. I have MANY exception blocks where I specifically check for and bypass SQLCODE = 0, which is a major PITA. I guess I am the only one who uses WHEN OTHERS in every block.

[Former Member · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

@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 · Accepted solution]

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.

[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]

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]

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]

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]

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]

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

[reimer_pods]

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

[Breck_Carter]

@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.