SQLGetData function (CLI) - Get data from a column

Purpose

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. An application can either bind LOBs with SQLBindCol() or use SQLGetData() to retrieve LOBs, but both methods cannot be used together. SQLGetData() can also be used to retrieve large data values in pieces.

SQLFetch() or SQLFetchScroll() 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 */
               SQLLEN            BufferLength,        /* cbValueMax */
               SQLLEN            *StrLen_or_IndPtr);  /* pcbValue */

Function arguments

Usage

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, SQL_C_WCHAR, 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.

When the application calls the function SQLGetData() to retrieve the actual LOB data it will, by default, make one request to the server and will store the entire LOB in memory provided BufferLength is large enough. If BufferLength is not large enough to hold the requested LOB data, it will be retrieved in pieces.

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 does not want any more data from the result set, the application can close the cursor by calling SQLCloseCursor() or SQLFreeStmt() 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 TargetValuePtr.

The content 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 with 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.

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() 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.

SQL_NO_TOTAL is returned as the length when truncation occurs if the CLI configuration keyword StreamGetData is set to 1 and CLI cannot determine the number of bytes still available to return in the output buffer.

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

Diagnostics

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);