Automation Objects

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

The Automation Objects diagram illustrates this hierarchy.

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 that are 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

The OraServer object represents a physical connection to an Oracle database server instance. It provides a method (OpenDatabase) for creating user sessions (OraDatabases) on the server connection it contains.

OraDatabase

The OraDatabase object represents a user connection to an Oracle database instance, and provides methods for execution of SQL commands and PL/SQL code. It 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 server.

'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 server can be shared by multiple user sessions. Using a single connection that is shared by multiple user sessions results in reduced resource usage in the Oracle server 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

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

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

set empno = employees.Fields("empno")

msgbox "Employee Number: " & empno.Value

where Fields is a collection object representing all columns in the current row. Accessing column values in the manner illustrated in the previous example prior to the browse loop is the most efficient mechanism and should be used where performance is a critical factor.

OraField objects can represent instances of any datatype supported by the Oracle database server. This includes all primitive types, such as VARCHAR2, NUMBER, INT, and FLOAT, as well all the object/relational types introduced in Oracle8i. See Support for Oracle Datatypes.

OraMetaData

The OraMetaData object is returned by invoking the Describe method of the OraDatabase interface. The describe method takes the name of a schema object, such as the emp table, and returns an OraMetaData object. The OraMetaData object provides methods for dynamically navigating and accessing all the attributes (OraMDAttribute collection) of a schema object described.

The following Visual Basic example illustrates a simple use of this facility. This code sample retrieves and displays several attributes of the emp table.

Set empMD = OraDatabase.Describe("emp")

'Display the name of the Tablespace

msgbox empMD("tablespace")

'Display name, data type, and size of each column in the emp table.

Set empColumnsMD = empMD("Columns")

for I = 1 to empColumns.Count

Set ColumnMD = empColumnsMD(I)

MsgBox ColumnMD("Name") & ColumnMD("Datatype")

& ColumnMD("Length")

Next I

OraParameters

The OraParameters is a collection container for the 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 server. The following sample creates a couple of 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

OraParameter objects can contain values for all the datatypes 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. See PL/SQL Supports.

OraParamArray

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

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.CeateSQL("update emp set sal = 3000

where ename = 'JONES' ")

MsgBox updateStmt.RecordCount

The SQLStmt object (updateStmt) can be later used to execute the previous query using a different value for the :SALARY placeholder. For example:

OraDatabase.Parameters("SALARY").value = 200000

updateStmt.Parameters("ENAME").value = "KING"

updateStmt.Refresh

OraAQ

The OraAQ Automation interface provides methods for enqueuing and dequeuing messages (OraAQMsg). It also provides a method for monitoring queues for message arrivals. Client applications are notified when messages of interest are dequeued with the dispatch interface provided by client.

An OraAQ object is instantiated by invoking the CreateAQ method of the OraDatabase interface.

OraAQMsg

The OraAQMsg object contains the message to be enqueued or dequeued. The message can be a raw message or any user-defined type.

OraAQAgent

The OraAQAgent object represents a message recipient and is only valid for queues which allow multiple consumers. An OraAQAgent object can be instantiated by invoking the AQAgent property of OraAQMsg.

The following Visual Basic example illustrates a simple use of the advanced queuing feature. In this example, a message of user-defined type (MESSAGE_TYPE) is enqueued into a queue (msg_queue) which supports multiple consumers.

Dim q as OraAQ

Dim qMsg as OraAQMsg

Dim agent as OraAQAgent

Set q = OraDatabase.CreateAQ("msg_queue")

Set qMsg = q.AQMsg(1,"MESSAGE_TYPE")

'To add SCOTT as a recipient for the message,

Set agent = qMsg.AQAgent("SCOTT")

'To enqueue,

q.Enqueue