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

Using oracle stored procedure in vb through adodb connection(通过adodb连接在vb中使用oracle存储过程)

问题描述

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

这篇关于通过adodb连接在vb中使用oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！