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.
Syntax
SQLRETURN SQLGetCol (SQLHSTMT hstmt,
SQLSMALLINT icol,
SQLSMALLINT fCType,
SQLPOINTER rgbValue,
SQLINTEGER cbValueMax,
SQLINTEGER *pcbValue);
Function 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:
|
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 If |
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
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()
.
check_error,
initialize, and terminate
functions used in the following
example. /*************************************************************************
** 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 */