jdc0724
asked on
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?
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
1. I haven't seen this yet and am requesting examples of the most "Efficient/clean/standard?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Download MDAC from http://www.microsoft.com/data/download.htm
You should then be able to use it in VB5 (probably...)
You should then be able to use it in VB5 (probably...)
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=M
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).Descriptio
Exit Sub
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=M
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).Descriptio
Exit Sub
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
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
per recommendation
SpideyMod
Community Support Moderator @Experts Exchange
SpideyMod
Community Support Moderator @Experts Exchange
ASKER
Cheers,
JDC0724