通过adodb连接在vb中使用oracle存储过程

2021-09-16 00:00:00 oracle vb6

oracle 中的包:

Package in oracle:

create or replace package pkgsearch as
type mycursor is ref cursor;
procedure acc_search(id in varchar,cur out mycursor);
end pkgsearch;

CREATE OR REPLACE PACKAGE BODY pkgsearch AS
PROCEDURE acc_search (id in varchar,cur out mycursor) IS
BEGIN
OPEN cur FOR SELECT *
FROM account
WHERE accno = 'id';
end acc_search;
end pkgsearch;

此包用于搜索帐户记录，其中 id 作为参数传递给程序.VB中的代码

This package is used to search account records where id is passed as paramter to procedure. Code in VB

dim con as new ADODB.connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
Dim str As String

con.open "dsn=xxxxx;uid=xxxxxx;pwd=xxxxxx"

With cmd
.ActiveConnection = con
.CommandText = "pkgsearch.acc_search"
.CommandType = adCmdStoredProc
Set param = .CreateParameter("id", adVarChar, adParamInput, 10, Text1.Text)
.Parameters.Append param
End With

Set rs = cmd.Execute

Text1.Text = rs.Fields(0).Value
Text2.Text = rs.Fields(1).Value

这产生:

运行时错误'-2147217887(80040e21)';ODBC 驱动程序不支持请求的属性.单击调试后，它会突出显示set rs =cmd.execute"

run-time error '-2147217887(80040e21)'; ODBC driver does not support the requested properties. After clicking on debug it highlights 'set rs =cmd.execute'

请提出一些修改建议.

在 VB 中执行命令后还有其他使用记录集的方法吗?

Is there any other way of using recordset after executing command in VB?

提前谢谢.

推荐答案

我不确定是否使用 Oracle，但我认为它与 SQL 几乎相同.

I am not sure about using Oracle but I assume its almost the same as SQL.

这是我用于 SQL 的:

This is what I used for SQL: 

sql = "exec STORED PROCEDURE HERE"
Set RS = ExecuteQuery(sql)
RS.MoveFirst

While not EOF
DO STUFF
RS.MoveNext
Wend

共享模块:

Public connect As New ADODB.Connection
Public cmd As ADODB.command

    Public Function ConnectToDBOpen() As Boolean
        If (connect.State = adStateOpen) Then
            connect.Close
        End If
        connect.CursorLocation = adUSeClient
        connect.Open "CONNECTION INFO HERE"

        ConnectToDBOpen = True
    End Function


    Public Function DisconnectFromDB()

        connect.Close

    End Function


    Public Function ExecuteQuery(ByVal sqlQuery As String) As ADODB.Recordset
        Dim rsOut As ADODB.Recordset
        Set rsOut = New ADODB.Recordset
        Dim recordsAffected As Long


        If ConnectToDBOpen Then
            Dim command
            Set command = New ADODB.command

            With command
                .ActiveConnection = connect
                .CommandText = sqlQuery
                .CommandType = adCmdText
            End With

            rsOut.Open command.Execute(recordsAffected)

            Set ExecuteQuery = rsOut
            Set command = Nothing
            Call DisconnectFromDB
        End If
    End Function

相关文章