Encoding issue

Encoding issue

Postby J4N » Wed 22 Aug 2012 08:36

Hi,

We have an oracle database with the following charset:
Code: Select all
SQL> SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8ISO8859P1

SQL>      select * from NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.4.0


We are currently using your driver to retrieve some of those data in a SQL server through Entity Framework.

It appears that most of special chars aren't correctly encoded now.

The destination database is using "nvarchar", so it's encoded in unicode.

I guess I should specify a charset in your driver to retrieve data with the correct charset, but I can't find how.

Can you give some advice on this?

Thank you very much.
J4N
 
Posts: 11
Joined: Thu 09 Aug 2012 12:49

Re: Encoding issue

Postby Shalex » Wed 22 Aug 2012 12:46

Try using the "Unicode=true;" connection string parameter. Does it help?

If not, send us the following information:
1) a small test project with the corresponding DDL/DML script and specify the steps to follow. Which exact error or incorrect result should we reproduce?
2) are you using the Direct or OCI (via Oracle client) mode? If OCI, tell us the version of your Oracle client;
3) the exact version (x.xx.xxx) of your dotConnect for Oracle. You can find it in the Tools > Oracle > About menu of Visual Studio.
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: Encoding issue

Postby J4N » Wed 22 Aug 2012 13:10

I(or my colleague) will test between today and tomorrow.

In the mean time, here are some responses:

1) Ouch this will be hard, we only have one database with something like 1go of data.
2) Direct mode
3) 7.1.40.0
J4N
 
Posts: 11
Joined: Thu 09 Aug 2012 12:49

Re: Encoding issue

Postby KeemA » Thu 23 Aug 2012 06:19

Hello, I'm J4N's colleague.

I tried to add "Unicode=true;" in the connectionString, but unfortunatly the problem is still not resolved.

Here is an example of the issued encoding data, copied directly from our database:

Data that is resulting: "...par l¿Union européenne et admises par la..."
The way it should be: "...par l'Union européenne et admises par la..."

"¿" instead of "'"

Another caracter that isn't encoded right:

Data that is resulting: "...01.02.1999, S. 1¿33..."
The way it should be: "...01.02.1999, S. 1-33..."

"¿" instead of "-"

I don't see other caracters that are wrong. Other special caracter like "é à ü è..." appear to be right.
KeemA
 
Posts: 3
Joined: Thu 23 Aug 2012 06:05

Re: Encoding issue

Postby KeemA » Mon 27 Aug 2012 05:56

Hello again,

We haven't heard anything from you for several days.

Do you need anymore information? It is a critical problem for us, and we haven't found any solution yet.
KeemA
 
Posts: 3
Joined: Thu 23 Aug 2012 06:05

Re: Encoding issue

Postby Shalex » Mon 27 Aug 2012 16:20

Please give us the following information:
1) send us a small test project with the corresponding DDL/DML script;
2) which Entity Framework version are you using in the project? Which .NET Framework version is installed on your workstation?
3) specify the "Language for non-unicode programs" value from Control Panel > Clock, Language, and Region on the machine where you are running the code.
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: Encoding issue

Postby KeemA » Tue 28 Aug 2012 11:52

Hi,

1) As a last resort
2) Entity Framework 4.3.1.0, .NET v4.0
3) French Switzerland

I've found on your forum a very similar problem that has been resolved:
http://forums.devart.com/viewtopic.php?t=21974

To solve your problem, you should set the encoding explicitly in the Charset property:
SQLConnection1.Params.Values['Charset']:= 'WE8ISO8859P1';


How can I set the Charset in the connectionString?

I haven't found anything about that in your doc: http://www.devart.com/dotconnect/oracle/docs/


Try using the "Unicode=true;" connection string parameter. Does it help?

I've made the change, but now instead of having:
"...par l¿Union européenne et admises par la..."
I have:
"...par lUnion européenne et admises par la..."
KeemA
 
Posts: 3
Joined: Thu 23 Aug 2012 06:05

Re: Encoding issue

Postby Shalex » Fri 31 Aug 2012 16:36

KeemA wrote:How can I set the Charset in the connectionString?

There is no such setting in dotConnect for Oracle (use "Unicode=true;" instead of Charset).

Does the problem persist in the OCI mode (via Oracle client)?

We need a small test project with the corresponding DDL/DML script to reproduce and identify the problem (contact form).
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: Encoding issue

Postby J4N » Mon 03 Sep 2012 08:58

Hi,

We just cannot give you an example so easily, this is a confidential database. So we have to create a new database and insert problematics data, ... Not so easy.


But it appears that the problem can be resolved by specifying the charset(which you say isn't possible with EF?)

I made a request with ORACLE SQL Developer, and the problematic character is displayed correctly.

I will try to create you an example when I got my licence key(trial period is over, we just baught a oracle dotconnect professional team edition).

With this licence, we have a "tech support", does this have a better(faster?) channel than this forum to contact you?
J4N
 
Posts: 11
Joined: Thu 09 Aug 2012 12:49

Re: Encoding issue

Postby Shalex » Thu 06 Sep 2012 15:06

J4N wrote:But it appears that the problem can be resolved by specifying the charset(which you say isn't possible with EF?)

The Charset is a setting of dbExpress driver for Oracle (not dotConnect for Oracle).

J4N wrote:I will try to create you an example when I got my licence key(trial period is over, we just baught a oracle dotconnect professional team edition).

If you didn't receive a license yet, we can send you an extended trial (30 days more): contact us.

J4N wrote:With this licence, we have a "tech support", does this have a better(faster?) channel than this forum to contact you?

Please send us your project and we will do our best to solve the issue as soon as possible.
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: Encoding issue

Postby J4N » Fri 07 Sep 2012 11:30

Our project is under NDA, so it's not possible to give you our project.

But I created you an example with one database with only one table, and a c# code that shows the problem.

I also included a screenshot of the data displayed in sql developer(which shows the correct encoding) and another one that shows the result of the example program.

You can download it here:
https://dl.dropbox.com/u/368403/Work/devart/DEVART_DEMO_SOLUTION.zip
There is no sensitive data in this example.
J4N
 
Posts: 11
Joined: Thu 09 Aug 2012 12:49

Re: Encoding issue

Postby J4N » Thu 13 Sep 2012 06:12

Do you have any information about how to resolve this problem?
J4N
 
Posts: 11
Joined: Thu 09 Aug 2012 12:49

Re: Encoding issue

Postby Shalex » Thu 13 Sep 2012 17:11

We are processing your request.

J4N wrote:The destination database is using "nvarchar", so it's encoded in unicode.

But your test table uses VARCHAR2:
Code: Select all
CREATE TABLE "DEMO_TABLE" ("ID" NUMBER(8, 0) NOT NULL ENABLE, "TESTFIELD" VARCHAR2(1000))

1. Does the problem persist with NVARCHAR2 columns?

J4N wrote:I made a request with ORACLE SQL Developer, and the problematic character is displayed correctly.

2. SQL Developer uses Oracle client. Please specify:
a) the version and NLS_LANG (from the registry) of your Oracle client;
b) does dotConnect for Oracle work correctly in the OCI mode (via Oracle client)?
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: Encoding issue

Postby J4N » Fri 14 Sep 2012 06:01

Shalex wrote:We are processing your request.

J4N wrote:The destination database is using "nvarchar", so it's encoded in unicode.

But your test table uses VARCHAR2:
Code: Select all
CREATE TABLE "DEMO_TABLE" ("ID" NUMBER(8, 0) NOT NULL ENABLE, "TESTFIELD" VARCHAR2(1000))

1. Does the problem persist with NVARCHAR2 columns?


"Destination database", which is a SQL Server. The problem is between the source database(which is oracle) and the program(like you can see in my example project.

Shalex wrote:
J4N wrote:I made a request with ORACLE SQL Developer, and the problematic character is displayed correctly.

2. SQL Developer uses Oracle client. Please specify:
a) the version and NLS_LANG (from the registry) of your Oracle client;
b) does dotConnect for Oracle work correctly in the OCI mode (via Oracle client)?

a)Not sure how I should find this. I found something in the preferences of oracle sql developer:
Image
Does that help?

b) I already answered to that in my second post: DirectMode. It's why we baught your library, because we can't use the oracle client


My client is becomming really angry, this software should be in production in one week. I need to get this problem solved quickly. Did you reproduce the problem?
J4N
 
Posts: 11
Joined: Thu 09 Aug 2012 12:49

Re: Encoding issue

Postby J4N » Mon 17 Sep 2012 09:43

Do you reproduce the problem?

It's becoming critical to us, we have the release this friday, 5 people of the customer IT have been booked for this deployment, we had to negotiate temporary access, ...

We need to make it work now otherwise we will have big penalties. I really need to get feedback and solutions. I've posted this bug almost one month ago and still no way to make it works.

It has to be a priority ticket, we will have huge penalties if it doesn't work.
J4N
 
Posts: 11
Joined: Thu 09 Aug 2012 12:49

Next

Return to dotConnect for Oracle