Call RPG Stored Procedure from VB 1

[Skittle]

Has anybody stumbled across an example or FAQ on how to call a stored procedure on the AS400 from a VB program?

I am familar with AS400 stored procedures and associating them with an RPG program but the VB bindings make my brain hurt.



Dazed and confused

 

[Mercury2]

Skittle,
I think that this excerpt from a VBA could help. I am not familiar with VB/VBA programming nor sure that all the parameters in the module are necessary to call the SPL subproc but it works in my shop. This module calls a subproc that gets a certain number of records from an AS400 file. It's a bit hit and miss and this coding took me effort to understand how to call a stored procedure.
Here is the VBA code though. This calls "MYLIB.MYSTOREPROC" stored procedure.

Public Sub ExchangeWithAS400()

Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command

objConn.Open "Provider=IBMDA400;Data Source=[i]My_AS400_IP[/i];", "[i]user[/i]", "[i]password[/i]"

Set objCmd.ActiveConnection = objConn

objCmd.CommandText = [i]"MYLIB.MYSTOREPROC"[/i]
objCmd.CommandType = [b]adCmdStoredProc[/b]

Set parm1 = objCmd.CreateParameter("Threshold") 
parm1.Type = adVarChar
parm1.Direction = adParamInput
parm1.Size = 7
parm1.Value = "0045000"
objCmd.Parameters.Append parm1

Set parm2 = objCmd.CreateParameter("Hsjncf")
parm2.Type = adVarChar
parm2.Direction = adParamInput
parm2.Size = 3
parm2.Value = "057"
objCmd.Parameters.Append parm2

Set parm3 = objCmd.CreateParameter("Hsabda")
parm3.Type = adVarChar
parm3.Direction = adParamInput
parm3.Size = 10
parm3.Value = "2003-11-24"
objCmd.Parameters.Append parm3

Set parm4 = objCmd.CreateParameter("Bigtimeqty")
parm4.Type = adVarChar
parm4.Direction = adParamReturnValue
parm4.Size = 5
parm4.Value = "00000"
objCmd.Parameters.Append parm4

Set parm5 = objCmd.CreateParameter("Rtncode")
parm5.Type = adVarChar
parm5.Direction = adParamReturnValue
parm5.Size = 1
parm5.Value = "0"
objCmd.Parameters.Append parm5

On Error Resume Next
objCmd.Execute
Debug.Print "BigtimeQty = "; parm4
End Sub

And here is the AS400 SPL stored procedure

Create Procedure [i]MYLIB.MYSTOREPROC[/i]
  ( In    InTimeThresHold  Numeric ( 7, 0 ),
    In    InHsJnCf         Char    ( 3    ),
    In    InHsAbDa         Date            ,
    Out   OutBigTimeQty    Numeric ( 5, 0 ),
    Out   RtnCode          Numeric ( 1, 0 ) )
    Language SQL

Begin

     -- Local variables
     Declare  WCount        Decimal (5, 0) Default 0;

     -- No Commitment Control
     Set Transaction Isolation Level NC;

     -- Count number of records
     Select Count(*)
     Into   WCount
     From   [i]Mylib/Myfile[/i]
     Where  HsJnCf  = InHsJnCf
     And    HsAbDa  = InHsAbDa
     And    HsWrNg >= InTimeThresHold;

     Set    OutBigTimeQty = WCount;
     Set    RtnCode       = 0;

End

Hope that helps.
Philippe --

I think there is a world market for maybe five computers.
- Thomas Watson, chairman of IBM, 1943.

 

[Skittle]

Wow! Thats really good.

Its coded much more clearly than the methods I've been struggling with. Many thanks, I shall have a go at adapting it.

Dazed and confused

 

[Skittle]

Hmm spoke to soon.

Doesn't quite dow hat I'm after.
I want to call an RPG program rather than an SQL routine.
I have never encountered an 'SPL' program before.

I tried using the same approach but with an RPG program instead of an SPL program. I created the procedure in SQL ( via STRSQL ) and I can run the RPG program via the procedure name within a STRSQL session but it doesn't
run when I execute the command. I don't get any errors, it just steps through it.

Anybody any ideas?

Dazed and confused

 

[Mercury2]

FYI, SPL stands for "SQL Procedural Language".
My VBA & SP work perfectly altogether in my shop but maybe you're planning to retrieve records from an AS400 file instead of a simple field with your VB code ? If so, I can show here an example next time if you ask me.
What is your SP supposed to do ?

 

[Mercury2]

If the RPG program contains embedded SQL and files are not journaled, have you tried to compile with COMMIT(*NONE) ?

 

[Skittle]

Ok sorted it.

I was calling an SQL procedure name, not the RPG program.
It all works fine now.

many thanks everybody.

Dazed and confused