Solved

Executing a DB2 Stored Procedure from VB

Posted on 2002-07-24
6
321 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
6 Comments
 
LVL 20

Accepted Solution

by:
hes earned 100 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 VB code 9 115
DIR issue 7 54
Problem to With line 4 57
How can I retrieve an image(png/gif/...) stored in SQL (Byte[] Array) with VBS and convert to BASE64 3 36
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

770 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