• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Executing a DB2 Stored Procedure from VB

Hi, I need some code samples of executing a DB2 stored procedure running on an IBM 3090 Mainframe.  Supposedly some of our techs have been able to get the stored procedure to execute and even get some results back.  Here are the questions I have:

1.   I haven't seen this yet and am requesting examples of the most "Efficient/clean/standard??" way to perform this task.

2.  Also, I guess even though our techs can execute the procedure and get results back they haven't figured out a way to pass parameters to the procedure on the mainframe.  

I am assuming this is an easy task with numbers of ways to do it.  I am putting the points at 100 in an effort to get the best advice possible and with examples.

Cheers,
JDC0724
0
jdc0724
Asked:
jdc0724
1 Solution
 
hesCommented:
Set a reference to Microsoft ActiveX Data Objects 2.x

Declare:

Public oConn As New ADODB.Connection
Public oRS As New ADODB.Recordset
Public oCM As New ADODB.Command

For your connection

oConn.Open = "Provider=DB2OLEDB;" & _
             "Network Transport Library=TCPIP;" &  _
             "Network Address=xxx.xxx.xxx.xxx;" & _
             "Initial Catalog=MyCatalog;" & _
             "Package Collection=MyPackageCollection;" & _
             "Default Schema=MySchema;" & _
             "User ID=MyUsername;" & _
             "Password=MyPassword"


 With oCM
    .ActiveConnection = oConn
   .CommandText = "NameofProcedure"
    .CommandType = 4
    .Parameters("parmname") = "Your Variable"
    Set oRS = .execute
 End With
0
 
jdc0724Author Commented:
I am using VB5 not 6.  I don't think ADO is available for VB5.  Is there a difference or can you use DAO Workspace, Connection and recordset objects??  

Cheers,
JDC0724
0
 
NazdorCommented:
Download MDAC from http://www.microsoft.com/data/download.htm

You should then be able to use it in VB5 (probably...)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mdouganCommented:
Another example using ADO.  Note, the name provided in the CommandText property is case sensitive.

Dim gConn As New ADODB.Connection
Dim records As New ADODB.Recordset
Dim CMD As New ADODB.Command
Dim PARM1 As New ADODB.Parameter
Dim pARM2 As New ADODB.Parameter

On Error GoTo ErrorRtn
Me.Cls

gConn.Provider = "MSDataShape"
gConn.CursorLocation = adUseClient
gConn.Open "DSN=MYDSN;UID=MYUID;PWD=MYPWD"

If Not gConn.State = adStateOpen Then
    MsgBox "Error Opening Connection"
    Exit Sub
End If

With CMD
    .ActiveConnection = gConn
    .CommandType = adCmdStoredProc
    .CommandText = "PROC001"
End With

With PARM1
    .Name = "MYPARM_A"
    .Direction = adParamInput
    .Type = adInteger
    .Value = 1292
End With

CMD.Parameters.Append PARM1

With pARM2
    .Name = "MYPARM_B"
    .Direction = adParamInput
    .Type = adChar
    .Size = 5
    .Value = "QXYZA"
End With

CMD.Parameters.Append pARM2

Set records.Source = CMD
records.Open
If records.State = adStateOpen Then
    While Not records.EOF And Not records.BOF
        Me.Print records.Fields(0).Value, records.Fields(1).Value
        records.MoveNext
    Wend
Else
    MsgBox "Error Opening Recordset"
    Exit Sub
End If

Exit Sub
ErrorRtn:
MsgBox gConn.Errors(0).Description
Exit Sub
0
 
DanRollinsCommented:
Hi jdc0724,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept hes's comment(s) as an answer.

jdc0724, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now