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 machine 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 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'.
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "Scott/Tiger", 0 )
EmpDb.Parameters.Add "ENAME", "JONES", ORAPARM_INPUT
Set Employees = EmpDb.CreateDynaset("SELECT ename,
empno FROM emp WHERE ename =
:ENAME",ORADYN_NOCACHE)
While NOT Employees.EOF
MsgBox "Name: " & Employees("ename").value & "Employee #: " &
Employees("empno").value
Employees.MoveNext
Wend
