Passing null to RPGLE program

[cjbratvold]

I'm a little confused. I'm not sure where to post this question....

Trying to write a SQL stored procedure RPGLE program that accepts one null capable parameter.

I got a couple questions regarding the RPG program.....

Ctl-Opt DftActGrp(*No) ALWNULL(*USRCTL);

Prototype...

       dcl-pr PROCEDURE_PROFILES extpgm('LP9904I1');
         pProfileName char(40) options(*nopass: *nullind);
       end-pr;

Program interface...

       dcl-pi  PROCEDURE_PROFILES;
         pProfileName char(40) options(*nopass: *nullind);
       end-pi;

Reference to null field...

       if %nullind(pProfileName) = *off;

From what I understand this should work.

However, it doesn't. When I put the program in debug within RDI the program starts debug and displays the variable value in the monitor, but when I press F5 to enter the first line of code it goes away and debug says the pointer is not set. The program fails later on with a MCH3601 at the %nullind() statement. The occurs if I use nulls from run SQL scripts or if I actually send a value.

Here is the SQL create statement....

CREATE PROCEDURE &LIB/                                
                          PROCEDURE_PROFILES (           
  IN P_PROFILE CHAR(40) )                             
  DYNAMIC RESULT SETS 1                               
  LANGUAGE RPGLE                                      
  SPECIFIC           PROCEDURE_PROFILES                   
  NOT DETERMINISTIC                                   
  READS SQL DATA                                      
  CALLED ON NULL INPUT                                
  EXTERNAL NAME 'LPCAPPDEV/LP9904I1'                  
  PARAMETER STYLE GENERAL WITH NULLS ;                

COMMENT ON SPECIFIC PROCEDURE           PROCEDURE_PROFILES
  IS 'MTech List of Profiles' ;

If I don't try to use null logic and remove the nullind stuff the program will work with blanks just fine. I rather use nulls though.

One other weird thing I noticed. If I reference the %parms() function it returns 2...shouldn't it always be 1???

Thanks for your help...

[Scott Klement]

The nulls in your RPG code are RPG-style nulls. These can be used in RPG code and/or native I/O. However, SQL handles nulls differently... they are passed as small integers in a separate variable. Take a look at the SQL manuals for details.

[B.Hauser]

If you use parameter style GENERAL with NULLs it passes additionally a parameter for each input and output parameter, which is defined as Int(5) or SMALLINT (in SQL).
If a NULL value is returned, you need to check this second parameter. -1 means a NULL value was passed.
You also need to enhance your Prototype and Procedure Interface with this additional (optional) parameters.

Also %PARMS will either return 2 or -1.

Using Optional Parameters and NULL values with procedures/functions registered as Stored Procedures or Functions is always critial.

What I do: I create a wrapper procedure for each parameter composition, and register them all with the same SQL Procedure/Function Name.
These wrapper procedure do nothing else than calling the original RPG procedure.

You may also check whether you can define Default-Values for your parameters in your stored procedure.

Birgitta