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

Right Vern!
What is there is how it is.
If you know this, take your own precautions and save the variables.

JS

El jue, 27 feb 2025 a las 14:45, Vern Hamberg via MIDRANGE-L (<
midrange-l@xxxxxxxxxxxxxxxxxx>) escribió:

Hi Javier

I think the implementation makes sense - it might have been a matter of
developer's or standards author's choice whether to set SQLSTATE (and
SQLCODE) to defaults (reset) before an SQL statement is processed or to
leave them at the value they had after the previous statement, then set
to defaults if there is no error. I've not read the SQL standards, so I
don't know what is said there. And I prefer what is happening now, as
one of these posts remarked is happening in the C++ code.

Nonetheless, we are told to save these return codes immediately after
any SQL statement has completed, so that we can use them to condition
further actions.

I used the term *SQL statement* above - I looked online and see that
many places refer to *IF statements*. So I'd need to see what the
standards call it.

--

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:part1.TW2QR0wS.ZQv1LuHs@centurylink.net> CAAC
(COMMON Americas Advisory Council) IBM Influencer 2023

On 2/27/2025 1:39 PM, Javier Sanchez wrote:

From my point of view, the code should not reset the value of SQLSTATE
before you evaluate another SQL statement, and what the C source code
version does is that: it first sets the value of SQLSTATE to '00000' just
before you execute your second IF statement, making it a true condition

and

not respecting the previous value of the last SQL statement. That's what
I'm saying, to me that is bad logic. But, that's how it is and we
already know how to get over it.

JS

El jue, 27 feb 2025 a las 13:29, Daniel Gross (<daniel@xxxxxxxx>)

escribió:

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

messageemail:MIDRANGE-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
oremail:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.
Pleasecontactsupport@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 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 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
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
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
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
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
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
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.