Re: Still more SQL date weirdness

On 01-Jun-2011 12:28 , James Lampert wrote:

I think I have error dates handled now:

Where @@@@@@@@@@ is a placeholder for the library, in the RPG
program that generates the SQL source member for the UDF, I now have
with the new lines marked):

DROP FUNCTION @@@@@@@@@@/DISPLAYDAT;
create function @@@@@@@@@@/DISPLAYDAT(i_date NUMERIC(8))
returns DATE
language sql deterministic not fenced set option datfmt=*ISO
begin
declare continue handler for sqlexception /**NEW**/
return date('99991231000000'); /**NEW**/

if i_date = 0 then return date('00010101000000'); end if;
return date(digits(i_date) concat '000000');
END

What I know about handlers wouldn't buy you a cup of coffee, so is
there any reason why this handler would create a problem? Am I
correct in my understanding that a handler's scope is confined to the
innermost BEGIN block in which it resides? That it can't leak out and
wreak havoc with exceptions elsewhere?

The SQLEXCEPTION handler is "heavy handed", encompassing of all exceptions. Thus that handler is generally a poor choice for a decision about the data, without knowing the actual details of the exception\diagnostics; much like using MONMSG CPF0000 versus the specific message [or message range] in a CLP can be a poor choice. Note that the EXIT HANDLER is presumably the "same" as a CONTINUE HANDLER in the given scenario. Aside from the unqualified scalar function references possibly being intercepted by functions of the same name for which the compatible parameter values could then cause some other error when invoking that other overloaded UDF, I do not see much possibility for negative effects. And that concern could be resolved with a PATH specification of "QSYS","QSYS2".

The description of the scoping is correct. The active nested "compound-statement" is the scope of the\its declarations. Those exceptions not handled in the current scope can be declared by and thus handled in a scope of the other compound statement(s) within which the current compound statement is nested.

Regards, Chuck