Processing SELECT statements in a DB2® UDB CLI application

 

If the statement is SELECT, these steps are generally needed to retrieve each row of the result set.

  1. Establish the structure of the result set, number of columns, column types and lengths.

  2. Bind application variables to columns in order to receive the data.

  3. Repeatedly fetch the next row of data, and receive it into the bound application variables.

    Columns that were not previously bound can be retrieved by calling SQLGetData() after each successful fetch.

Each of the above steps requires some diagnostic checks. The first step requires analyzing the processed or prepared statement. If the SQL statement is generated by the application, this step is not necessary. This is because the application knows the structure of the result set and the data types of each column. If the SQL statement is generated (for example, entered by a user) at run time, the application needs to query:

This information can be obtained by calling SQLNumResultCols() and SQLDescribeCol() (or SQLColAttributes()) after preparing the statement or after executing the statement.

The second step allows the application to retrieve column data directly into an application variable on the next call to SQLFetch(). For each column to be retrieved, the application calls SQLBindCol() to bind an application variable to a column in the result set. Similar to variables bound to parameter markers using SQLSetParam(), columns are bound using deferred arguments. This time the variables are output arguments, and data is written to them when SQLFetch() is called. SQLGetData() can also be used to retrieve data, so calling SQLBindCol() is optional.

The third step is to call SQLFetch() to fetch the first or next row of the result set. If any columns have been bound, the application variable is updated. If any data conversion is indicated by the data types specified on the call to SQLBindCol, the conversion occurs when SQLFetch() is called.

The last (optional) step is to call SQLGetData() to retrieve any columns that were not previously bound. All columns can be retrieved this way, provided they were not bound, or a combination of both methods can be used. SQLGetData() is also useful for retrieving variable length columns in smaller pieces, which cannot be done with bound columns. Data conversion can also be indicated here, as in SQLBindCol().

 

Parent topic:

Processing results in a DB2 UDB CLI application

 

Related concepts


Data types and data conversion in DB2 UDB CLI functions

 

Related reference


SQLBindCol - Bind a column to an application variable
SQLColAttributes - Obtain column attributes
SQLDescribeCol - Describe column attributes
SQLFetch - Fetch next row
SQLGetData - Get data from a column
SQLNumResultCols - Get number of result columns