Call Level Interface Guide and Reference

Sending/Retrieving Long Data in Pieces

When manipulating long data, it may not be feasible for the application to load the entire parameter data value into storage at the time the statement is executed, or when the data is fetched from the database. A method has been provided to allow the application to handle the data in a piecemeal fashion. The technique to send long data in pieces is called Specifying Parameter Values at Execute Time because it can also be used to specify values for fixed size non-character data types such as integers.

An application can also use the SQLGetSubString() function to retrieve a portion of a large object value. See Figure 16 in Using Large Objects for details.

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.

Fetching Data in Pieces

Typically, based on its knowledge of a column in the result set (via SQLDescribeCol() or prior knowledge), the application may choose to allocate the maximum memory the column value could occupy and bind it via SQLBindCol(). However, in the case of character and binary data, the column can be arbitrarily long. If the length of the column value exceeds the length of the buffer the application can allocate or afford to allocate, a feature of SQLGetData() lets the application use repeated calls to obtain in sequence the value of a single column in more manageable pieces.

Basically, as shown on the left side of Figure 9, a call to SQLGetData() returns SQL_SUCCESS_WITH_INFO (with SQLSTATE 01004) to indicate more data exists for this column. SQLGetData() is called repeatedly to get the remaining pieces of data until it returns SQL_SUCCESS, signifying that the entire data have been retrieved for this column.

The function SQLGetSubString() can also be used to retrieve a specific portion of a large object value. See SQLGetSubString - Retrieve Portion of A String Value for more information. For other alternative methods to retrieve long data, refer to Using Large Objects.

Figure 9. Piecewise Input and Retrieval

Piecewise Input and Retrieval Example

For an example of piecewise input of an image blob refer to picin2.c, shown in CLI Sample dtlob.c.

For an example of piecewise retrieval of an image blob refer to showpic2.c, shown in CLI Sample tbread.c.