Executing a DB2 Stored Procedure from VB

Posted on 2002-07-24
Medium Priority
Last Modified: 2010-05-02
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.

Question by:jdc0724
LVL 20

Accepted Solution

hes earned 400 total points
ID: 7175460
Set a reference to Microsoft ActiveX Data Objects 2.x


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;" & _

 With oCM
    .ActiveConnection = oConn
   .CommandText = "NameofProcedure"
    .CommandType = 4
    .Parameters("parmname") = "Your Variable"
    Set oRS = .execute
 End With

Author Comment

ID: 7175470
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??  


Expert Comment

ID: 7177192
Download MDAC from http://www.microsoft.com/data/download.htm

You should then be able to use it in VB5 (probably...)
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 18

Expert Comment

ID: 7186411
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

gConn.Provider = "MSDataShape"
gConn.CursorLocation = adUseClient

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
If records.State = adStateOpen Then
    While Not records.EOF And Not records.BOF
        Me.Print records.Fields(0).Value, records.Fields(1).Value
    MsgBox "Error Opening Recordset"
    Exit Sub
End If

Exit Sub
MsgBox gConn.Errors(0).Description
Exit Sub
LVL 49

Expert Comment

ID: 8040574
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

Expert Comment

ID: 8095687
per recommendation

Community Support Moderator @Experts Exchange

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question