Re: When is SqlState reset?

Thanks for the reactions, I looked up documentation in the SQL reference.

It states:

Considerations for SQLSTATE and SQLCODE SQL variables: When the first SQL-procedure-statement in
the IF statement is executed, the SQLSTATE and SQLCODE SQL variables reflect the result of evaluating
the search-conditions of that IF statement. If an IF statement does not include an ELSE clause and
none of the search-conditions evaluate to true, then when the statement that follows the IF statement is
executed, the SQLSTATE and SQLCODE SQL variables reflect the result of evaluating the search conditions
of that IF statement.

As I read it, it doesn't say that the IF itself is changing the value of SQLSTATE, but that's what Javier found. I changed my production code to save the SqlState value in a seperate variable, but it still seems weird to me.

I'll get in touch with IBM about the documentation


Kind regards


Martijn van Breden

lead software architect




My understanding is that SQLCODE and SQLSTATE are set to the result
after each SQL statement, which probably means reset at the start of
each SQL statement. Since this code is SQl procedural language, it looks
like IF is an SQL statement.

I just googled this and ran across and SAP question about it - seems
there might have been a change in behavior there, and a change to what
is really the SQL standard.

Please remember that many things in SQL are the same in different
implementations, but I am not saying that SAP should be used
conclusively to determine what is going on with IBM i - this was just an
interesting similarity.

--

*Regards*

*Vern Hamberg*

<cid:part1.VByWODzW.Wwn8nQpF@centurylink.net>

On 2/24/2025 9:09 AM, Javier Sanchez wrote:

Interesting. And yes! Weird.
I replicated your code and it's like you say.
I debugged the procedure using the "C" source code, and I could see that,
YES it changes the value of SQLSTATE to '00000' just after your first "if",
it does it using this:
memset(SQLSTATE, '0', 5);
Then it immediately also resets SQLCODE to zero.

Additionally, when you declared the SQLSTATE variable, it actually did not
set it to blanks as your default clause specified. I inspected it and it
rather initialized it with the value '00000'.

This explains the "weird" behavior.

HTH

JS


El lun, 24 feb 2025 a las 8:07, Martijn van Breden (<
m.vanbreden@xxxxxxxxxxxxxxxxxxxxxxxxxx>) escribió:

Hi all

I ran into some peculiar piece of code that didn't do what I expected and
decided to run a small test.
The goal is to see if SqlState is being reset in an if-statement. My human
brain says it shouldn't be reset in the example code below

begin
declare sqlState char(5) default '';
declare myLocalVar char(1) default 'X';
select IBMREQD into myLocalVar from sysibm.sysdummy1 where 0 = 1;
if SqlState = '02000' then
if SqlState = '00000' then
call systools.lprintf ('2:' || SqlState || myLocalVar);
end if;
end if;
end;

SqlState and myLocalVar are declared
I select IBMREQD from sysdummy1 with a where condition that assures it's
not found (the value in sysdummy1 is 'Y'). My SqlState is therefore 02000,
I checked that. So, my subsequent if-statement is then true.
In my mind the inner if-statement can't be true if the outer is, so it
should never produce an entry in the joblog.

However, it does give "2:00000X" in the joblog.
I'm aware that the call to lprintf will alter the SqlState value, but I
only expect a new value for SqlState after the call.

It seems that the if statement itself modifies SqlState or am I
overlooking something? It just seems weird...


Kind regards,



Martijn van Breden

lead software architect




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.