SQLGetData()
- Get data from a column
SQLGetData()
retrieves data for a single
column in the current row of the result set. You can also use SQLGetData()
to
retrieve large data values in pieces. After you call SQLGetData()
for
each column, call SQLFetch()
or SQLExtendedFetch()
for
each row that you want to retrieve.
You must call SQLFetch()
before SQLGetData()
.
Using this function is an alternative to using SQLBindCol()
,
which transfers data directly into application variables or LOB
locators on each SQLFetch()
or SQLExtendedFetch()
call.
ODBC specifications for SQLGetData()
ODBC specification level | In X/Open CLI CAE specification? | In ISO CLI specification? |
---|---|---|
1.0 | Yes | Yes |
Syntax
For 31-bit applications, use the following syntax:
SQLRETURN SQLGetData (SQLHSTMT hstmt,
SQLUSMALLINT icol,
SQLSMALLINT fCType,
SQLPOINTER rgbValue,
SQLINTEGER cbValueMax,
SQLINTEGER FAR *pcbValue);
For 64-bit applications, use the following syntax:
SQLRETURN SQLGetData (SQLHSTMT hstmt,
SQLUSMALLINT icol,
SQLSMALLINT fCType,
SQLPOINTER rgbValue,
SQLLEN cbValueMax,
SQLLEN FAR *pcbValue);
Function arguments
The following table lists the data type, use, and description for each argument in this function.
Data type | Argument | Use | Description |
---|---|---|---|
SQLHSTMT | hstmt | input | Specifies the statement handle on which the result set is generated. |
SQLUSMALLINT | icol | input | Specifies the column number of the result set for which the data retrieval is requested. |
SQLSMALLINT | fCType | input | Specifies the C data
type of the column that icol indicates.
You can specify the following types for the fCType argument:
|
SQLPOINTER | rgbValue1 | output | Points to a buffer where the retrieved column data is stored. |
SQLINTEGER (31-bit) or SQLLEN (64-bit)2 | cbValueMax | input | Specifies the maximum size of the buffer to which the rgbValue argument points. |
SQLINTEGER * (31-bit) or SQLLEN * (64-bit)2 | pcbValue1 | output | Points to the value that indicates the amount of space
that the data you are retrieving requires. If the data is 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 The value is SQL_NO_TOTAL if truncation occurs and the ODBC driver cannot determine the number of bytes still available to return. SQLGetData() retrieves enough data to fill the buffer and returns SQL_NO_TOTAL as the length. If |
Notes:
|
Usage
SQLGetData()
in
combination with SQLBindCol()
on the same result
set, if you use SQLFetch()
. Do not use SQLExtendedFetch()
.
Use the following procedure to retrieve data with SQLGetData()
:
- Call
SQLFetch()
, which advances cursor to first row, retrieves first row, and transfers data for bound columns. - Call
SQLGetData()
, which transfers data for the specified column. - Repeat step 2 for each column needed.
- Call
SQLFetch()
, which advances the cursor to the next row, retrieves the next row, and transfers data for bound columns. - Repeat steps 2, 3 and 4 for each row that is in the result set, or until the result set is no longer needed.
You can also use SQLGetData()
to retrieve
long columns if the C data type (which you specify with the fCType argument)
is SQL_C_CHAR, SQL_C_BINARY, SQL_C_DBCHAR, or if fCType is
SQL_C_DEFAULT and the column type denotes a binary or character string.
SQLGetData()
determines which one of the following
encoding schemes is used for character and graphic data. - The ODBC driver places EBCDIC data into application variables
when both of the following conditions are true:
- CURRENTAPPENSCH = EBCDIC is specified in the initialization file, the CCSID that is specified for the CURRENTAPPENSCH keyword is an EBCDIC CCSID, or the CURRENTAPPENSCH keyword is not specified in the initialization file.
- The fCType argument specifies SQL_C_CHAR
or SQL_C_DBCHAR in the
SQLGetData()
call.
- The ODBC driver places Unicode UCS-2 data into application variables
when the fCType argument specifies SQL_C_WCHAR
in the
SQLGetData()
call. - The ODBC driver places Unicode UTF-8 data into application variables
when both of the following conditions are true:
- CURRENTAPPENSCH = UNICODE is specified in the initialization file, or the CCSID that is specified for the CURRENTAPPENSCH keyword is a Unicode CCSID (1200, 1208, 13488 or 17584).
- The fCType argument specifies SQL_C_CHAR
in the
SQLGetData()
call.
- The ODBC driver places ASCII data into application variables when
both of the following conditions are true:
- CURRENTAPPENSCH = ASCII is specified in the initialization file, or the CCSID that is specified for the CURRENTAPPENSCH keyword is an ASCII CCSID.
- The fCType argument specifies SQL_C_CHAR
or SQL_C_DBCHAR in the
SQLGetData()
call.
Handling data truncation: After each SQLGetData()
call,
if the data available for return is greater than or equal to cbValueMax,
the data is truncated. Truncation is indicated by a function return
code of SQL_SUCCESS_WITH_INFO coupled with a SQLSTATE denoting data
truncation. You can call SQLGetData()
again, on the
same column, to subsequently retrieve the truncated data. To obtain
the entire column, repeat these calls until SQLGetData()
returns
SQL_SUCCESS. If you call SQLGetData()
after it returns
SQL_SUCCESS, it returns SQL_NO_DATA_FOUND.
When Db2 ODBC truncates digits to the right of the decimal point from numeric data types, Db2 ODBC issues a warning. When Db2 ODBC truncates digits to the left of the decimal point, however, Db2 ODBC returns an error. (See Diagnostics for more information.)
To
eliminate warnings when data is truncated, call SQLSetStmtAttr()
with
the SQL_ATTR_MAX_LENGTH attribute set to a maximum length value. Then
allocate a buffer for the rgbValue argument
that is the same size (plus the nul-terminator) as the value that
you specified for SQL_ATTR_MAX_LENGTH. If the column data is larger
than the maximum number of bytes that you specified for SQL_ATTR_MAX_LENGTH,
SQL_SUCCESS is returned. When you specify a maximum length, Db2 ODBC returns the length you
specify, not the actual length, for the pcbValue argument.
SQLGetData()
to
retrieve LOB column data sequentially, use the Db2 ODBC LOB functions when you need a only portion
or a few sections of LOB data. Use the following procedure instead
of SQLGetData()
if you want to retrieve portions
of LOB values:
Discarding data from an active retrieval: To discard
data from a retrieval that is currently active, call SQLGetData()
with
the icol argument set to the next column
position from which you want to retrieve data. To discard data that
you have not retrieved, call SQLFetch()
to advance
the cursor to the next row. Call SQLFreeStmt()
or SQLCloseCursor()
if
you have finished retrieving data from the result set.
Allocating buffers: The fCType input argument determines the type of data conversion (if any) that occurs before the column data is placed into the buffer to which the rgbValue argument points.
- The size of the buffer that the rgbValue argument
specifies must be a multiple of 2 bytes. (The cbValueMax value
must specify this value as a multiple of 2 bytes also.) Before you
call
SQLGetData()
, callSQLDescribeCol()
orSQLColAttribute()
to determine the SQL data type and the length, in bytes, of data in the result set. - The pcbValue argument must not specify a null pointer. Db2 ODBC stores the number of octets that are stored in the buffer to which the rgbValue argument points.
- If you retrieve data in pieces, Db2 ODBC attempts to fill rgbValue to the nearest multiple of two octets that is less than or equal to the value the cbValueMax argument specifies. If cbValueMax is not a multiple of two, the last byte in that buffer is never used. Db2 ODBC does not split a double-byte character.
The buffer that the rgbValue argument specifies contains nul-terminated values, unless you retrieve binary data, or the SQL data type of the column is graphic (DBCS) and the C buffer type is SQL_C_CHAR. If you retrieve data in pieces, you must perform the proper adjustments to the nul-terminator when you reassemble these pieces. (That is, you must remove nul-terminators before concatenating the pieces of data.)
Return codes
SQLGetData()
,
it returns one of the following values: - SQL_SUCCESS
- SQL_SUCCESS_WITH_INFO
- SQL_ERROR
- SQL_INVALID_HANDLE
- SQL_NO_DATA_FOUND
SQL_SUCCESS is returned if SQLGetData()
retrieves
a zero-length string. For zero-length strings, pcbValue contains
0, and rgbValue contains a nul-terminator.
SQL_NO_DATA_FOUND
is returned when the preceding SQLGetData()
call
has retrieved all of the data for this column.
If the preceding
call to SQLFetch()
failed, do not call SQLGetData()
.
In this case, SQLGetData()
retrieves undefined data.
Diagnostics
The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.
SQLSTATE | Description | Explanation |
---|---|---|
01004 | Data truncated. | Data that is returned for the column that the icol argument
specifies is truncated. String or numeric values are right truncated.
(SQLGetData() returns SQL_SUCCESS_WITH_INFO for this
SQLSTATE.) |
07006 | Invalid conversion. | This SQLSTATE is returned for one or more of the
following reasons:
|
08S01 | Communication link failure. | The communication link between the application and data source fails before the function completes. |
22002 | Invalid output or indicator buffer specified. | The pointer that is specified in the pcbValue argument is a null pointer, and the value of the column is also null. The function cannot report SQL_NULL_DATA. |
22008 | Invalid datetime format or datetime field overflow. | Datetime field overflow occurred. Example: An arithmetic operation on a date or timestamp results in a value that is not within the valid range of dates, or a datetime value cannot be assigned to a bound variable because the variable is too small. |
22018 | Error in assignment. | A returned value is incompatible with the data type that the fCType argument denotes. |
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. |
58004 | Unexpected system failure. | Unrecoverable system error. |
HY001 | Memory allocation failure. | Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function. |
HY002 | Invalid column number. | This SQLSTATE is returned for one or more of the
following reasons:
|
HY003 | Program type out of range. | The fCTypeargument specifies an invalid data type or SQL_C_DEFAULT. |
HY009 | Invalid use of a null pointer. | This SQLSTATE is returned for one or more of the
following reasons:
|
HY010 | Function sequence error. | This SQLSTATE is returned for one or more of the
following reasons:
|
HY013 | Unexpected memory handling error. | Db2 ODBC is not able to access the memory that is required to support execution or completion of the function. |
HY019 | Numeric value out of range. | When the numeric value (as numeric or string) for the column is returned, the whole part of the number is truncated. |
HY090 | Invalid string or buffer length. | The value of the cbValueMax argument
is less than 0 and thefCType argument specifies
one of the following values:
|
HYC00 | Driver not capable. | This SQLSTATE is returned for one or more of the
following reasons:
|
Restrictions
ODBC has defined column 0 for bookmarks. Db2 ODBC does not support bookmarks.
Example
SQLGetData()
to retrieve
data. You can compare this example with the one in SQLFetch()
for
a comparison in using bound columns. SQLGetData()
/******************************************************************/
/* Populate BIOGRAPHY table from flat file text. Insert */
/* VITAE in 80-byte pieces via SQLPutData. Also retrieve */
/* NAME, UNIT and VITAE for all members. VITAE is retrieved*/
/* via SQLGetData. */
/******************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include "sqlcli1.h"
#define TEXT_SIZE 80
int insert_bio (SQLHSTMT hStmt, // insert_bio prototype
char *bio,
int bcount);
int main( )
{
SQLHENV hEnv = SQL_NULL_HENV;
SQLHDBC hDbc = SQL_NULL_HDBC;
SQLHSTMT hStmt = SQL_NULL_HSTMT, hStmt2 = SQL_NULL_HSTMT;
SQLRETURN rc = SQL_SUCCESS;
FILE *fp;
SQLINTEGER RETCODE = 0;
char pTable [200];
char *pDSN = "STLEC1";
UDWORD pirow;
SDWORD cbValue;
char *i_stmt = "INSERT INTO BIOGRAPHY VALUES (?, ?, ?)";
char *query = "SELECT NAME, UNIT, VITAE FROM BIOGRAPHY";
char text [TEXT_SIZE]; // file text
char vitae [3200]; // biography text
char Narrative [TEXT_SIZE];
SQLINTEGER vitae_ind = SQL_DATA_AT_EXEC; // bio data is
// passed at execute time
// via SQLPutData
SQLINTEGER vitae_cbValue = TEXT_SIZE;
char *t = NULL;
char *c = NULL;
char name [21];
SQLINTEGER name_ind = SQL_NTS;
SQLINTEGER name_cbValue = sizeof(name);
char unit [31];
SQLINTEGER unit_ind = SQL_NTS;
SQLINTEGER unit_cbValue = sizeof(unit);
char tmp [80];
char *token = NULL, *pbio = vitae;
char insert = SQL_FALSE;
int i, bcount = 0;
(void) printf ("**** Entering CLIP09.\n\n");
/*****************************************************************/
/* Allocate environment handle */
/*****************************************************************/
RETCODE = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, hEnv, &hDbc);
if (RETCODE != SQL_SUCCESS)
goto dberror;
/*****************************************************************/
/* Allocate connection handle to DSN */
/*****************************************************************/
RETCODE = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
if( RETCODE != SQL_SUCCESS ) // Could not get a Connect Handle
goto dberror;
/*****************************************************************/
/* CONNECT TO data source (STLEC1) */
/*****************************************************************/
RETCODE = SQLConnect(hDbc, // Connect handle
(SQLCHAR *) pDSN, // DSN
SQL_NTS, // DSN is nul-terminated
NULL, // Null UID
0 ,
NULL, // Null Auth string
0);
if( RETCODE != SQL_SUCCESS ) // Connect failed
goto dberror;
/*****************************************************************/
/* Allocate statement handles */
/*****************************************************************/
rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
if (rc != SQL_SUCCESS)
{
(void) printf ("**** Allocate statement handle failed.\n");
goto dberror;
}
rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt2);
if (rc != SQL_SUCCESS)
{
(void) printf ("**** Allocate statement handle failed.\n");
goto dberror;
}
/*****************************************************************/
/* Prepare INSERT statement. */
/*****************************************************************/
rc = SQLPrepare (hStmt,
(SQLCHAR *) i_stmt,
SQL_NTS);
if (rc != SQL_SUCCESS)
{
(void) printf ("**** Prepare of INSERT failed.\n");
goto dberror;
}
/*****************************************************************/
/* Bind NAME and UNIT. Bind VITAE so that data can be passed */
/* via SQLPutData. */
/*****************************************************************/
rc = SQLBindParameter (hStmt, // bind NAME
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
sizeof(name),
0,
name,
sizeof(name),
&name_ind);
if (rc != SQL_SUCCESS)
{
(void) printf ("**** Bind of NAME failed.\n");
goto dberror;
}
rc = SQLBindParameter (hStmt, // bind Branch
2,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
sizeof(unit),
0,
unit,
sizeof(unit),
&unit_ind);
if (rc != SQL_SUCCESS)
{
(void) printf ("**** Bind of UNIT failed.\n");
goto dberror;
}
rc = SQLBindParameter (hStmt, // bind Rank
3,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_LONGVARCHAR,
3200,
0,
(SQLPOINTER) 3,
0,
&vitae_ind);
if (rc != SQL_SUCCESS)
{
(void) printf ("**** Bind of VITAE failed.\n");
goto dberror;
}
/*****************************************************************/
/* Read biographical text from flat file */
/*****************************************************************/
if ((fp = fopen ("DD:BIOGRAF", "r")) == NULL) // open command file
{
rc = SQL_ERROR; // open failed
goto exit;
}
/*****************************************************************/
/* Process file and insert biographical text */
/*****************************************************************/
while (((t = fgets (text, sizeof(text), fp)) != NULL) &&;
(rc == SQL_SUCCESS))
{
if (text[0] == #') // if commander data
{
if (insert) // if BIO data to be inserted
{
rc = insert_bio (hStmt,
vitae,
bcount); // insert row into BIOGRAPHY Table
bcount = 0; // reset text line count
pbio = vitae; // reset text pointer
}
token = strtok (text+1, ","); // get member NAME
(void) strcpy (name, token);
token = strtok (NULL, "#"); // extract UNIT
(void) strcpy (unit, token); // copy to local variable
// SQLPutData
insert = SQL_TRUE; // have row to insert
}
else
{
memset (pbio, ' ', sizeof(text));
strcpy (pbio, text); // populate text
i = strlen (pbio); // remove '\n' and '\0'
pbio [i--] =' ';
pbio [i] =' ';
pbio += sizeof (text); // advance pbio
bcount++; // one more text line
}
}
if (insert) // if BIO data to be inserted
{
rc = insert_bio (hStmt,
vitae,
bcount); // insert row into BIOGRAPHY Table
}
fclose (fp); // close text flat file
/*****************************************************************/
/* Commit insert of rows */
/*****************************************************************/
rc =SQLEndTran(SQL_HANDLE_DBC, hDbc, SQL_COMMIT);
if (rc != SQL_SUCCESS)
{
(void) printf ("**** COMMIT FAILED.\n");
goto dberror;
}
/*****************************************************************/
/* Open query to retrieve NAME, UNIT and VITAE. Bind NAME and */
/* UNIT but leave VITAE unbound. Retrieved using SQLGetData. */
/*****************************************************************/
RETCODE = SQLPrepare (hStmt2,
(SQLCHAR *)query,
strlen(query));
if (RETCODE != SQL_SUCCESS)
{
(void) printf ("**** Prepare of Query Failed.\n");
goto dberror;
}
RETCODE = SQLExecute (hStmt2);
if (RETCODE != SQL_SUCCESS)
{
(void) printf ("**** Query Failed.\n");
goto dberror;
}
RETCODE = SQLBindCol (hStmt2, // bind NAME
1,
SQL_C_DEFAULT,
name,
sizeof(name),
&name_cbValue);
if (RETCODE != SQL_SUCCESS)
{
(void) printf ("**** Bind of NAME Failed.\n");
goto dberror;
}
RETCODE = SQLBindCol (hStmt2, // bind UNIT
2,
SQL_C_DEFAULT,
unit,
sizeof(unit),
&unit_cbValue);
if (RETCODE != SQL_SUCCESS)
{
(void) printf ("**** Bind of UNIT Failed.\n");
goto dberror;
}
while ((RETCODE = SQLFetch (hStmt2)) != SQL_NO_DATA_FOUND)
{
(void) printf ("**** Name is %s. Unit is %s.\n\n", name, unit);
(void) printf ("**** Vitae follows:\n\n");
for (i = 0; (i < 3200 && RETCODE != SQL_NO_DATA_FOUND); i += TEXT_SIZE)
{
RETCODE = SQLGetData (hStmt2,
3,
SQL_C_CHAR,
Narrative,
sizeof(Narrative) + 1,
&vitae_cbValue);
if (RETCODE != SQL_NO_DATA_FOUND)
(void) printf ("%s\n", Narrative);
}
}
/*****************************************************************/
/* Deallocate statement handles */
/*****************************************************************/
rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, hStmt);
rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, hStmt2);
/*****************************************************************/
/* DISCONNECT from data source */
/*****************************************************************/
RETCODE = SQLDisconnect(hDbc);
if (RETCODE != SQL_SUCCESS)
goto dberror;
/*****************************************************************/
/* Deallocate connection handle */
/*****************************************************************/
RETCODE = SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
if (RETCODE != SQL_SUCCESS)
goto dberror;
/*****************************************************************/
/* Free environment handle */
/*****************************************************************/
rc = SQLFreeHandle (SQL_HANDLE_ENV, hEnv);
if (RETCODE == SQL_SUCCESS)
goto exit;
dberror:
RETCODE=12;
exit:
(void) printf ("**** Exiting CLIP09.\n\n");
return RETCODE;
}
/*****************************************************************/
/* Function insert_bio is invoked to insert one row into the */
/* BIOGRAPHY Table. The biography text is inserted in sets of */
/* 80 bytes via SQLPutData. */
/*****************************************************************/
int insert_bio (SQLHSTMT hStmt,
char *vitae,
int bcount)
{
SQLINTEGER rc = SQL_SUCCESS;
SQLPOINTER prgbValue;
int i;
char *text;
/*****************************************************************/
/* NAME and UNIT are bound... VITAE is provided after execution */
/* of the INSERT using SQLPutData. */
/*****************************************************************/
rc = SQLExecute (hStmt);
if (rc != SQL_NEED_DATA) // expect SQL_NEED_DATA
{
rc = 12;
(void) printf ("**** NEED DATA not returned.\n");
goto exit;
}
/*****************************************************************/
/* Invoke SQLParamData to position ODBC driver on input parameter*/
/*****************************************************************/
if ((rc = SQLParamData (hStmt,
&prgbValue)) != SQL_NEED_DATA)
{
rc = 12;
(void) printf ("**** NEED DATA not returned.\n");
goto exit;
}
/*****************************************************************/
/* Iterate through VITAE in 80 byte increments.... pass to */
/* ODBC Driver via SQLPutData. */
/*****************************************************************/
for (i = 0, text = vitae, rc = SQL_SUCCESS;
(i < bcount) && (rc == SQL_SUCCESS);
i++, text += TEXT_SIZE)
{
rc = SQLPutData (hStmt,
text,
TEXT_SIZE);
}
/*****************************************************************/
/* Invoke SQLParamData to trigger ODBC driver to execute the */
/* statement. */
/*****************************************************************/
if ((rc = SQLParamData (hStmt,
&prgbValue)) != SQL_SUCCESS)
{
rc = 12;
(void) printf ("**** INSERT Failed.\n");
}
exit:
return (rc);
}