|
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 firstSQL-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>:
create
Ladies and Gentlemen here in this thread:
If you could please just do the same exercise as I did, go write and
Daniel's procedure. SET option debug view to *source.it
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
to '00000', even though the DECLARE statement specified a default oflanguage
blanks. For me, this is against my will, because I am telling the
to set it to blanks, not to '00000', but the code anyway does it.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
executes the second IF statement, it finds it with the value '00000' andescribió:
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>)
list
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
relatedTo 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
listquestions.--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxxrelated questions.
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
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
