SQLGetData function (CLI) - Get data from a column
SQLBindCol()
function
or use the SQLGetData()
function
to retrieve LOBs, but both methods cannot be used together. The SQLGetData() function
can also be used to retrieve large data values in pieces.Specification:
- CLI 1.1
- ODBC 1.0
- ISO CLI
The SQLFetch() function
or the SQLFetchScroll()
function
must be called before the SQLGetData() function
when you are retrieving a result-set. However,
if you are retrieving the OUT parameters of a store procedure that
are LOB or XML data type after you set the SQL_STREAM_OUTPUTLOB_ON_CALL_TRUE
attribute, the SQLGetData() function must be called
without calling the SQLFetch() function or the SQLFetchScroll()
function.
After application calls the SQLGetData() function for each column, the SQLFetch() function or the SQLFetchScroll() function must be called to retrieve the next row in a result-set.
Syntax
SQLRETURN SQLGetData (
SQLHSTMT StatementHandle, /* hstmt */
SQLUSMALLINT ColumnNumber, /* icol */
SQLSMALLINT TargetType, /* fCType */
SQLPOINTER TargetValuePtr, /* rgbValue */
SQLLEN BufferLength, /* cbValueMax */
SQLLEN *StrLen_or_IndPtr); /* pcbValue */
Function 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 from left to right.
|
SQLSMALLINT | TargetType | input | The C data type of the column identifier by ColumnNumber.
The following types are supported:
Specifying SQL_ARD_TYPE results in the data being converted to the data type specified in the SQL_DESC_CONCISE_TYPE field of the ARD. Specifying SQL_C_DEFAULT results in the data being converted to its default C data type. |
SQLPOINTER | TargetValuePtr | output | Pointer to buffer where the retrieved column data is to be stored. |
SQLLEN | BufferLength | input | Maximum size of the buffer pointed to by TargetValuePtr. This value is ignored when the driver returns fixed-length data. |
SQLLEN * | StrLen_or_IndPtr | output | Pointer to value which indicates the number of bytes CLI has
available to return in the TargetValuePtr buffer.
If the data is being retrieved in pieces, the StrLen_or_IndPtr buffer
contains the number of bytes remaining. The value is SQL_NULL_DATA if the data value of the column is null. If the StrLen_or_IndPtr pointer is NULL and the SQLFetch() function is called for a column that contains null data, then the SQLFetch() function fails since there is no means to report null data. If the SQLFetch() function fetches a column that contains binary data, then the pointer to StrLen_or_IndPtr must not be NULL or the SQLFetch() function fails since it has no other means of informing the application about the length of the data that is retrieved in the TargetValuePtr buffer. |
Note: CLI will
provide some performance enhancement if TargetValuePtr is
placed consecutively in memory after StrLen_or_IndPtr
|
Usage
Different Db2® data sources have
different restrictions on how the SQLGetData()
function can be
used.
SQLGetInfo()
function with the
SQL_GETDATA_EXTENSIONS option to determine which SQLGetData()
extensions are
supported by the Db2 data source.
Following bitmask values can be returned from the SQLGetInfo()
function call
with the SQL_GETDATA_EXTENSIONS option: - SQL_GD_ANY_COLUMN: Indicates that the
SQLGetData()
function can be called for any unbound column, including the column before the last bound column. - SQL_GD_ANY_ORDER: Indicates that the
SQLGetData()
function can be called for unbound columns in any order. - SQL_GD_BLOCK: Indicates that the
SQLGetData()
function can be called for an unbound column in a block when the rowset size is greater than 1. The application can also call theSQLSetPos()
function with the SQL_POSITION option to position the cursor on the correct row before an application calls theSQLGetData()
function. - SQL_GD_BOUND: Indicates that the
SQLGetData()
function can be called for bound columns or unbound columns. The Db2 software does not currently support this feature.
The SQLGetData() function can also be used to retrieve long columns if the C data type (TargetType) is SQL_C_CHAR, SQL_C_BINARY, SQL_C_DBCHAR, SQL_C_WCHAR, or if the TargetType argument is SQL_C_DEFAULT and the column type denotes a binary or character string.
Upon each SQLGetData() function call, if the data available for return is greater than or equal to the BufferLength argument value, the data truncation occurs. Truncation is indicated by a function return code of SQL_SUCCESS_WITH_INFO coupled with an SQLSTATE denoting data truncation. The application can call the SQLGetData() function 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 the SQLGetData() function returns SQL_NO_DATA_FOUND.
When the application calls the SQLGetData() function to retrieve the actual LOB data it will, by default, make one request to the server and store the entire LOB in memory if the BufferLength value specified is large enough. If the BufferLength value is not large enough to hold the requested LOB data, the LOB data is retrieved in pieces.
- Bind the column to a LOB locator.
- Fetch the row.
- Use the locator in a SQLGetSubString() function call, to retrieve the data in pieces (the SQLGetLength() and SQLGetPosition() functions might also be required 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 avoid the data truncation by calling the SQLSetStmtAttr() function with the SQL_ATTR_MAX_LENGTH attribute value for the maximum length that can be returned by 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 is returned and the maximum length, not the actual length is returned in the StrLen_or_IndPtr value.
To
discard the column data part way through the retrieval, the application
can call the SQLGetData() function with the ColumnNumber argument
set to the next column position of interest. To discard data that
were not retrieved for the entire row, the application can call the SQLFetch() function
to advance the cursor to the next row. If the application does not
want any more data from the result set, the application can close
the cursor by calling the SQLCloseCursor()
function
or theSQLFreeStmt()
function
with the SQL_CLOSE or SQL_DROP option.
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 the TargetValuePtr argument.
- The length of the TargetValuePtr buffer (BufferLength) must be a multiple of 2. The application can determine the SQL data type of the column by first calling the SQLDescribeCol() function or the SQLColAttribute() function.
- The pointer to the StrLen_or_IndPtr argument must not be NULL since the CLI driver uses the StrLen_or_IndPtr value to stores the length of the data that is stored in the TargetValuePtr buffer.
- If the data is to be retrieved in piecewise fashion, the CLI driver attempts to fill the TargetValuePtr value to the nearest multiple of two octets that are still less than or equal to the BufferLength value. This means if the BufferLength value is not a multiple of two, the last byte in that buffer is untouched.
The content that is returned in the TargetValuePtr value 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 must strip off the null-terminator before the application concatenates 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).
Except for scrollable cursors, applications that use the SQLFetchScroll() function to retrieve data can call the SQLGetData() function only when the rowset size is 1 (equivalent to issuing the SQLFetch() function). The SQLGetData() function can retrieve only the column data for a row where the cursor is currently positioned.
The SQLGetData() function with the SQL_STREAM_OUTPUTLOB_ON_CALL_TRUE attribute for retrieving OUT parameters of stored procedures that are LOB or XML data type
When you are calling a stored procedure, the SQLGetData() function can be used to retrieve stream (small chunks) of OUT parameter data that are LOB or XML data type when the SQL_STREAM_OUTPUTLOB_ON_CALL_TRUE attribute is set in the connection or statement level. Setting the StreamOutputLOBonCall keyword to true in either the db2dsdriver.cfg file or the db2cli.ini file has the same effect as setting the SQL_STREAM_OUTPUTLOB_ON_CALL_TRUE attribute.- Specify NULL value for the rgbValue argument of the SQLBindParameters() function for OUT parameters that are LOB and XML data type.
- All OUT parameter data from the stored procedure call must be retrieved with the SQLGetData() function before you retrieve any result sets with the SQLFetch() function. Calling the SQLFetch() function causes the CLI driver to delete all the data buffers.
- The LOB locators are not used.
- The OUT parameters of stored procedures are not ARRAY form of LOB or XML data type.
The SQLGetData() function with scrollable cursors
The SQLGetData() function can also be used with scrollable cursors. You can save a pointer to any row in the result-set with a bookmark. The application can then use that bookmark as a relative position to retrieve a rowset of information.
After you position the cursor to a row in a row set with the SQLSetPos() function, you can obtain the bookmark value from column 0 with the SQLGetData() function.
Return codes
- SQL_SUCCESS
- SQL_SUCCESS_WITH_INFO
- SQL_STILL_EXECUTING
- SQL_ERROR
- SQL_INVALID_HANDLE
- SQL_NO_DATA_FOUND
- SQL_NO_TOTAL
SQL_NO_DATA_FOUND is returned when the preceding SQLGetData() function call retrieved all of the data for this column.
SQL_SUCCESS is returned if a zero-length string is retrieved by the SQLGetData() function. If so, the StrLen_or_IndPtr value is 0, and the TargetValuePtr value contains a null terminator.
SQL_NO_TOTAL is returned as the length when truncation occurs if the CLI configuration keyword StreamGetData is set to 1 and the CLI driver cannot determine the number of bytes still available to return in the output buffer.
If the preceding SQLFetch() function call fails, the SQLGetData() function call cannot be called since the result is undefined.
Diagnostics
SQLSTATE | Description | Explanation |
---|---|---|
01004 | Data truncated. | Data that is 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 was called before for the same ColumnNumber value but with a different TargetType value. |
07009 | Invalid descriptor index. | The value that is specified for ColumnNumber was equal to 0, and the SQL_ATTR_USE_BOOKMARKS statement attribute was SQL_UB_OFF. The value that is specified for the argument ColumnNumber was greater than the number of columns in the result set. |
22002 | Invalid output or indicator buffer specified. | The pointer value that is 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 cause 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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations. |
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 ( 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. The function was called before a statement was prepared on the statement handle. |
HY011 | Operation invalid at this time. | Calls to SQLGetData() for
previously accessed LOB columns are not allowed. See AllowGetDataLOBReaccess CLI/ODBC configuration keyword. |
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 CLI.
The requested conversion from the SQL data type to the application data TargetType cannot be performed by CLI or the data source. The column was bound with the SQLBindFileToCol() function. |
HYT00 | Timeout expired. | The timeout period expired before the data source returned
the result set. The timeout period can be set with the SQL_ATTR_QUERY_TIMEOUT
attribute for SQLSetStmtAttr() . |
Restrictions
None.
Example
/* use SQLGetData to get the results */
/* get data from column 1 */
cliRC = SQLGetData(hstmt,
1,
SQL_C_SHORT,
&deptnumb.val,
0,
&deptnumb.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* get data from column 2 */
cliRC = SQLGetData(hstmt,
2,
SQL_C_CHAR,
location.val,
15,
&location.ind);