|
|
|
OraQuery class is directly derived from the OraCommand and OraRecordset classes. This leads to encapsulation of the following features all in one place: preparing and executing SQL and PL/SQL statements, using parameters, keeping and handling recordsets and their individual fields.
Use OraQuery class if your SQL statement returns recordset as is the case with the SELECT statement. Otherwise you may consider using OraCommand class which doesn't take an overhead of storing and handling recordsets.
Following example demonstrates the common usage of OraQuery:
OraConnection connection("scott/tiger@ora");
OraQuery query(connection);
query.setCommandText("SELECT * FROM Dept WHERE DeptNo > :DeptNo");
Here :DeptNo is a placeholder for the value to be passed to the database. Now we need to set its initial value:
query.param("DeptNo").setDataType(dtInt);
query.param("DeptNo").setInt(20);
Now we may post this query to the server by calling open method:
query.open();
Then we should verify whether there are records returned by this query. If so we will cycle through every record reading its fields. We will stop cycling when we reach the last returned record in the recordset:
if(query.isRowsReturn())
while (!query.isEOF())
{
name = query.field("DNAME").getString();
num = query.field("DEPTNO").getInt();
...
// Make your processing
...
query.next();
}
Finally to release all allocated resources call close member function:
query.close();
Note that by default OraQuery stores in memory only the current record and prevents us from returning to the previous records. To circumvent this default behaviour we may instruct OraQuery object to allocate buffers for all fetched records. Doing so OCL effectively provides local cache which may apart from allowing backward navigation also improve performance and reduce network traffic. Turn on cache as follows:
query.setCached(true);
OCL gives ability to fetch rows from REF CURSOR parameter. To fetch records from the REF CURSOR parameter call executeQuery method that will return recordset for this cursor. In proc demo project you can find an example of how to work with this type of procedures.
See Also
OraQuery, OraCommand, OraRecordset, Proc demo project
OCL | Using OCL | Index
