Step 1: Executing a Query Multiple Times

This first optimization technique is for cases when a query has to be executed several times. This technique involves the use of deferred execution. With deferred execution, the statement is first prepared for execution and then executed. With immediate execution (as seen in DB Link Basic Use) the two steps are carried out simultaneously. Deferred execution is used in the following cases:

  • The Statement Contains Parameters: In this case, the statement is first prepared without knowing the values of the parameters, and it is then executed once the parameters are set to their values. A query with parameters is always executed using deferred execution, even if it is executed only once.

The Statement is to be Executed Several Times

Deferred execution is done using the parse step and the execute step. The parse step is done only once. It sends the query to the RDBMS, which prepares the execution plan. Then the execute step can be done several times. The same execution plan will be reused each time by the RDBMS.

The Statement Contains Parameters

In this case, placeholders for parameters must be considered. Most RDBMSs support the ISO SQL standard syntax for placeholders: the ? symbol. Exceptions to this are Oracle® and SqlBase:

  • For Oracle, the syntax can be :<n>, where <n> is an integer starting from 1.

  • Oracle also uses named parameters. The syntax of named parameters is :<name>.

Example of Deferred Execution

An example of deferred execution is now presented.

First, the RDBMS connection is verified in order to use the proper placeholder syntax. This is done by checking the return value from getName. This method returns the RDBMS to which the program is connected.

const char* insertStr = 0 ;

if (!strncmp(dbms->getName(), "oracle", 6))

insertStr = "insert into OPTIMS1 values (:1, :2)" ;

else

insertStr = "insert into OPTIMS1 values (?, ?)" ;

This statement is parsed with the parse method:

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

IldDisplayError("Parse of query failed : ", request) ;

Ending(dbms) ;

exit(1) ;

}

Then the parameters are bound to set their types. This parameter binding may also be used to specify other parameter information (See Step 3: Accessing Data Directly, which deals with external binding, for further information).

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

IldDisplayError("First parameter binding failed : ", request) ;

Ending(dbms) ;

exit(1) ;

}

if (!request->bindParam((IldUShort)0, IldStringType)) {

IldDisplayError("Second parameter binding failed : ", request) ;

Ending(dbms) ;

exit(1) ;

}

The last step is execution. A loop is run to set the parameters to different values, and the prepared query is executed with these parameter values:

static const IldUShort strLen = 20 ;

IldUShort i, j ;

IldInt nbRows, nVal ;

// strBuf will be used to build a different string for each execution.

char strBuf[strLen + 1] ;

strBuf[strLen] = 0 ;

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

nVal = i ;

// Build a new string value for this execution.

for (j = 0 ; j < strLen ; ++j)

strBuf[j] = 'a' + i ;

// Set parameter values.

if (!request->setParamValue(nVal, 0) ||

!request->setParamValue(strBuf, 1)) {

IldDisplayError("Set parameter value failed :", request) ;

Ending(dbms) ;

exit(1) ;

}

// Execute the query.

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

IldDisplayError("Insertion failed : ", request) ;

Ending(dbms) ;

exit(1) ;

}

else cout << "\t" << nbRows << " row inserted." << endl ;

}

Conclusion

This step described how to run a query with parameters using the deferred execution method.

The next steps describe how to make this application even more efficient.

See source code.