Call Level Interface Guide and Reference

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 or LOB locators on each SQLFetch() or SQLFetchScroll() call. SQLGetData() can also be used to retrieve large data values in pieces.

SQLFetch() must be called before SQLGetData().

After calling SQLGetData() for each column, SQLFetch() or SQLFetchScroll() 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

Table 94. 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 1 if bookmarks are not used (SQL_ATTR_USE_BOOKMARKS statement attribute set to SQL_UB_OFF).
Column numbers start at 0 if bookmarks are used (the statement attribute set to SQL_UB_ON or SQL_UB_VARIABLE).

SQLSMALLINT TargetType input The C data type of the column identifier by ColumnNumber. The following types are supported:

SQL_C_BINARY
SQL_C_BIT
SQL_C_BLOB_LOCATOR
SQL_C_CHAR
SQL_C_CLOB_LOCATOR
SQL_C_DBCHAR
SQL_C_DBCLOB_LOCATOR
SQL_C_DOUBLE
SQL_C_FLOAT
SQL_C_LONG
SQL_C_NUMERIC ^a
SQL_C_SBIGINT
SQL_C_SHORT
SQL_C_TYPE_DATE
SQL_C_TYPE_TIME
SQL_C_TYPE_TIMESTAMP
SQL_C_TINYINT
SQL_C_UBIGINT

Specifying SQL_C_DEFAULT results in the data being converted to its default C data type, refer to Table 2 for more information.
SQLPOINTER TargetValuePtr output Pointer to buffer where the retrieved column data is to be stored.
SQLINTEGER BufferLength input Maximum size of the buffer pointed to by TargetValuePtr
SQLINTEGER * StrLen_or_IndPtr output Pointer to value which indicates the number of bytes DB2 CLI has available to return in the TargetValuePtr buffer. If the data is being retrieved in pieces, this contains the number of bytes still remaining.
The value is SQL_NULL_DATA if the data value of the column is null. If this pointer is NULL and SQLFetch() has obtained a column containing null data, then this function will fail because it has no means of reporting this.
If SQLFetch() has fetched a column containing binary data, then the pointer to StrLen_or_IndPtr must not be NULL or this function will fail because it has no other means of informing the application about the length of the data retrieved in the TargetValuePtr buffer.

Note: DB2 CLI will provide some performance enhancement if TargetValuePtr is placed consecutively in memory after StrLen_or_IndPtr

Usage

SQLGetData() can be used with SQLBindCol() for the same result set, as long as SQLFetch() and not SQLFetchScroll() is used. The general steps are:

SQLFetch() - advances cursor to first row, retrieves first row, transfers data for bound columns.
SQLGetData() - transfers data for the specified column.
Repeat step 2 for each column needed.
SQLFetch() - advances cursor to next row, retrieves next row, transfers data for bound columns.
Repeat steps 2, 3 and 4 for each row in the result set, or until the result set is no longer needed.

SQLGetData() can also be used to retrieve long columns if the C data type (TargetType) is SQL_C_CHAR, SQL_C_BINARY, SQL_C_DBCHAR, or if TargetType is SQL_C_DEFAULT and the column type denotes a binary or character string.

Upon each SQLGetData() call, if the data available for return is greater than or equal to BufferLength, truncation occurs. Truncation is indicated by a function return code of SQL_SUCCESS_WITH_INFO coupled with a SQLSTATE denoting data truncation. The application can call SQLGetData() again, with the same ColumnNumber value, to get subsequent data from the same unbound column starting at the point of truncation. To obtain the entire column, the application repeats such calls until the function returns SQL_SUCCESS. The next call to SQLGetData() returns SQL_NO_DATA_FOUND.

Although SQLGetData() can be used for the sequential retrieval of LOB column data, use the DB2 CLI LOB functions if only a portion of the LOB data or a few sections of the LOB column data are needed:

Bind the column to a LOB locator.
Fetch the row.
Use the locator in a SQLGetSubString() call, to retrieve the data in pieces (SQLGetLength() and SQLGetPosition() may also be required in order to determine the values of some of the arguments).
Repeat step 2.

Truncation is also affected by the SQL_ATTR_MAX_LENGTH statement attribute. The application can specify that truncation is not to be reported by calling SQLSetStmtAttr() with SQL_ATTR_MAX_LENGTH and a value for the maximum length to return for any one column, and by allocating a TargetValuePtr buffer of the same size (plus the null-terminator). If the column data is larger than the set maximum length, SQL_SUCCESS will be returned and the maximum length, not the actual length will be returned in StrLen_or_IndPtr.

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 the cursor to the next row; or, if it is not interested in any more data from the result set, call SQLFreeStmt() to close the cursor.

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.

For SQL graphic column data:

The length of the TargetValuePtr buffer (BufferLength) should be a multiple of 2. The application can determine the SQL data type of the column by first calling SQLDescribeCol() or SQLColAttribute().
The pointer to StrLen_or_IndPtr must not be NULL since DB2 CLI will be storing the number of octets stored in TargetValuePtr.
If the data is to be retrieved in piecewise fashion, DB2 CLI will attempt to fill TargetValuePtr to the nearest multiple of two octets that is still less than or equal to BufferLength. This means if BufferLength is not a multiple of two, the last byte in that buffer will be untouched; DB2 CLI will not split a double-byte character.

The contents returned in TargetValuePtr is always null-terminated unless the column data to be retrieved is binary, or if the SQL data type of the column is graphic (DBCS) and the C buffer type is SQL_C_CHAR. If the application is retrieving the data in multiple chunks, it should make the proper adjustments (for example, strip off the null-terminator before concatenating the pieces back together assuming the null termination environment attribute is in effect).

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 is returned (refer to the diagnostics section).

With the exception of scrollable cursors, applications that use SQLFetchScroll() to retrieve data should call SQLGetData() only when the rowset size is 1 (equivalent to issuing SQLFetch()). SQLGetData() can only retrieve column data for a row where the cursor is currently positioned.

Using SQLGetData() with Scrollable Cursors

SQLGetData() can also be used with scrollable cursors. You can save a pointer to any row in the result set; a bookmark. The application can then use that bookmark as a relative position to retrieve a rowset of information.

Once you have positioned the cursor to a row in a rowset using SQLSetPos(), you can obtain the bookmark value from column 0 using SQLGetData(). In most cases you will not want to bind column 0 and retrieve the bookmark value for every row, but use SQLGetData() to retrieve the bookmark value for the specific row you require.

See Scrollable Cursors for more information.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE
SQL_NO_DATA_FOUND

SQL_NO_DATA_FOUND is returned when the preceding SQLGetData() call has retrieved all of the data for this column.

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

If the preceding call to SQLFetch() failed, SQLGetData() should not be called since the result is undefined.

Diagnostics

Table 95. SQLGetData SQLSTATEs

SQLSTATE Description Explanation
01004 Data truncated. Data returned for the specified column (ColumnNumber) was 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 has been 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 was a null pointer and the value of the column is null. There is no means to report SQL_NULL_DATA.
22003 Numeric value out of range. Returning the numeric value (as numeric or string) for the column would have caused the whole part of the number to be truncated.
22005 Error in assignment. A returned value was incompatible with the data type denoted by the argument TargetType.
22007 Invalid datetime format. Conversion from character a string to a datetime format was indicated, but an invalid string representation or value was specified, or the value was an invalid date.
22008 Datetime field overflow. Datetime field overflow occurred; for example, an arithmetic operation on a date or timestamp has a result that is not within the valid range of dates, or a datetime value cannot be assigned to a bound variable because it is too small.
24000 Invalid cursor state. The previous SQLFetch() resulted in SQL_ERROR or SQL_NO_DATA found; as a result, the cursor is not positioned on a row.
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 was less than 0 or greater than the number of result columns.
The specified column was 0, but the application did not enable bookmarks (by setting the SQL_ATTR_USE_BOOKMARKS statement attribute).
SQLExtendedFetch() was called for this result set.
HY003 Program type out of range. TargetType was not a valid data type or SQL_C_DEFAULT.
HY010 Function sequence error. The specified StatementHandle was not in a cursor positioned state. The function was called without first calling SQLFetch().
The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
HY013 Unexpected memory handling error. DB2 CLI was 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, SQL_C_BINARY, SQL_C_DBCHAR or (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.
The column was bound using SQLBindFileToCol().
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
01004	Data truncated.	Data returned for the specified column (ColumnNumber) was 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 has been 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 was a null pointer and the value of the column is null. There is no means to report SQL_NULL_DATA.
22003	Numeric value out of range.	Returning the numeric value (as numeric or string) for the column would have caused the whole part of the number to be truncated.
22005	Error in assignment.	A returned value was incompatible with the data type denoted by the argument `TargetType`.
22007	Invalid datetime format.	Conversion from character a string to a datetime format was indicated, but an invalid string representation or value was specified, or the value was an invalid date.
22008	Datetime field overflow.	Datetime field overflow occurred; for example, an arithmetic operation on a date or timestamp has a result that is not within the valid range of dates, or a datetime value cannot be assigned to a bound variable because it is too small.
24000	Invalid cursor state.	The previous `SQLFetch()` resulted in SQL_ERROR or SQL_NO_DATA found; as a result, the cursor is not positioned on a row.
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 was less than 0 or greater than the number of result columns. The specified column was 0, but the application did not enable bookmarks (by setting the SQL_ATTR_USE_BOOKMARKS statement attribute). `SQLExtendedFetch()` was called for this result set.
HY003	Program type out of range.	`TargetType` was not a valid data type or SQL_C_DEFAULT.
HY010	Function sequence error.	The specified `StatementHandle` was not in a cursor positioned state. The function was called without first calling `SQLFetch()`. The function was called while in a data-at-execute (`SQLParamData()`, `SQLPutData()`) operation. The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called.
HY013	Unexpected memory handling error.	DB2 CLI was 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, SQL_C_BINARY, SQL_C_DBCHAR or (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. The column was bound using `SQLBindFileToCol()`.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

Restrictions

None.

CLI Sample tbread.c

Refer to CLI Sample utilcli.c for a comparison between using bound columns and using SQLGetData().

(The complete sample tbread.c is also available here .)

 
/* From the CLI sample TBREAD.C */
/* ... */
 
        sqlrc = SQLGetData( hstmt, 2, SQL_C_CHAR, location.val, 15,
                            &location.ind ) ;
        STMT_HANDLE_CHECK( hstmt, sqlrc);

References

[ Top of Page | Previous Page | Next Page ]