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

You are right. I removed the optional and omit keywords from the last
three parameters and they are now returning correct values. Sigh....

Thanks.

"Scott Klement" <rpg400-l@xxxxxxxxxxxxxxxx> wrote in message
news:<mailman.10808.1221539149.2545.rpg400-l@xxxxxxxxxxxx>...

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 =