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

asked 12 Jul, 09:29

chaddnolen's gravatar image

chaddnolen
15
accept rate: 0%

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.

(13 Jul, 04:12) Volker Barth
Replies hidden
Comment Text Removed

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!

(13 Jul, 07:59) chaddnolen

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

(13 Jul, 08:23) Volker Barth

> RLL should reveal if there is a difference

Not for statements inside a stored procedure.

(13 Jul, 09:26) Breck Carter

Well, with options PROCEDURES or All, statements within procedures should get logged, as well.

(13 Jul, 13:44) Volker Barth

You are absolutely correct! ( forgetting stuff is bad enough, remembering wrong stuff is much worse :)

(13 Jul, 16:55) Breck Carter
showing 1 of 6 show all flat view

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

answered 13 Jul, 09:24

Breck%20Carter's gravatar image

Breck Carter
31.9k5147041031
accept rate: 20%

edited 13 Jul, 09:27

"SQLCODE indicates the disposition of the most recently executed SQL statement" - but is IF an SQL statement?

(14 Jul, 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, 07:23) Breck Carter

"SELECT SQLCODE INTO @sqlcode"

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, 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, 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, 11:09) 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:

×188
×143
×61

question asked: 12 Jul, 09:29

question was seen: 175 times

last updated: 14 Jul, 11:22