Re: When is SqlState reset?

SQLSTATE represents the state (success, error, warning) of the SQL "processor" - it is NOT a "return code" of an SQL statement. It can be treated like that - especially with embedded SQL as the application is giving away control to SQL and checks the state when it gets control back.

Every action - the execution of a statement, the evaluation of an expression, ... - can result in an SQLSTATE.

For control statements like IF or FOR they usually don't have a result state - but the expressions that they are evaluating have. If an expression evaluates without error or warning, the SQLSTATE has to be '00000' to reflect that.

With SQL we have to think "descriptive" - our traditional "procedural" or "imperative" thinking doesn't always match with SQLs architecture.

And yes - GET DIAGNOSTICS is also "returning" an SQLSTATE when executed.

HTH
Daniel

Am 27.02.2025 um 22:21 schrieb Vern Hamberg via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>:

Maybe I can answer my own question about whether *IF* is a statement - the table of contents of the *IBM i Db2 SQL Reference* calls it that in the SQL PL chapter - everything is a statement, *LOOP*, *FOR*, even *GET DIAGNOSTICS* - but I think that these return codes are not changed after that one.

--

*Regards*

*Vern Hamberg*

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

On 2/27/2025 2:44 PM, Vern Hamberg via MIDRANGE-L wrote:
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.

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