Announcement

Collapse
No announcement yet.

SQL External Procedure Nulls to RPGLE parameter nulls

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL External Procedure Nulls to RPGLE parameter nulls

    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:
    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;
    Step 2, include the null indicators parameter in the RPGLE program
    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;
    Step 3, call the procedure with either the parameter or *null depending on the null indicator
    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;
    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:
    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 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?

  • #2
    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).

    Comment


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

      Comment

      Working...
      X