I an writing an SQL External Procedure and RPGLE program. That program will call a service program procedure that has a parameter with *OMIT. I want to pass through a null on the SQL parameter call, through the RPGLE program, to the *OMIT parameter in the procedure (where it is detected with %addr(parm) = *null).
I have done it but it was quite fiddly, so I was hoping there is a better way.
Step 1, define the external SQL proc to support a null parameter:
Step 2, include the null indicators parameter in the RPGLE program
Step 3, call the procedure with either the parameter or *null depending on the null indicator
But it's annoying to have to duplicate the procedure call. And it doesn't scale well if I want multiple nullable parameters.
So my idea was, have an additional variable with a pointer initialised to null. If the parameter is not null, set the address to the parameter address. Then I can just pass that variable to the procedure, and it will either be the address of the value or a null address. But that didn't work as you cannot read %addr() on a const parameter.
So I had to define another variable, set it to the value of the parameter, and point the pointer to that.
It worked, but is fiddly:
if piParm2 is SQL null, then parm2Nullable's address stays as null, and myProc can detect it with %addr() = *null
if piParm2 is not SQL null, the piParm2's value is copied to parm2, and parm2Nullable's address is set to parm2, and myProc receives the value
Is there a better way than this?
I have done it but it was quite fiddly, so I was hoping there is a better way.
Step 1, define the external SQL proc to support a null parameter:
Code:
create procedure MyPgmSQL ( in piParm1 varchar(50), in piParm2 varchar(50) default null in piParm3 varchar(50), ) specific MyPgmSQL external name MyPgmR language RPGLE parameter style general with nulls;
Code:
dcl-Proc MyPgmR; dcl-Pi *n; piParm1 varchar(50) const; piParm2 varchar(50) const; piParm3 varchar(50) const; piNullList int(5) dim(3); end-Pi;
Code:
dcl-Proc MyPgmR; dcl-Pi *n; piParm1 varchar(50) const; piParm2 varchar(50) const; piParm3 varchar(50) const; piNullList int(5) dim(3); end-Pi; if piNullIist(2) = -1; myProc(piParm1:*null:piParm3); else; myProc(piParm1:piParm2:piParm3); endIf; end-Proc;
So my idea was, have an additional variable with a pointer initialised to null. If the parameter is not null, set the address to the parameter address. Then I can just pass that variable to the procedure, and it will either be the address of the value or a null address. But that didn't work as you cannot read %addr() on a const parameter.
So I had to define another variable, set it to the value of the parameter, and point the pointer to that.
It worked, but is fiddly:
Code:
dcl-Proc MyPgmR; dcl-Pi *n; piParm1 varchar(50) const; piParm2 varchar(50) const; piParm3 varchar(50) const; piNullList int(5) dim(3); end-Pi; dcl-s parm2Nullable like(piParm2) based(@parm2Nullable); dcl-s @parm2Nullable pointer inz(*null); dcl-s parm2 like(piParm2); if piNullIist(2) <> -1; parm2 = piParm2; @parm2Nullable = %addr(parm2); endIf; myProc(piParm1:parm2Nullable:piParm3); end-Proc;
if piParm2 is not SQL null, the piParm2's value is copied to parm2, and parm2Nullable's address is set to parm2, and myProc receives the value
Is there a better way than this?
Comment