Re: When is SqlState reset? -- MIDRANGE-L

Hi,

After reading the docs completely, it seems to be very clear. The page in InfoCenter is: https://www.ibm.com/docs/en/i/7.5?topic=pl-if-statement

And Martijn you already quoted the right part:

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

The SQLSTATE reflect the result of "evaluating the search conditions" - so evaluating

SqlState = '02000'

as an logical expression, results in an SQLSTATE = '00000' - which seems absolutely plausible.

Birgitta you are absolutely right - the (SQL-)IF is translated to a C-IF - but the expression is still evaluated by SQL - and that evaluation could go wrong (like with an unknown variable or something) and therefore could also result in a SQLSTATE <> '00000'.

So I was wrong - it's not the IF statement itself, that sets the SQLSTATE - but the evaluation of the (logical) expression does.

HTH
Daniel

Am 27.02.2025 um 16:33 schrieb Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>:

SQL code is converted into C-Code with embedded SQL ... and only the
embedded SQL Statements produce SQLCODE and SQLSTATE.
There is a native C command for an IF, so the IF-Statement is converted in
the C-IF ... which is NO longer an SQL statement and consequently does not
produce/change the SQLCODE and/or SQLSTATE.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Martijn van Breden
Sent: Thursday, 27 February 2025 16:18
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 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.

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

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