Host variables are C or C++ language variables that are referenced within SQL statements. They allow an application to pass input data to and receive output data from the database manager. After the application is precompiled, host variables are used by the compiler as any other C/C++ variable. Follow the rules described in the following sections when naming, declaring, and using host variables.
The SQL precompiler identifies host variables by their declared name. The following rules apply:
EXEC SQL BEGIN DECLARE SECTION;
char varsql; /* allowed */
char sqlvar; /* not allowed */
char SQL_VAR; /* not allowed */
EXEC SQL END DECLARE SECTION;
void f1(int i)
{
EXEC SQL BEGIN DECLARE SECTION;
short host_var_1;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT COL1 INTO :host_var_1 from TBL1;
}
void f2(int i)
{
EXEC SQL BEGIN DECLARE SECTION;
short host_var_2;
EXEC SQL END DECLARE SECTION;
EXEC SQL INSERT INTO TBL1 VALUES (:host_var_2);
}
It is also possible to have several local host variables with the same name as long as they all have the same type and size. To do this, declare the first occurrence of the host variable to the precompiler between BEGIN DECLARE SECTION and END DECLARE SECTION statements, and leave subsequent declarations of the variable out of declare sections. The following code shows an example of this:
void f3(int i)
{
EXEC SQL BEGIN DECLARE SECTION;
char host_var_3[25];
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT COL2 INTO :host_var_3 FROM TBL2;
}
void f4(int i)
{
char host_var_3[25];
EXEC SQL INSERT INTO TBL2 VALUES (:host_var_3);
}
Since f3 and f4 are in the same module, and since host_var_3 has the same type and length in both functions, a single declaration to the precompiler is sufficient to use it in both places.
An SQL declare section must be used to identify host variable declarations. This alerts the precompiler to any host variables that can be referenced in subsequent SQL statements.
The C/C++ precompiler only recognizes a subset of valid C or C++ declarations as valid host variable declarations. These declarations define either numeric or character variables. Typedefs for host variable types are not allowed. Host variables can be grouped into a single host structure. For more information on host structures, see Host Structure Support in C and C++. You can declare C++ class data members as host variables. For more information on classes, see Using Class Data Members as Host Variables in C and C++.
A numeric host variable can be used as an input or output variable for any numeric SQL input or output value. A character host variable can be used as an input or output variable for any character, date, time or timestamp SQL input or output value. The application must ensure that output variables are long enough to contain the values that they receive.
For information on declaring host variables for structured types, see Declaring Structured Type Host Variables.
Syntax for Numeric Host Variables in C or C++ shows the syntax for declaring numeric host variables in C or C++.
Syntax for Numeric Host Variables in C or C++
(1)
>>-+----------+---+----------+---+-float-------------------+---->
+-auto-----+ +-const----+ | (2) |
+-extern---+ '-volatile-' +-double------------------+
+-static---+ | (3) |
'-register-' +-short-------+-----+-----+
| '-int-' |
+-+---------------------+-+
| +-sqlint32------------+ |
| | (4) | |
| '-long-------+-----+--' |
| '-int-' |
'-+---------------------+-'
+-sqlint64------------+
+-__int64-------------+
+-long long--+-----+--+
| '-int-' |
| (5) |
'-long-------+-----+--'
'-int-'
.-,-----------------------------------------------------------.
V |
>--------+------------------------------+---varname--+-----------+--+>
| .------------------------. | '-=--value--'
| V | |
'-----+-*-+---+----------+--+--'
'-&-' +-const----+
'-volatile-'
>----;---------------------------------------------------------><
Notes:
Form 1: Syntax for Fixed and Null-terminated Character Host Variables in C/C++ shows the syntax for declaring fixed and null-terminated character host variables in C or C++.
Form 1: Syntax for Fixed and Null-terminated Character Host Variables in C/C++
>>-+----------+---+----------+--+----------+--char-------------->
+-auto-----+ +-const----+ '-unsigned-'
+-extern---+ '-volatile-'
+-static---+
'-register-'
.-,-----------------------------------.
V |
>---------+-| CHAR |-----+---+-----------+--+--;---------------><
'-| C String |-' '-=--value--'
CHAR
(1)
|--+------------------------------+---varname-------------------|
| .------------------------. |
| V | |
'-----+-*-+---+----------+--+--'
'-&-' +-const----+
'-volatile-'
C String
(2)
|---+-varname-------------------------------------------+---[length]------|
'-(--+------------------------------+---varname--)--'
| .------------------------. |
| V | |
'-----+-*-+---+----------+--+--'
'-&-' +-const----+
'-volatile-'
Notes:
Form 2: Syntax for Variable Length Character Host Variables in C/C++ shows the syntax for declaring variable length character host variables in C or C++.
Form 2: Syntax for Variable Length Character Host Variables in C/C++
>>-+----------+---+----------+--struct--+-----+----------------->
+-auto-----+ +-const----+ '-tag-'
+-extern---+ '-volatile-'
+-static---+
'-register-'
(1)
>----{--short--+-----+--var1--;--+----------+--char--var2--[length]--------;--}->
'-int-' '-unsigned-'
.-,-------------------------------------------------------------------------------.
V |
>--------+------------------------------+---varname--+-------------------------------+--+>
| .------------------------. | '-=--{--value-1--,--value-2--}--'
| V | |
'-----+-*-+---+----------+--+--'
'-&-' +-const----+
'-volatile-'
>----;---------------------------------------------------------><
Notes:
Variable Length Character Host Variable Considerations:
Indicator variables should be declared as a short data type.
Graphic host variable declarations can take one of three forms:
For details on using graphic host variables, see Handling Graphic Host Variables in C and C++.
Syntax for Graphic Declaration (Single-Graphic Form and Null-Terminated Graphic Form) shows the syntax for declaring a graphic host variable using the single-graphic form and the null-terminated graphic form.
Syntax for Graphic Declaration (Single-Graphic Form and Null-Terminated Graphic Form)
(1)
>>-+----------+---+----------+---+-----------+------------------>
+-auto-----+ +-const----+ +-sqldbchar-+
+-extern---+ '-volatile-' '-wchar_t---'
+-static---+
'-register-'
.-,-----------------------------------.
V |
>---------+-| CHAR |-----+---+-----------+--+--;---------------><
'-| C String |-' '-=--value--'
CHAR
(2)
|--+------------------------------+---varname-------------------|
| .------------------------. |
| V | |
'-----+-*-+---+----------+--+--'
'-&-' +-const----+
'-volatile-'
C String
(3)
|---+-varname-------------------------------------------+---[length]------|
'-(--+------------------------------+---varname--)--'
| .------------------------. |
| V | |
'-----+-*-+---+----------+--+--'
'-&-' +-const----+
'-volatile-'
Notes:
Graphic Host Variable Considerations:
Syntax for Graphic Declaration (VARGRAPHIC Structured Form) shows the syntax for declaring a graphic host variable using the VARGRAPHIC structured form.
Syntax for Graphic Declaration (VARGRAPHIC Structured Form)
>>-+----------+---+----------+--struct--+-----+----------------->
+-auto-----+ +-const----+ '-tag-'
+-extern---+ '-volatile-'
+-static---+
'-register-'
(1) (2)
>----{--short--+-----+--var-1--;--+-----------+---var-2--[length-----]---;--}->
'-int-' +-sqldbchar-+
'-wchar_t---'
.-,-----------------------------------------------------.
V |
>--------+------------------------------+---| Variable |--;---+-><
| .------------------------. |
| V | |
'-----+-*-+---+----------+--+--'
'-&-' +-const----+
'-volatile-'
Variable
|---variable-name----+-------------------------------+----------|
'-=--{--value-1--,--value-2--}--'
Notes:
Graphic Declaration (VARGRAPHIC Structured Form) Considerations:
Syntax for Large Object (LOB) Host Variables in C/C++ shows the syntax for declaring large object (LOB) host variables in C or C++.
Syntax for Large Object (LOB) Host Variables in C/C++
>>-+----------+---+----------+--SQL TYPE IS----+-BLOB---+------->
+-auto-----+ +-const----+ +-CLOB---+
+-extern---+ '-volatile-' '-DBCLOB-'
+-static---+
'-register-'
(1)
>-----(length-----)--------------------------------------------->
.-,-------------------------------------------------------------------------------------.
V |
>--------+------------------------------+---variable-name--| LOB Data |-----------------------+>
| .------------------------. |
| V | |
'-----+-*-+---+----------+--+--'
'-&-' +-const----+
'-volatile-'
>----;---------------------------------------------------------><
LOB Data
|--+-------------------------------+----------------------------|
+-={init-len,"init-data"}-------+
+-=SQL_BLOB_INIT("init-data")---+
+-=SQL_CLOB_INIT("init-data")---+
'-=SQL_DBCLOB_INIT("init-data")-'
Notes:
LOB Host Variable Considerations:
SQL TYPE IS BLOB my_blob;
| Note: | Wide character literals, for example, L"Hello", should only be used in a precompiled program if the WCHARTYPE CONVERT precompile option is selected. |
BLOB Example:
Declaration:
static Sql Type is Blob(2M) my_blob=SQL_BLOB_INIT("mydata");
Results in the generation of the following structure:
static struct my_blob_t {
sqluint32 length;
char data[2097152];
} my_blob=SQL_BLOB_INIT("mydata");
CLOB Example:
Declaration:
volatile sql type is clob(125m) *var1, var2 = {10, "data5data5"};
Results in the generation of the following structure:
volatile struct var1_t {
sqluint32 length;
char data[131072000];
} * var1, var2 = {10, "data5data5"};
DBCLOB Example:
Declaration:
SQL TYPE IS DBCLOB(30000) my_dbclob1;
Precompiled with the WCHARTYPE NOCONVERT option, results in the generation of the following structure:
struct my_dbclob1_t {
sqluint32 length;
sqldbchar data[30000];
} my_dbclob1;
Declaration:
SQL TYPE IS DBCLOB(30000) my_dbclob2 = SQL_DBCLOB_INIT(L"mydbdata");
Precompiled with the WCHARTYPE CONVERT option, results in the generation of the following structure:
struct my_dbclob2_t {
sqluint32 length;
wchar_t data[30000];
} my_dbclob2 = SQL_DBCLOB_INIT(L"mydbdata");
Syntax for Large Object (LOB) Locator Host Variables in C/C++ shows the syntax for declaring large object (LOB) locator host variables in C or C++.
Syntax for Large Object (LOB) Locator Host Variables in C/C++
>>-+----------+---+----------+--SQL TYPE IS--------------------->
+-auto-----+ +-const----+
+-extern---+ '-volatile-'
+-static---+
'-register-'
.-,---------------.
V |
>-----+-BLOB_LOCATOR---+-------| Variable |--+--;--------------><
+-CLOB_LOCATOR---+
'-DBCLOB_LOCATOR-'
Variable
|-+-----------------------------------------------------------------+-|
| .------------------------. |
| V | |
'-----+-*-+---+----------+--+--variable-name----+--------------+--'
'-&-' +-const----+ '-= init-value-'
'-volatile-'
LOB Locator Host Variable Considerations:
CLOB Locator Example (other LOB locator type declarations are similar):
Declaration:
SQL TYPE IS CLOB_LOCATOR my_locator;
Results in the generation of the following declaration:
sqlint32 my_locator;
Syntax for File Reference Host Variables in C/C++ shows the syntax for declaring file reference host variables in C or C++.
Syntax for File Reference Host Variables in C/C++
>>-+----------+---+----------+--SQL TYPE IS----+-BLOB_FILE---+-->
+-auto-----+ +-const----+ +-CLOB_FILE---+
+-extern---+ '-volatile-' '-DBCLOB_FILE-'
+-static---+
'-register-'
.-,---------------.
V |
>---------| Variable |--+--;-----------------------------------><
Variable
|-+-----------------------------------------------------------------+-|
| .------------------------. |
| V | |
'-----+-*-+---+----------+--+--variable-name----+--------------+--'
'-&-' +-const----+ '-= init-value-'
'-volatile-'
Note:
CLOB File Reference Example (other LOB file reference type declarations are similar):
Declaration:
static volatile SQL TYPE IS BLOB_FILE my_file;
Results in the generation of the following structure:
static volatile struct {
sqluint32 name_length;
sqluint32 data_length;
sqluint32 file_options;
char name[255];
} my_file;
In C++ declare sections, you cannot initialize host variables using parentheses. The following example shows the correct and incorrect methods of initialization in a declare section:
EXEC SQL BEGIN DECLARE SECTION;
short my_short_2 = 5; /* correct */
short my_short_1(5); /* incorrect */
EXEC SQL END DECLARE SECTION;
The C/C++ precompiler cannot directly process any C macro used in a declaration within a declare section. Instead, you must first preprocess the source file with an external C preprocessor. To do this, specify the exact command for invoking a C preprocessor to the precompiler through the PREPROCESSOR option.
When you specify the PREPROCESSOR option, the precompiler first processes all the SQL INCLUDE statements by incorporating the contents of all the files referred to in the SQL INCLUDE statement into the source file. The precompiler then invokes the external C preprocessor using the command you specify with the modified source file as input. The preprocessed file, which the precompiler always expects to have an extension of ".i", is used as the new source file for the rest of the precompiling process.
Any #line macro generated by the precompiler no longer references the original source file, but instead references the preprocessed file. In order to relate any compiler errors back to the original source file, retain comments in the preprocessed file. This helps you to locate various sections of the original source files, including the header files. The option to retain comments is commonly available in C preprocessors, and you can include the option in the command you specify through the PREPROCESSOR option. You should not have the C preprocessor output any #line macros itself, as they may be incorrectly mixed with ones generated by the precompiler.
Notes on Using Macro Expansion:
xlC -P -DMYMACRO=1
xlC -E > x.i
For example, you can use macro expansion in your source code as follows:
#define SIZE 3
EXEC SQL BEGIN DECLARE SECTION;
char a[SIZE+1];
char b[(SIZE+1)*3];
struct
{
short length;
char data[SIZE*6];
} m;
SQL TYPE IS BLOB(SIZE+1) x;
SQL TYPE IS CLOB((SIZE+2)*3) y;
SQL TYPE IS DBCLOB(SIZE*2K) z;
EXEC SQL END DECLARE SECTION;
The previous declarations resolve to the following after you use the PREPROCESSOR option:
EXEC SQL BEGIN DECLARE SECTION;
char a[4];
char b[12];
struct
{
short length;
char data[18];
} m;
SQL TYPE IS BLOB(4) x;
SQL TYPE IS CLOB(15) y;
SQL TYPE IS DBCLOB(6144) z;
EXEC SQL END DECLARE SECTION;
With host structure support, the C/C++ precompiler allows host variables to be grouped into a single host structure. This provides a shorthand for referencing that same set of host variables in an SQL statement. For example, the following host structure can be used to access some of the columns in the STAFF table of the SAMPLE database:
struct tag
{
short id;
struct
{
short length;
char data[10];
} name;
struct
{
short years;
double salary;
} info;
} staff_record;
The fields of a host structure can be any of the valid host variable types. These include all numeric, character, and large object types. Nested host structures are also supported up to 25 levels. In the example above, the field info is a sub-structure, whereas the field name is not, as it represents a VARCHAR field. The same principle applies to LONG VARCHAR, VARGRAPHIC and LONG VARGRAPHIC. Pointer to host structure is also supported.
There are two ways to reference the host variables grouped in a host structure in an SQL statement:
EXEC SQL SELECT id, name, years, salary
INTO :staff_record
FROM staff
WHERE id = 10;
The precompiler converts the reference to staff_record into a list, separated by commas, of all the fields declared within the host structure. Each field is qualified with the host structure names of all levels to prevent naming conflicts with other host variables or fields. This is equivalent to the following method.
EXEC SQL SELECT id, name, years, salary
INTO :staff_record.id, :staff_record.name,
:staff_record.info.years, :staff_record.info.salary
FROM staff
WHERE id = 10;
References to field names must be fully qualified even if there are no other host variables with the same name. Qualified sub-structures can also be referenced. In the example above, :staff_record.info can be used to replace :staff_record.info.years, :staff_record.info.salary.
Since a reference to a host structure (first example) is equivalent to a comma-separated list of its fields, there are instances where this type of reference may lead to an error. For example:
EXEC SQL DELETE FROM :staff_record;
Here, the DELETE statement expects a single character-based host variable. By giving a host structure instead, the statement results in a precompile-time error:
SQL0087N Host variable "staff_record" is a structure used where structure references are not permitted.
Other uses of host structures, which may cause an SQL0087N error to occur, include PREPARE, EXECUTE IMMEDIATE, CALL, indicator variables and SQLDA references. Host structures with exactly one field are permitted in such situations, as are references to individual fields (second example).
An indicator table is a collection of indicator variables to be used with a host structure. It must be declared as an array of short integers. For example:
short ind_tab[10];
The example above declares an indicator table with 10 elements. The following shows the way it can be used in an SQL statement:
EXEC SQL SELECT id, name, years, salary
INTO :staff_record INDICATOR :ind_tab
FROM staff
WHERE id = 10;
The following lists each host structure field with its corresponding indicator variable in the table:
| Note: | An indicator table element, for example ind_tab[1], cannot be referenced individually in an SQL statement. The keyword INDICATOR is optional. The number of structure fields and indicators do not have to match; any extra indicators are unused, and any extra fields do not have indicators assigned to them. |
A scalar indicator variable can also be used in the place of an indicator table to provide an indicator for the first field of the host structure. This is equivalent to having an indicator table with only 1 element. For example:
short scalar_ind;
EXEC SQL SELECT id, name, years, salary
INTO :staff_record INDICATOR :scalar_ind
FROM staff
WHERE id = 10;
If an indicator table is specified along with a host variable instead of a host structure, only the first element of the indicator table, for example ind_tab[0], will be used:
EXEC SQL SELECT id
INTO :staff_record.id INDICATOR :ind_tab
FROM staff
WHERE id = 10;
If an array of short integers is declared within a host structure:
struct tag
{
short i[2];
} test_record;
The array will be expanded into its elements when test_record is referenced in an SQL statement making :test_record equivalent to :test_record.i[0], :test_record.i[1].
C/C++ null-terminated strings have their own SQLTYPE (460/461 for character and 468/469 for graphic).
C/C++ null-terminated strings are handled differently depending on the value of the LANGLEVEL precompiler option. If a host variable of one of these SQLTYPEs and declared length n is specified within an SQL statement, and the number of bytes (for character types) or double-byte characters (for graphic types) of data is k, then:
When specified in any other SQL context, a host variable of SQLTYPE 460 with length n is treated as a VARCHAR data type with length n as defined above. When specified in any other SQL context, a host variable of SQLTYPE 468 with length n is treated as a VARGRAPHIC data type with length n as defined above.
Host variables may be declared as pointers to specific data types with the following restrictions:
char mystring[20];
char (*mystring)[20];
EXEC SQL BEGIN DECLARE SECTION;
char (*arr)[10]; /* correct */
char *(arr); /* incorrect */
char *arr[10]; /* incorrect */
EXEC SQL END DECLARE SECTION;
The first declaration is a pointer to a 10-byte character array. This is a valid host variable. The second is an invalid declaration. The parentheses are not allowed in a pointer to a character. The third declaration is an array of pointers. This is not a supported data type.
The host variable declaration:
char *ptr
is accepted, but it does not mean null-terminated character string of undetermined length. Instead, it means a pointer to a fixed-length, single character host variable. This may not be what is intended. To define a pointer host variable that can indicate different character strings, use the first declaration form above.
EXEC SQL BEGIN DECLARE SECTION;
char (*mychar)[20]; /* Pointer to character array of 20 bytes */
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT column INTO :*mychar FROM table; /* Correct */
You can declare class data members as host variables (but not classes or objects themselves). The following example illustrates the method to use:
class STAFF
{
private:
EXEC SQL BEGIN DECLARE SECTION;
char staff_name[20];
short int staff_id;
double staff_salary;
EXEC SQL END DECLARE SECTION;
short staff_in_db;
.
.
};
Data members are only directly accessible in SQL statements through the implicit this pointer provided by the C++ compiler in class member functions. You cannot explicitly qualify an object instance (such as SELECT name INTO :my_obj.staff_name ...) in an SQL statement.
If you directly refer to class data members in SQL statements, the database manager resolves the reference using the this pointer. For this reason, you should leave the optimization level precompile option (OPTLEVEL) at the default setting of 0 (no optimization). This means that no SQLDA optimization will be done by the database manager. (This is true whenever pointer host variables are involved in SQL statements.)
The following example shows how you might directly use class data members which you have declared as host variables in an SQL statement.
class STAFF
{
·
·
·
public:
·
·
·
short int hire( void )
{
EXEC SQL INSERT INTO staff ( name,id,salary )
VALUES ( :staff_name, :staff_id, :staff_salary );
staff_in_db = (sqlca.sqlcode == 0);
return sqlca.sqlcode;
}
};
In this example, class data members staff_name, staff_id, and staff_salary, are used directly in the INSERT statement. Because they have been declared as host variables (see the example in Example of Declaring Class Data Members as Host Variables), they are implicitly qualified to the current object with the this pointer. In SQL statements, you can also refer to data members that are not accessible through the this pointer. You do this by referring to them indirectly using pointer or reference host variables.
The following example shows a new method, asWellPaidAs that takes a second object, otherGuy. This method references its members indirectly through a local pointer or reference host variable, as you cannot reference its members directly within the SQL statement.
short int STAFF::asWellPaidAs( STAFF otherGuy )
{
EXEC SQL BEGIN DECLARE SECTION;
short &otherID = otherGuy.staff_id
double otherSalary;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT SALARY INTO :otherSalary
FROM STAFF WHERE id = :otherID;
if( sqlca.sqlcode == 0 )
return staff_salary >= otherSalary;
else
return 0;
}
You cannot use the C++ scope resolution operator '::', nor the C/C++ member operators '.' or '->' in embedded SQL statements. You can easily accomplish the same thing through use of local pointer or reference variables, which are set outside the SQL statement to point to the desired scoped variable, and then used inside the SQL statement to refer to it. The following example shows the correct method to use:
EXEC SQL BEGIN DECLARE SECTION;
char (& localName)[20] = ::name;
EXEC SQL END DECLARE SECTION;
EXEC SQL
SELECT name INTO :localName FROM STAFF
WHERE name = 'Sanders';
To handle graphic data in C or C++ applications, use host variables based on either the wchar_t C/C++ data type or the sqldbchar data type provided by DB2. You can assign these types of host variables to columns of a table that are GRAPHIC, VARGRAPHIC, or DBCLOB. For example, you can update or select DBCS data from GRAPHIC or VARGRAPHIC columns of a table.
There are three valid forms for a graphic host variable:
Single-graphic host variables have an SQLTYPE of 468/469 that is equivalent to GRAPHIC(1) SQL data type. (See Syntax for Graphic Declaration (Single-Graphic Form and Null-Terminated Graphic Form).)
Null-terminated refers to the situation where all the bytes of the last character of the graphic string contain binary zeros ('\0's). They have an SQLTYPE of 400/401. (See Syntax for Graphic Declaration (Single-Graphic Form and Null-Terminated Graphic Form).)
VARGRAPHIC structured host variables have an SQLTYPE of 464/465 if their length is between 1 and 16 336 bytes. They have an SQLTYPE of 472/473 if their length is between 2000 and 16 350 bytes. (See Syntax for Graphic Declaration (VARGRAPHIC Structured Form).)
Some character encoding schemes, particularly those from east Asian countries require multiple bytes to represent a character. This external representation of data is called the multi-byte character code representation of a character and includes double-byte characters (characters represented by two bytes). Graphic data in DB2 consists of double-byte characters.
To manipulate character strings with double-byte characters, it may be convenient for an application to use an internal representation of data. This internal representation is called the wide-character code representation of the double-byte characters and is the format customarily used in the wchar_t C/C++ data type. Subroutines that conform to ANSI C and X/OPEN Portability Guide 4 (XPG4) are available to process wide-character data and to convert data in wide-character format to and from multi-byte format.
Note that although an application can process character data in either multi-byte format or wide-character format, interaction with the database manager is done with DBCS (multi-byte) character codes only. That is, data is stored in and retrieved from GRAPHIC columns in DBCS format. The WCHARTYPE precompiler option is provided to allow application data in wide-character format to be converted to/from multi-byte format when it is exchanged with the database engine.
While the size and encoding of DB2 graphic data is constant from one platform to another for a particular code page, the size and internal format of the ANSI C or C++ wchar_t data type depends on which compiler you use and which platform you are on. The sqldbchar data type, however, is defined by DB2 to be two bytes in size, and is intended to be a portable way of manipulating DBCS and UCS-2 data in the same format in which it is stored in the database. For more information on UCS-2 data, see Japanese and Traditional Chinese EUC and UCS-2 Code Set Considerations and refer to the Administration Guide.
You can define all DB2 C graphic host variable types using either wchar_t or sqldbchar. You must use wchar_t if you build your application using the WCHARTYPE CONVERT precompile option (as described in The WCHARTYPE Precompiler Option in C and C++).
| Note: | When specifying the WCHARTYPE CONVERT option on a Windows platform, you should note that wchar_t on Windows platforms is Unicode. Therefore, if your C/C++ compiler's wchar_t is not Unicode, the wcstombs() function call may fail with SQLCODE -1421 (SQLSTATE=22504). If this happens, you can specify the WCHARTYPE NOCONVERT option, and explicitly call the wcstombs() and mbstowcs() functions from within your program. |
If you build your application with the WCHARTYPE NOCONVERT precompile option, you should use sqldbchar for maximum portability between different DB2 client and server platforms. You may use wchar_t with WCHARTYPE NOCONVERT, but only on platforms where wchar_t is defined as two bytes in length.
If you incorrectly use either wchar_t or sqldbchar in host variable declarations, you will receive an SQLCODE 15 (no SQLSTATE) at precompile time.
Using the WCHARTYPE precompiler option, you can specify which graphic character format you want to use in your C/C++ application. This option provides you with the flexibility to choose between having your graphic data in multi-byte format or in wide-character format. There are two possible values for the WCHARTYPE option:
The advantage to using WCHARTYPE CONVERT is that it allows your application to fully exploit the ANSI C mechanisms for dealing with wide-character strings (L-literals, 'wc' string functions, etc.) without having to explicitly convert the data to multi-byte format before communicating with the database manager. The disadvantage is that the implicit conversions may have an impact on the performance of your application at run time, and may increase memory requirements.
If you select WCHARTYPE CONVERT, declare all graphic host variables using wchar_t instead of sqldbchar.
If you want WCHARTYPE CONVERT behavior, but your application does not need to be precompiled (for example, a CLI application), then define the C preprocessor macro SQL_WCHART_CONVERT at compile time. This ensures that certain definitions in the DB2 header files use the data type wchar_t instead of sqldbchar.
| Note: | The WCHARTYPE CONVERT precompile option is not currently supported in programs running on the DB2 Windows 3.1 client. For those programs, use the default (WCHARTYPE NOCONVERT). |
If you select WCHARTYPE NOCONVERT, declare all graphic host variables using the sqldbchar type for maximum portability to other DB2 client/server platforms.
Refer to the Command Reference for more information.
Other guidelines you need to observe are:
Notes:
If your application code page is Japanese or Traditional Chinese EUC, or if your application connects to a UCS-2 database, you can access GRAPHIC columns at a database server by using either the CONVERT or the NOCONVERT option, and wchar_t or sqldbchar graphic host variables, or input/output SQLDAs. In this section, DBCS format refers to the UCS-2 encoding scheme for EUC data. Consider the following cases:
The DB2 client converts graphic data from the wide character format to your application code page, then to UCS-2 before sending the input SQLDA to the database server. Any graphic data is sent to the database server tagged with the UCS-2 code page identifier. Mixed character data is tagged with the application code page identifier. When graphic data is retrieved from a database by a client, it is tagged with the UCS-2 code page identifier. The DB2 client converts the data from UCS-2 to the client application code page, then to the wide character format. If an input SQLDA is used instead of a host variable, then you are required to ensure that graphic data is encoded using the wide character format. This data will be converted to UCS-2 and then sent to the database server. These conversions will impact performance.
The graphic data is assumed by DB2 to be encoded using UCS-2 and is tagged with the UCS-2 code page, and no conversions are done. DB2 assumes that the graphic host variable is being used simply as a bucket. When the NOCONVERT option is chosen, graphic data retrieved from the database server is passed to the application encoded using UCS-2. Any conversions from the application code page to UCS-2 and from UCS-2 to the application code page are your responsibility. Data tagged as UCS-2 is sent to the database server without any conversions or alterations.
To minimize conversions you can either use the NOCONVERT option and handle the conversions in your application, or not use GRAPHIC columns. For the client environments where wchar_t encoding is in two-byte Unicode, for example Windows NT or AIX version 4.3 and higher, you can use the NOCONVERT option and work directly with UCS-2. In such cases, your application should handle the difference between big-endian and little-endian architectures. With NOCONVERT option, DB2 Universal Database uses sqldbchar which is always two-byte big-endian.
Do not assign IBM-eucJP/IBM-eucTW CS0 (7-bit ASCII) and IBM-eucJP CS2 (Katakana) data to graphic host variables either after conversion to UCS-2 (if NOCONVERT is specified) or by conversion to the wide character format (if CONVERT is specified). This is because characters in both of these EUC code sets become single-byte when converted from UCS-2 to PC DBCS.
In general, although eucJP and eucTW store GRAPHIC data as UCS-2, the GRAPHIC data in these database is still non-ASCII eucJP or eucTW data. Specifically, any space padded to such GRAPHIC data is DBCS space (also known as ideographic space in UCS-2, U+3000). For a UCS-2 database, however, GRAPHIC data can contain any UCS-2 character, and space padding is done with UCS-2 space, U+0020. Keep this difference in mind when you code applications to retrieve UCS-2 data from a UCS-2 database versus UCS-2 data from eucJP and eucTW databases.
For general EUC application development guidelines, see Japanese and Traditional Chinese EUC and UCS-2 Code Set Considerations.