Encoding issue

[J4N]

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.

[Shalex]

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.

[J4N]

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

[KeemA]

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]

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.

[Shalex]

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.

[KeemA]

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..."

[Shalex]

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).

[J4N]

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?

[Shalex]

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).

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.

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.

[J4N]

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/de ... LUTION.zip
There is no sensitive data in this example.

[J4N]

Do you have any information about how to resolve this problem?

[Shalex]

We are processing your request.

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?

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)?

[J4N]

We are processing your request.

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.

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:

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]

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.