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

Hi Javier,

I don't think we have a bug here.

The evaluation of an expression in SQL is something, that can result in an error.

Think of an SQL SELECT statement, where you do a "division by zero" in the SELECT clause. You will receive a SQLSTATE of '01564' - this is the result of the failed evaluation of the expression - not the result of the SELECT. The SELECT only passes this SQLSTATE back to the caller.

The evaluation of an expression sets the SQLCODE - this is only consequential from SQL's point-of-view.

IMHO - this all works as intended - only because the intention does not align with our reception, doesn't make it a bug.

HTH
Daniel

Am 27.02.2025 um 19:20 schrieb Javier Sanchez <javiersanchezbarquero@xxxxxxxxx>:

What still makes it "weird", this behavior in SQL P/L. For some reason IBM
decided to let the C preprocessor code it like that, but using our logic
discussed here, it should be "a big bug". Good to know to rather use save
variables.

JS

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

Absolutely right!

If that is coded as embedded SQL in RPG, you would simply code the IF as
RPG - and the RPG compiler generates code to compare the (RPG) variable
SQLSTATE without evaluating it in SQL.

So indeed the "same" code would work as RPG with embedded SQL.

Regards,
Daniel

Am 27.02.2025 um 18:34 schrieb Javier Sanchez <

javiersanchezbarquero@xxxxxxxxx>:

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.

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