Re: When is SqlState reset?

Hi Peter

Thank you very much!

I believe that IF is the SQL statement and the comparison is a known as a condition expression, it's not a statement, so far as I know from various sources.

Here's the original test code again -

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
callsystools <https://archive.midrange.com/midrange-l/202502/msg00318.html#>.lprintf ('2:' || SqlState || myLocalVar);
end if;
end if;
end;

We are told that if we want to use the value of SQLSTATE, we must assign it to a separate variable. In RPG, the SQLSTATE variable is declared automatically, while in SQL-PL, one has to declare it, I believe.

So here, after the SELECT failed to return a row (internally), SQLSTATE was set to '02000'. There should have been another variable declared, such as mySqlState, and that set to sqlState, then use this IF statement -
   If mySqlState = '00000'
should have been used as the internal IF statement, and the test would have failed, hence, nothing would have been printed.

In RPG, there is no concept of setting some state value after an expression is done, at least that we care about. So using the IF opcode has no impact as the IF statement does in SQL.