Behavior of TSQLStoredProc together with Unicode

devartfan · Post by **devartfan** » Fri 09 Jul 2010 15:34

i have the following small procedure in my database:

CREATE OR REPLACE PROCEDURE "MIRROR" (
IN_VAL IN VARCHAR2 ,
OUT_VAL OUT VARCHAR2
)
IS
BEGIN
OUT_VAL := IN_VAL;
END;
/

In my application i use an TSQLStoredproc to feed this procedure with data. Used a TEdit for the input and tLabel for the output.

Only if the in characters are from codepage "1252" the in and out values are the same.
If the in values are unicode characters not found in codepage 1252 only
Question marks are displayed.
For in_val and out_val datatype ftstring was used.

When changing both datatypes to ftwidestring. The displayed characters
doesn't have any relationship to the input characters.

What is wrong

bork · Post by **bork** » Tue 13 Jul 2010 10:21

Hello

To use the Unicode characters in your parameters you should set the UseUnicode parameter to True. You can find more detailed information about this in the dbExress Oracle driver documentation (the Readme.html file).

Also you should keep in mind that in this case Unicode will be used for transferring data between your application and Oracle client. But if you use the VARCHAR2 data type then Oracle client converts Unicode characters to Oracle server charset and some information can be lost. If you want to use the Unicode parameters then you should use the NVARCHAR2 data type:

Code: Select all

CREATE OR REPLACE PROCEDURE "MIRROR" ( 
IN_VAL IN NVARCHAR2 , 
OUT_VAL OUT NVARCHAR2 
) 
IS 
BEGIN 
OUT_VAL := IN_VAL; 
END; 
/

devartfan · Post by **devartfan** » Wed 14 Jul 2010 11:49

Hello,
I've changed the database procedure as you have suggested.
The result is the same as before.

The internal character set of my database is AL32UTF8.
The connection is opened with the parameter useunicode=true.

the parameters are set/read with the methode
parambyname ('in_val').aswidestring resp.
parambyname ('out_val').aswidestring

bork · Post by **bork** » Thu 15 Jul 2010 11:48

We have reproduced your issue. We will notify you as soon as we have any result.

devartfan · Post by **devartfan** » Tue 26 Oct 2010 10:14

For us this problem is much more serious than the length problem
with the surrogate pairs.
Meanwhile we need a solution here urgent.

Regards

bork · Post by **bork** » Fri 05 Nov 2010 14:43

Hello

We are working hard on solving this issue. But our progress is slow for the time being because we are limited by the dbExpress architecture. dbExpress doesn't allow to differ VARCHAR2 and NVARCHAR2 data types. As soon as this problem is solved we will notify you.

bork · Post by **bork** » Tue 23 Nov 2010 14:21

Hello

We added the UnicodeAsNational option. It enables or disables sending all Unicode parameters in national charset to the server. It allows you to send NVARCHAR parameters to the server in the correct charset. This fix will be included in the next dbExpress driver for Oracle build.