I use the following statement prepared and bound in ODBC:
SELECT (CASE profile WHEN ? THEN 1 ELSE 2 END) AS profile_order
FROM engine_properties;
Executed in an ODBC 3.0 connection to an Oracle 10g database in AL32UTF8 charset, even after binding to a wchar_t string using SQLBindParameter(SQL_C_WCHAR)
, it still gives the error ORA-12704: character set mismatch.
Why? I'm binding as wchar. Shouldn't a wchar be considered an NCHAR?
If I change the parameter to wrap it with TO_NCHAR()
then the query works without error. However since these queries are used for multiple database backends, I don't want to add TO_NCHAR just on Oracle text bindings. Is there something that I am missing? Another way to solve this without the TO_NCHAR hammer?
I haven't been able to find anything relevant via searches or in the manuals.
More details...
-- error
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 do use TO_NCHAR, it's okay (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