?
Solved

Executing a DB2 Stored Procedure from VB

Posted on 2002-07-24
6
Medium Priority
?
325 Views
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.

Cheers,
JDC0724
0
Comment
Question by:jdc0724
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 20

Accepted Solution

by:
hes earned 400 total points
ID: 7175460
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
 
LVL 1

Author Comment

by:jdc0724
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??  

Cheers,
JDC0724
0
 
LVL 4

Expert Comment

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

You should then be able to use it in VB5 (probably...)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:mdougan
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
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
 
LVL 49

Expert Comment

by:DanRollins
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
0
 

Expert Comment

by:SpideyMod
ID: 8095687
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

762 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