SqlOraPLSQLPrepare does not like Umlauts

Post found bugs and possible workarounds.
johi

SqlOraPLSQLPrepare does not like Umlauts

Post by johi » 31 Jan 2008, 15:38

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

SqlOraPLSQLPrepare does not like Umlauts

Post by Jean-Marc Gemperle » 01 Feb 2008, 16:37

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
You do not have the required permissions to view the files attached to this post.

johi

SqlOraPLSQLPrepare does not like Umlauts

Post by johi » 01 Feb 2008, 17:11

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

SqlOraPLSQLPrepare does not like Umlauts

Post by Jean-Marc Gemperle » 04 Feb 2008, 15:04

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
You do not have the required permissions to view the files attached to this post.

johi

SqlOraPLSQLPrepare does not like Umlauts

Post by johi » 04 Feb 2008, 15:27

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

SqlOraPLSQLPrepare does not like Umlauts

Post by Jean-Marc Gemperle » 04 Feb 2008, 17:21

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

SqlOraPLSQLPrepare does not like Umlauts

Post by johi » 28 Apr 2008, 17:01

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

SqlOraPLSQLPrepare does not like Umlauts

Post by johi » 13 Jun 2008, 15:03

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

SqlOraPLSQLPrepare does not like Umlauts

Post by johi » 16 Sep 2008, 06:28

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.

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests