Lookup for text with sami characters returns no results

[object]

Hi,

We are using dotConnect for Oracle, version 8.4.333. Our Oracla database contains some data with Northern Sami national characters (columns are defined as NVARCHAR2). Here's the example of the text: "Bearráigeahččojođiheaddji".

If we try to lookup up a record with such text using dotConnect (with Entity Framework or directly, it doesn't matter), the search returns no results. I tested the application with Entity Framework Profiler, and the statement is generated correctly:

Code: Select all

SELECT "top".ROLE_ID,
       "top".ROLE,
       "top".PURPOSE,
       "top".REFERENCE,
       "top".LINK,
       "top".LABEL,
       "top".RESTRICTION,
       "top".CREATED,
       "top".CHANGED
FROM   (SELECT "Extent1".ROLE_ID,
               "Extent1".ROLE,
               "Extent1".PURPOSE,
               "Extent1".REFERENCE,
               "Extent1".LINK,
               "Extent1".LABEL,
               "Extent1".RESTRICTION,
               "Extent1".CREATED,
               "Extent1".CHANGED
        FROM   ROLES "Extent1"
        WHERE  (LOWER("Extent1".ROLE)) = N'bearráigeahččojođiheaddji'
        ORDER  BY "Extent1".ROLE_ID ASC) "top"
WHERE  ROWNUM <= 1

If I execute this statement using Oracle client tools, I get the correct result, so apparently both statement generation and statement execution by Oracle works correct. But if I execute it from C# code using Devart.Data.Oracle.dll, no results are returned. What can go wrong?

[Shalex]

Try using the "Unicode=true;" connection string parameter. Does this fix the issue?

[object]

Yes, that fixed it, thanks for a quick response! But I can't understand why without this flag it still works for some characters (including even some Sami characters) but fails for others, while having Unicode=true in the connection strings fixes it for all.

[Shalex]

Here is a general information how the Unicode property affects the work of dotConnect for Oracle.

The Unicode property defines the charset that will be used at the network level for string transferring. The choice of this option has influence on the amount of conversions in the program and the possibility of data loss. If the Unicode property is set to true, UTF8 is used at the network level. Otherwise, the charset from regional settings of OS is used.

1) If Unicode=true, the following scheme is implemented:
UTF8 (stored at the server) -> UTF8 (transported through the network) -> UTF16 (string in .NET).

2) If Unicode=true:
UTF16 -> UTF8 -> UTF16. Unfortunately, UTF16 can not be used at the network level.

3) If Unicode=false:
WE8MSWIN1252->WE8MSWIN1252->UTF16.
If Unicode=false, the encoding works in the following way. Here is a chain of data transformations:
your database encoding (NLS_CHARACTERSET) ->
Oracle client encoding (e.g., HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0, the NLS_LANG key value) ->
the Encoding.Default value (it is equal to the regional settings of your machine) ->
.NET string (UTF16).

[object]

Great explanation! Thanks a lot.