Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Executing a DB2 Stored Procedure from VB

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

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.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…
Suggested Courses

636 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