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

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 =