Input and retrieval of long data in pieces

When an application must manipulate long data values, loading the entire values into storage can become impractical. For this reason, Db2 ODBC provides a technique that enables you to handle long data values in pieces.

The technique for handling long data values in pieces is called specifying parameter values at execute time. It is the same method that you can use to specify values for fixed-size non-character data types, such as integers.

The following figure depicts both the processes of sending data in pieces and retrieving data in pieces. The right side of the figure shows the process that you use to send data in pieces; the left side of the figure shows the process that you use to retrieve data in pieces.
Figure 1. Input and retrieval of data in pieces
Begin figure summary. This figure is a flowchart that depicts the functions you use send data to the database server in pieces and retrieve data in pieces. Detailed description available.

Data-at-execute parameters

A data-at-execute parameter is a bound parameter for which a value is prompted at execution time. Normally, you store a value in memory to use for a parameter before you call SQLExecute() or SQLExecDirect().

To create data-at-execute parameters, call SQLBindParameter() and specify both of the following arguments for each data-at-execute parameter you want to create:
  • Set the pcbValue argument to SQL_DATA_AT_EXEC.
  • Set the rgbValue argument to a value you can use to uniquely identify the parameter for which you need to supply data. This value names that parameter so that you can refer to it later.
SQLExecDirect() and SQLExecute() return SQL_NEED_DATA for statements that contain data-at-execute parameters to prompt you to supply a value. When SQLExecDirect() or SQLExecute() returns SQL_NEED_DATA, you must perform the following steps in your application:
  1. Call SQLParamData() to conceptually advance to the first such parameter. SQLParamData() returns SQL_NEED_DATA and provides the value of the input rgbValue buffer that you specified in the SQLBindParameter() call. This value helps you identify the information that you need to supply.
  2. Call SQLPutData() to pass the actual data for the parameter. You call SQLPutData() repeatedly to send long data in pieces.
  3. Call SQLParamData() after you provide the entire data for this data-at-execute parameter. If additional data-at-execute parameters need data, SQLParamData() returns SQL_NEED_DATA. Repeat steps 2 and 3 until SQLParamData() returns SQL_SUCCESS.

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

While the data-at-execution flow is in progress, you can call only the following Db2 ODBC functions:
  • SQLParamData() and SQLPutData(), as the previous procedure to specify parameter values at execute time describes.
  • SQLCancel(), which is used to cancel the flow and force an exit from the loops on the right side of Figure 1 without executing the SQL statement.
  • SQLGetDiagRec()
You cannot terminate the transaction nor set connection attributes in a data-at-execution flow.

Data retrieval in pieces

You can call SQLGetData() repeatedly to retrieve smaller pieces of data.

Typically to retrieve data, you allocate application variables to hold the data that you retrieve, and you call SQLBindCol() to associate these application variables with a column in a result set.

Based on the size of the values that a column contains, you choose the amount of memory that values from this column can occupy in your application. (To determine the size of the largest value in a specific result column, call SQLDescribeCol(). The output argument pcbColDef returns this information.)

In the case of character and binary data, columns can contain large values. If the size of a column value exceeds the size of the buffer that you allocate, you can call SQLGetData() repeatedly to obtain this value in a sequence of pieces that are more manageable in size.

As Figure 1 depicts, SQLGetData() returns SQL_SUCCESS_WITH_INFO (with SQLSTATE 01004) to indicate that more data exists for this column. Call SQLGetData() repeatedly to retrieve the remaining pieces of data. When you retrieve the final piece of data, SQLGetData() returns SQL_SUCCESS.