Re: When is SqlState reset?

Javier,

you are mixing things up a bit.

You are trying to interpret the C-Source as something, that is wrong, or is doing things, that you don't want. But that intermediate C-Source is only Db2 for i's way to implement what the original SQL/PL statements should do - and C is just the implementation language, as Db2 for i has no own SQL/PL interpreter or compiler.

If you want to understand what happens, you have to look at the SQL/PL statements, NOT at the intermediate C-Source.

The docs for the SQL/PL statement "IF" are very clear. The statement does "set" the SQLSTATE to the SQLSTATE "result" of the expression that it evaluates.

Please re-read it: https://www.ibm.com/docs/en/i/7.5?topic=pl-if-statement

Here a short quote of the relevant part:

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.

It is the first sentence after the colon, that gives you all the information that you need. After the (SQL/PL) IF statement the SQLSTATE and SQLCODE reflect the result of the evaluation of the "search conditions" (the expression) of the IF statement.

So an expression that evaluates without error, always leaves an SQLSTATE of "00000". And the intermediate C-Source reflects exactly that.

But I hear you asking "Why?" - well, think of a SQL function call inside the IF expression - or an arithmetic calculation (like a division) that can result in an SQLSTATE which might not be "00000".

If you don't want to have the SQLSTATE set during the evaluation of the IF-expression, you have to implement your procedure in C, COBOL or RPG with embedded SQL - you won't have this problem here. And you have that option, because Db2 for i allows externally written procedures and functions in other languages with and without embedded SQL. And it uses this feature to implement its own SQL/PL via intermediate C-Source.

Nobody would code that in embedded SQL like that. But we are in SQL/PL - that IF statement is NOT a simple C IF statement. It sure looks like that in the intermediate C-Source, but it has to implement the SQL/PL behavior according to the docs.

And yes - you are right - this discussion runs long enough. The results from my personal point of view are:

a) there is not bug

b) SQL/PL IF works according to the docs

c) the behavior of the SQL/PL IF statement in regards of SQLSTATE and SQLCODE sure is somehow surprising for most programmers, who are used to embedded SQL but not to SQL/PL

HTH and kind regards,
Daniel

P.S.: it wasn't me, who originally wrote the procedure - I haven't even re-created the procedure or read the intermediate C-Source, as it is all written in the documentation.

Am 01.03.2025 um 23:04 schrieb Javier Sanchez <javiersanchezbarquero@xxxxxxxxx>:

Ladies and Gentlemen here in this thread:
If you could please just do the same exercise as I did, go write and create
Daniel's procedure. SET option debug view to *source.
Then prepare to debug it, and execute it and run step by step.
1. When the precompiler created the C language code for this procedure,
please stare at the point where the first IF statement is going to be
executed, not even when it is executed. You will see that the C code set it
to '00000', even though the DECLARE statement specified a default of
blanks. For me, this is against my will, because I am telling the language
to set it to blanks, not to '00000', but the code anyway does it.
2. When it executes "the query" that yields an SQLSTATE value of '02000',
it correctly reads this value and compares it, but just as it does that,
it, without "my permission or will", changes it to '00000'. Then, when it
executes the second IF statement, it finds it with the value '00000' and
this yields a TRUE condition which before my eyes is NOT CORRECT!.

Until you try and see this, I believe this discussion will still generate
more emails that are unnecessary.
Focus on the original code that it generates, then allow yourself to a
logical conclusion that it is not correct.

WADR
JS

El vie, 28 feb 2025 a las 12:07, Peter Dow (<petercdow@xxxxxxxxx>) escribió:

Hi Vern,

Btw, congratulations on your new job!

One of the problems with all this SqlState discussion is what is an "SQL
statement" in SQL P/L?

In Martijn van Breden's original code,

if SqlState = '02000' then

the logical expression (SQLSTATE = '02000') is apparently considered an
SQL statement.

If the logical expression was (MyVar1 = MyVar2), would that be an SQL
statement that changes SQLSTATE?

--
*Peter Dow* /
909 793-9050
petercdow@xxxxxxxxx
/

On 2/28/2025 5:49 AM, Vern Hamberg via MIDRANGE-L wrote:
Hi Daniel

The term "return code" is used in the SQL reference to describe what
SQLSTATE and SQLCODE are -for example, from the manual at
https://www.ibm.com/docs/en/ssw_ibm_i_75/pdf/rzalapdf.pdf

An SQLCODE is a return code. The return code is sent by the database
manager after completion of each SQL statement.

An SQLSTATE value is a return code that indicates the outcome of the
most recently executed SQL statement.

And in the SQL Reference is this interesting sentence -

When an SQL statement other than GET DIAGNOSTICS or
compound-statement is processed, the current diagnostics area is
cleared, before processing the SQL statement.

Is a 2nd execution of GET DIAGNOSTICS going to give the same reported
values? It doesn't clear the diagnostics area, so I expect it should
still give the same information.

We might be saying the same thing in different ways, as someone says,
agreeing violently!

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