Setting Up a Query for Multiple or Repeated Use

Queries > Setting Up a Query for Multiple or Repeated Use

When your application reuses the same basic SQL statement several times, at once or in various places, with different values as parameters, you can allocate an IldRequest object, which will be used to prepare the query for execution and will be kept until actual execution is needed.

Such a query contains placeholders in the form of question marks, “?”, as defined in the ISO SQL standard, or even in an RDBMS-specific form, such as “:1” or “:var”. These last two forms are supported only by Oracle which does not support the standard placeholder format. As a consequence, queries that need to be prepared cannot be made portable on all RDBMSs and your application code must allow for this.

The general procedure to set up a query for multiple or repeated use is the following:

1. Call the function IldRequest::parse to prepare a parameterized query.

Once the query has been prepared successfully, you must bind the variables (which are the program counterpart of the placeholders) to the proper type and, possibly, to the application memory.

2. To do so, use the member function IldRequest::bindParam.

Before actual execution, set the variable values either in your application memory or in the Rogue Wave® DB Link internal memory.

3. To do so, use the member function IldRequest::setParamValue.

An overloaded version of that member function exists for each possible Rogue Wave DB Link type.

When there are many rows to be inserted, this schema can be implemented in two different ways:

As a loop inserting one row at a time.

For each row of parameter values, call IldRequest::setParamValue for all parameters, then call IldRequest::execute.

As a batch inserting several rows at once.

4. Using the array bind mode, pass all parameter values, up to the number of rows indicated by IldRequest::getParamArraySize.

5. Call the member function IldRequest::execute.

If there are fewer rows than the bind array size, you can pass the actual number of rows as the second parameter to the function IldRequest::execute.

Version 5.8