SQL External Procedure Nulls to RPGLE parameter nulls

[Vectorspace]

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:

  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;

Step 2, include the null indicators parameter in the RPGLE program

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;

Step 3, call the procedure with either the parameter or *null depending on the null indicator

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;

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:

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 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?

[Scott Klement]

Another option is to pass the SQL null indicators as parameters to myProc...

Or convert the SQL nukk indicator to an RPG-style one with %NULLIND, and pass options(*nullind).

[Vectorspace]

In either case, that means amending how myProc() checks for null parameters?