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

Maybe you didn't debug the C source listing, what I found is that the first
IF statement generates code to call DB2 with the query and is sets SQLSTATE
to what in internal variable used in a data structure for that call, which
at that moment is a NOT FOUND condition ('02000'), and just after comparing
the value against '02000' it unconditionally sets SQLSTATE to '00000' just
before the second IF statement. SQLSTATE is then '00000' which evaluates to
TRUE in the second IF.. And it then yet again sets it to '00000' after
evaluating it.

What you could expect with an SQLRPGLE embedded SQL program, may not be the
same though. Because the SQLSTATE and SQLCODE variables not only are
automatically created by the precompiler, but the generated RPG code does
not change the value of SQLSTATE "before" you use it again in another SQL
statement.

So your code maybe does work as you expect in an SQLRPGLE program with the
same SQL statements.
Test it, you may find this is true.

JS

El jue, 27 feb 2025 a las 10:04, Daniel Gross (<daniel@xxxxxxxx>) escribió:

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.

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