Call iseries db2 clp stored procedure from vb excel with parameters

[GPEICM]

Hello to All,
I'm trying to retrieve information from AS/400 in an VBA EXCEL application.
So, I've made an RPG program that return's a parameter, then CLP (calls the RPGLE and returns the parameter) and then a stored procedure which calls that CLP.
That i want to execute in VBA code to call the stored procedure (CLP) and retrieve data from AS/400.
The problem is, that when I run my VB code, it return's "empty" in the output variable.

The RPGLE PPROT code:
C     *ENTRY        PLIST                                         
C                   PARM                    RTN              10   

C                   EVAL      RTN = 'XXX'                            
C                   SETON                                        LR

/* CLP CODE*/
PGM PARM(&RTN) 
DCL VAR(&RTN) TYPE(*CHAR) LEN(3) VALUE(' ') 
/* call rpgle PPROT 
CALL PGM(PPROT) PARM(&RTN) 
ENDPGM

The iseries STORED PROCEDURE:
------------------------------------------------
/* it has to return the parameter returned from RPGLE
CREATE PROCEDURE MYLIBL/pnorma7(OUT output
CHAR ( 3)) LANGUAGE CL NOT DETERMINISTIC NO SQL EXTERNAL NAME
MYLIBL/pnorma7 PARAMETER STYLE GENERAL
---------------------------------------------------

Finally the VB Excell code:
-------------------------------------------------------
Public liga As New ADODB.Connection
Public Rcds As Variant
Public rec1 As New ADODB.Recordset
Public prm As ADODB.Parameter
Public cmd As New ADODB.Command

liga.Open "Provider=IBMDA400;Data Source=MYDATASOURCE;", "", ""
cmd.ActiveConnection = liga

cmd.CommandType = adCmdText
cmd.CommandText = "Call MYLIBL.PNORMA7 (?)"
Set prm = cmd.CreateParameter("OUT", adChar, adParamOutput, 10)
cmd.Parameters.Append prm

cmd.NamedParameters = True
cmd.CommandTimeout = 0


'Execute procedure in Iseries
 
cmd.Execute
' Debug code to ensure parameters are set correctly
For Each prm In cmd.Parameters
    Debug.Print prm.Name & " : " & prm.Value
Next

'->>>>>>> PROBLEM: PRM.VALUE IS EMPTY
'->>>>>>>          It should return 'XXX'
'->>>>>>> In System I navigator, it return's correctly: 
'->>>>>>>>>>>>>>>>>>>>>>>>>>>>>> call MYLIBL.PNORMA7(' ')

                                  Code return = 0
                                  Output parameter #1 = XXX
                                  Instruction executed with exit   (53 ms)

What is wrong with the output parameter en VB excell if it work's fine when calling in system i navigator?

Thenk you in advance
UAMI

[GLS400]

Re: Call iseries db2 clp stored procedure from vb excel with parameters

Hi gpeicm:

First off ...... I have no clue about excel/iseries interfaces but I see a couple of issues.
1. how big is rtn?

C                   PARM                    RTN              10   
DCL VAR(&RTN) TYPE(*CHAR) LEN(3) VALUE(' ') 
CREATE PROCEDURE MYLIBL/pnorma7(OUT output CHAR ( 3))
Set prm = cmd.CreateParameter("OUT", adChar, adParamOutput, 10) 

it looks like it is defined as 3 and 10.

2. Shouldn't the procedure's parameter definition be in/out? <---- not really sure about this
3. does mydatasource reference the library within the procedure?

Best of Luck
GLS

[Scott Klement]

Re: Call iseries db2 clp stored procedure from vb excel with parameters

I don't understand the purpose of the CL program. All it does is pass the same parameter to an RPG program... what's the point? Why not call the RPG program directly?

If you do need the CL for something you didn't include in your message... then... how can you put VALUE() on the DCL for a parameter?! Surely the value is what the caller passes, since it's a parameter?!?! Having VALUE() doesn't make much sense.

I've never seen someone do this from Excel. Is it possible that it's not allowed? (Indeed, this seems like it could be a security risk if it is.) Not familiar with the VB code you're using. Is it possible that you are not checking for errors, and therefore might be getting an error message that would provide more useful information? I really have no clue about doing this from Excel, sorry.

[tomliotta]

Re: Call iseries db2 clp stored procedure from vb excel with parameters

Hard to say. If you comment out the CALL to RPG and simply use CHGVAR to put a value into &RTN, does that value get returned? (Use CHGVAR, not a DCL VALUE().)

If it does, the first thing I'd do is correct the parameter definition mismatch between your CL and RPG. There's no good way to predict results of parameter mismatches. The very next thing I'd do is get rid of the *ENTRY PLIST and put a prototype definition in its place, though it probably won't make it work differently.

The DCL VALUE() clause shouldn't be a problem. It causes a severity 20 error (CPD0702) at compile-time, and mostly should result in ignoring the 'value'.