Call Level Interface Guide and Reference

Specifying Parameter Values at Execute Time

A bound parameter for which value is prompted at execution time instead of stored in memory before calling SQLExecute() or SQLExecDirect() is called a data-at-execute parameter. To indicate such a parameter on an SQLBindParameter() call, the application:

• Sets the input data length pointer to point to a variable that, at execute time, will contain the value SQL_DATA_AT_EXEC.

• If there is more than one data-at-execute parameter, sets each input data pointer argument to some value that it will recognize as uniquely identifying the field in question.

If there are any data-at-execute parameters when the application calls SQLExecDirect() or SQLExecute(), the call returns with SQL_NEED_DATA to prompt the application to supply values for these parameters. The application responds as follows:

1. It calls SQLParamData() to conceptually advance to the first such parameter. SQLParamData() returns SQL_NEED_DATA and provides the contents of the input data pointer argument specified on the associated SQLBindParameter() call to help identify the information required.

2. It calls SQLPutData() to pass the actual data for the parameter. Long data can be sent in pieces by calling SQLPutData() repeatedly.

3. It calls SQLParamData() again after it has provided the entire data for this data-at-execute parameter. If more data-at-execute parameters exist, SQLParamData() again returns SQL_NEED_DATA and the application repeats steps 2 and 3 above.

When all data-at-execute parameters have been assigned values, SQLParamData() completes execution of the SQL statement and produces a return value and diagnostics as the original SQLExecDirect() or SQLExecute() would have produced. The right side of Figure 9 illustrates this flow.

While the data-at-execution flow is in progress, the only DB2 CLI functions the application can call are:

• SQLParamData() and SQLPutData() as given in the sequence above.

• The SQLCancel() function which is used to cancel the flow and force an exit from the loop(s) on the right side of Figure 9 without executing the SQL statement.

• The SQLGetDiagRec() function. The application also must not end the transaction nor set any connection attributes.

Using the parameter at execute time technique to input Large Object data may require the creation and use of a temporary file at the client. For alternative methods to input long data, refer to "Using Large Objects".

[ DB2 List of Books | Search the DB2 Books ]