ADDLIBLE from SQL stored procedure

[tdavis]

I have an RPG program that I am calling from the stored procedure below. There is no problem
as long as the caller has the library in their library list, via the connection profiles job
description. But, if that user does not then MYRPGPGM cannot be found and it errors out.

I see that I can execute ADDLIBLE MYRPGLIB (where that is the library name where MYRPGPGM resides).
But I am not sure about syntax or where these additional lines should be placed.

Is this possible, or do I also need to CALL the program here rather than define it as EXTERNAL?

I tried a lot of combinations but all error out when trying to create the SP.
Any help is appreciated!

DECLARE LIBRARY_NAME VARCHAR(10);
SET V_LIBRARY_NAME = 'MYRPGLIB';
CALL QSYS2/QCMDEXC('ADDLIBLE ' CONCAT LIBRARY_NAME)

DROP PROCEDURE IF EXISTS GG_COPY_INITIAL_LOAD;
CREATE PROCEDURE QGPL/GG_COPY_INITIAL_LOAD(
IN LIBRARYNAME CHAR (10),
IN FILENAME CHAR (10),
INOUT SEGMENTSIZE CHAR (10),
IN FILTERS CHAR (80),
OUT RETURN_CODE CHAR (7),
OUT ERROR_MESSAGE CHAR (150),
OUT RETURN_VIEWCOUT CHAR (10),
OUT RETURN_RECCOUNT CHAR (10),
OUT RETURN_JOBID CHAR (28))
LANGUAGE RPGLE RESULT SETS 1
SPECIFIC MYRPGPGM NOT DETERMINISTIC MODIFIES SQL DATA
EXTERNAL NAME MYRPGPGM PARAMETER STYLE GENERAL

[Vectorspace]

A SQL External Stored Procedure is not a procedure/program in it's own right, it cannot execute code. It's simply an SQL definition for another program. In effect it's like the SQL equivalent of an RPGLE DCL-PR statement.

The normal solution is to require that the caller has the correct library list in their SQL connection before calling.


You could create a regular not-external SQL Stored procedure (so written in SQL PL), let's call it GG_COPY_INITIAL_LOAD_EXTERNAL. This will call QCMDEXC to set the libl and then call GG_COPY_INITIAL_LOAD. However, you still have the problem that the library that GG_COPY_INITIAL_LOAD_EXTERNAL, unless the caller has specified QGPL in its connection settings, or caller does "CALL QGPL/GG_COPY_INITIAL_LOAD_EXTERNAL(...)" to specify which library to find it in.

What I have done in the past, because we have huge library lists, is to require that the SQL connection specifies a few vital libraries - enough for the initial call to the EXTERNAL procedure. Then the EXTERNAL procedure sets up the libl before calling the actual procedure.