I have an Oracle stored procedure (see below) which I can execute successfully from the simple interface from Oracle called SQL*Plus.
Trying to run this procedure from VB6 is giving me trouble, though.
I have:
Code:
On Error GoTo Er
' Open Connection
Dim c As New ADODB.Connection
c.Provider = "MSDASQL"
c.ConnectionString = "DRIVER={Microsoft ODBC for Oracle};" & _
"SERVER=DIS;" & _
"UID=xxx;" & _
"PASSWORD=xxxxxxxx;"
c.ConnectionTimeout = 10
c.Open
' Create a Command obj. & set .CommandText
' to the name of the stored procedure.
Dim cmd As New ADODB.Command
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SEND_EMAIL"
' Create, fill-in, then add a param/arg to the Cmd Obj.,
' in order.
Dim prm As ADODB.Parameter
Set prm = New ADODB.Parameter: prm.Type = adVarChar
prm.Direction = adParamInput
prm.Name = "sender_name"
prm.Value = ""
prm.Size = Len(prm.Value) + 1
cmd.Parameters.Append prm
Set prm = New ADODB.Parameter: prm.Type = adVarChar
prm.Direction = adParamInput
prm.Name = "sender"
prm.Value = "[email protected]"
prm.Size = Len(prm.Value)
cmd.Parameters.Append prm
Set prm = New ADODB.Parameter: prm.Type = adVarChar
prm.Direction = adParamInput
prm.Name = "recipient_name"
prm.Value = ""
prm.Size = Len(prm.Value) + 1
cmd.Parameters.Append prm
Set prm = New ADODB.Parameter: prm.Type = adVarChar
prm.Direction = adParamInput
prm.Name = "recipient"
prm.Value = "[email protected]"
prm.Size = Len(prm.Value)
cmd.Parameters.Append prm
Set prm = New ADODB.Parameter: prm.Type = adVarChar
prm.Direction = adParamInput
prm.Name = "copy_to_name"
prm.Value = ""
prm.Size = Len(prm.Value) + 1
cmd.Parameters.Append prm
Set prm = New ADODB.Parameter: prm.Type = adVarChar
prm.Direction = adParamInput
prm.Name = "copy_to"
prm.Value = ""
prm.Size = Len(prm.Value) + 1
cmd.Parameters.Append prm
Set prm = New ADODB.Parameter: prm.Type = adVarChar
prm.Direction = adParamInput
prm.Name = "subject"
prm.Value = "Test Sending"
prm.Size = Len(prm.Value) + 1
cmd.Parameters.Append prm
Set prm = New ADODB.Parameter: prm.Type = adVarChar
prm.Direction = adParamInput
prm.Name = "message"
prm.Value = "Message Body"
prm.Size = Len(prm.Value)
cmd.Parameters.Append prm
' .Execute the Command object.
cmd.Execute
Rs: Exit Sub
Er: . . .
The form of the stored procedure is:
Code:
TEXTPROCEDURE "SEND_EMAIL"
( sender_name IN VARCHAR2,
sender IN VARCHAR2,
recipient_name IN VARCHAR2,
recipient IN VARCHAR2,
copy_to_name IN VARCHAR2,
copy_to IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2)
The errors are:
Code:
c.Errors.Count: 1
c.Errors(0).NativeError: 0
c.Errors(0).Number: 0
c.Errors(0).Description: [Microsoft][ODBC Driver Manager]
Driver's SQLSetConnectAttr failed.
err.Number: 3709
err.Source: ADODB.Command
err.Description: The connection cannot be used to perform
this operation. It is either closed or
invalid in this context.
c.State: 1 (adStateOpen)
What can this mean?
Executing Oracle Stored Procedure
