Type mismatch on Toracledataset with Sting fields on a AL32UTF8 DB

[altink]

Hello

I have a TOracleDataset bound to a TOracleSession, connected to an Oracle 11g R2 database with:
NLS_LANGUAGE=AMERICAN
NLS_CHARACTERSET=WE8MSWIN1252

The NLS_LANG environment variable on the client is set to AMERICAN_AMERICA.WE8MSWIN1252.

The OracleDataset has declared String fields from columns on the database table's with some fields of VARCHAR2(x).

Up to this point everything is OK, I can open the table in design-time and run-time

TOracleSession properties:
TOracleSession.BytesPerCharacter = bcAutoDetect
TOracleSession.Preferences.ForceWideStringFields = True

Problem:
I switch the OracleSession's connection to another Oracle database same version 11g R2 (and install), but with:
NLS_LANGUAGE=AMERICAN
NLS_CHARACTERSET=AL32UTF8

I set the NLS_LANG environment variable on the client to AMERICAN_AMERICA.AL32UTF8.

When I try to open the OracleDataset, in both run-time and design-time, I get the error:
OracleDataset:type mismatch for Field 'FIELD', expecting: String, actual: WideString.

When I switch again to an WE8MSWIN1252 DB (and set the same for the env. variable NLS_LANG) everything is again OK.

Please advise me on how to have my program compliant with both Oracle Western Languages (WE8MSWIN1252) and Unicode (AL32UTF8)

at your disposal for further info
thank and regards
Altin

 

[Marco Kalter]

You can set the TOracleSession.Preferences.ForceWideStringFields property to True, which forces all StringFields to WideStringFields in TOracleDataSets connected to this session. This way your application will always work with WideStringFields, regardless if it is connected to a Unicode database or not.

 

[altink]

The TOracleSession.Preferences.ForceWideStringFields property is already set to True, as put above, and it didn't made a difference in my error, it comes out the same.

Please advise

thanks and regards
Altin

 

[altink]

Hi Marco

Clarification:
I did access the WE8MSWIN1252 database with
TOracleSession.Preferences.ForceWideStringFields = False

I used ForceWideStringFields=True in case of AL32UTF8 DB - but no result.

If I use ForceWideStringFields=True, even with a WE8MSWIN1252 I get the error:
OracleDataset:type mismatch for Field 'FIELD', expecting: String, actual: WideString.

I have started a project which already has some hundreds datasets, all with persistent (Add All) Fields:
Delphi BE (10.2)
DOA 4.1.3.5
Oracle 11gR2.
All database text fields are VARCHAR2(x) and some CLOBS. VARCHAR2(x) are recognized by DOA as TStringFields.

Everything works fine so far and looks very stable.

But we need to add Unicode compliance to this project without making two projects, one for WE8MSWIN1252 (all TstringFields) and another for AL32UTF8 (all TwidestringFields)

Please advise,
thank you in advance
Altin

 

[Marco Kalter]

I think you will need to change the persistent StringFields to WideStrungFields at design-time.

 

[altink]

Thank you very much Marco.

Please let me summarize:
In an existing DOA project, built with TOracleDatasets with persistent TStringFields on VARCHAR2(x) columns, into a WE8MSWIN1252 Oracle DB;
the only way to have the project compliant with both Western Languages and Unicode is like in the following:

1. One re-declares all persistent TStringFields as TwideStringFields of every TOracleDataset in the project
2. Session's ForceWideStringFields is set True so that the DOA dataset "percieves" all VARCHAR2(x) as TStringFields
3. The above is the only way for what I am looking

Can you confirm my statement above?

thanks and regards
Altin

 

[altink]

correction:
2. Session's ForceWideStringFields is set True so that the DOA datasets "percieves" all VARCHAR2(x) as TWideStringFields and not TStringFields no matter when DB is WE8MSWIN1252 or AL32UTF8

 

[Marco Kalter]

Yes, that should work. You can test it with a little demo app in your environment of course.

 

[altink]

Thank you very much Marco

I did the test and it worked. As far as I saw it this (TStringField->TWideStringField) was a common solution in this case, not only for DOA components, but in general.

My fear is (and sorry for my superficial knowledge):
Since now and one both Western and Unicode will be treated the same by the application (wide-strings);
When I am de-facto accessing an WE8MSWIN1252, will I suffer all the Oracle bugs and issues sepacially related to Unicode, or everything is resolved inside DOA?
Cause the first is fearful, while the second (DOA) is known to be robust.

best regards
Altin

 

[Marco Kalter]

When reading/writing character data from/to the WE8MSWIN1252 database, Direct Oracle Access will not use Unicode encoding. It will still use WE8MSWIN1252 encoding like before.

 

[altink]

Thank you Marco

the same persistent field conversion should also be:
TMemoField -> TWideMemoField

in this case there will be no errors, just a non-working display of characters.

I am looking for any other string-related field conversion

thanks and regards
Altin