Lookup for text with sami characters returns no results

Lookup for text with sami characters returns no results

Postby object » Wed 25 Feb 2015 07:17

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?
object
 
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Re: Lookup for text with sami characters returns no results

Postby Shalex » Wed 25 Feb 2015 12:16

Try using the "Unicode=true;" connection string parameter. Does this fix the issue?
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Lookup for text with sami characters returns no results

Postby object » Wed 25 Feb 2015 14:20

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.
object
 
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Re: Lookup for text with sami characters returns no results

Postby Shalex » Thu 26 Feb 2015 14:57

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).
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Lookup for text with sami characters returns no results

Postby object » Tue 03 Mar 2015 07:32

Great explanation! Thanks a lot.
object
 
Posts: 89
Joined: Tue 26 Oct 2010 08:29


Return to dotConnect for Oracle