SqlOraPLSQLPrepare does not like Umlauts

[johi]

TD 5.1 SP1 with Oracle native router

Set bRet = SqlOraPLSQLPrepare(hSql, 'begin dbms_application_info.set_client_info (\'Österreich\'); end;')
Umlaut U+00D6 is garbled.

select client_info from v$session
¿terreich (WE8ISO8859P1 DB-Encoding)
▒sterreich (AL32UTF8 DB-Encoding)

[Jean-Marc Gemperle]

Hi Johi,

With the latest TD5.1 SP2 internal build I'm currently using I can't repro the problem . So I guess it is a good new...see attached and let me know in case I did something wrong in my tests. Like you my ORA INSTANCE is AL32UTF8 and the client uses WINDOW charset.

PS: The issue of long string > 255 returning ANSI instead of UNICODE should get fixed in the SP2.

SP2 is shedulled for end of FEB...

Cheers
JM

[johi]

Hi JM,
I hope you have set the client environment variable NLS_LANG encoding AL32UTF8, not WE8MSWIN1252.
Please verify also with some non western europe characters like русский язык
Greetings

[Jean-Marc Gemperle]

Hi Johi,

Yes sorry from your initial mail and your select I though you were using WE8ISO8859P1....
Indeed it is a problem with 5.1SP2...The SP looses the UNICODE chars though ORACLE is fully UNICODE configured....
Attached testcase and bug...
OK with OLEDB....

TD-4282
TD510 SqlOraPLSQLPrepare against an ORACLE UNICODE INSTANCE and CLIENT NLS_LANG=UTF8 does not handle UNICODE charset right....

Cheer
JM

[johi]

Hi Jean Marc, thanks for your reply.
One additional general remark. I do not understand, why Oracle router is affected by NLS_LANG encoding setting at all. In the C-code I have ported myself I am using OCI with setting OCI_UTF16 in order to pass and receive all parameters in UTF-16, the client character set encoding defined in NLS_LANG is then ignored. See also the explanation by an expert on Oracle international and encoding issues, Sergiusz Wolicki :
http://forums.oracle.com/forums/thread. ... ID=1943715

[Jean-Marc Gemperle]

Hi Johi,

Thanks for the heads up. I got from you sometimes ago that we should not care about the NSL_LANG registry setting and allways use UTF8 with the OCI. I guess we all agree with that one, regardless the technical issues, a good reason is that no one really wants to go on the client machines and change there DEFAULT ORACLE INSTALL NLS_LANG from generally WE8MSWIN1252 to UTF8 to fix TD issues basically workarounding the issue we have with the SQLBASE API and the UNICODE conversion failure :

TD-4270
Error: 10446 SQL UBC Unicode conversion failed when client locale is not set to UTF8

I'm surprised to see dbms_application_info.set_client_info failing with TD when ORACLE is fully configured to use UNICODE...
Also I would not know why on the OCI programing in our router we are dependent to that NLS_LANG setting.

I have forwarded your interogation about this issue....

Thanks for the reference from Oracle.

Cheers
JM

[johi]

Update: with SP2 TD-4282 shows with Unicode client NLS_LANG-encoding AL32UTF8 as well as with non-Unicode WE8MSWIN1252.
Workaround: restrict literals in SqlOraPLSQLExecute to ASCII 7-Bit
! Set bRet = SqlOraPLSQLPrepare(hSql, 'begin dbms_application_info.set_client_info (\'☺ Österreich\'); end;')
Set bRet = SqlOraPLSQLPrepare(hSql, 'begin dbms_application_info.set_client_info (unistr(\'\\263a \\00d6sterreich\')); end;')
Set bRet = SqlOraPLSQLExecute(hSql)

[johi]

problem persists with SP3.
Apparently SqlOraPLSQLPrepare receives the statement from TD5.1 in UTF-16 encoding, converts it to ANSI-codepage 1252 instead of UTF-8 and passes that byte sequence to OCI. Interestingly enough, OCI accepts the illegal UTF-8 sequence and stores it in the database.
RAWTOHEX(client_info) returns "D6737465727265696368". RAWTOHEX('Österreich") returns UTF8: "C396737465727265696368"

[johi]

Any chances sqlora32.dll (internal call oraPLSCompile) will be ported to support Unicode in the near future?
At the moment, SqlOraPLSQLPrepare statements are restricted to 7-Bit Ascii.