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

asked 02 Oct '10, 19:04

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

edited 17 Dec '11, 12:06

1

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

(02 Oct '10, 22:21) Volker Barth

@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 :)

(03 Oct '10, 04:57) Breck Carter
Comment Text Removed

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

(03 Oct '10, 10:43) Volker Barth

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

(03 Oct '10, 15:51) Breck Carter

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

(04 Oct '10, 08:28) Volker Barth

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???

(01 Dec '10, 19:27) elriba
Comment Text Removed
Comment Text Removed
Comment Text Removed

@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?"

(02 Dec '10, 12:31) Breck Carter

Do you have a self-contained repro, Breck?

(06 Dec '10, 19:34) Glenn Paulley

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

(03 Jan '11, 10:45) Breck Carter
More comments hidden
showing 4 of 9 show all flat view

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.

permanent link

answered 14 Oct '11, 11:52

Elmi%20Eflov's gravatar image

Elmi Eflov
7811014
accept rate: 31%

edited 17 Oct '11, 11:47

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676

1

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!

(16 Oct '11, 05:00) Breck Carter
Replies hidden

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.

(17 Oct '11, 11:16) Elmi Eflov

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

(17 Oct '11, 11:47) Volker Barth
2

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.

(17 Oct '11, 11:56) Elmi Eflov

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
permanent link

answered 22 Sep '11, 04:52

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

What would be the expected error code for such cases?

(22 Sep '11, 05:32) Volker Barth
1

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

(22 Sep '11, 07:29) Ivan T. Bowman

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.

permanent link

answered 03 Dec '10, 12:07

Martin's gravatar image

Martin
8.6k119151237
accept rate: 14%

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"? :)

(03 Dec '10, 12:46) 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.

(03 Dec '10, 12:47) Breck Carter
3

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

(03 Dec '10, 17:34) Martin
1

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

(17 Dec '10, 17:40) Reimer Pods

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

(03 Jan '11, 10:40) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×102

question asked: 02 Oct '10, 19:04

question was seen: 7,938 times

last updated: 17 Dec '11, 12:06