CALL ISERIES DB2 CLP STORED PROCEDURE FROM VB EXCEL WITH PARAMETERS

[UAMI4]

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.

[b]The RPGLE PPROT code:[/b]
C     *ENTRY        PLIST                                         
C                   PARM                    RTN              10   

C                   EVAL      RTN = 'XXX'                            
C                   SETON                                        LR

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




[/code]


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

[COLOR=#3465A4]'->>>>>>> PROBLEM: PRM.VALUE IS EMPTY
'->>>>>>>          It should return 'XXX'
'->>>>>>> In System I navigator, it return's correctly: 
'->>>>>>>>>>>>>>>>>>>>>>>>>>>>>> call acr_gis.PNORMA7(' ')

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

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

 

[UAMI4]

Ok,

I discovered!

cmd.CommandText = "{Call ACR_GIS.PNORMA7 (?)}