Deferred Execution

Cursors > Processing SQL Statements > Deferred Execution

Deferred Execution

It is not always useful to execute a query immediately, either because it contains placeholders for which values must be passed or because you want to reuse the same query and thus avoid the time needed to prepare it for execution.

The database system must prepare a query before executing it. Preparing a query involves:

1. Parsing the query and checking the SQL syntax,

2. Preparing an execution plan,

3. Calling the query optimizer.

These steps can take place only once, rather than repeatedly, if you intend to use the same query several times —whether containing placeholder values or not.

To send the same query several times, you must follow this protocol:

1. Prepare the query by calling IldRequest::parse.

2. Bind the placeholders by calling IldRequest::bindParam.

3. If needed, bind output columns by calling IldRequest::bindCol.

4. For each execution, pass values to the placeholders by calling the function IldRequest::setParamValue.

5. Call IldRequest::execute.


Note: When using any of the member functions related to the result set or to the parameters set, remember that Rogue Wave DB Link follows the C or C++ convention, where indexes start from 0, rather than the standard SQL convention, where indexes start at 1.

Here is an example:

{

const char* selectStr = (!strcmp(dbms->getName(), "oracle") ||

!strcmp(dbms->getName(), "sqlbase")) ?

"select * from DBLTABLE where no > :1"

"select * from DBLTABLE where no > ?";

cout << "Parsing a request containing a host variable: " << endl;

cout << "\t" << selectStr << endl;

if (!request->parse(selectStr)) {

IldDisplayError("Parse failed: ", request);

Ending(dbms, request);

}

cout << endl;

cout << "Host variable bind with integer type and value 0" << endl;

if (!request->bindParam((IlUShort)0, IldIntegerType)) {

IldDisplayError("Binding failed: ", request);

Ending(dbms, request);

}

request->setParamValue((IlInt)0, 0);

cout << "Executing the request" << endl;

if (!request->execute()) {

IldDisplayError("Execute failed: ", request);

Ending(dbms, request);

}

cout << endl;

cout << "Results from the request: " << endl;

if (request->getColCount()) {

while (request->fetch().hasTuple()) {

if (!request->isColNull(0))

cout << request->getColIntegerValue(0);

else

cout << "-";

cout << "\t";

if (!request->isColNull(1))

cout << request->getColStringValue(1);

else

cout << "-";

cout << endl;

}

Preparing a Statement

To prepare a statement, issue a call to IldRequest::parse. Its argument is the SQL statement string to be prepared. Its action is roughly equivalent to the SQL statements PREPARE and DESCRIBE.


Warning: Parsing a request discards any previously parsed queries, as well as all pending result sets of the IldRequest object.

This parse method must not be called for statements that cannot be prepared. These statements vary depending on the RDBMS you are connected to. Check the RDBMS client API manuals for information about which statements can be prepared.


Note: A simple select statement that includes no placeholder should not be prepared when you use the ODBC port. The resulting data will be irrelevant in the bound memory space.

Multiple Execution

Multiple execution means that one call to the member function IldRequest::execute will process several rows in the database. This function takes two optional arguments:

The first argument is a pointer to an IlInt variable. After execution is successful, this argument is set to the number of processed rows.

The second argument, a number of type IlUInt, is the number of times the query has to be executed. When specified, this number must be positive and less than or equal to the value returned by a call to the function IldRequest::getParamArraySize.


Note: When you use ODBC, this second argument can be set only if the driver is ODBC level 2 compliant. Also with ODBC, the array bind mode can be used only if the driver is ODBC level 2 compliant.

{

// Since the "count" argument is set to 1, there will be only one

// update performed despite the variable array size set to 2 !!

if (!request->execute(&rowCount, 1)) {

IldDisplayError("Execution failed: ", request);

Ending(dbms, request);

cout << "Row processed count " << rowCount << endl << endl;

}

Repeated Execution

Once prepared, a query can be executed as many times as needed by successive calls to the overloaded member function IldRequest::execute. Before each execution, you can set new bindings for the input variables (placeholders) and the output columns. You must also pass the values of the input variables.

Example:

{

for (int i = 0; i < 5; i++) {

cout << "Set " << i << "th variable to name: "

<< names[i] << " and age: " << ages[i] << endl;

request->setParamValue(names[i], 0, 0);

request->setParamValue(ages[i], 1, 0);

// execute the insertion

cout << "Inserting row" << endl;

if (!request->execute(&n)) {

IldDisplayError("Execution failed: ", request);

delete cust;

Ending(dbms, request);

}

cout << n << "row(s) inserted." << endl;

}

A frequent mistake is to use the basic member function IldRequest::execute. This function takes a string as its first argument, thus causing an error, in this context, due to unbound variables.

Version 5.8