SQLA 16.0.0.1915 I have a select statement in a Stored Proc that truly encounters an error: the SELECT INTO returned two rows. My error code (IF sqlcode < 0) executes, but it reports sqlcode=0 and sqlstate=00000. I used the Sybase Central debugger to stop after the SET ls_debug_info... to make sure it wasn't affected by a following line of code. I hope it is a simple mistake, but I am baffled.
|
EDIT: From the docs (somewhat hidden in the topic "Default handling of warnings"):
Note: An initial "SET mySQLCODE = SQLCODE;" statemement before the IF block will store that value but will reset SQLSTATE to 00000, too, so you may need a SELECT INTO statement to temporarily store both SQLCODE and SQLSTATE... |
FWIW, in case your code must not be compatible with v12 or below, you might use the new v16 functions like ERROR_SQLCODE() and ERROR_SQLSTATE() together with TRY-CATCH blocks. AFAIK, these functions make the "triggering" error available even after further SQL statements have been called. EDIT: The v16 docs are not really clear here, but ERROR_SQLCODE() and the like seem to work with "classic" Watcom-SQL exception handlers, too, cf. this small sample:
This does return:
Apparently, the IF statement will reset SQLCODE and SQLSTATE here but the according functions still give access to the triggering error. Is it just me who would like a blog article by the SQL Anywhere team on such important improvements? Even for an "Invisible database" enhancements like those should be made visible:)
(03 Jul '14, 05:35)
Volker Barth
Replies hidden
1
See section 9. TRY CATCH And Friends in Top 10 Cool New Features in SAP Sybase SQL Anywhere 16.
(03 Jul '14, 10:31)
Breck Carter
Well I am glad it is a simplistic and obvious problem. I am not so glad to realize I have a LOT of code that still looks like that! I started using the TRY-CATCH some time ago after Breck pointed out the advantages and gave a sample in reply to another problem I had. But I now see I have a lot of old code that isn't actually going to work if there is ever an exception. Live and learn. The advantage of having used a keypunch and punched tape are that I have had time to "learn" a lot :).
(03 Jul '14, 10:45)
Bill Aumen
1
Sure enough I had looked into that valuable document before I posted my suggestion here - nevertheless even that really profound section does not explicitly tell that ERROR_SQLCODE() will conserve the SQLCODE over statement-boundaries... But I don't want to sound picky, I'm happy that you keep blogging... If the SQL Anywhere team would do so at least now and then (besides Jason's appreciated "From Glenn's Archives"), it would be nice.
(03 Jul '14, 12:00)
Volker Barth
I am MUCH happier now since I have discovered my most frequent code actually does work: IF sqlcode <> 0 THEN RAISERROR 18420 '18420 trigger prreg_pay_register_bu, sqlcode = ' || STRING(sqlcode); END IF; This is the code I originally developed and tested, so I should have stuck with it and not tried to get fancier :). So... more searching for the code that doesn't work. And more use of TRY-CATCH.
(03 Jul '14, 17:01)
Bill Aumen
Replies hidden
Hm, are you sure the IF statement does not reset the SQLCODE to 0 here? In my tests (see above) the IF statement had had that undesired effect... Re-tested with 12.0.1.3324: begin declare nTest int = 0; set nTest = 1 / 0; exception when others then if SQLCODE < 0 then message 'SQLCODE = ' || SQLCODE || ', SQLSTATE = ' || SQLSTATE || '.' to client; end if; end; returns "SQLCODE = 0, SQLSTATE = 00000." whereas begin declare nTest int = 0; set nTest = 1 / 0; exception when others then message 'SQLCODE = ' || SQLCODE || ', SQLSTATE = ' || SQLSTATE || '.' to client; end; returns "SQLCODE = -628, SQLSTATE = 22012". The displayed SQLCODE differs in a similar way when using RAISERROR instead of MESSAGE.
(03 Jul '14, 17:42)
Volker Barth
FWIW the example in Bill's original question doesn't use EXCEPTION, it just tests SQLCODE after a SELECT.
(04 Jul '14, 07:24)
Breck Carter
Yes, I'm aware of that. However, even that does behave in the way described in my own tests: -- Forcing a -186 error ("Subquery cannot return more than one row") select * from systable where table_id = (select table_id from systable); if SQLCODE <> 0 then message 'Error: ' || SQLCODE to client; end if; returns "Error: 0" as well. Or is there any particular T-SQL feature at work in Bill's procedure?
(04 Jul '14, 07:58)
Volker Barth
Dunno about T-SQL, but this nugget throws cold water on the whole discussion: "SQLCODE was deprecated in the ANSI SQL/1992 standard, and was eliminated entirely from SQL/1999. SQLCODE values continue to be maintained in SQL Anywhere for backward compatibility for applications. SQLSTATE is the preferred status indicator."
(04 Jul '14, 09:18)
Breck Carter
Well, I still prefer SQLCODE over those SQLSTATE strings (where one would have to test "IF SQLSTATE NOT LIKE '00___'" instead of "IF SQLCODE <> 0") - and how would one distinguish between errors and warnings? But besides that: The discussion itself does both apply to SQLCODE and SQLSTATE - both are reset by the next statement, so that does not really cool down the current issue. (Not cooling down itself is fitting, we're just awaiting the WIFA World Cup quarter-final between France and Germany...)
(04 Jul '14, 09:46)
Volker Barth
Hmmm. The one difference I see between my code that did work and your example is that I used RAISERROR instead of MESSAGE??? In my app, I check the results of the update, and this is how it looked back in my PowerBuilder app. That's SQLdbCode and SQLErrtext: Database error code: -18415 SQLSTATE = S1000 [Sybase][ODBC Driver][SQL Anywhere]RAISERROR executed: 18415 trigger prreg_pay_register_bu, sqlcode = 100
(04 Jul '14, 11:12)
Bill Aumen
Replies hidden
FWIW, as stated, I have tested with RAISERROR as well... But nevermind, if it does work in your case, well then it's obviously alright with you:)
(05 Jul '14, 04:41)
Volker Barth
Comment Text Removed
Comment Text Removed
Here is what I think might be the difference. Foxhound reminds me of a non-default option setting I use: SET OPTION PUBLIC.continue_after_raiserror = 'OFF'; -- default 'On'
(07 Jul '14, 11:40)
Bill Aumen
|