Why Oracle requires TO_NCHAR when binding SQL_C_WCHAR text via ODBC
I am using the following statement prepared and linked in ODBC:
SELECT (CASE profile WHEN ? THEN 1 ELSE 2 END) AS profile_order
FROM engine_properties;
Running on ODBC 3.0 connection to Oracle 10g database in AL32UTF8 encoding, even after binding to string using wchar_t SQLBindParameter(SQL_C_WCHAR)
, it still gives ORA-12704: character set mismatch error.
Why? I am linking as wchar. Shouldn't wchar be considered NCHAR?
If I change the parameter to wrap it TO_NCHAR()
, then the request works without error. However, since these queries are used for multiple database backends, I don't want to add TO_NCHAR only to the Oracle text bindings. Is there something I am missing? Another way to solve this without the TO_NCHAR hammer?
I have not been able to find anything relevant when searching or in the guides.
Read more ...
- mistake
SELECT (CASE profile WHEN '_default' THEN 1 ELSE 2 END) AS profile_order
FROM engine_properties;
- ok
SELECT (CASE profile WHEN TO_NCHAR('_default') THEN 1 ELSE 2 END) AS profile_order
FROM engine_properties;
SQL> describe engine_properties; Name Null? Type ----------------------------------------- -------- - --------------------------- EID NOT NULL NVARCHAR2 (22) LID NOT NULL NUMBER (11) PROFILE NOT NULL NVARCHAR2 (32) PKEY NOT NULL NVARCHAR2 (50) VALUE NOT NULL NVARCHAR2 (64) READONLY NOT NULL NUMBER (5)
This version without TO_NCHAR works fine in SQL Server and PostgreSQL (via ODBC) and SQLite (direct). However, in Oracle it returns "ORA-12704: character set mismatch".
SQLPrepare(SELECT (CASE profile WHEN ? THEN 1 ELSE 2 END) AS profile_order
FROM engine_properties;) = SQL_SUCCESS
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR,
SQL_VARCHAR, 32, 0, "_default", 18, 16) = SQL_SUCCESS
SQLExecute() = SQL_ERROR
SQLGetDiagRec(1) = SQL_SUCCESS
[SQLSTATE: HY000, NATIVE: 12704, MESSAGE: [Oracle][ODBC]
[Ora]ORA-12704: character set mismatch]
SQLGetDiagRec(2) = SQL_NO_DATA
If I use TO_NCHAR everything is fine (but won't work in SQL Server, Postgres, SQLite, etc.).
SQLPrepare(SELECT (CASE profile WHEN TO_NCHAR(?) THEN 1 ELSE 2 END) AS profile_order
FROM engine_properties;) = SQL_SUCCESS
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR,
SQL_VARCHAR, 32, 0, "_default", 18, 16) = SQL_SUCCESS
SQLExecute() = SQL_SUCCESS
SQLNumResultCols() = SQL_SUCCESS (count = 1)
SQLFetch() = SQL_SUCCESS
source to share
If the Oracle database character set is AL32UTF8, why are the columns defined as NVARCHAR2? This means that you want these columns to be encoded using the national character set (usually AL16UTF16, but this may be different in your database). Unless you are mostly storing Asian language data (or other data that requires 3 bytes of storage in AL32UTF8), it is relatively rare to create NVARCHAR2 columns in an Oracle database when the database character set supports Unicode.
In general, you are much better off serving with the database character set (CHAR and VARCHAR2) instead of trying to work with the national character set (NCHAR and NVARCHAR2 columns) because there are much less hoops to jump over to the design / configuration side of things. Since you are not increasing the set of characters that you can encode by choosing NVARCHAR2 data types, I will argue that you would be happier with VARCHAR2 data types.
source to share
Thanks to Justin.
I cannot say that I understand exactly how to choose between VARCHAR2 and NVARCHAR2. I tried using VARCHAR2 for my date (which includes many different languages, both European and Asian) and it didn't work.
I had another game again and found that using Justin's suggestion works in this combination:
- AL32UTF8 Database Font
- Column types VARCHAR2
- set NLS_LANG = .UTF8 before running sqlplus.exe
- data files using UTF-8 (i.e. files with all INSERT statements)
- inserting and retrieving rows from a database using SQL_C_WCHAR
I still don't find Oracle interesting to play with (for example) PostgreSQL, though ... :-)
source to share