SmartQuery as Provider for ClientDataset can't read CLOB with UNICODE content

[s_grosskr]

Hi Support Team.

I send you (@alex_p) a sample app with shows the following problem:
I have a ClientDataset, bound to a TLocalConnection.
The ClientDataset has a Provider, which has a TSmartQuery as Providerdataseet.

I work on a orcle database with unicode character set (AL32UTF8).
For the OracleSession I set
OraSession.Options.UseUnicode := True;
OraSession.Options.UnicodeEnvironment := True;

When I open a table with a unicode-String in a CLOB, I get rubbish.
In the sample app is another ODAC-Dataset, which reads the value "directly".
In this mode everything is fine.

Changing all ClientDatasets to ODCC-Datasets is not an option for me,
this is a big, fat application with 100s of forms and datasets.

I work with Delphi XE2 Update 4, ODAC Version is 9.4.14

How to use the sample:
- Change database connection string (myServer, myUser, myPassword) to your database
- create a CLOB-field in the sample schema scott/tiger in the table emp by pressing "Create CLOB Field"
- the field is created and filled with the famous russian word "Достопримечательности" for employee # 7900
- start
- in the memo field you see only an "R". When you inspect the value (cds.fieldByName( 'CLOB_TEST' ).AsWideString), you will see something like this:
'R'#0'u'#0's'#0's'#0'i'#0'a'#0'n'#0':'#0' '#0#$14#4'>'#4'A'#4'B'#4'>'#4'?'#4'@'#4'8'#4'<'#4'5'#4'G'#4'0'#4'B'#4'5'#4';'#4'L'#4'='#4'>'#4'A'#4'B'#4'8'#4
so I think the UTF-16 String is not converted correctly to a delphi unicde string
- when done testing, you can drop the new field by pressing "Drop CLOB Field"

Thanks for your help.
With regards
Stefan "ostijo" Grosskreutz

[AlexP]

To display correct UniCode data, you should disable both options: UseUnicode and UnicodeEnvironment

[s_grosskr]

When I do this, the Memo reads "Russian: ¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿"
Setting the Unicode properties in the Sesion is neccessary to show normal VARCHAR Fields with unicode characters, turning this back results in ¿¿¿ for every multibyte char, even with VARCHAR Fields.
You can test this, when you set the name of the employee to a mulit-byte value,
e.g. "UPDATE EMP SET ENAME = ''Αλέξ'' WHERE EMPNO = 7900;"

So this does not solve the problem.

[s_grosskr]

I did some further research, cause I suspected the MIDAS/ClientDataset tier to make the problems.
So I rebuild the whole program with an ADOConnection / ADOQuery as the Provider for the ClientDataset.
This worked!! This shows me, that the error must be on your side (ODAC).
Did you manage to get the example working? Did you see the error?
Of course you must have a database with a UNICODE character set (AL32UTF8 e.g.).
I work with Oracle Server 11g Version 11.2.0.2 and with the 11.2 Oracle Client.

Thanks for your efforts.

[AlexP]

Yes, if regional settings of the client differ from the encoding of the inserted text, then incorrect data is displayed when options are disabled. We will try to fix this behavior in one of the next versions.

[s_grosskr]

Are you sure, that this is a problem with the UseUnicode Option and regional settings?
In Unicode Mode all regional settings should have no effect, or am I wrong?
What bothers me, is: with UseUnicode=True everything works fine in the following cases:
- VARCHAR Field in Midas/Clientdataset Mode (clientdataset with TSmartQuery as Provider)
- VARCHAR Field with direct ODAC Dataset
- CLOB Field with direct ODAC Dataset

Only this case fails:
- CLOB Field in Midas/Clientdataset Mode (clientdataset with TSmartQuery as Provider)

So the problem only shows up, when a TSmartQuery works as a Provider for a TClientDataset and only with CLOB Fields. Maybe you should have a deeper look in this special case.

Maybe you can explain a little more about the UseUnicde Option, maybe I'm using it the wrong way.
But as I stated above: with UseUnicode=False all VARCHAR Fields are srambled too...

Thanks again - ostijo

[AlexP]

Thank you, we continue investigation of the problem and will inform you as soon as any results are available.

[AlexP]

This behavior is due to the fact that Delphi has no ftWideOraClob type. To display data correctly, you should use DataTypeMapping - set the following rule:

Code: Select all

  if OraSession.Options.UseUnicode then
    OraSession.DataTypeMap.AddDBTypeRule(oraClob, ftWideMemo);

[s_grosskr]

Thank you very much. That did it!

[AlexP]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.

[s_grosskr]

Thanks again, very good work!

If you have any further questions, feel free to contact us

I will do so, thanks.
I hope, you're still working on my other issue:
http://forums.devart.com/viewtopic.php?f=5&t=32265
(Error, when filtering a CLOB field and posting changes that excludes the row from the filtered dataset)

[AlexP]

We are working on this problem solution. We will inform you as soon as we have any results.