Writing Unicode to Oracle

jp · Post by jp » Tue 29 May 2007 11:42

Hi,

I am trying to write unicode data from TntControls to an Oracle 9i Database using a TOraQuery and I cannot get it to work - just get '?' characters in the database.
The database is using the AL32UTF8 character set - would I need to do any sort of conversion? In the session settings I have useunicode disabled, character set to AL32UTF8 & charlength set to zero.

The query looks like this...

with dmOracleUpload.qryInsert do
begin
close;
sql.clear;
sql.add('insert into JPTEST_UNICODE values(:v1)');
parambyname('v1').aswidestring := TntEdit1.Text;
Execute;
end;

The field being written to is an NVARCHAR2.

If anyone can advise.

many thanks,

jp

Plash · Post by **Plash** » Wed 30 May 2007 07:36

You should set the UseUnicode option to True, and the CharSet option to the empty string.

jp · Post by jp » Fri 01 Jun 2007 10:24

I tried that and no luck - still seeing ? characters when I read the data back.
I can read back unicode data written to the database with other applications fine with the ODAC drivers - just seems to be a problem writing the data. Is there anything else I can try?

thanks,

jp

Plash · Post by **Plash** » Fri 01 Jun 2007 13:07

We could not reproduce the problem. Please send to odac*crlab*com a complete small sample that demonstrates the problem, including script to create server objects.

mdietike · Post by **mdietike** » Wed 06 Jun 2007 09:40

Hi, I am facing almost the same problem, but I am connecting with .direct := true (.net := true) session option.
The result is I cannot read DB Fields having any sort of special characters.
I tried this with ODAC 4.50.3.24 aswell as with the trial version 6.05.0.7

Plash · Post by **Plash** » Thu 07 Jun 2007 14:17

We also could not reproduce the problem with the Direct option set to True. Please send to odac*crlab*com a complete small sample that demonstrates the problem, including script to create server objects.

mdietike · Post by **mdietike** » Fri 06 Jul 2007 07:31

Thanks Plesh for your answer. It solved my problem. See the solution to my problem below:

Hello,

The error is raised because ODAC does not support Unicode characters in the SQL
text. You should use a parameter. For example:

SQL.Add('insert into T_CHARSETTEST');
SQL.Add('( TEST_X ) Values (:X)');
ParamByName('X').AsString := 'Estrutura de Suporte p/ Máquina de Louça';

Best regards,
Oleg Plashkin

----- Original Message -----
From: "Martin Dietiker"
To:
Sent: Friday, June 08, 2007 12:56 PM
Subject: Re: Reply: Writing Unicode to Oracle (forum="Oracle Data Access
Components")

> Hello Plash,
>
> thanks for your replay. I have now provided a small Delphi7 project using
> ODAC components.
> I have tested both ODAC versions 4.50.3.24 as well as the trial version
> 6.05.0.7 with this project.
>
> the result is:
>
> ODAC version 4.50.3.24:
>
> connection with OCI: display of the data is correct, insert of the data is
> correct as well.
>
> direct connection: display of the data is incorrect, insert does not function
> (ORA-01756: quoted string not properly terminated)
>
>
> ODAC trial version 6.05.0.7:
>
> connection with OCI: display of the data is correct, insert of the data is
> correct as well.
>
> direct connection: display of the data IS CORRECT, insert does not function
> (ORA-01756: quoted string not properly terminated)
>
>
> The small project you will find as attachement.
>
> My nls_database_parameters are as follows:
> select * from nls_database_parameters
> order by parameter;
>
> PARAMETER VALUE
> ------------------------------ ------------------------------------------------------------------------------------------------------------------------
> NLS_CALENDAR GREGORIAN
> NLS_CHARACTERSET UTF8
> NLS_COMP BINARY
> NLS_CURRENCY $
> NLS_DATE_FORMAT DD-MON-RR
> NLS_DATE_LANGUAGE AMERICAN
> NLS_DUAL_CURRENCY $
> NLS_ISO_CURRENCY AMERICA
> NLS_LANGUAGE AMERICAN
> NLS_LENGTH_SEMANTICS BYTE
> NLS_NCHAR_CHARACTERSET AL16UTF16
> NLS_NCHAR_CONV_EXCP FALSE
> NLS_NUMERIC_CHARACTERS .,
> NLS_RDBMS_VERSION 9.2.0.1.0
> NLS_SORT BINARY
> NLS_TERRITORY AMERICA
> NLS_TIME_FORMAT HH.MI.SSXFF AM
> NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
> NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
> NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
>
>
> the table I am using:
> CREATE TABLE T_CHARSETTEST
> (
> TEST_X VARCHAR2(40 CHAR)
> )
>
> hope this helps in testing.
>
> Thanks for your response in advance!
>
>
>