How to use ADO to access Oracle database Stored Procedures

Source: Network/Editor: getting started with programming: Unknown

I. About ado

In a client/server-based database environment, you can use the ole db interface to access data. However, it defines a low-level COM interface, which is not easy to use but cannot be used by VB, VBA, access by VBScript and other advanced programming tools.

With ado, you can easily make VB and other programming languages directly access data (through the ole db interface ). ADO is based on the object-oriented method. Its object model is shown in (omitted)

As you can see, the ADO object model contains a total of six objects, which is much simpler than the data access object (DAO. Therefore, it is often used to access the database.

Ii. Accessing database instances through ado

Next, we will take oracle as an example to use VB6.0 to access the stored procedures in its database. In this example, we first create two stored procedures on the Oracle database, one with no parameters and the other with parameters. Then, use ADO to access these two stored procedures. The procedure is as follows:

1. Run the following DDL script on the Oracle server:

　　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. Create a package on the Oracle server ):

　　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. Create the following package body on the Oracle server ):

　　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. Open a new project in VB 6.0. The form1 form is created by default.

5. Add two buttons to the form, including geteveryone and getone.

6. Add the following code in the code window:

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 register geteveryone_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 register getone_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 ()
'Use the appropriate value to replace the following user IDs,
Password (PWD) and server name (server)
Conn = "uid = *****; Pwd = *****; driver = "_
& "{Microsoft ODBC
Oracle}; server = dseoracle ;"
Set Cn = new ADODB. Connection
'Create a connection object
With Cn
. Connectionstring = Conn
. Cursorlocation = aduseclient
. Open
End
Qsql = "{call packperson. allperson (
{Resultset 9, SSN, fname ,"_
& "Lname })}"
Set cpw1 = new ADODB. Command
'Create command object
With cpw1
Set. activeconnection = Cn
. Commandtext = qsql
. Commandtype = ad1_text
End
Qsql = "{call packperson. oneperson (?,
{Resultset 2, SSN ,"_
& "Fname, lname })}"
'Call the Stored Procedure
Set cpw2 = new ADODB. Command
With cpw2
Set. activeconnection = Cn
. Commandtext = qsql
. Commandtype = ad1_text
. Parameters. append. createparameter (
, Adinteger ,_
Adparaminput)
'Add stored procedure parameters
End
Set rs = new ADODB. recordset
With RS
. Cursortype = adopenstatic
. Locktype = adlockreadonly
End
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. Run the program. When you click the detail geteveryone button, the program calls the Stored Procedure packperson. allperson without parameters in the Oracle database, and the packperson. oneperson stored procedure is called when you click the detail getone button.