8 Introduction to Automation Objects

Overview of Automation Objects

The OO4O operational hierarchy of the objects expresses has-a and belongs-to relationships.

Figure 8-1 shows the operational hierarchy.

Figure 8-1 OO4O Automation Objects

Description of "Figure 8-1 OO4O Automation Objects"

The Automation objects diagram illustrates this hierarchy.

OraSession Object Overview

The OraSession object is returned when an instance of the OO4O Automation Server is created. It mainly serves as an interface for establishing connections to Oracle databases. It also contains methods for starting, committing, and canceling transactions on the connections contained in the OraDatabase objects created. The following Visual Basic example creates an instance of the OO4O Automation Server.

'OracleInProcServer.XOraSession is the symbolic name for a 
'globally unique component identifier. 
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
 

OraServer Object Overview

The OraServer object represents a physical connection to an Oracle database instance. It provides a method, OpenDatabase, for creating user sessions, which represents OraDatabase objects. It makes it possible to do "connection multiplexing."

OraDatabase Object Overview

The OraDatabase object represents a user connection to an Oracle database instance, and provides methods to execute SQL statements and PL/SQL code. The OraDatabase object is returned by the OpenDatabase method of the OraSession or the OraServer object.

The following example illustrates the use of the OpenDatabase method of the OraSession. OraDatabase objects created by this method contain a distinct physical connection to an Oracle database.

'Establish a connection to the ExampleDb database 
Set hrDBSession = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 

The following example demonstrates how a physical network connection to an Oracle database can be shared by multiple user sessions. Using a single connection that is shared by multiple user sessions results in reduced resource usage in an Oracle Database and can increase scalability.

'Create a server connection 
 
Set hrDBServer = CreateObject("OracleInProcServer.XOraServer") 
Set hrDBServer = oo4o.Open("ExampleDb") 
Set userSession1 = hrDBServer.OpenDatabase("scott/tiger", 0) 
 
'execute queries ... 
Set userSession2= hrDBServer.OpenDatabase("scott/tiger", 0) 
 
'execute queries ... 

OraDynaset Object Overview

An OraDynaset object represents the result set of a SQL SELECT query or a PL/SQL cursor variable returned from a stored procedure or function. It is essentially a client-side scrollable and updatable cursor that allows for browsing the set of rows generated by the query it executes. It is created by the CreateDynaset or CreateCustomDynaset method of an OraDatabase interface.

The following Visual Basic example executes a query, loops through the result set, and displays values of columns returned.

Set employees = OraDatabase.CreateDynaset("select empno, ename from emp", 0&)
 
'While there are more rows 
while not employees.EOF 
 
'Display the values of empno and ename column of the current row 
msgbox employees("empno") & employees("ename") 
 
'Move to the next row 
employees.MoveNext 
wend

OraField Object Overview

The OraField object is an abstraction of a column in an OraDynaset object. It contains the value as well as the metadata that describes a column of the current row in the dynaset. In the previous example for the OraDynaset object, the Field interface for empno can be obtained using this additional code:

set empno = employees.Fields("empno") 
msgbox "Employee Number: " & empno.Value 

OraFields is a collection object representing all columns in the current row.

OraField objects can represent instances of any data type supported by Oracle Database. This includes all primitive types, such as VARCHAR2, NUMBER, INT, and FLOAT, as well all the object-relational types introduced in Oracle8i.

OraParameters Object Overview

The OraParameters object is a collection container for OraParameter objects. An OraParameter object is used to supply data values for placeholders used in the SQL statements or PL/SQL blocks at run time. It can be used to provide input values as well as contain values that are returned from the database. The following sample creates two parameter objects and uses them in an update query.

OraDatabase.Parameters.Add "SALARY", 4000, ORAPARM_INPUT 
OraDatabase.Parameters.Add "ENAME", "JONES", ORAPARM_INPUT 
Set updateStmt = OraDatabase.CreateSQL("update emp set sal = :SALARY" & _ 
             "where ename = :ENAME ") 

OraParameter Object Overview

OraParameter objects can contain values for all the data types supported by Oracle9i including object-relational data types. They can be passed as input or output arguments to PL/SQL stored procedures and functions. The values of the OraParameter objects can also represent PL/SQL cursors in the form of OraDynaset objects.

OraParamArray Object Overview

An OraParamArray object provides the mechanism for binding and fetching an array of values. It is typically used for performing bulk inserts and updates.

'Create a table 
OraDatabase.ExecuteSQL ("create table part_nos(partno number," & _
             "description char(50), primary key(partno))") 
 
'Create two parameter arrays of size 10 to hold values for 
'part numbers (size 22 bytes), and their description (50 bytes long). 
OraDatabase.Parameters.AddTable "PARTNO", ORAPARM_INPUT, ORATYPE_NUMBER, 10, 22 
OraDatabase.Parameters.AddTable "DESCRIPTION", ORAPARM_INPUT, _ 
            ORATYPE_CHAR, 10, 50 
 
'Initialize the arrays 
For I = 0 To 10 
   OraDatabase.Parameters("PARTNO").put_Value = I, I 
   OraDatabase.Parameters("DESCRIPTION ") = "some description", I 
Next I 
 
'Execute the query 
Set OraSqlStmt = OraDatabase.CreateSql("insert into " & _
            "part_nos(partno, description) values(:PARTNO,:DESCRIPTION)", 0&)

OraSQLStmt Object Overview

The OraSQLStmt object is typically used for executing non-select SQL queries and PL/SQL blocks. The following line of code executes an update query and displays the number of rows affected.

Set updateStmt = OraDatabase.CreateSQL("update emp set sal = 3000" & _ 
                "where ename = 'JONES' ") 
MsgBox updateStmt.RecordCount 

The OraSQLStmt object (updateStmt) can be used later to execute the same query with a different value for the :SALARY placeholder. For example:

OraDatabase.Parameters("SALARY").value = 200000 
updateStmt.Parameters("ENAME").value = "KING" 
updateStmt.Refresh