SQLRPGLE program works within RPGLE but not as part of SQL function 1

[cjkoontz]

Sorry for the bother.

I have an SQLRPGLE based service program that "works" when called from an RPGLE based program, but when the same service program is wrapped in an SQL function, then used within STRSQL it fails with a data decimal error.

I don't know how much info would be sufficient, here, but, the SQLRPGLE object (P1) has this procedure identifier:

0008.39 D BBPLSTSTS PR 60A
0008.40 D APPYER 2S 0 CONST
0008.41 D APPNBR 8S 0 CONST
0008.50 P BBPLSTSTS B EXPORT

0008.51 D BBPLSTSTS PI 60A
0008.52 D APPYER 2S 0 CONST
0008.53 D APPNBR 8S 0 CONST

The SQL function (F1) that sits over it was created as:

Create function xlib/F1 (APPYER DEC(2,0),
APPNBR DEC(8,0))
Returns char(60)
Language rpgle
Not deterministic
External name 'XLIB/P1(BBPLSTSTS)'
Parameter style General

And the STRSQL statement it fails upon is:

select x.bppyer, x.bppcnb,
BBFlststs(x.bppyer, x.bppcnb)
from bplib/bp300ap x
where x.bppyer = 05 and x.bppcnb = 71

Is the error obvious?

 

[Mercury2]

Is the function name BBP... or BBF..., is it a typo ?

I don't see any parms indicating the sql usage in the create function stm, i.e. CONTAINS SQL, READS SQL DATA or MODIFIES SQL DATA. I don't know if it really matters though.

Show also the sql msgid you get.

 

[cjkoontz]

Rats, you're right.

The SQL select statement should read:

select x.bppyer, x.bppcnb, Fl(x.bppyer, x.bppcnb)
from bplib/bp300ap x
where x.bppyer = 05 and x.bppcnb = 71

I don't see an SQL msgid, but rather a message at the bottom of finished statement in STRSQL that reads as:

Query cannot be run. See lower level messages.

If I check my interactive job log messages, I see the following underlying message:

MCH1202

Decimal data error.

The sign or the digit codes of the packed or the zoned
decimal operand is in error. Valid signs are hex A-F, valid digit range is hex 0-9

 

[Mercury2]

If I were you, I'd debug my function with STRSRVJOB since it isn't a long process: compile with *SOURCE and STRSRVJOB on a new session.

 

[cjkoontz]

Thanks. I did do that.

I debugged P1 running under the service job for the SQL function.

I see what's going on -- the parameters coming into the procedure are being filled with garbage.

(Does that somehow have to do with the return value on the procedure interface?)

 

[Mercury2]

This one will likely work. Add NUMERIC to indicate to the function that both parms are zoned decimal.

select x.bppyer, x.bppcnb,
Fl( NUMERIC(x.bppyer), NUMERIC(x.bppcnb) )
from bplib/bp300ap x
where x.bppyer = 05 and x.bppcnb = 71

As for alphameric fields VARCHAR goes well, I can't remember how the parameters should be declared in the program to avoid this if numeric. Check the SQL Redbook on IBM web site.

 

[cjkoontz]

Thanks for the replies.

Unfortunately, I get the following message:

NUMERIC in *LIBL type *N not found.

 

[Mercury2]

Try DEC ou INT instead.

Fl( DEC(x.bppyer), DEC(x.bppcnb) )

 

[cjkoontz]

Thanks. But under debug --

Fl( DEC(x.bppyer), DEC(x.bppcnb) )

- and -

Fl( DEC(x.bppyer,2,0), DEC(x.bppcnb,8,0) )

Still pass along garbage data to P1.

 

[Mercury2]

I see what I think is an error:
In the Rpg program the parms declaration is extended (or zoned) decimal while you declare them as condensed (or packed) decimal in the function --> garbage !

D APPYER 2S 0 CONST
D APPNBR 8S 0 CONST

Create function xlib/F1 (APPYER DEC(2,0),
APPNBR DEC(8,0))

f.e. 2S 0 isn't the same as DEC(2, 0).
The 'S' stands for zoned decimal and DEC for packed decimal.

Hence, you'd better change 'S' for 'P' in your RPG program and call the function w/o DEC i.e. Fl(x.bppyer,x.bppcnb).
That should work since I wrote some functions in which input parms are declared as packed decimal and they work like a charm.

 

[cjkoontz]

I think you're right, Mercury2.

I walked through this stuff with IBM, just a bit ago, and I changed the prototype parameters to packed fields, and the SELECT for F1 worked.

(Through debug I could see the values being passed.)