Re: External Stored Procedures

To be a bit more specific versus just alluding... This has nothing to do with RPG; the topic should be on Midrange-L, not the RPG list.

Regardless...

The given CREATE PROCEDURE creates a routine named REM125SP. The given CALL statement invokes a different procedure\routine name. The former suggests there are no parameters, the latter suggests there is a parameter. Please correct the mismatches. I think maybe there is greater confusion than just that, because later there is comment of using SQL _in_ "the external procedure", but the external procedure is just an alias\link\redirect to the 'external' [to SQL] object; i.e. no SQL is allowed other than the clauses on the CREATE.

The token _USER_ is a special register. The token SESSION_USER is also a special register, albeit having an associated SET invocation, but not SET as in typical assigning of a variable name directly. So just reference these special registers as variables in predicates for their current value, but do not attempt to set them directly as variables with an assignment; i.e. VALUES INTO is an assignment. For example, to use SESSION_USER, just refer to that variable in a predicate just as in the "IF USER =" example; i.e. use the effective identical "IF SESSION_USER =", which would work the same way.

If the CALL is unqualified, the library list can be used as is or modified [e.g. ADDLIBLE] to effect the call of the desired [created] procedure, but the EXTERNAL NAME defines the program that is called. Create a procedure in each library to the corresponding external object to mimic calling a program /object/. If the library name to be used [where the procedure was created] is specific to the session user, then probably the best is to use a string to PREPARE the CALL with the proper\expected qualifier [library name].

Regards, Chuck

DLee@xxxxxxxx wrote:

CRPence wrote:

If the procedure is called using the *SYSTEM naming instead of
the *SQL naming, then the default path will be the library list
[*LIBL] used to locate the unqualified callee.

CREATE PROCEDURE &LIB/REM125SP ( ) RESULT SETS 1 LANGUAGE CL SPECIFIC &LIB/REM125SP NOT DETERMINISTIC NO SQL CALLED ON NULL INPUT EXTERNAL NAME CRPRDLIBTS/REM110CL PARAMETER STYLE GENERAL;
CALL CRPRDLIBTS/STRTSTLIB(COCD);

I'm using the stored procedure to call a cl program, and I'm using *system naming. When in production, the program will be in
library prod, when testing the program will be in test. If I can
know the session user, I can determine the library to call the program from. If there is a way I can retrieve the session user in the external stored procedure, I can do the below:

IF USER = 'CRPRDQUA' THEN CALL TEST/STRTSTLIB;
ELSE CALL PROD/STRTSTLIB;
END IF;
I did attempt to do a VALUES SESSION_USER INTO USER statement but
this didn't compile, and wasn't sure if the syntax I was using
was wrong, or if it just isn't allowed in an external stored
procedure. It just seems more difficult than it should be.