DB2 Stored Procedures called from REXX

[dragonmsw]

I'm having a problem invoking DB2 Stored Procedures from REXX. I've
written a generic SP tester where you enter the schema and name of the
SP. The next panel displays the parms for the SP and allows you to
fill in the ones you need to and then invokes the SP.

I'm having two problems in particular that relate to specifying parms
for the SP call.

(1) I can't make it work at all for a VARCHAR parm. Everytime there is
a VARCHAR parm, REXX seems to be passing it to DB2 as a character
string and I get a parameter error (-305). How can I specify a VARCHAR
string from REXX?

(2) If there is a character parm and the user enters in a numeric value
(which is valid) - REXX treats it as a number during the call to DB2
and then I get a parameter error (-305). Is there a way to force the
REXX/SQL interface to recognize the variable as a string regardless of
content?

Thanks much,
Michael

[wfs]

> (1) I can't make it work at all for a VARCHAR parm. Everytime there is

a VARCHAR parm, REXX seems to be passing it to DB2 as a character
string and I get a parameter error (-305). How can I specify a VARCHAR
string from REXX?
I have the opposite problem, I could never get rexx to use fixed length
strings... so I had to define all my sp parameters as varchar.

can you post the sp definition and your sample rexx call
(2) If there is a character parm and the user enters in a numeric value
(which is valid) - REXX treats it as a number during the call to DB2
and then I get a parameter error (-305). Is there a way to force the
REXX/SQL interface to recognize the variable as a string regardless of
content?

I use "'"string"'" (double quote - single quote - double
quote - this makes sure its treated as a string by the rexx/sql interface

Bill
<dr*******@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com... I'm having a problem invoking DB2 Stored Procedures from REXX. I've
written a generic SP tester where you enter the schema and name of the
SP. The next panel displays the parms for the SP and allows you to
fill in the ones you need to and then invokes the SP.

I'm having two problems in particular that relate to specifying parms
for the SP call.

(1) I can't make it work at all for a VARCHAR parm. Everytime there is
a VARCHAR parm, REXX seems to be passing it to DB2 as a character
string and I get a parameter error (-305). How can I specify a VARCHAR
string from REXX?

(2) If there is a character parm and the user enters in a numeric value
(which is valid) - REXX treats it as a number during the call to DB2
and then I get a parameter error (-305). Is there a way to force the
REXX/SQL interface to recognize the variable as a string regardless of
content?

Thanks much,
Michael

[dragonmsw]

I can't post the SP definition because my exec is actually a generic SP
invoker (a SP tester). So the parms could be anything! But, here's
my REXX code for the actual invocation.

An ISPF table has been setup to hold the parameter names and values (as
specified on an input panel). Here's the TBCREATE:
'ISPEXEC TBCREATE SPTESTPM NOWRITE REPLACE' ,
'KEYS(PNAME PTYPE PLEN PSCALE PIO) NAMES(PVALUE)'

So the table has the name of the variable, the definition of it (type,
len, scale, and if IN or OUT) and the value of the parameter.

Then we come to this code:
----

/* Set up variables needed to run the SP. */
FullName = Schema'.'SPname

/* Set the parms for the SP. Read the information from */
/* the parm table and set the variables appropriately. */
'ISPEXEC TBTOP SPTESTPM'
'ISPEXEC TBSKIP SPTESTPM'
ParmString = ''
ParmList. = ''
ParmCount = 0
Do i = 1 While rc = 0

/* Format character variables only to proper length. */
If Ptype = 'CHAR' Then Pvalue = Left(Pvalue,Plen)

/* Assign Pvalue into variable name stored in Pname. Prefix */
/* parm name with PZ to avoid conflict with our REXX code. */
/* Also store parm name in a stem for later lookup. */
PZparm = Strip(Left('PZ'Pname,18)) /* New name for parm. */
junk = Value(PZparm,Pvalue) /* Assign value. */
ParmCount = ParmCount + 1 /* Count of parms. */
ParmList.ParmCount = Pname /* Store original name. */

/* Build string of parm names to pass to SP. */
If ParmString = '' Then
ParmString = ':'PZparm
Else
ParmString = ParmString',:'PZparm

'ISPEXEC TBSKIP SPTESTPM'
End
If ParmString <> '' Then ParmString = '('ParmString')'

/* Connect to DB2. Initializes RoutineRC showing SQL status. */
Call DB2connect

/* If we're OK, continue. */
If RoutineRC = 0 Then Do

/* Execute the Stored Proc. */
Address DSNREXX "EXECSQL CALL :FULLNAME" ParmString
...retrieve results...
-----

Thanks.

-Michael