7 Unicode Support
This chapter provides information about the Unicode support in the Oracle Database ODBC driver.
Topics:
7.1 Unicode Support within the ODBC Environment
The Microsoft or unixODBC ODBC Driver Manager (Driver Manager) makes all ODBC drivers, regardless of if they support Unicode, appear as if they are Unicode compliant. This allows ODBC applications to be written independent of the Unicode capabilities of underlying ODBC drivers.
The extent to which the Driver Manager can emulate Unicode support for ANSI ODBC drivers is limited by the conversions possible between the Unicode data and the local code page. Data loss is possible when the Driver Manager is converting from Unicode to the local code page. Full Unicode support is not possible unless the underlying ODBC driver supports Unicode. the Oracle Database ODBC driver provides full Unicode support.
Parent topic: Unicode Support
7.2 Unicode Support in ODBC API
The ODBC API supports both Unicode and ANSI entry points using the "W" and "A" suffix convention. An ODBC application developer need not explicitly call entry points with the suffix. An ODBC application that is compiled with the UNICODE and _UNICODE preprocessor definitions generates the appropriate calls. For example, a call to SQLPrepare
is compiled as SQLPrepareW
.
The C data type, SQL_C_WCHAR
, was added to the ODBC interface to allow applications to specify that an input parameter is encoded as Unicode or to request column data returned as Unicode. The macro SQL_C_TCHAR
is useful for applications that must be built as both Unicode and ANSI. The SQL_C_TCHAR
macro compiles as SQL_C_WCHAR
for Unicode applications and as SQL_C_CHAR
for ANSI applications.
The SQL data types: SQL_WCHAR
, SQL_WVARCHAR
, and
SQL_WLONGVARCHAR
have been added to the ODBC interface to represent
columns defined in a table as Unicode. Potentially, these values are returned from calls
to SQLDescribeCol
, SQLColAttribute
,
SQLColumns
, and SQLProcedureColumns
.
Unicode encoding is supported for SQL column types NCHAR
,
NVARCHAR2
, and NCLOB
. Additionally, Unicode
encoding is supported for SQL column types CHAR
and
VARCHAR2
if the character semantics are specified in the column
definition.
The ODBC driver supports these SQL column types and maps them to ODBC SQL data types.
The following table lists the supported SQL data types and the equivalent ODBC SQL data type.
Table 7-1 Supported SQL Data Types and the Equivalent ODBC SQL Data Type
SQL Data Types | ODBC SQL Data Types |
---|---|
|
|
|
|
|
|
|
|
|
|
Footnote 1
CHAR
maps to SQL_WCHAR
if the character semantics were specified in the column definition and if the character set for the database is Unicode.
Footnote 2
VARCHAR2
maps to SQL_WVARCHAR
if the character semantics were specified in the column definition and if the character set for the database is Unicode.
Parent topic: Unicode Support
7.3 Unicode Functions in the Driver Manager
The Driver Manager (DM) performs the following functions when it detects that the underlying ODBC driver does not support Unicode:
-
The DM converts Unicode function calls to ANSI function calls before calling the ANSI ODBC driver. String arguments are converted from Unicode to the local code page. For example, a call to
SQLPrepareW
is converted to callSQLPrepare
. The text of the SQL statement parameter is converted from Unicode to the local code page. -
The DM converts return parameters that are character data from the local code page to Unicode. For example, returning the column name through
SQLColAttribute
. -
The DM converts data from the local code page to Unicode for columns bound as
SQL_C_WCHAR
. -
The DM converts data from Unicode to the local code page for input parameters bound as
SQL_C_WCHAR
.
Parent topic: Unicode Support
7.4 SQLGetData Performance
The SQLGetData
function allows an ODBC application to specify the
data type to receive a column as after the data has been fetched. OCI requires the
Oracle Database ODBC driver to specify the data type before it is fetched. In this case,
the Oracle Database ODBC driver uses the knowledge it has about the data type of the
column as defined in the database to determine how to best default to fetching the
column through OCI.
If a column that contains character data is not bound by
SQLBindCol
, the Oracle Database ODBC driver must determine if it must
fetch the column as Unicode or as the local code page. The driver could default to
receiving the column as Unicode, however, this may result in as many as two unnecessary
conversions. For example, if the data were encoded in the database as ANSI, there would
be an ANSI to Unicode conversion to fetch the data into the Oracle Database ODBC driver.
If the ODBC application then requested the data as SQL_C_CHAR
, there
would be an additional conversion to revert the data back to its original encoding.
The default encoding of the Oracle client is used when fetching data. However, an ODBC application can overwrite this default and fetch the data as Unicode by binding the column or the parameter as the WCHAR
data type.
Parent topic: Unicode Support
7.5 Unicode Samples
As the Oracle Database ODBC driver itself was implemented using TCHAR macros, Oracle recommends that ODBC application programs use TCHAR to take advantage of the driver.
The following links are program examples showing how to use TCHAR, which becomes the WCHAR
data type in case you compile with UNICODE
and _UNICODE
.
Example 1: Connection to Database
No difference other than specifying Unicode literals for SQLConnect
.
SQLHENV envHnd; SQLHDBC conHnd; SQLHSTMT stmtHnd; RETCODE rc; rc = SQL_SUCCESS; // ENV is allocated rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &envHnd); // Connection Handle is allocated rc = SQLAllocHandle(SQL_HANDLE_DBC, envHnd, &conHnd); rc = SQLConnect(conHnd, _T("stpc19"), SQL_NTS, _T("scott"), SQL_NTS, _T("tiger"), SQL_NTS); . . . if (conHnd) { SQLDisconnect(conHnd); SQLFreeHandle(SQL_HANDLE_DBC, conHnd); } if (envHnd) SQLFreeHandle(SQL_HANDLE_ENV, envHnd);
Example 2: Simple Retrieval
The following example retrieves the employee names and the job titles from the EMP
table. With the exception that you must specify TCHAR compliant data to every ODBC function, there is no difference to the ANSI case. If the case is a Unicode application, you have to specify the length of the buffer to the BYTE
length when you call SQLBindCol
(for example, sizeof(ename)
).
/* ** Execute SQL, bind columns, and Fetch. ** Procedure: ** ** SQLExecDirect ** SQLBindCol ** SQLFetch ** */ static SQLTCHAR *sqlStmt = _T("SELECT ename, job FROM emp"); SQLTCHAR ename[50]; SQLTCHAR job[50]; SQLINTEGER enamelen, joblen; _tprintf(_T("Retrieve ENAME and JOB using SQLBindCol 1.../n[%s]/n"), sqlStmt); /* Step 1: Prepare and Execute */ rc = SQLExecDirect(stmtHnd, sqlStmt, SQL_NTS); /* select */ checkSQLErr(envHnd, conHnd, stmtHnd, rc); /* Step 2: Bind Columns */ rc = SQLBindCol(stmtHnd, 1, SQL_C_TCHAR, ename, sizeof(ename), &enamelen); checkSQLErr(envHnd, conHnd, stmtHnd, rc); rc = SQLBindCol(stmtHnd, 2, SQL_C_TCHAR, job, sizeof(job), &joblen); checkSQLErr(envHnd, conHnd, stmtHnd, rc); do { /* Step 3: Fetch Data */ rc = SQLFetch(stmtHnd); if (rc == SQL_NO_DATA) break; checkSQLErr(envHnd, conHnd, stmtHnd, rc); _tprintf(_T("ENAME = %s, JOB = %s/n"), ename, job); } while (1); _tprintf(_T("Finished Retrieval/n/n"));
Example 3: Retrieval Using SQLGetData (Binding After Fetch)
This example shows how to use SQLGetData
. For those who are not familiar with ODBC programming, the fetch is allowed before binding the data using SQLGetData
, unlike in an OCI program. There is no difference to the ANSI application in terms of Unicode-specific issues.
/* ** Execute SQL, bind columns, and Fetch. ** Procedure: ** ** SQLExecDirect ** SQLFetch ** SQLGetData */ static SQLTCHAR *sqlStmt = _T("SELECT ename,job FROM emp"); // same as Case 1. SQLTCHAR ename[50]; SQLTCHAR job[50]; _tprintf(_T("Retrieve ENAME and JOB using SQLGetData.../n[%s]/n"), sqlStmt); if (rc != SQL_SUCCESS) { _tprintf(_T("Failed to allocate STMT/n")); goto exit2; } /* Step 1: Prepare and Execute */ rc = SQLExecDirect(stmtHnd, sqlStmt, SQL_NTS); // select checkSQLErr(envHnd, conHnd, stmtHnd, rc); do { /* Step 2: Fetch */ rc = SQLFetch(stmtHnd); if (rc == SQL_NO_DATA) break; checkSQLErr(envHnd, conHnd, stmtHnd, rc); /* Step 3: GetData */ rc = SQLGetData(stmtHnd, 1, SQL_C_TCHAR, (SQLPOINTER)ename, sizeof(ename), NULL); checkSQLErr(envHnd, conHnd, stmtHnd, rc); rc = SQLGetData(stmtHnd, 2, SQL_C_TCHAR, (SQLPOINTER)job, sizeof(job), NULL); checkSQLErr(envHnd, conHnd, stmtHnd, rc); _tprintf(_T("ENAME = %s, JOB = %s/n"), ename, job); } while (1); _tprintf(_T("Finished Retrieval/n/n"));
Example 4: Simple Update
This example shows how to update data. Likewise, the length of data for SQLBindParameter
has to be specified with the BYTE
length, even in the case of a Unicode application.
/ * ** Execute SQL, bind columns, and Fetch. ** Procedure: ** ** SQLPrepare ** SQLBindParameter ** SQLExecute */ static SQLTCHAR *sqlStmt = _T("INSERT INTO emp(empno,ename,job) VALUES(?,?,?)"); static SQLTCHAR *empno = _T("9876"); // Emp No static SQLTCHAR *ename = _T("ORACLE"); // Name static SQLTCHAR *job = _T("PRESIDENT"); // Job _tprintf(_T("Insert User ORACLE using SQLBindParameter.../n[%s]/n"), sqlStmt); /* Step 1: Prepare */ rc = SQLPrepare(stmtHnd, sqlStmt, SQL_NTS); checkSQLErr(envHnd, conHnd, stmtHnd, rc); /* Step 2: Bind Parameter */ rc = SQLBindParameter(stmtHnd, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_DECIMAL,4, 0, (SQLPOINTER)empno, 0, NULL); checkSQLErr(envHnd, conHnd, stmtHnd, rc); rc = SQLBindParameter(stmtHnd, 2, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_CHAR, lstrlen(ename)*sizeof(TCHAR), 0, (SQLPOINTER)ename, lstrlen(ename)*sizeof(TCHAR), NULL); checkSQLErr(envHnd, conHnd, stmtHnd, rc); rc = SQLBindParameter(stmtHnd, 3, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_CHAR, lstrlen(job)*sizeof(TCHAR), 0, (SQLPOINTER)job, lstrlen(job)*sizeof(TCHAR), NULL); checkSQLErr(envHnd, conHnd, stmtHnd, rc); /* Step 3: Execute */ rc = SQLExecute(stmtHnd); checkSQLErr(envHnd, conHnd, stmtHnd, rc);
Example 5: Update and Retrieval for Long Data (CLOB)
This example may be the most complicated case to update and retrieve data for long data, like CLOB
, in Oracle. Because the length of data must be the BYTE
length, lstrlen(TCHAR data)*sizeof(TCHAR)
is needed to derive the BYTE
length.
/* ** Execute SQL, bind columns, and Fetch. ** Procedure: ** ** SQLPrepare ** SQLBindParameter ** SQLExecute ** SQLParamData ** SQLPutData ** ** SQLExecDirect ** SQLFetch ** SQLGetData */ static SQLTCHAR *sqlStmt1 = _T("INSERT INTO clobtbl(clob1) VALUES(?)"); static SQLTCHAR *sqlStmt2 = _T("SELECT clob1 FROM clobtbl"); SQLTCHAR clobdata[1001]; SQLTCHAR resultdata[1001]; SQLINTEGER ind = SQL_DATA_AT_EXEC; SQLTCHAR *bufp; SQLTCHAR ch; int clobdatalen, chunksize, dtsize, retchklen, i, len; _tprintf(_T("Insert CLOB1 using SQLPutData.../n[%s]/n"), sqlStmt1); /* Set CLOB Data * for (i=0, ch=_T('A'); i< sizeof(clobdata)/sizeof(SQLTCHAR); ++i, ++ch) { if (ch > _T('Z')) ch = _T('A'); clobdata[i] = ch; } clobdata[sizeof(clobdata)/sizeof(SQLTCHAR)-1] = _T('/0'); clobdatalen = lstrlen(clobdata); chunksize = clobdatalen / 7; /* Step 1: Prepare */ rc = SQLPrepare(stmtHnd, sqlStmt1, SQL_NTS); checkSQLErr(envHnd, conHnd, stmtHnd, rc); /* Step 2: Bind Parameter with SQL_DATA_AT_EXEC */ rc = SQLBindParameter(stmtHnd, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_LONGVARCHAR, clobdatalen*sizeof(TCHAR), 0, (SQLPOINTER)clobdata, clobdatalen*sizeof(TCHAR), &ind); checkSQLErr(envHnd, conHnd, stmtHnd, rc); /* Step 3: Execute */ rc = SQLExecute(stmtHnd); checkSQLErr(envHnd, conHnd, stmtHnd, rc); sdhamoth: Continuation: /* Step 4: ParamData (initiation) */ rc = SQLParamData(stmtHnd, (SQLPOINTER*)&bufp); checkSQLErr(envHnd, conHnd, stmtHnd, rc); for (dtsize=0, bufp = clobdata; dtsize < clobdatalen; dtsize += chunksize, bufp += chunksize) { if (dtsize+chunksize<clobdatalen) len = chunksize; else len = clobdatalen-dtsize; /* Step 5: PutData */ rc = SQLPutData(stmtHnd, (SQLPOINTER)bufp, len*sizeof(TCHAR)); checkSQLErr(envHnd, conHnd, stmtHnd, rc); } /* Step 6: ParamData (termination) */ rc = SQLParamData(stmtHnd, (SQLPOINTER*)&bufp); checkSQLErr(envHnd, conHnd, stmtHnd, rc); rc = SQLFreeStmt(stmtHnd, SQL_CLOSE); _tprintf(_T("Finished Update/n/n")); rc = SQLAllocStmt(conHnd, &stmtHnd); if (rc != SQL_SUCCESS) { _tprintf(_T("Failed to allocate STMT/n")); goto exit2; } /* Clear Result Data */ memset(resultdata, 0, sizeof(resultdata)); chunksize = clobdatalen / 15; /* 15 times to put */ /* Step 1: Prepare */ rc = SQLExecDirect(stmtHnd, sqlStmt2, SQL_NTS); /* select */ checkSQLErr(envHnd, conHnd, stmtHnd, rc); /* Step 2: Fetch */ rc = SQLFetch(stmtHnd); checkSQLErr(envHnd, conHnd, stmtHnd, rc); for(dtsize=0, bufp = resultdata; dtsize < sizeof(resultdata)/sizeof(TCHAR) && rc != SQL_NO_DATA; dtsize += chunksize-1, bufp += chunksize-1) { if (dtsize+chunksize<sizeof(resultdata)/sizeof(TCHAR)) len = chunksize; else len = sizeof(resultdata)/sizeof(TCHAR)-dtsize; /* Step 3: GetData */ rc = SQLGetData(stmtHnd, 1, SQL_C_TCHAR, (SQLPOINTER)bufp, len*sizeof(TCHAR), &retchklen); } if (!_tcscmp(resultdata, clobdata)) { _tprintf(_T("Succeeded!!/n/n")); } else { _tprintf(_T("Failed!!/n/n")); }
Parent topic: Unicode Support