|The following is a new section for this chapter.
|There are two main areas of support for DB2 CLI Unicode Applications: |
|The following is a list of the ODBC API functions that support both Unicode |(W) and ANSI (A) versions (the function name will have a W for Unicode):
|SQLBrowseConnect SQLForeignKeys SQLPrimaryKeys |SQLColAttribute SQLGetConnectAttr SQLProcedureColumns |SQLColAttributes SQLGetConnectOption SQLProcedures |SQLColumnPrivileges SQLGetCursorName SQLSetConnectAttr |SQLColumns SQLGetDescField SQLSetConnectOption |SQLConnect SQLGetDescRec SQLSetCursorName |SQLDataSources SQLGetDiagField SQLSetDescField |SQLDescribeCol SQLGetDiagRec SQLSetStmtAttr |SQLDriverConnect SQLGetInfo SQLSpecialColumns |SQLDrivers SQLGetStmtAttr SQLStatistics |SQLError SQLNativeSQL SQLTablePrivileges |SQLExecDirect SQLPrepare SQLTables
|Unicode functions that always return, or take, string length arguments |are passed as count-of-characters. For functions that return length information for server data, |the display size and precision are described in number of characters. When the length (transfer size of the data) could refer to string |or nonstring data, the length is described in octet lengths. For example, |SQLGetInfoW will still take the length as count-of-bytes, but SQLExecDirectW |will use count-of-characters. CLI will return result sets in either Unicode |or ANSI, depending on the application's binding. If an application binds to |SQL_C_CHAR, the driver will convert SQL_WCHAR data to SQL_CHAR. The driver |manager maps SQL_C_WCHAR to SQL_C_CHAR for ANSI drivers but does no mapping |for Unicode drivers.
|There are two new CLI or ODBC defined data types, SQL_C_WCHAR and SQL_WCHAR. |SQL_C_WCHAR indicates that the C buffer contains UCS-2 data. SQL_WCHAR indicates |that a particular column or parameter marker contains Unicode data. For DB2 |Unicode Servers, graphic columns will be described as SQL_WCHAR. Conversion |will be allowed between SQL_C_WCHAR and SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR |and SQL_CLOB, as well as between the graphic data types.
|
|Table 32. Supported Data Conversions
SQL Data Type |
S Q L _ C _ C H A R |
S Q L _ C _ W 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 _ 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 |
S Q L _ C _ B I G I N T |
S Q L _ C _ N U M E R I C |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
BLOB |
X |
X |
|
|
|
|
|
|
|
|
D |
|
|
|
X | |||
CHAR |
D |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
|
|
|
X |
X | |
CLOB |
D |
X |
|
|
|
|
|
|
|
|
X |
|
|
X |
| |||
DATE |
X |
X |
|
|
|
|
|
D |
|
X |
|
|
|
|
| |||
DBCLOB |
|
X |
|
|
|
|
|
|
|
|
X |
|
D |
|
|
X | ||
DECIMAL |
D |
X |
X |
X |
X |
X |
X |
|
|
|
X |
X |
|
|
|
X |
X | |
DOUBLE |
X |
X |
X |
X |
X |
X |
D |
|
|
|
|
X |
|
|
|
X |
X | |
FLOAT |
X |
X |
X |
X |
X |
X |
D |
|
|
|
|
X |
|
|
|
X |
X | |
GRAPHIC (Non-Unicode) |
X |
X |
|
|
|
|
|
|
|
|
|
|
D |
|
| |||
GRAPHIC (Unicode) |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
D |
|
|
|
X |
|
INTEGER |
X |
X |
D |
X |
X |
X |
X |
|
|
|
|
X |
|
|
|
X |
X | |
LONG VARCHAR |
D |
X |
|
|
|
|
|
|
|
|
X |
|
|
|
| |||
LONG VARGRAPHIC (Non-Unicode) |
X |
X |
|
|
|
|
|
|
|
|
X |
|
D |
|
| |||
LONG VARGRAPHIC (Unicode) |
X |
X |
|
|
|
|
|
|
|
|
X |
|
D |
|
|
|
|
|
NUMERIC |
D |
X |
X |
X |
X |
X |
X |
|
|
|
|
X |
|
|
|
X | ||
REAL |
X |
X |
X |
X |
X |
D |
X |
|
|
|
|
X |
|
|
|
X | ||
SMALLINT |
X |
X |
X |
D |
X |
X |
X |
|
|
|
|
X |
|
|
|
X |
X | |
BIGINT |
X |
X |
X |
X |
X |
X |
X |
|
|
|
X |
X |
|
|
|
D |
X | |
TIME |
X |
X |
|
|
|
|
|
|
D |
X |
|
|
|
|
| |||
TIMESTAMP |
X |
X |
|
|
|
|
|
X |
X |
D |
|
|
|
|
| |||
VARCHAR |
D |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
|
|
|
X |
X | |
VARGRAPHIC (Non-Unicode) |
X |
X |
|
|
|
|
|
|
|
|
|
|
D |
|
| |||
VARGRAPHIC (Unicode) |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
D |
|
|
|
X |
|
|
|
|Before Unicode applications were supported, applications that were written |to work with single byte character data could be made to work with double |byte graphic data by a series of cli ini file keywords, such as |GRAPHIC=1,2 or 3, Patch2=7 etc. These workarounds presented graphic data |as character data, and also affected the reported length of the data.
|These keywords are no longer required for Unicode applications, and in |fact should not be used otherwise there could be serious side effects. If |it is not known if a particular application is a Unicode application, we |suggest you try without any of the keywords that affect the handling of graphic |data.
|In Non-Unicode databases, data in LONG VARGRAPHIC and LONG VARCHAR columns |cannot be compared. Data in GRAPHIC/VARGRAPHIC and CHAR/VARCHAR columns can |only be compared, or assigned to each other, using explicit cast |functions since no implicit code page conversion is supported. This includes |GRAPHIC/VARGRAPHIC and CHAR/VARCHAR literals where a GRAPHIC/VARGRAPHIC literal |is differentiated from a CHAR/VARCHAR literal by a G prefix.
|For Unicode databases, casting between GRAPHIC/VARGRAPHIC and CHAR/VARCHAR |literals is not required. Also, a G prefix is not required in front of a GRAPHIC/VARGRAPHIC |literal. Provided at least one of the arguments is a literal, implicit conversions |occur. This allows literals with or without the G prefix to be used within |statements that use either SQLPrepareW() or SQLExecDirect(). Literals for |LONG VARGRAPHICs still must have a G prefix.
|For more information, please see "Casting Between Data |Types" in Chapter 3 Language Elements of the SQL Reference.
|The following three keywords have been added to avoid any extra overhead |when Unicode applications connect to a database. |
|With Unicode support enabled, and when called by a Unicode application, |CLI will attempt to connect to the database using the best client codepage |possible to ensure there is no unnecessary data loss due to codepage conversion. |This may increase the connection time as codepages are exchanged, or may |cause codepage conversions on the client that did not occur before this support |was added.
|Setting this keyword to True will cause all Unicode data to |be converted to the application's local codepage first, before the data is |sent to the server. This can cause data loss for any data that cannot be represented |in the local codepage.
|
|Non-Unicode applications always connect to the database using the application's |local codepage, or the DB2Codepage environment setting. By default, CLI will |ensure that Unicode applications will connect to Unicode databases using UTF-8 |and UCS-2 codepages, and will connect to non-Unicode databases using the database's |codepage. This ensures there is no unnecessary data loss due to codepage |conversion.
|This keyword allows the user to specify the database's codepage |when connecting to a non-Unicode database in order to avoid any extra overhead |on the connection.
|Specify a value of 1 to cause SQLDriverConnect() |to return the correct value in the output connection string, so the value |can be used on future SQLDriverConnect() calls.
|
|This keyword is equivalent to ConnectCodepage=1208, and is added only |for convenience. Set this keyword to avoid extra connect overhead when connecting |to DB2 for OS/390 Version 7 or higher. There is no need to set this keyword |for DB2 for Windows, DB2 for Unix or DB2 for OS/2 databases, since there is |no extra processing required. |
|The following is a new section added to this appendix.
|A Unicode ODBC application sends and retrieves character data primarily |in UCS-2. It does this by calling Unicode versions of the ODBC functions |('W' suffix) and by indicating Unicode data types. The application does not |explicitly specify a local code page. The application can still call the |ANSI functions and pass local code page strings.
|For example, the application may call SQLConnectW() and pass the DSN, |User ID and Password as Unicode arguments. It may then call SQLExecDirectW() |and pass in a Unicode SQL statement string, and then bind a combination of |ANSI local code page buffers (SQL_C_CHAR) and Unicode buffers (SQL_C_WCHAR). |The database data types may be local code page or UCS-2 and UTF-8.
|If a CLI application calls SQLConnectW or calls SQLSetConnectAttr with |SQL_ATTR_ANSI_APP set to SQL_AA_FALSE, the application is considered a Unicode |application. This means all CHAR data is sent and received from the database |in UTF-8 format. The application can then fetch CHAR data into SQL_C_CHAR |buffers in local code page (with possible data loss), or into SQL_C_WCHAR |buffers in UCS-2 without any data loss.
|If the application does not do either of the two calls above, CHAR data |is converted to the applications local codepage at the server. This means |CHAR data fetched into SQL_C_WCHAR may suffer data loss.
|If the DB2CODEPAGE instance variable is set (using db2set) to code page |1208 (UTF-8), the application will receive all CHAR data in UTF-8 since this |is now the local code page. The application must also ensure that all CHAR |input data is also in UTF-8. ODBC also assumes that all SQL_C_WCHAR data is |in the native endian format. CLI will perform any required byte-reversal |for SQL_C_WCHAR.
|This release of DB2 Universal Database contains the SQLConnectW() API. |A Unicode driver must export SQLConnectW in order to be recognized as a Unicode |driver by the driver manager. It is important to note that many ODBC applications |(such as Microsoft Access and Visual Basic) call SQLConnectW(). In previous |releases of DB2 Universal Database, DB2 CLI has not supported this API, and |thus was not recognized as a Unicode driver by the ODBC driver manager. |This caused the ODBC driver manager to convert all Unicode data to the application's |local code page. With the added support of the SQLConnectW() function, these |applications will now connect as Unicode applications and DB2 CLI will take |care of all required data conversion.
|DB2 CLI now accepts Unicode APIs (with a suffix of "W") and regular ANSI |APIs. ODBC defines a set of functions with a suffix of "A", but the driver |manager does not pass ANSI functions with the "A" suffix to the driver. Instead, |it converts these functions to ANSI function calls without the suffix, and |then passes them to the driver.
|An ODBC application that calls the SQLConnectW() API is considered a Unicode |application. Since the ODBC driver manager will always call the SQLConnectW() |API regardless of what version the application called, ODBC introduced the |SQL_ATTR_ANSI_APP connect attribute to notify the driver if the application |should be considered an ANSI or UNICODE application. If SQL_ATTR_ANSI_APP |is not set to SQL_AA_FALSE, DB2 CLI converts all Unicode data to the local |code page before sending it to the server.