0963-0966

Previous Table of Contents Next

Page 963

CHAPTER 41

Oracle OLE Automation Server

IN THIS CHAPTER

• Background 964
• Getting Started 965
• Executing SQL Statements and PL/SQL Blocks 966
• Using Parameters 969
• The OraDynaset Edit, Update, and AddNew Methods 974
• LONG and LONG RAW Column Support 975
• Transactions 977
• Error Handling 978
• Performance Tuning 979
• Thread Safety 982

Page 964

Oracle Objects for OLE (OO4O) is an in-process OLE automation server that implements a set of interfaces and objects for connecting to Oracle database servers, executing queries, and processing results. It may be used from any programming or scripting language or application that supports the Microsoft COM and OLE automation technology.

Background

OO4O has been developed and evolved specifically for use with Oracle database servers and does not adhere to or use the ODBC API for its implementation. This freedom enables it to provide an optimized API for accessing features that are unique to Oracle and are otherwise cumbersome or inefficient to use from other ODBC-based components , such as Microsoft's DAO, RDO, and ADO.

What Is an OLE Automation Server?

An OLE automation server is a software component based on the Microsoft Component Object Model (COM). COM was introduced as part of Microsoft OLE 2 technology, and is a specification for a standard mechanism by which one piece of software (a COM component) provides its services (COM objects/interfaces) to another, in a programming-language_independent and object-oriented fashion.

COM objects offer methods that can be called by client applications implemented in any programming language, such as C, C++, Visual Basic, and Java that supports the COM specification.

COM components can also be developed in a variety of languages, and can be hosted in a dynamic link library (DLL) or in a standalone application. Distributed COM (DCOM) is an extension to COM that allows COM objects to be accessed from client processes running on other computers in a network.

An automation server is a special kind of COM component that makes it possible for interpretive and macro languages, such as Visual Basic and Visual Basic for Applications (VBA) in
Excel, to access its objects at runtime.

OO4O is an in-process automation server. An in-process server is a DLL that is loaded and runs in the same address space as the Automation Controller.

The OraDatabase interface in OO4O is an example of an automation object that contains a connection to an Oracle database server, and provides methods for the execution of SQL and PL/SQL statements.

Automation servers such as OO4O are becoming increasingly popular due to their ease of use, object-oriented nature, and programming language neutrality.

Page 965

Getting Started

Let's look at an example to get a feel for how OO4O interfaces are used. Examples are presented in VB scripts throughout this chapter because VB and VBA seem to be the most popular development languages in the Microsoft Windows environments. As stated earlier, OO4O can be used in any environment or language that supports the OLE Automation technology.

The example that follows does the following:

• Creates an instance of the OO4O server
• Connects to an Oracle database (ExampleDB)
• Executes a query (select ename, empno, sal from emp)
• Displays the results, (values of ename, empno, and sal in the emp table) for all rows retrieved one row at a time in a dialog box.

 `Declare instance variables for all Automation objects to be used. Dim OracleDBSession As Object Dim EmployeeDatabase As Object Dim Employees As Object Dim EmployeeRecord As Object `Create an instance of OO4O Server and Obtain an OraSession Interface Set OracleDBSession = CreateObject(OracleInProcServer.XOraSession) `Establish a connection to the ExampleDB database. Set EmployeeDatabase = OracleDBSession.OpenDatabase("ExampleDB", "Scott/Tiger", &H0&) `Execute the query. Employees is a reference to an OraDynaset interface and `represents the result set of the query. Set Employees = EmployeeDataBase.CreateDynaset(`select ename, empno  from emp", &H0&) `Get a reference to the current row (initially row 0) Set EmployeeRecord = Employees.Fields `Loop through the result set and display them. while  Employees.EOF == FALSE     `While there are more employee rows in the result 'set `Display the name and employee number of the current employee record        MsgBox "Name: " & EmployeeRecord("ename").Value & "Emp #: " & EmployeeRecord("empno").Value & EmployeeRecord("sal").Value    `Move to next record    Employees.MoveNext Wend 

Page 966

Accessing the Automation Server

To access interfaces provided by an automation server, an instance of the server needs to be created first. In VB, this is done by calling the CreateObject function. The argument to this function is the component's ID. CreateObject returns an interface from which other interfaces may be obtained from the component. OraSession is the interface that is returned by OO4O when CreateObject is called.

In the preceding example, this was done with the following statement:

 OracleDBSession  = CreateObject(OracleInProcServer.XOraSession) 

OracleInProcServer.XOraSession is the version-independent program ID for OO4O that is registered in the Windows Registry by the Oracle client installation program. It is the symbolic name for the Class ID (CLSID), which is a globally unique identifier (GUID) that identifies the OO4O component. OracleDBSession is the variable that holds an instance of the OraSession interface. OraSession mainly serves as a factory object for creating OraDatabases.

Connecting to Oracle Database Servers

The OpenDatabase method of the OraSession interface is used to establish connections to Oracle databases. This method also returns a reference to the OraDatabase interface. OraDatabase provides methods for executing SQL statements and PL/SQL blocks.

The following statement connects to the ExampleDB database using Scott for the username and Tiger for the password. EmployeeDatabase is a reference to the OraDatabase interface returned by the OpenDatabase method of the OracleDBSession.

 EmployeeDatabase = OracleDBSession.OpenDatabase("ExampleDB", "Scott/Tiger", &H0&) 

ExampleDB is the SQL*Net alias that identifies the database in which the emp table resides. &H0& represents default options and is discussed in the context of other concepts.

Executing SQL Statements and PL/SQL Blocks

Dynaset creation methods (CreateDynaset, CreatePlsqlDynaset, CreateCustomDynaset, and CreateCustomPlsqlDynaset) along with ExecuteSQL and CreateSQL constitute all the methods provided by the OraDatabase interface for SQL statement and PL/SQL code execution.

An OraDynaset interface represents the result set of a SQL SELECT query or a PL/SQL cursor variable returned from a stored procedure. It is essentially a client-side scrollable (forward and backward) cursor that allows for browsing the set of rows generated by the query it executes.

The CreateDynaset method executes a SQL SELECT query, whereas CreatePlsqlDynaset executes a PL/SQL-stored procedure returning a PL/SQL cursor variable.

Previous Table of Contents Next