When writing a DB2 CLI application it is necessary to work with both SQL data types and C data types. This is unavoidable since the DBMS uses SQL data types, and the application must use C data types. This means the application must match C data types to SQL data types when transferring data between the DBMS and the application (when calling DB2 CLI functions).
To help address this, DB2 CLI provides symbolic names for the various data types, and manages the transfer of data between the DBMS and the application. It will also perform data conversion (from a C character string to an SQL INTEGER type, for example) if required. To accomplish this, DB2 CLI needs to know both the source and target data type. This requires the application to identify both data types using symbolic names.
Table 3 list each of the SQL data types, with its corresponding symbolic name, and the default C symbolic name.
Table 3. SQL Symbolic and Default Data Types
| SQL Data Type | Symbolic SQL Data Type | Default Symbolic C Data Type | ||
|---|---|---|---|---|
| BLOB | SQL_BLOB | SQL_C_BINARY | ||
| BLOB LOCATOR a | SQL_BLOB_LOCATOR | SQL_C_BLOB_LOCATOR | ||
| CHAR | SQL_CHAR | SQL_C_CHAR | ||
| CHAR FOR BIT DATA b | SQL_BINARY | SQL_C_BINARY | ||
| CLOB | SQL_CLOB | SQL_C_CHAR | ||
| CLOB LOCATOR a | SQL_CLOB_LOCATOR | SQL_C_CLOB_LOCATOR | ||
| DATE | SQL_TYPE_DATE d | SQL_C__TYPE_DATE d | ||
| DBCLOB | SQL_DBCLOB | SQL_C_DBCHAR | ||
| DBCLOB LOCATOR a | SQL_DBCLOB_LOCATOR | SQL_C_DBCLOB_LOCATOR | ||
| DECIMAL | SQL_DECIMAL | SQL_C_CHAR | ||
| DOUBLE | SQL_DOUBLE | SQL_C_DOUBLE | ||
| FLOAT | SQL_FLOAT | SQL_C_DOUBLE | ||
| GRAPHIC | SQL_GRAPHIC | SQL_C_DBCHAR | ||
| INTEGER | SQL_INTEGER | SQL_C_LONG | ||
| LONG VARCHAR b | SQL_LONGVARCHAR | SQL_C_CHAR | ||
| LONG VARCHAR FOR BIT DATA b | SQL_LONGVARBINARY | SQL_C_BINARY | ||
| LONG VARGRAPHIC b | SQL_LONGVARGRAPHIC | SQL_C_DBCHAR | ||
| NUMERIC c | SQL_NUMERIC c | SQL_C_CHAR | ||
| REAL | SQL_REAL | SQL_C_FLOAT | ||
| SMALLINT | SQL_SMALLINT | SQL_C_SHORT | ||
| TIME | SQL_TYPE_TIME d | SQL_C_TYPE_TIME d | ||
| TIMESTAMP | SQL_TYPE_TIMESTAMP d | SQL_C_TYPE_TIMESTAMP d | ||
| VARCHAR | SQL_VARCHAR | SQL_C_CHAR | ||
| VARCHAR FOR BIT DATA b | SQL_VARBINARY | SQL_C_BINARY | ||
| VARGRAPHIC | SQL_VARGRAPHIC | SQL_C_DBCHAR | ||
|
| ||||
Table 4 shows the generic type definitions for each symbolic C type.
| C Symbolic Data Type | C Type | Base C type | ||
|---|---|---|---|---|
| SQL_C_CHAR | SQLCHAR | unsigned char | ||
| SQL_C_BIT | SQLCHAR | unsigned char or char (Value 1 or 0) | ||
| SQL_C_TINYINT | SQLSCHAR | signed char (Range -128 to 127) | ||
| SQL_C_SHORT | SQLSMALLINT | short int | ||
| SQL_C_LONG | SQLINTEGER | long int | ||
| SQL_C_DOUBLE | SQLDOUBLE | double | ||
| SQL_C_FLOAT | SQLREAL | float | ||
| SQL_C_TYPE_DATE b | DATE_STRUCT | see Table 5 | ||
| SQL_C_TYPE_TIME b | TIME_STRUCT | see Table 5 | ||
| SQL_C_TYPE_TIMESTAMP b | TIMESTAMP_STRUCT | see Table 5 | ||
| SQL_C_CLOB_LOCATOR a | SQLINTEGER | long int | ||
| SQL_C_BINARY | SQLCHAR | unsigned char | ||
| SQL_C_BLOB_LOCATOR a | SQLINTEGER | long int | ||
| SQL_C_DBCHAR | SQLDBCHAR | wchar_t | ||
| SQL_C_DBCLOB_LOCATOR | SQLINTEGER | long int | ||
| ||||
Table 5. C DATE, TIME, and TIMESTAMP Structures
| C Type | Generic Structure | Windows Structure |
|---|---|---|
| DATE_STRUCT |
typedef struct DATE_STRUCT
{
SQLSMALLINT year;
SQLUSMALLINT month;
SQLUSMALLINT day;
} DATE_STRUCT;
|
typedef struct tagDATE_STRUCT
{
SWORD year;
UWORD month;
UWORD day;
} DATE_STRUCT;
|
| TIME_STRUCT |
typedef struct TIME_STRUCT
{
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
} TIME_STRUCT;
|
typedef struct tagTIME_STRUCT
{
UWORD hour;
UWORD minutes;
UWORD second;
} TIME_STRUCT;
|
| TIMESTAMP_STRUCT |
typedef struct TIMESTAMP_STRUCT
{
SQLUSMALLINT year;
SQLUSMALLINT month;
SQLUSMALLINT day;
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
SQLINTEGER fraction;
} TIMESTAMP_STRUCT;
|
typedef struct tagTIMESTAMP_STRUCT
{
SWORD year;
UWORD month;
UWORD day;
UWORD hour;
UWORD minute;
UWORD second;
UDWORD fraction;
} TIMESTAMP_STRUCT;
|
|
Refer to Table 6 for more information on the SQLUSMALLINT C data type.
| ||
As well as the data types that map to SQL data types, there are also C
symbolic types used for other function arguments, such as pointers and
handles. Both the generic and ODBC data types are shown below.
Table 6. C Data Types and Base C Data Types
| Defined C Type | Base C Type | Typical Usage |
|---|---|---|
| SQLPOINTER | void * | Pointers to storage for data and parameters. |
| SQLHANDLE | long int | Handle used to reference all 4 types of handle information. |
| SQLHENV | long int | Handle referencing environment information. |
| SQLHDBC | long int | Handle referencing database connection information. |
| SQLHSTMT | long int | Handle referencing statement information. |
| SQLUSMALLINT | unsigned short int | Function input argument for unsigned short integer values. |
| SQLUINTEGER | unsigned long int | Function input argument for unsigned long integer values. |
| SQLRETURN | short int | Return code from DB2 CLI functions. |
|
Versions of DB2 CLI prior to Version 2.1:
| ||
As mentioned previously, DB2 CLI manages the transfer and any required conversion of data between the application and the DBMS. Before the data transfer actually takes place, the source, target or both data types are indicated when calling SQLBindParameter(), SQLBindCol() or SQLGetData(). These functions use the symbolic type names shown in Table 3, to identify the data types involved.
For example, to bind a parameter marker that corresponds to an SQL data type of DECIMAL(5,3), to an application's C buffer type of double, the appropriate SQLBindParameter() call would look like:
SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE,
SQL_DECIMAL, 5, 3, double_ptr, 0, NULL);
Table 3 shows only the default data conversions. The functions mentioned in the previous paragraph can be used to convert data to other types, but not all data conversions are supported or make sense. Table 7 shows all the conversions supported by DB2 CLI.
The first column in Table 7 contains the data type of the SQL data type, the remaining columns represent the C data types. If the C data type columns contains:
As an example, the table indicates that a CHAR (or a C character string as indicated in Table 7) can be converted into a SQL_C_LONG (a signed long). In contrast, a LONGVARCHAR cannot be converted to a SQL_C_LONG.
Refer to Appendix F. "Data Conversion" for information about the required formats and the results of converting between data types.
Limits on precision, and scale, as well as truncation and rounding rules
for type conversions follow rules specified in the SQL
Reference with the following exception; truncation of values to the right of the
decimal point for numeric values may return a truncation warning, whereas
truncation to the left of the decimal point returns an error. In cases of
error, the application should call
SQLGetDiagRec() to obtain the SQLSTATE and additional information on the failure.
When moving and converting floating point data values between the application
and DB2 CLI, no correspondence is guaranteed to be exact as the values may
change in precision and scale.
Table 7. Supported Data Conversions
SQL Data Type |
S Q L _ C _ C H A R |
S Q L _ C _ L O N G |
S Q L _ C _ S H O R T |
S Q L _ C _ T I N Y I N T |
S Q L _ C _ F L O A T |
S Q L _ C _ D O U B L E |
S Q L _ C _ T Y P E _ D A T E |
S Q L _ C _ T Y P E _ T I M E |
S Q L _ C _ T Y P E _ T I M E S T A M P |
S Q L _ C _ B I N A R Y |
S Q L _ C _ B I T |
S Q L _ C _ D B C H A R |
S Q L _ C _ C L O B _ L O C A T O R |
S Q L _ C _ B L O B _ L O C A T O R |
S Q L _ C _ D B C L O B _ L O C A T O R | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
BLOB |
X |
|
|
|
|
|
|
|
|
D |
|
|
|
X |
| ||
CHAR |
D |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
|
|
|
| ||
CLOB |
D |
|
|
|
|
|
|
|
|
X |
|
|
X |
|
| ||
DATE |
X |
|
|
|
|
|
D |
|
X |
|
|
|
|
|
| ||
DBCLOB |
|
|
|
|
|
|
|
|
|
X |
|
D |
|
|
X | ||
DECIMAL |
D |
X |
X |
X |
X |
X |
|
|
|
|
X |
|
|
|
| ||
DOUBLE |
X |
X |
X |
X |
X |
D |
|
|
|
|
X |
|
|
|
| ||
FLOAT |
X |
X |
X |
X |
X |
D |
|
|
|
|
X |
|
|
|
| ||
GRAPHIC |
X |
|
|
|
|
|
|
|
|
|
|
D |
|
|
| ||
INTEGER |
X |
D |
X |
X |
X |
X |
|
|
|
|
X |
|
|
|
| ||
LONG VARCHAR |
D |
|
|
|
|
|
|
|
|
X |
|
|
|
|
| ||
LONG VARGRAPHIC |
X |
|
|
|
|
|
|
|
|
X |
|
D |
|
|
| ||
NUMERIC |
D |
X |
X |
X |
X |
X |
|
|
|
|
X |
|
|
|
| ||
REAL |
X |
X |
X |
X |
D |
X |
|
|
|
|
X |
|
|
|
| ||
SMALLINT |
X |
X |
D |
X |
X |
X |
|
|
|
|
X |
|
|
|
| ||
TIME |
X |
|
|
|
|
|
|
D |
X |
|
|
|
|
|
| ||
TIMESTAMP |
X |
|
|
|
|
|
X |
X |
D |
|
|
|
|
|
| ||
VARCHAR |
D |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
|
|
|
| ||
VARGRAPHIC |
X |
|
|
|
|
|
|
|
|
|
|
D |
|
|
| ||
| |||||||||||||||||