|
Problem: We are upgrading our SA-11 database to SA-17 and are receiving many errors in our stored procedures. We have tracked it down to what looks like a behavior change with SQLCODE / SQLSTATE, specifically after an IF statement is used. Reproducible Example: When connected to a SQL Anywhere 11 database, we receive a SQLCODE of 100 after the following stored procedure call, but after upgrading to a SQL Anywhere 17 database, we receive a SQLCODE of 0. create procedure sqlcode_test() begin declare @dummy integer; // this statement sets SQLCODE to 100 select 1 into @dummy where 1 = 2; // this IF statement seems to chagne the SQLCODE from 100 to 0 in SA-17 but does NOT in SA-11 if @dummy <> 1 then return end if; raiserror 20001 'SQLCODE: ' + cast(sqlcode as varchar); return end |
|
The good news is, you are correct: In SQL Anywhere 17 your IF statement resets SQLCODE to 0, whereas your IF does not reset SQLCODE in earlier versions all the way up to 16. The bad news is, the new behavior agrees with the documentation: "SQLCODE indicates the disposition of the most recently executed SQL statement." That's what it says in the V11 Help and the V17 Help. It said the same thing for SQL Anywhere Version 6: "The SQLCODE value is set after each statement." In other words, it's a bug fix in Version 17. There's more bad news: The fact SQLCODE is not reliably persistent has been fairly well known ( ok, somewhat well known :) for a long time. I can remember an SQL Anywhere engineer telling me to always "SELECT SQLCODE INTO @sqlcode" immediately after any interesting statement, then check @sqlcode afterwards... never assume SQLCODE is persistent. FWIW here's a couple of excerpts from the SQL Anywhere Studio 9 Developer's Guide from 2004... SQLCODE returns an INTEGER containing the numeric warning or error
code from the previous statement (e.g., 100 for “row not found” and -306 for
“deadlock detected”).
-----
Here is an example of an EXCEPTION handler that can be used inside a
BEGIN block to turn exceptions into RAISERROR messages; this code has the
advantage that the original SQLCODE, SQLSTATE, and ERRORMSG() values
are all preserved inside the error message passed back to the client application:
CREATE PROCEDURE p1()
RESULT (
key_1 INTEGER,
non_key_1 VARCHAR ( 100 ) )
BEGIN
DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );
DECLARE error_99001 EXCEPTION FOR SQLSTATE '99001';
SIGNAL error_99001;
EXCEPTION
WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
RAISERROR 99999
'SQLCODE = %1!, SQLSTATE = %2!, ERRORMSG() = %3!',
@sqlcode, @sqlstate, @errormsg;
END;
You can download the the book here: http://www.risingroad.com/foxhound/book/W_P_SQL_Anywhere_Studio_9_Developers_Guide.pdf
"SQLCODE indicates the disposition of the most recently executed SQL statement" - but is IF an SQL statement?
(14 Jul '21, 05:57)
Dmitri
Replies hidden
It's in the list for Version 17: Alphabetical List of SQL Statements It was also in the list for ASA 6, and that version of the Help said "The SQLCODE value is set after each statement. You can check the SQLCODE to see whether or not the statement succeeded." That was a lie, of course, so some people (like me) conjured up stories to tell ourselves about "DML and DDL statements" versus "control statements" and how SQLCODE must apply to some statements and not others. Because that's how it ... sort of ... did ... behave... until now :) But wait, it gets worse! Did you know that "SQLSTATE is the preferred status indicator for the outcome of a SQL statement"? That's what the Help has been telling us since SQL Anywhere 10 gave us this news: 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. No, I'm not going to stop using SQLCODE any time soon ...so yes, you can say this... :}
(14 Jul '21, 07:23)
Breck Carter
That's what I'm also used to (or an according SET assignment). Although I never seemed to care what would happen with SQLCODE when that assignment itself would fail...
(14 Jul '21, 08:57)
Volker Barth
Replies hidden
1
Experimentation shows that (at least for now) a successful SET statement does NOT cause SQLCODE to be reset to zero, so (at least for now) you can safely use three SET statements to save into @sqlcode, @sqlstate and @errormsg. ...as opposed to one SELECT SQLCODE, SQLSTATE, ERRORMSG() INTO @sqlcode, @sqlstate, @errormsg; Experimentation also shows that SQL Anywhere 17 may have added IF to the list of statements that reset SQLCODE, but it didn't add SET. So the bug still exists :)
(14 Jul '21, 11:04)
Breck Carter
FWIW if @chaddnolen wants to call the new behavior a bug, I won't argue. SQLCODE has behaved badly for over two decades, there's no reason apply a half-baked "fix" now, especially if it breaks legacy code. ...betcha didn't see that coming :)
(14 Jul '21, 11:09)
Breck Carter
|
|
You can capture and store the SQLCODE immediately after the operation that sets it, then use this stored value for further logic or error messages. This will ensure consistent behavior across different database versions by manually managing the SQLCODE value. |
Wild guess: That could also be due to optimization, say because the v17 optimizer may treat the condition 1 = 2 differently. You could check via request level logging whether the statements are treated differently.
If you comment out the IF statement, SQLCODE returns 100, which is what I would expect from the 1=2 statement, so I'm leaning towards the IF statement as doing something with SQLCODE. Just in case, I'll check optimizer settings - thank you!
Hm, I didn't mean the optimizer settings are different but the optimizer itself might work differently, but as stated, that's just a wild guess, and RLL should reveal if there is a difference...
> RLL should reveal if there is a difference
Not for statements inside a stored procedure.
Well, with options PROCEDURES or All, statements within procedures should get logged, as well.
You are absolutely correct! ( forgetting stuff is bad enough, remembering wrong stuff is much worse :)