Re: SQL CREATE FUNCTION difficulties

On 14-Dec-2011 11:20 , James Lampert wrote:

In connection with several SQL VIEWs added to our CRM product as the
"New Data Access Layer" (mainly for BIRT at this point), we create
user-defined functions, by generating (from an RPG program) and
executing (via RUNSQLSTM) an SQL script.

Is there a specific reason to generate a script and then run that script rather than just performing the SQL in a [even that same] program? Of course there are a variety of valid reasons [esp. per that specific RPG program should not], but there is no reason that SQLRPGLE module(s) could not effect the work directly instead of using a script.

At present, the RPG program decides whether or not to begin the
script with a DROP FUNCTION, by checking whether the function's
*SRVPGM exists.

The existence of the UDF is, primarily, according to the SQL, what is tracked to the SQL catalog of routines. Thus the problem origin is with checking existence within the /QSYS.LIB for a specific object, rather than checking existence according to what the SQL considers [primarily for] existence per a row in its catalog VIEW SYSFUNCS.

Unfortunately, if function was already created, and the *SRVPGM for
the function has been deleted without doing a DROP FUNCTION, no DROP
FUNCTION is generated, and the CREATE FUNCTION fails.

Why not just always generate the DROP FUNCTION? Is that because of the pre-v5r4 difficulty with the end-severity on RUNSQLSTM requests? FWiW, eventually [IBM i 7.1] there is the CREATE OR REPLACE enhanced SQL syntax. If scripting and interpreted is required, then REXX could be scripted instead, or a different SQL script processor than RUNSQLSTM could be invoked if its ERRLVL() is the concern, or scripted CL command requests where a CMD executes only one SQL statement that was specified on a parameter of that CL CMD request.

Can somebody who's more familiar with SQL suggest a less fragile way
of unconditionally creating (or re-creating) the function and its
underlying *SRVPGM, one that won't break under the described
conditions?

A good start is to unconditionally issue DROP FUNCTION first.

However unless a DROP is performed under isolation against an existing routine, the opportunity remains that any previous existence-check for that routine no longer holds true, and an eventual CREATE may still fail with an "already exists" condition. Code that should function properly, generally, might best handle both the "not found" for DROP and "already exists" for the CREATE.

Regards, Chuck