Executing Queries

Queries are statements that retrieve data from a database. A query can return zero, one, or many rows of data. All queries begin with the SQL keyword SELECT, as in the following example:

SELECT ename, empno FROM emp

In OO4O, SELECT statements such as this are used with the CreateDynaset method of the OraDatabase interface to execute queries. This method returns an OraDynaset object that is then used to access and manipulate the set of rows returned. An OraDynaset object encapsulates the functionality of a client-side scrollable (forward and backward) cursor that allows browsing the set of rows returned by the query it executes. Result set rows are locally cached in a temporary file on the client computer to provide for backward scrollability.

Note: Caching of result sets on the client's local disk can be disabled if backward scrollability is not a requirement. This is strongly recommended and can lead to significant performance improvements. Passing the ORADYN_NOCACHE option in the CreateDynaset method disables caching. This constant is defined in the file oraconst.txt and can be found in the root directory where OO4O is installed.

The following example code connects to the ExampleDb database, executes a query, moves through the result set of rows, and displays the column values of each row in a simple message box.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")

Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "Scott/Tiger", 0 )

' SELECT query described above used in next line

Set Employees = EmpDb.CreateDynaset("SELECT ename, empno FROM

emp",ORADYN_NOCACHE)

While NOT Employees.EOF

MsgBox "Name: " & Employees("ename").value & "Employee #: " &

Employees("empno").value

Employees.MoveNext

Wend

Employees("ename") and Employees("empno") in the example return values of the ename and the empno columns from the current row in the result set respectively. An alternative method of accessing the column values is to use the position of the column, Employees(0) for the ename column and Employee(1) for empno. This method obtains the column value faster than referencing a column by its name.

The Employees.MoveNext statement in the example sets the current row of the result set to the next row. The EOF property of the OraDynaset is set to true if attempt is made to move past the last row in the result set.

The MoveNext method is one of the navigational methods in the OraDynaset interface.

Navigational methods include: MoveFirst, MoveLast, MoveNext, MovePrevious, MoveNextn, MovePreviousn, MoveRel, and MoveTo.

An OraDynaset object also provides methods for updating and deleting rows retrieved from base tables or views that can be updated. In addition, it provides an easy way for inserting new rows. See the OraDynaset interface.

Queries can also require the program to supply data to the database using input (bind) variables, as in the following example:

SELECT name, empno

FROM employees

WHERE ename = :ENAME

In the preceding SQL statement, :ENAME is a placeholder for a value that will be supplied by the application.

In OO4O, the OraParameter object is used to supply data values for place holders.

To define a parameter, use the OraParameters Collection object. This object is obtained by referencing the "Parameters" property of an OraDatabase interface. The OraParameters collection provides methods for adding, removing, and obtaining references to OraParameter objects.

The following statement adds an input parameter to the OraParameters collection contained in the EmpDb object.

EmpDb.Parameters.Add "ENAME", "JONES", ORAPARM_INPUT

ENAME is the name of the parameter and must be the same as the name of the placeholder in the SQL statement, :ENAME in the sample code. JONES is provided as the initial value and ORAPARM_INPUT notifies OO4O that it will be used as an INPUT parameter.

The following example creates an OraDynaset object that contains only one row for an employee whose name is 'JONES'.

Set OO4OSession = CreateObject(“OracleInProcServer.XOraSession")