Oracle_QY.Execute If Oracle_QY(10) <> 0 Then MsgBox "操作失败,返回值Res=" & Str(Oracle_QY(10)) & vbCrLf & Oracle_QY(11) Exit Sub End If AccessData.Recordset.MoveNext Loop
---- 一、关于ADO ---- 在基于Client/Server结构的数据库环境中,通过OLE DB接口可以存取数据,但它定 义的是低层COM接口,不仅不易使用,而且不能被VB,VBA,VBScript等高级编程工具访问。 而使用ADO则可以很容易地使VB等编程语言直接访问数据(通过OLE DB接口)。ADO是基于面 ---- 由上图可见,ADO对象模型总共才包括六个对象,相对于数据访问对象(DAO)来说 ---- 二、ADO访问数据库实例 ---- 下面,我们以Oracle为例,使用VB6.0来访问其数据库中的存储过程。在此例中,我 们首先在Oracle数据库上创建有两个存储过程,一个不带参数,另一个带有参数。然后,使用 ---- 1. 在Oracle服务器上运行以下DDL脚本: DROP TABLE person; CREATE TABLE person (ssn NUMBER(9) PRIMARY KEY, fname VARCHAR2(15), lname VARCHAR2(20)); INSERT INTO person VALUES(555662222,'Sam','Goodwin'); INSERT INTO person VALUES(555882222,'Kent','Clark'); INSERT INTO person VALUES(666223333,'Jane','Doe'); COMMIT; / ---- 2. 在Oracle服务器上创建包(package): CREATE OR REPLACE PACKAGE packperson AS TYPE tssn is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname is TABLE of VARCHAR2(15) INDEX BY BINARY_INTEGER; TYPE tlname is TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER; PROCEDURE allperson (ssn OUT tssn, fname OUT tfname, lname OUT tlname); PROCEDURE oneperson (onessn IN NUMBER, ssn OUT tssn, fname OUT tfname, lname OUT tlname); END packperson; / ---- 3. 在Oracle服务器上创建以下包体(package body): CREATE OR REPLACE PACKAGE BODY packperson AS PROCEDURE allperson (ssn OUT tssn, fname OUT tfname, lname OUT tlname) IS CURSOR person_cur IS SELECT ssn, fname, lname FROM person; percount NUMBER DEFAULT 1; BEGIN FOR singleperson IN person_cur LOOP ssn(percount) := singleperson.ssn; fname(percount) := singleperson.fname; lname(percount) := singleperson.lname; percount := percount + 1; END LOOP; END; PROCEDURE oneperson (onessn IN NUMBER, ssn OUT tssn, fname OUT tfname, lname OUT tlname) IS CURSOR person_cur IS SELECT ssn, fname, lname FROM person WHERE ssn = onessn; percount NUMBER DEFAULT 1; BEGIN FOR singleperson IN person_cur LOOP ssn(percount) := singleperson.ssn; fname(percount) := singleperson.fname; lname(percount) := singleperson.lname; percount := percount + 1; END LOOP; END; END; / ---- 4. 在 VB 6.0 中打开一个新的工程,缺省创建表单 Form1。 ---- 5. 在表单上添加二个按钮,cmdGetEveryone和cmdGetOne。 ---- 6. 在代码窗口中添加以下代码: Option Explicit Dim Cn As ADODB.Connection Dim CPw1 As ADODB.Command Dim CPw2 As ADODB.Command Dim Rs As ADODB.Recordset Dim Conn As String Dim QSQL As String Dim inputssn As Long Private Sub cmdGetEveryone_Click() Set Rs.Source = CPw1 Rs.Open While Not Rs.EOF MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2) Rs.MoveNext Wend Rs.Close End Sub Private Sub cmdGetOne_Click() Set Rs.Source = CPw2 inputssn = InputBox( "Enter the SSN you wish to retrieve:") CPw2(0) = inputssn Rs.Open MsgBox "Person data: " & Rs(0) & " , " & Rs(1) & ", " & Rs(2) Rs.Close End Sub Private Sub Form_Load() '使用合适的值代替以下用户ID, 口令(PWD)和服务器名称(SERVER) Conn = "UID=*****;PWD=*****;driver=" _ & "{Microsoft ODBC for Oracle};SERVER=dseOracle;" Set Cn = New ADODB.Connection '创建Connection对象 With Cn .ConnectionString = Conn .CursorLocation = adUseClient .Open End With QSQL = "{call packperson.allperson( {resultset 9,ssn,fname,"_ & "lname})}" Set CPw1 = New ADODB.Command '创建Command对象 With CPw1 Set .ActiveConnection = Cn .CommandText = QSQL .CommandType = adCmdText End With QSQL ="{call packperson.oneperson(?, {resultset 2,ssn, "_ & " fname,lname})}" '调用存储过程 Set CPw2 = New ADODB.Command With CPw2 Set .ActiveConnection = Cn .CommandText = QSQL .CommandType = adCmdText .Parameters.Append.CreateParameter( ,adInteger, _ adParamInput) '添加存储过程参数 End With Set Rs = New ADODB.Recordset With Rs .CursorType = adOpenStatic .LockType = adLockReadOnly End With End Sub Private Sub Form_Unload(Cancel As Integer) Cn.Close Set Cn = Nothing Set CPw1 = Nothing Set CPw2 = Nothing Set Rs = Nothing End Sub ---- 7. 运行程序。当点下cmdGetEveryone按钮时,程序调用Oracle数据库中不带参数的 存储过程packperson.allperson,点下cmdGetOne按钮时调用packperson.oneperson存储过程
Dim ProcStr As String
Dim Oracle_CN As rdoConnection
Dim Oracle_QY As rdoQuery '连接到Oracle
With rdoEngine
.rdoDefaultCursorDriver = rdUseClientBatch
.rdoDefaultUser = "User_name"
.rdoDefaultPassword = "User_pass"
.rdoDefaultLoginTimeout = 1
End With
Oracle_connectStr = "SERVER=" & Oracle_db_name & ";" & _
"DRIVER={Microsoft ODBC for Oracle};"
Set Oracle_CN = rdoEngine.rdoEnvironments(0).OpenConnection _
("", rdDriverNoPrompt, False, Oracle_connectStr)
OracleSQL = "{call write_km2(?,?,?,?,?,?,?,?,?,?,?,?)}"
Set Oracle_QY = Oracle_CN.CreateQuery("", OracleSQL)
Oracle_QY(0) = Trim(Str(.Fields("考生证号")))
Oracle_QY(1) = .Fields("身份证号")
Oracle_QY(2) = .Fields("准驾车型")
Oracle_QY(3) = Ksrq
Oracle_QY(4) = Kscj
Oracle_QY(5) = Kscs
Oracle_QY(6) = .Fields("主考人")
Oracle_QY(7) = .Fields("监考人")
Oracle_QY(8) = .Fields("经办人")
Oracle_QY(9) = Zt
'Oracle_QY (10) = Res
'Oracle_QY (11) = errMessage
Oracle_QY.Execute
If Oracle_QY(10) <> 0 Then
MsgBox "操作失败,返回值Res=" & Str(Oracle_QY(10)) & vbCrLf & Oracle_QY(11)
Exit Sub
End If
AccessData.Recordset.MoveNext
Loop
http://www.xnovo.com/doc/html/aebbbg/bgbbchaaxxzgietotb.html
---- 一、关于ADO
---- 在基于Client/Server结构的数据库环境中,通过OLE DB接口可以存取数据,但它定
义的是低层COM接口,不仅不易使用,而且不能被VB,VBA,VBScript等高级编程工具访问。
而使用ADO则可以很容易地使VB等编程语言直接访问数据(通过OLE DB接口)。ADO是基于面
---- 由上图可见,ADO对象模型总共才包括六个对象,相对于数据访问对象(DAO)来说
---- 二、ADO访问数据库实例
---- 下面,我们以Oracle为例,使用VB6.0来访问其数据库中的存储过程。在此例中,我
们首先在Oracle数据库上创建有两个存储过程,一个不带参数,另一个带有参数。然后,使用
---- 1. 在Oracle服务器上运行以下DDL脚本:
DROP TABLE person;
CREATE TABLE person
(ssn NUMBER(9) PRIMARY KEY,
fname VARCHAR2(15),
lname VARCHAR2(20));
INSERT INTO person VALUES(555662222,'Sam','Goodwin');
INSERT INTO person VALUES(555882222,'Kent','Clark');
INSERT INTO person VALUES(666223333,'Jane','Doe');
COMMIT;
/
---- 2. 在Oracle服务器上创建包(package):
CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(15)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(20)
INDEX BY BINARY_INTEGER;
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
END packperson;
/
---- 3. 在Oracle服务器上创建以下包体(package body):
CREATE OR REPLACE PACKAGE BODY packperson AS
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person
WHERE ssn = onessn;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;
/
---- 4. 在 VB 6.0 中打开一个新的工程,缺省创建表单 Form1。
---- 5. 在表单上添加二个按钮,cmdGetEveryone和cmdGetOne。
---- 6. 在代码窗口中添加以下代码:
Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command
Dim CPw2 As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Dim inputssn As Long
Private Sub cmdGetEveryone_Click()
Set Rs.Source = CPw1
Rs.Open
While Not Rs.EOF
MsgBox "Person data: " & Rs(0) & ",
" & Rs(1) & ", " & Rs(2)
Rs.MoveNext
Wend
Rs.Close
End Sub
Private Sub cmdGetOne_Click()
Set Rs.Source = CPw2
inputssn = InputBox(
"Enter the SSN you wish to retrieve:")
CPw2(0) = inputssn
Rs.Open
MsgBox "Person data: " & Rs(0) & "
, " & Rs(1) & ", " & Rs(2)
Rs.Close
End Sub
Private Sub Form_Load()
'使用合适的值代替以下用户ID,
口令(PWD)和服务器名称(SERVER)
Conn = "UID=*****;PWD=*****;driver=" _
& "{Microsoft ODBC for
Oracle};SERVER=dseOracle;"
Set Cn = New ADODB.Connection
'创建Connection对象
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With
QSQL = "{call packperson.allperson(
{resultset 9,ssn,fname,"_
& "lname})}"
Set CPw1 = New ADODB.Command
'创建Command对象
With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
End With
QSQL ="{call packperson.oneperson(?,
{resultset 2,ssn, "_
& " fname,lname})}"
'调用存储过程
Set CPw2 = New ADODB.Command
With CPw2
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
.Parameters.Append.CreateParameter(
,adInteger, _
adParamInput)
'添加存储过程参数
End With
Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cn.Close
Set Cn = Nothing
Set CPw1 = Nothing
Set CPw2 = Nothing
Set Rs = Nothing
End Sub
---- 7. 运行程序。当点下cmdGetEveryone按钮时,程序调用Oracle数据库中不带参数的
存储过程packperson.allperson,点下cmdGetOne按钮时调用packperson.oneperson存储过程