SQLGetData--Get data from a column

Purpose

Specification: DB2 CLI 1.1 ODBC 1.0 ISO CLI

SQLGetData() retrieves data for a single column in the current row of the result set. This is an alternative to SQLBindCol(), which is used to transfer data directly into application variables on each SQLFetch() call.

SQLFetch() must be called before SQLGetData().

After calling SQLGetData() for each necessary column, SQLFetch() is called to retrieve the next row.

Syntax 

SQLRETURN   SQLGetData       (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLUSMALLINT      ColumnNumber,      /* icol */
                SQLSMALLINT       TargetType,        /* fCType */
                SQLPOINTER        TargetValuePtr,    /* rgbValue */
                SQLINTEGER        BufferLength,      /* cbValueMax */
                SQLINTEGER   *FAR StrLen_or_IndPtr); /* pcbValue */

Function arguments

ǥ 66. SQLGetData arguments

Data type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLUSMALLINT ColumnNumber input Column number for which the data retrieval is requested. Result set columns are numbered sequentially. Column numbers start at one.
SQLSMALLINT TargetType input The C data type of the column identified by ColumnNumber. The following types are supported:

SQL_C_BINARY

SQL_C_BIT

SQL_C_CHAR

SQL_C_DOUBLE

SQL_C_FLOAT

SQL_C_LONG

SQL_C_SHORT

SQL_C_TYPE_DATE

SQL_C_TYPE_TIME

SQL_C_TYPE_TIMESTAMP

SQL_C_TINYINT

Specifying SQL_C_DEFAULT results in the data being converted to its default C data type.

SQLPOINTER TargetValuePtr output Pointer to the buffer where the retrieved column data is to be stored.

The output buffer needs to be word-aligned (even). Many processors such as the Motorola 68000 have word-alignment rules, and for non-character data types, the application should align the buffer properly.

SQLINTEGER BufferLength input Maximum size of the buffer pointed to by TargetValuePtr.

If TargetType denotes a binary or character string, then BufferLength must be > 0, or an error returns. Otherwise, the argument is ignored.

SQLINTEGER * StrLen_or_IndPtr output Pointer to the value that indicates the number of bytes that DB2 CLI has available to return in the TargetValuePtr buffer. If data truncation occurs, this contains the total number of bytes required to retrieve the whole column.

For binary and character data types, the application can alternatively choose the piecemeal retrieval mode to retrieve large data piece by piece. In this mode, the StrLen_or_IndPtr argument contains the number of bytes left in the column.

The value is SQL_NULL_DATA if the data value of the column is null. If this pointer is NULL and SQLFetch() obtained a column containing null data, then this function fails because it has no means of reporting this.

If SQLFetch() fetched a column containing binary data, then the pointer to StrLen_or_IndPtr must not be NULL or this function fails because it has no other means of informing the application about the length of the data retrieved in the TargetValuePtr buffer.

Usage

SQLGetData() can be used with SQLBindCol() for the same result set if SQLFetch() is used. The general steps are:

  1. SQLFetch() advances to the first row, retrieves the first row, and transfers data for bound columns.
  2. SQLGetData() transfers data for the specified column.
  3. SQLGetData() repeats step 2 for each column needed.
  4. SQLFetch() advances to the next row, retrieves the next row, and transfers data for bound columns.
  5. Steps 2, 3 and 4 are repeated for each row in the result set, or until the result set is no longer needed.

To discard the column data part way through the retrieval, the application can call SQLGetData() with ColumnNumber set to the next column position of interest. To discard data that has not been retrieved for the entire row, the application should call SQLFetch() to advance to the next row; or, if no more data from the result set is needed, calls SQLFreeStmt().

The TargetType input argument determines the type of data conversion (if any) needed before the column data is placed into the storage area pointed to by TargetValuePtr.

The value returned in TargetValuePtr is null-terminated unless the column data to be retrieved is binary.

Truncation of numeric data types is reported as a warning if the truncation involves digits to the right of the decimal point. If truncation occurs to the left of the decimal point, an error returns.

Return codes

SQL_SUCCESS returns if a zero-length string is retrieved by SQLGetData(). If this is the case, StrLen_or_IndPtr contains 0, and TargetValuePtr contains a null terminator.

If the preceding call to SQLFetch() fails, do not call SQLGetData() because the result is undefined.

Diagnostics

ǥ 67. SQLGetData SQLSTATEs

SQLSTATE Description Explanation
01004 Data truncated. Data returned for the specified column (ColumnNumber) is truncated. String or numeric values are right truncated. SQL_SUCCESS_WITH_INFO is returned.
07006 Invalid conversion. The data value cannot be converted to the C data type specified by the argument TargetType.

The function is called before for the same ColumnNumber value but with a different TargetType value.

22002 Invalid output or indicator buffer specified. The pointer value specified for the argument StrLen_or_IndPtr is a null pointer and the value of the column is null. There is no means to report SQL_NULL_DATA.
22005 Error in assignment. A returned value is incompatible with the data type denoted by the argument TargetType.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY002 Invalid column number. The specified column is less than 0 or greater than the number of result columns.
HY003 Program type out of range. TargetType is not a valid data type or SQL_C_DEFAULT.
HY010 Function sequence error. The function is called without first calling SQLFetch().
HY013 Unexpected memory handling error. DB2 CLI is unable to access memory required to support execution or completion of the function.
HY090 Invalid string or buffer length. The value of the argument BufferLength is less than 0 and the argument TargetType is SQL_C_CHAR or SQL_C_BINARY, or TargetType is SQL_C_DEFAULT and the default type is one of SQL_C_CHAR, SQL_C_BINARY, or SQL_C_DBCHAR.
HYC00 Driver not capable. The SQL data type for the specified data type is recognized but not supported by DB2 CLI.

The requested conversion from the SQL data type to the application data TargetType cannot be performed by DB2 CLI or the data source.

Restrictions

None.

Related reference