SQLGetCol - Retrieve one column of a row of the result set

SQLGetCol() retrieves data for a single column in the current row of the result set. This is an alternative to SQLBindCol(), which transfers data directly into application variables on a call to SQLFetch(). SQLGetCol() is also used to retrieve large character-based data in pieces.

SQLFetch() must be called before SQLGetCol().

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

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLGetColW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI.

Syntax

SQLRETURN SQLGetCol  (SQLHSTMT       hstmt,
                      SQLSMALLINT    icol,
                      SQLSMALLINT    fCType,
                      SQLPOINTER     rgbValue,
                      SQLINTEGER     cbValueMax,
                      SQLINTEGER     *pcbValue);

Function arguments

Table 1. SQLGetCol arguments
Data type	Argument	Use	Description
SQLHSTMT	hstmt	Input	Statement handle.
SQLSMALLINT	icol	Input	Column number for which the data retrieval is requested.
SQLSMALLINT	fCType	Input	Application data type of the column identified by icol. The following types are supported: SQL_BIGINT SQL_BINARY SQL_BLOB SQL_CHAR SQL_CLOB SQL_DATETIME SQL_DBCLOB SQL_DECFLOAT SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_GRAPHIC SQL_INTEGER SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_VARBINARY SQL_VARGRAPHIC
SQLPOINTER	rgbValue	Output	Pointer to buffer where the retrieved column data is to be stored.
SQLINTEGER	cbValueMax	Input	Maximum size of the buffer pointed to by rgbValue. If fcType is either SQL_DECIMAL or SQL_NUMERIC, cbValueMax must be a precision and scale. The method to specify both values is to use (precision * 256) + scale. This is also the value returned as the LENGTH of these data types when using `SQLColAttribute()`.
SQLINTEGER *	pcbValue	Output	Pointer to the value that indicates the number of bytes DB2 for i CLI has available to return in the rgbValue buffer. If the data is being retrieved in pieces, this contains the number of bytes still remaining, excluding any bytes of the column's data that has been obtained from previous calls to `SQLGetCol()`. 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 fails because it has no means of reporting this. If `SQLFetch()` has fetched a column containing graphic data, then the pointer to pcbValue must not be NULL or this function fails because it has no means of informing the application about the length of the data retrieved in the rgbValue buffer.

Usage

SQLGetCol() can be used with SQLBindCol() for the same row, as long as the value of icol does not specify a column that has been bound. The general steps are:

SQLFetch() - advances cursor to first row, retrieves first row, transfers data for bound columns.
SQLGetCol() - transfers data for specified (unbound) 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.

SQLGetCol() retrieves long columns if the C data type (fCType) is SQL_CHAR or if fCType is SQL_DEFAULT and the column type is CHAR or VARCHAR.

On each SQLGetCol() call, if the data available for return is greater than or equal to cbValueMax, truncation occurs. A function return code of SQL_SUCCESS_WITH_INFO that is coupled with an SQLSTATE that denotes data truncation indicates truncation. The application can call SQLGetCol() again, with the same icol value, to obtain later 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 SQLGetCol() returns SQL_NO_DATA_FOUND.

To discard the column data part way through the retrieval, the application can call SQLGetCol() with icol set to the next column position of interest. To discard unretrieved data 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 fCType input argument determines the type of data conversion (if any) needed before the column data is placed into the storage area pointed to by rgbValue.

The contents returned in rgbValue is always null-terminated unless SQLSetEnvAttr() is used to change the SQL_ATTR_OUTPUT_NTS attribute or if the application is retrieving the data in multiple chunks. If the application is retrieving the data in multiple chunks, the null-terminating byte is only added to the last portion of data.

Truncation of numeric data types is not reported 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).

For decimal floating point data types, a precision of 32, 64, or 128 can be specified by using the default symbolic C data type constants. For example, to specify a decimal floating point data type with a precision of 128 bytes, ValueType can be set to SQL_C_DECIMAL128.

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE
SQL_NO_DATA_FOUND

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

SQL_SUCCESS is returned if a zero-length string is retrieved by SQLGetCol(). If this is the case, pcbValue contains 0, and rgbValue contains a null terminator.

If the preceding call to SQLFetch() fails, SQLGetCol() should not be called because the result is undefined.

Diagnostics

Table 2. SQLGetCol SQLSTATEs
SQLSTATE	Description	Explanation
07006	Restricted data type attribute violation	The data value cannot be converted to the C data type specified by the argument fCType.
HY001	Memory allocation failure	The driver is unable to allocate memory required to support the processing or completion of the function.
HY009	Argument value that is not valid	The value of the argument cbValueMax is less than 1 and the argument fCType is SQL_CHAR. The specified column number is not valid. The argument rgbValue or pcbValue is a null pointer.
HY010	Function sequence error	The specified hstmt is not in a cursor positioned state. The function is called without first calling `SQLFetch()`.
HY013 *	Memory management problem	The driver is unable to access memory required to support the processing or completion of the function.
HY021	Internal descriptor that is not valid	The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.
HYC00	Driver not capable	The SQL data type for the specified data type is recognized but not supported by the driver. The requested conversion from the SQL data type to the application data fCType cannot be performed by the driver or the data source.

Restrictions

ODBC requires that icol not specify a column of a lower number than the column last retrieved by SQLGetCol() for the same row on the same statement handle. ODBC also does not permit the use of SQLGetCol() to retrieve data for a column that resides before the last bound column, (if any columns in the row have been bound).

DB2 for i CLI has relaxed both of these rules by allowing the value of icol to be specified in any order and before a bound column, provided that icol does not specify a bound column.

Example

Refer to the example in the SQLFetch - Fetch next row for a comparison between using bound columns and using SQLGetCol().

Refer to Example: Interactive SQL and the equivalent DB2 for i CLI function calls for a listing of the check_error, initialize, and terminate functions used in the following example.

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

/*************************************************************************
** file = getcol.c
**
** Example of directly executing an SQL statement.
** Getcol is used to retrieve information from the result set.
** Compare to fetch.c
**
** Functions used:
**
**        SQLAllocConnect      SQLFreeConnect
**        SQLAllocEnv          SQLFreeEnv
**        SQLAllocStmt         SQLFreeStmt
**        SQLConnect           SQLDisconnect
**
**        SQLBindCol           SQLFetch
**        SQLTransact          SQLError
**        SQLExecDirect        SQLGetCursor
**************************************************************************/
 
#include <stdio.h>
#include <string.h>
#include "sqlcli.h"
 
#define MAX_STMT_LEN 255
 
int initialize(SQLHENV *henv,
               SQLHDBC *hdbc);
 
int terminate(SQLHENV henv,
              SQLHDBC hdbc);
 
int print_error (SQLHENV    henv,
                 SQLHDBC    hdbc,
                 SQLHSTMT   hstmt);
 
int check_error (SQLHENV    henv,
                 SQLHDBC    hdbc,
                 SQLHSTMT   hstmt,
                 SQLRETURN  frc);
 
/*******************************************************************
** main
** - initialize
** - terminate
*******************************************************************/
int main()
{
    SQLHENV     henv;
    SQLHDBC     hdbc;
    SQLCHAR     sqlstmt[MAX_STMT_LEN + 1]="";
    SQLRETURN   rc;
 
    rc = initialize(&henv, &hdbc);
    if (rc != SQL_SUCCESS) return(terminate(henv, hdbc));
 
    {SQLHSTMT   hstmt;
     SQLCHAR    sqlstmt[]="SELECT deptname, location from org where division = 'Eastern'";
     SQLCHAR    deptname[15],
                location[14];
     SQLINTEGER rlength;
 
        rc = SQLAllocStmt(hdbc, &hstmt);
        if (rc != SQL_SUCCESS )
            check_error (henv, hdbc, SQL_NULL_HSTMT, rc);
 
        rc = SQLExecDirect(hstmt, sqlstmt, SQL_NTS);
        if (rc != SQL_SUCCESS )
            check_error (henv, hdbc, hstmt, rc);
 
        printf("Departments in Eastern division:\n");
        printf("DEPTNAME       Location\n");
        printf("-------------- -------------\n");
 
        while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS)
        {
            rc = SQLGetCol(hstmt, 1, SQL_CHAR, (SQLPOINTER) deptname, 15, &rlength);
            rc = SQLGetCol(hstmt, 2, SQL_CHAR, (SQLPOINTER) location, 14, &rlength);
            printf("%-14.14s %-13.13s \n", deptname, location);
        }
        if (rc != SQL_NO_DATA_FOUND )
            check_error (henv, hdbc, hstmt, rc);
    }
 
    rc = SQLTransact(henv, hdbc, SQL_COMMIT);
    if (rc != SQL_SUCCESS )
        check_error (henv, hdbc, SQL_NULL_HSTMT, rc);
 
    terminate(henv, hdbc);
    return (SQL_SUCCESS);
 
}/* end main */