4 Advanced OO4O Features

Oracle LOBs, Objects, and Collections

Table 4-1 lists Oracle LOBs, Objects, and collection types with associated OO4O interfaces.

How the preceding interfaces are retrieved in OO4O depend on how they are stored in the database or accessed in a SQL statement. These are the possible scenarios:

• Column of a table

  If a table contains LOBs, object types, and collections as columns and the dynaset SELECT statement is based on this table, then the Value property of the OraField object representing that column returns corresponding OO4O interfaces for that type.

• Bind variable in a SQL statement or PL/SQL block

  If a SQL statement or PL/SQL block has LOBs, object types, and collections as bind variables, then an OraParameter object should be created with a corresponding server type using the Add method. The Value property of the OraParameter object representing that bind variable returns the corresponding OO4O interfaces for that type.

• Attribute of an Oracle object instance

  If an Oracle object instance has LOBs, object types, or collections as attributes, then the corresponding OO4O interface for any attribute is retrieved by using the subscript or name of the attribute from the OraObject or OraRef, or by using the Value property of an OraAttribute object.

• Element of VARRAY and nested table

  If an Oracle VARRAY and nested table has object types and REF as its elements, then the corresponding OO4O interface is retrieved using the element index as the subscript from the OraCollection object.

When OO4O interfaces for these types are retrieved as part of a dynaset, then the OO4O interfaces represent instances of LOBs, objects, and collection types for the current row of the dynaset. If the current row changes due to a move operation, then the OO4O interfaces represent instances of LOBs, objects, and collection types for the new current row. When OO4O interfaces for these types are retrieved as part of an OraParameter object and the OraParameter value changes to due to a OraSQLStmt Refresh method, then the OO4O interface represents a new instance LOB, object, and collection type for that OraParameter.

Internally, OO4O maintains one OO4O interface for each OraField, OraParameter, and OraAttribute object. To retain the instance of LOBs, objects, and collection types independent of a dynaset move operation or an OraSQLStmt refresh operation, use the Clone method on the corresponding OO4O interface. This method makes a copy of LOBs, objects, and collection types instance and returns a corresponding OO4O interface associated with that copy.

LOB Data Types

Table 4-2 lists the four LOB data types and their corresponding OO4O interfaces.

The following example creates a table that has BLOB and CLOB columns, and inserts rows into the table using the ExecuteSQL method on an OraDatabase object.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
InvDb.ExecuteSQL("create table part(part_id NUMBER, part_name 
VARCHAR2(20),part_image BLOB, part_desc CLOB)") 
InvDb.ExecuteSQL ("insert into part values (1,'ORACLE NETWORK',EMPTY_BLOB()," & _
              "EMPTY_CLOB())") 
InvDb.ExecuteSQL ("insert into part values (2,'ORACLE SERVER', EMPTY_BLOB()," & _
           "EMPTY_CLOB())") 

The EMPTY_BLOB() and EMPTY_CLOB() PL/SQL functions provide an empty LOB to insert into the LOB column.

Using OraBLOB and OraCLOB

OraBLOB and OraCLOB interfaces in OO4O provide methods for performing operations on large objects in the database including BLOB, CLOB, and NCLOB, and BFILE data types.

The following Visual Basic example illustrates how to read the PartImage from the part table:

Dim Buffer as Variant
Set Part = OraDatabase.CreateDynaset("select * from part", 0&)
set PartImage = OraDynaset.Fields("part_image").Value
 
'read the data into the buffer
amount_read = PartImage.Read(buffer)
 
'copy the image content into the file
PartImage.CopyToFile "d:\image\partimage.jpg"

Retrieving LOBs From the Database

OraBlob, OraClob, and OraBFile objects can be retrieved using an OraDynaset object or a parameter object:

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
Set Part = InvDb.CreateDynaset("select * from part", 0&) 
Set PartDesc = Part.Fields("part_desc").Value 
Set PartImage = Part.Fields("part_image").Value 

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
InvDb.Parameters.Add "PartDesc", Null, ORAPARM_OUTPUT,ORATYPE_CLOB 
InvDb.Parameters.Add "PartImage", Null, ORAPARM_OUTPUT,ORATYPE_BLOB 
InvDb.ExecuteSQL ("BEGIN select part_desc, part_image into :PARTDESC," & _
             ":PARTIMAGE from part where part_id = 1 for update NOWAIT; END;") & _
             "for update NOWAIT; END;") 
Set PartDesc = InvDb.Parameters("PartDesc").Value 
Set PartImage = InvDb.Parameters("PartImage").Value 

Performance Considerations with LOB Read and Write

When reading and writing LOBs, there are several options that can optimize an application's memory usage and reduce the number of network round-trips.

Writing LOB Data

The Write method of the OraBlob and OraClob objects writes data from a local buffer to a LOB in the database. The CopyFromFile (OraLOB) method writes content of a local file to a LOB in the database.

Any operation that changes the value of a LOB, including the Write method, can only occur when the row the LOB is associated with has been locked. If a LOB field is null, it must first be updated with an empty LOB before a method can write to the LOB field.

LOB data can be written in one piece or in a series of multiple pieces., as described in the following topics:

• Single-Piece Write Operation

• Multiple-Piece Write Operation

Dim buffer() as byte 
ReDim buffer(10000) 
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
Set Part = InvDb.CreateDynaset("select * from part", 0&) 
Set PartImage = Part.Fields("part_image").Value 
PartImage.Offset = 1000 
FNum = FreeFile 
Open "PartImage.Dat" For Binary As #FNum 
Get #FNum, , buffer 
Part.Edit 
 
amount_written = PartImage.Write(buffer) 
Part.Update 
Close FNum 

Part.Edit 
PartImage.CopyFromFile "PartImage.dat" , 10000, 1000 
Part.Update 

Dim buffer() as byte 
chunksize = 10000 
ReDim buffer(chunksize) 
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
Set Part = InvDb.CreateDynaset("select * from part", 0&) 
Set PartImage = Part.Fields("part_image").Value 
 
FNum = FreeFile 
Open "PartImage.Dat" For Binary As #FNum 
PartImage.Offset = 1000 
PartImage.PollingAmount =102000 
remainder = 102000 
Part.Edit 
Get #FNum, , buffer 
amount_written = PartImage.Write(buffer, chunksize, ORALOB_FIRST_PIECE) 
 
While PartImage.Status = ORALOB_NEED_DATA 
remainder = remainder - chunksize 
If remainder < chunksize Then 
piecetype = ORALOB_LAST_PIECE 
chunksize = remainder 
 
Else 
piecetype = ORALOB_NEXT_PIECE 
End If 
Get #FNum, , buffer 
amount_written = PartImage.Write(buffer, chunksize, piecetype) 
Wend 
Close FNum 
Part.Update 

Reading LOB Data

The OraBlob and OraClob Read method reads data to a local buffer from a LOB in the database. The CopyFromFile method reads the contents of a LOB into a local file.

LOB data can be read in one piece or in a series of multiple pieces, as described in the following topics:

• Single-Piece Read Operation

• Multiple-Piece Read Operation

Dim buffer as Variant 
Dim buf() As Byte 
chunksize = 10000 
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
Set Part = InvDb.CreateDynaset("select * from part", 0&) 
Set PartImage = Part.Fields("part_image").Value 
FNum = FreeFile 
Open "image.dat" For Binary As #FNum 
PartImage.Offset = 1000 
amount_read = PartImage.Read(buffer,10000) 
buf = buffer 
Put #FNum, , buf 
Close FNum 

PartImage.CopyToFile "image.dat" , 10000, 1000 

Dim buffer as Variant 
Dim buf() As Byte 
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0) 
Set Part = InvDb.CreateDynaset("select * from part", 0&) 
Set PartImage = Part.Fields("part_image").Value 
FNum = FreeFile 
Open "image.dat" For Binary As #FNum 
PartImage.offset = 1000 
PartImage.PollingAmount = 102000 
amount_read = PartImage.Read(buffer, chunksize) 
buf = buffer 
Put #FNum, , buf 
While PartImage.Status = ORALOB_NEED_DATA 
amount_read = PartImage.Read(buffer, chunksize) 
buf = buffer 
Put #FNum, , buf 
Wend 
Close FNum 

About the OraObject Interface

The OraObject interface is a representation of an Oracle embedded object or a value instance. It contains a collection interface (OraAttributes) for accessing and manipulating (updating and inserting) individual attributes of a value instance.

Individual attributes of an OraAttributes collection interface can be accessed by using a subscript or the name of the attribute.

The following Visual Basic example illustrates how to access attributes of the Address object in the person_tab table:

Set Person = OraDatabase.CreateDynaset("select * from person_tab",0&) 
set Address = Person.Fields("Addr").Value 
msgbox Address.Zip 
msgbox.Address.City 

Using the OraObject Interface

The following example creates an ADDRESS object type having street, city, state and zip as its attributes and a PERSON table having an ADDRESS object type column. It also inserts data using the ExecuteSQL method of the OraDatabase object.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
HRDb.ExecuteSQL("create type ADDRESS as object ( street 
VARCHAR2(200), city varchar2(20), state CHAR(2), zip varchar2(10) )")  
HRDb.ExecuteSQL("create table person (name varchar2(20), age number," & _ 
                 "addr ADDRESS) ") 
HRDb.ExecuteSQL("insert into person values('nasser',40, " & _
                 "address('Wine Blvd', 'Pleasanton', 'CA', '94065'))") 
HRDb.ExecuteSQL("insert into person values('Maha', 25," & _ 
                 "address('Continental Way', 'Belmont', 'CA', '94002'))")
HRDb.ExecuteSQL("insert into person values('chris',30, address('First " & _
                 "Street', 'San Francisco', 'CA' ,'94123'))") 

The following topics discuss manipulating the OraObject interface:

• Retrieving an Embedded/Value Instance from the Database

• Accessing Attributes of an Embedded/Value Instance

• Modifying Attributes of an Embedded/Value Instance

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
set Person = hrDb.CreateDynaset("select * from person", 0&) 
set Address = Person.Fields("Addr").Value 

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
hrDb.Parameters.Add "ADDRESS", Null, ORAPARM_OUTPUT, ORATYPE_OBJECT, "ADDRESS"
'execute the sql statement which selects Address from the person_tab 
hrDb.ExecuteSQL ("BEGIN select Addr into :ADDRESS from person where " & _
            "age = 40; end;") 
'retrieve Address object from the OraParameter 
set address = hrDb.Parameters("ADDRESS").Value 

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
set Person = hrDb.CreateDynaset("select * from person", 0&) 
set Address = Person.Fields("Addr").Value 
msgbox Address.City 
msgbox Address.Street 
msgbox Address.State 
msgbox Address.Zip 

For I=1 to Address.Count 
   msgbox Address(I) 
Next I 

set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0)
set Person = hrDb.CreateDynaset("select * from person", 0&)
set Address = Person.Fields("Addr").Value
Person.Edit
      Address.Street  =  "Oracle Parkway"
      Address.City = "Redwood shores"
Person.Update

CREATE OR REPLACE TYPE bank_account AS OBJECT ( 
    acct_number INTEGER(5), 
    balance REAL, 
    MEMBER PROCEDURE open (amount IN REAL), 
    MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL), 
    MEMBER PROCEDURE deposit (SELF IN OUT bank_bccount,num IN 
                        INTEGER, amount IN REAL), 
); 

Dim BankObj as OraObject 
assumes that we have valid BankObj 
set BankObj = ..... 
 
'create a OraParameter object for bank_account object and set it to BankObj 
OraDatabase.Parameters.Add "BANK", BankObj, ORAPARM_BOTH, ORATYPE_OBJECT, _
                     "BANK_ACCOUNT" 
 
'create a OraParameter object for num argument and set the value to 100 
OraDatabase.Parameters.Add "ACCOUNT_NO", 100, ORAPARM_INPUT, ORATYPE_NUMBER 
 
'create a OraParameter object for amount argument and set the value to 1200 
OraDatabase.Parameters.Add "AMOUNT", 1200, ORAPARM_OUTPUT, ORATYPE_NUMBER
 
'display the balance from the bank object 
Bankobj.balance 
 
'now execute the PL/SQL block for member method execution 
OraDatabase.ExecuteSQL ("BEGIN BANK_ACCOUNT.DEPOSIT :BANK," & _ 
                    (":ACCOUNT_NO,:AMOUNT); END;") 
 
'get the modified bank object from the parameter 
set Bankobj  = OraDatabase.Parameters("BANK").Value 
 
'display the new balance 
Bankobj.balance 

About the OraRef Interface

The OraRef interface represents an instance of a referenceable object (REF) in client applications. The object attributes are accessed in the same manner as attributes of an object represented by the OraObject interface. The OraRef interface is derived from an OraObject interface through the containment mechanism in COM. REF objects are updated and deleted independently of the context from which they originated, such as dynasets. The OraRef interface also encapsulates the functionality for navigating through graphs of objects utilizing the Complex Object Retrieval Capability (COR) in Oracle Call Interface (OCI).

Using the OraRef Interface

This section demonstrates the creation of an object table named PERSON_TAB. The object table is based on the object type PERSONOBJ. Each reference to the rows of this object table is stored in an aperson REF type column of the CUSTOMERS table. The following code creates database schemas:

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
HRDb.ExecuteSQL("create type PERSONOBJ  as object ( name varchar2(20), " & _
                 "age number, addr ADDRESS)")
HRDb.ExecuteSQL("create table person_tab of personobj") 
HRDb.ExecuteSQL("insert into person_tab values('nasser',40," & _ 
                  "address('Wine Blvd', 'Pleasanton', 'CA', '94065'))") 
HRDb.ExecuteSQL("insert into person_tab values('Maha', 25, " & _
                  "address('Continental Way', 'Belmont', 'CA', '94002'))") 
HRDb.ExecuteSQL("insert into person_tab values('chris',30, " & _
               "address('First Street', 'San Francisco', 'CA' , '94123'))") 

The following code creates a CUSTOMERS table having an aperson REF column referencing rows of the object table:

HRDb.ExecuteSQL("create table CUSTOMERS (account number, 
aperson REF personobj)") 
 
HRDb.ExecuteSQL("insert into customers values(10, null)") 
HRDb.ExecuteSQL("insert into customers values(20, null)") 
HRDb.ExecuteSQL("insert into customers values(30, null)") 
HRDb.ExecuteSQL("update customers set aperson = (select ref(p) from " & _
                 "person_tab p where p.name = 'nasser') where account = 10") 
HRDb.ExecuteSQL("update customers set aperson = (select ref(p) from " & _
                 "person_tab p where p.name = 'Maha') where account = 20") 
HRDb.ExecuteSQL("update customers set aperson = (select ref(p) from " & _
                 "person_tab p where p.name = 'chris') where account = 30") 

The following topics discuss manipulating the OraRef Interface:

• Retrieving a REF from the Database

• Accessing Attributes of a Referenceable Instance

• Modifying Attributes of a Referenceable Instance

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
set Customer = hrDb.CreateDynaset("select * from customers", 0&) 
set Person = Customer.Fields("aperson").Value 

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
hrDb.Parameters.Add "PERSON", Null, ORAPARM_OUTPUT, ORATYPE_REF,"PERSONOBJ"
 
'execute the sql statement which selects Address from the person_tab 
hrDb.ExecuteSQL ("BEGIN select aperson into :PERSON from customers" & _ 
                "where account = 10; end;") 
 
'retrieve Person object from the OraParameter 
set Person = hrDb.Parameters("PERSON").Value 

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
set Customer = hrDb.CreateDynaset("select * from customers", 0&) 
set Person = Customer.Fields("APERSON").Value 
msgbox Person.Name 
set Address = Person.Addr 
msgbox Address.City 

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
set Customer = hrDb.CreateDynaset("select * from customers", 0&) 
set Person = Customer.Fields("APERSON").Value 
Person.Edit 
Person.Age = 45 
Person.Update 

About the OraCollection Interface

The OraCollection interface provides methods for accessing and manipulating Oracle collection types, namely variable-length arrays (VARRAYs) and nested tables in OO4O. Elements contained in a collection are accessed by subscripts.

The following Visual Basic example illustrates how to access attributes of the EnameList object from the department table:

Set Person = OraDatabase.CreateDynaset("select * from department",0&)
set EnameList = Department.Fields("Enames").Value
 
'access all elements of the EnameList VArray
for I=1 to I=EnameList.Size
   msgbox EnameList(I)
Next I

Retrieving a Collection Type Instance from the Database

A collection type can be retrieved using OO4O in the following ways:

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
Set Dept = hrDb.CreateDynaset("select * from department", 0&) 
Set EnameList = Dept.Fields("ENAMES").Value 

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
hrDb.Parameters.Add "ENAMES", Null, ORAPARM_OUTPUT, ORATYPE_VARRAY, "ENAMELIST" 
hrDb.ExecuteSQL ("BEGIN select enames into :ENAMES from department" & _ 
              "where dept_id = 10; END;") 
set EnameList = hrDb.Parameters("ENAMES").Value 

Accessing Collection Elements

Individual element values are accessed by using a subscript. For example, the Value returned by the OraCollection object for subscript 1 is the element value at index 1. The maximum value of the subscript is equal to the total number of elements in the collection including any deleted elements. The OraCollection subscript starts from 1.

The following example code retrieves the Enamelist collection instance and accesses its elements at the first and second index.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
Set Dept = hrDb.CreateDynaset("select * from department", 0&) 
Set EnameList = Dept.Fields("ENAMES").Value 
msgbox EnameList(1) 
msgbox EnameList(2) 

This code displays all the element values of the EnameList collection.

For I = 1 to EnameList.Size 
   msgbox EnameList(I) 
Next I 

Modifying Collection Elements

If the collection instance is retrieved using a dynaset object, element values can be modified between a dynaset Edit and Update pair. The following example code modifies the second element value of an Enamelist collection instance.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0) 
 
Set OraDynaset = hrDb.CreateDynaset("select * from department", 0&) 
Set EnameList = OraDynaset.Fields("ENAMES").Value 
 
OraDynaset.Edit 
    EnameList(2) = "Chris" 
OraDynaset.Update 

Creating a VARRAY Collection Type

The example code that follows creates a VARRAY collection type ENAMELIST and a department table having ENAMELIST collection type column.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 
set hrDb = OO4OSession.OpenDatabase("Exampledb", "scott/tiger", 0)
hrDb.ExecuteSQL("CREATE TYPE ENAMELIST AS VARRAY(20) OF VARCHAR2(30)") 
hrDb.ExecuteSQL("CREATE TABLE department (dept_id NUMBER(2),name" & _ 
               "VARCHAR2(15),ENAMES ENAMELIST)") 

The following script inserts some collection data into department table:

hrDb.ExecuteSQL("INSERT INTO department VALUES(10, 'ACCOUNTING'," & _ 
               "ENAMELIST('KING','CLARK','MILLER') )") 
hrDb.ExecuteSQL("INSERT INTO department VALUES(20, 'RESEARCH'," & _ 
               "ENAMELIST('JONES','SCOTT','ADAMS','SMITH','FORD') )") 
hrDb.ExecuteSQL("INSERT INTO department VALUES(30, 'SALES'," & _ 
               "ENAMELIST('BLAKE','MARTIN','ALLEN','TURNER','JAMES') )")

Creating a Dynaset from an OraCollection Object

A SELECT query can be issued against instances of the VARRAY and nested table collection types using SQL THE or TABLE operators and individual elements can be accessed as rows. If these collection types have object types for element types, then individual attributes of the object type represents fields of a row.

For example, if an object type X has attributes a, b, and c, and the element type of the collection is object type X, then the SELECT query on this collection returns a, b, and c fields.

In OO4O, read-only dynaset objects can be created from SELECT queries on the collection. Individual elements are accessed using row navigation. If the collection type has an object type as its element type, then attributes of that object type (element) are accessed using the OraField object.

This discussion assumes you have a Course object type and a CourseList nested table collection type with Course as its element type, as described here:

CREATE TYPE Course AS OBJECT ( 
    course_no NUMBER(4), 
    title VARCHAR2(35), 
    credits NUMBER(1) 
); 
CREATE TYPE CourseList AS TABLE OF Course;

In OO4O, CourseList OraCollection represents an instance of the CourseList collection type.

Dim CourseList as OraCollection 

Assume that you have valid a CourseList collection instance:

set CourseList = ...... 

The SQL THE or TABLE operator needs collection type as a bind variable. Create a OraParameter object for the CourseList OraCollection as follows:

OraDatabase.Parameters.Add "COURSELIST", CourseList, ORAPARM_INPUT, _
            ORATYPE_TABLE, "COURSELIST" 

Create a read-only dynaset based on the CourseList using the SQL THE operator:

Set CourseListDyn = OraDatabase.CreateDynaset("select * from THE (select" & _
           "CAST(:COURSELIST AS COURSELIST) from dual)", ORADYN_READONLY)

You can also create a read-only dynaset based on the CourseList using the SQL TABLE operator, which is available only in OO4O with libraries from release Oracle9i and on:

Set CourseListDyn = OraDatabase.CreateDynaset("select * from" & _
          "TABLE(CAST(:COURSELIST AS COURSELIST))", ORADYN_READONLY) 

'display the course_no field 
msgbox CourseListDyn.Fields("course_no").Value 

'display the title field 
msgbox CourseListDyn.Fields("title").Value 

'move to next row 
OraDynaset.MoveNext 

Example: Creating a Dynaset from an OraCollection Object

The following example illustrates how to create a dynaset from an OraCollection object. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples".

Dim OraSession as OraSession 
Dim OraDatabase as OraDatabase 
Dim OraDynaset as OraDynaset 
Dim CourseList as OraCollection 
Dim Course as OraObject 
Dim CourseListDyn as OraDynaset 
 
'create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger",  0&) 
 
'create a dynaset object from division 
set OraDynaset = OraDatabase.CreateDynaset("select * from division", 0&) 
 
'retrieve a Courses column from Division. Here Value property 
'of OraField object 'returns CourseList OraCollection 
set CourseList = OraDynaset.Fields("Courses").Value 
 
'create a input parameter for CourseList for nested table dynaset 
OraDatabase.Parameters.Add "COURSELIST", CourseList, ORAPARM_INPUT, _
                 ORATYPE_TABLE, "COURSELIST" 
 
'create a read only dynaset based on the CourseList. 
Set CourseListDyn = OraDatabase.CreateDynaset("select * from" & _ 
                 "THE(select CAST(:COURSELIST AS COURSELIST) from dual)", _
                 ORADYN_READONLY) 
 
'dynaset can also be created from Oracle8 collection using the
'following statement 
'Set CourseListDyn = OraDatabase.CreateDynaset("select * from    
'TABLE(CAST(:COURSELIST AS COURSELIST))", ORADYN_READONLY) 

'get the field values of the collection dynaset 
msgbox CourseListDyn.Fields("title").Value 
msgbox CourseListDyn.Fields("course_no").Value 
 
'move the original dynaset to second row 
Oradynaset.MoveNext 
 
'set the new value of CourseList  collection from the second row 
'of main dynaset to the "COURSELIST" parameter 
OraDatabase.Parameters("COURSELIST").Value = CourseList 
 
'refresh the collection dynaset. Now the collection dynaset values are refreshed 
'with new collection value. CourseListDyn.Refresh 
'get the field values of the collection dynaset 
msgbox CourseListDyn.Fields("title").Value 
msgbox CourseListDyn.Fields("course_no").Value 

Monitoring Messages

The OraAQ monitor methods (MonitorStart and MonitorStop) provide asynchronous dequeuing through notifications. This is suitable for applications that prefer to process messages in nonblocking mode. Applications can request to be notified on arrival of messages, by supplying an Automation object to the Monitor method. This object implements a method called NotifyMe to receive notifications. Messages can be monitored based on consumer name, message ID, or correlation.

The following sample code demonstrates a simple use of this facility. It illustrates a computerized trading system that executes buy/sell limit orders.

The sample instantiates a queue object for the STOCKS_TO_TRADE queue and monitors messages intended for consumer BROKER_AGENT. STOCKS_TO_TRADE queues messages of the user-defined type TRADEORDER_TYPE. This encapsulates all the information required to initiate a trade order. When messages addressed to the BROKER_AGENT are dequeued, the NotifyMe method of the CallbackClient object is invoked, and a stock trade is performed.

'First instantiate the CallbackClient. The queue monitor 
' will invoke the NotifyMe on this class module.
Public CB_Client As New CallbackClient 
 
  Dim DB As OraDatabase
  Dim Q as OraAQ
  set Q = DB.CreateAQ("STOCKS_TO_TRADE") 

'Notify by calling cbclient::NotifyMe when there are messages
' for consumer '"BROKER_AGENT"
  Q.consumer = "BROKER_AGENT"

'Note that cbclient is a dispatch interface that supports the NotifyMe method.
  Dim s as string
  s = "BROKER_AGENT"
 'Notify the client only when there are messages for "BROKER_AGENT"
  Q.MonitorStart CB_Client, Q, s, 1
'other processing is performed here... 
 
  Q.MonitorStop 
Return 
'Now implement the NotifyMe method of the CallbackClient class module
'and the necessary arguments that will contain the dequeued message
'NotifyMe is the callback interface defined by user. Ctx here is the
'Q object passed in at the time of MontiorStart.
Public sub NotifyMe (ByVal Ctx As Variant, ByVal Msgid As Variant )
  On Error GoTo NotifyMeErr
  Dim tradingSignal as OraAQMsg
  'Tradeorder contains details of the customer order
  Dim tradeorder as OraObject
  If IsNull(Msgid) Then
          MsgBox "No Message"
          'Get Error
          MsgBox OraDatabase.LastServerErrText
  Else
          mvarMsgid = Msgid
          Set tradingSignal = Ctx.AQMsg(1,"STOCK_TYPE","TRADER")
          set tradeorder = tradingSignal.Value

          'Tradeorder is the object of UDT "STOCK_TYPE"Access signal attribute 
          'of tradeorder as tradeorder("signal).Value or tradeorder!signal
          if (tradeorder!signal = "SELL")
            'Sell the stock
             SellStock(tradeorder!NoOfShares, tradeorder!Ticker, _
                    tradeorder!Price,  tradeorder!ValidUntil)
          else if (tradeorder!signal = "BUY")
             'Buy the stock
              BuyStock(tradeorder!NoOfShares,tradeorder!Ticker, _
                   tradeorder!Price,tradeorder!ValidUntil)
          end if
  End If
NotifyMeErr:
  Call RaiseError(MyUnhandledError, "newcallback:NotifyMe Method")
End Sub

Failover Notification Registration

To address the problems described, OO4O supports application failover notifications. To receive failover notifications, a notification handler must be registered with the MonitorForFailover method of the OraDatabase object. The notification handler must be an automation object (class module in Visual Basic) that implements the OnFailover method. An IDispatch pointer to this automation object must be passed in, along with any client-specific context, at the time of registering for failover notifications.

In the event of failover, the OnFailover method is invoked several times during the course of reestablishing the user's session. The first call to the OnFailover method of the notification handler occurs when the database first detects an instance connection loss. This is intended to allow the application to inform the user of an upcoming delay. If a failover is successful, a second call to the OnFailover method occurs when the connection is reestablished and usable. At this time, the client may want to replay the ALTER SESSION commands and inform the user that a failover has happened.

If a failover is unsuccessful, then the OnFailover method is called to inform the application that the failover will not take place.

An example of failover registration is included as part of the example in the next section.

Enabling Failover

To enable failover notifications, the option ORADB_ENLIST_FOR_CALLBACK must be passed into the call to the OpenDatabase method.

Example: Failover Notification

The following sample shows a typical developer-defined OnFailover implementation and demonstrates how to register an application.

'Implement the OnFailover method of the FailoverClient class module and the 
' necessary arguments that will contain the dequeued message. Ctx  here is
' the application-defined context sensitive object that was passed
' in while registering with MonitorForFailover.
' An error of OO4O_FO_ERROR indicates that failover was unsuccessful, but the 
' application can handle the and retry failover by returning  
' a value of OO4O_FO_RETRY 
 
Public Function OnFailover(Ctx As Variant, fo_type As Variant,fo_event _
                     as variant, fo_OraDB as Variant) 
Dim str As String 

OnFailover=0 
str = Switch(fo_type = 1&, "NONE", fo_type = 2&, "SESSION", fo_type = _ 
                  4&, "SELECT") 
If IsNull(str) Then 
   str = "UNKNOWN!" 
End If 
If fo_event= OO4O_FO_ERROR Then 
   MsgBox "Failover error gotten. Retrying " 
   OnFailover = OO4O_FO_RETRY 
   End If 
If fo_event = OO4O_FO_BEGIN Then 
   MsgBox " Failing Over .... with failover type : " & str 
Else 
   MsgBox "Failover Called with event : " & fo_event 
End If 
End Function 

Registering the Application to Receive Failover Notifications

' First instantiate the Failover_Client. The Failover notification 
' will invoke the OnFailover on this class module 
 
Public Failover_Client As New FailoverClient 
Dim OraDatabase As OraDatabase 
Dim OraSession As OraSession 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
' Pass in the entire database name (ie., the entire Tnsnames entry 
' with the domain name)in the opendatabase call 
Set OraDatabase = OraSession.DbOpenDatabase("Exampledb.us.oracle.com", _ 
          "scott/tiger", ORADB_ENLIST_FOR_CALLBACK) 
OraDatabase.MonitorForFailover Failover_Client, OraDatabase

Obtaining Datetime and Interval Data Types

OO4O datetime and interval data types can be obtained using:

• The Value property of an OraField object in a dynaset.

• The Value property of an OraParameter object as an input or an output parameter in SQL statements or PL/SQL blocks.

• An attribute of another object or REF.

• The following OraSession methods:

  • CreateOraIntervalDS

  • CreateOraIntervalYM

  • CreateOraTimeStamp

  • CreateOraTimeStampTZ

Descriptions of Datetime and Interval Data Types

• OraTimeStamp object

  Provides methods for operations on Oracle TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE data types. Operations include accessing the datetime values and performing datetime operations.

• OraTimeStampTZ object

  Provides methods for operations on Oracle TIMESTAMP WITH TIME ZONE data types. Operations include accessing the datetime and time zone values and performing datetime operations.

• OraIntervalDS object

  Provides methods for operations on the Oracle INTERVAL DAY TO SECOND. This data type represents a period of time in terms of days, hours, minutes, seconds, and nanoseconds.

• OraIntervalYM object

  Provides methods for operations on the Oracle INTERVAL YEAR TO MONTH. This data type represents a period of time in terms of years and months.