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, the CreateDynaset method of the OraDatabase interface is used 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 machine to provide for backward scrollability.

Note: Local caching of result sets can be disabled if backward scrollability is not a requirement. This is strongly recommended and can lead to significant performance improvements.

The following example code connects to the HRDB 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("HRDB", "Scott/Tiger", 0 )

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

emp",ORADYN_NOCACHE)

While NOT Employees.EOF

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

Employees("empno")

Employees.MoveNext

Wend

Passing the ORADYN_NOCACHE option in the CreateDynaset method disables caching of the result set on the client's local disk. This constant is defined in the file ORACONST.TXT and can be found in the root directory where OO4O is installed.

Employees("ename") and Employees("empno") in the previous 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 that sets the current row to the one past the current position.

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

An OraDynaset object also provides methods for updating and deleting rows that are retrieved from a base table or view 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 above 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'.