Re: When is SqlState reset?

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.