on 2010 Oct 02 8:04 PM
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;
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).
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
@Elmi: So this is the exact error situation Ivan has discovered (see his answer). Or are there other cases where this might occur?
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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).
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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"? 🙂
Maybe you can include a SELECT TRACEBACK(); into your logging to narrow down from where the unexpected exception arises?
@Martin: that's a good one, I didn't know something like that existed. Always leaning something here, great thing!
@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.
User | Count |
---|---|
87 | |
9 | |
9 | |
8 | |
6 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.