Re: SQL Procedure return x'00' for output parameter with options(*nopass :*omit)

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

Hi Lim,

I'm worried about the use of options(*nopass:*omit) in a stored 
procedure.  SQL does not usually pass an operational descriptor (not 
even a minimal one) so %parms will not work.  Therefore, any time you 
have *nopass parameters on a procedure call, you're likely to have problems.

Also, SQL does not support *OMIT as far as I know?  That won't break 
anything, of course,  but none of the parameters will be omitted, so 
unless you also call this procedure directly from an ILE program, 
there's little point in allowing *OMIT, since it'll never be passed...

Anyway, I have to wonder if the lack of data in parms 4-6 might be due 
to the program checking for %parms, when SQL doesn't provide the descriptor?


Lim Hock-Chai wrote:
> hello, 
> I'm having some problem with store procedure and hope someone can help
> me with it.  Here I go:
>  
> I have an export procedure, cryptoShadowEncrypt_SP, in a service program
> that accepts six parameters.  The first two are input only and the last
> four are output only parameters.  I was able to create a SQL
> store-procedure over this export procedure.  When I call the store
> procedure, the first output parameter returns to me with correct and
> expected value.  However, the last three output parameters are being
> returned with values of x'00'.   Below is the prototype and the create
> procedure statements:
>  
> D cryptoShadowEncrypt_SP...                                       
> D                 pr                                              
> D  piCryptoFieldID...                                             
> D                                     const                       
> D                                     like(cryptoReffldFieldID)   
> D  piPlainText                        const                       
> D                                     like(cryptoReffldPlainText) 
> D  piRtnIndexValue...                                             
>                                      like(cryptoReffldIndexValue) 
> D  piRtnSuccessful...                                             
> D                                 n   options(*nopass :*omit)     
> D  piRtnMsgID                         options(*nopass :*omit)     
> D                                     like(cryptoReffldRtnMsgID)  
> D  piRtnMsgText                       options(*nopass :*omit)     
> D                                     like(cryptoReffldRtnMsgText)
>
>  
> create procedure                                        
>   cryptoShadowEncrypt(                                  
>     in  cryptoFieldID    char(30),                      
>     in  plainText        char(32624),                   
>     out rtnIndexValue    dec(13,0),                     
>     out rtnIsSucessful   char(1),                       
>     out rtnMsgID         char(7),                       
>     out rtnMsgText       char(80)                       
>   )                                                     
>                                                         
>   language RPGLE                                        
>                                                         
>   external name 'PALHC/SVCRYPTO(CRYPTOSHADOWENCRYPT_SP)'
>                                                         
>   program type sub                                      
>                                                         
>   not deterministic                                     
>                                                         
>   no sql                                                
>
>   parameter style general
>
>  
>  
>  
> Below is the result when I call it from navigator:
> call qgpl.cryptoShadowEncrypt('BLPCUM_CUACCT', '123456789' ,0' ,' ' ,'
> ',' ')
>  
> Output Parameter #3 = 6694
>  
> Output Parameter #4 = 
>  
> Output Parameter #5 = 
>  
> Output Parameter #6 =