TOraQuery: problem with € in parameter

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Daniele Buttarelli
Posts: 56
Joined: Fri 02 Aug 2013 07:51

TOraQuery: problem with € in parameter

Post by Daniele Buttarelli » Wed 14 Jun 2017 10:05

My Environment:
- Oracle 11.2.0.1.0 (NLS_CHARACTERSET = WE8ISO8859P15, NLS_LANGUAGE=ITALIAN)
- Delphi XE7
- ODAC 9.4.14

I have a TOraQuery with this SQL:
UPDATE WKF_TBL_DATA
SET VCHAR1 = :pVCHAR1
WHERE COD_INTERNO = 'xxxx'

I'm trying to write 'Test 1 €' in a VARCHAR2 field with this statement:
OraQuery1.ParamByName( 'pVCHAR1' ).AsString := 'Test 1 €';
OraQuery1.ExecSQL;

The value stored in database, instead, is 'Test 1 ¤'.
How can I solve this problem?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: TOraQuery: problem with € in parameter

Post by MaximG » Thu 15 Jun 2017 10:19

To save the euro symbol in a field of the VARCHAR2 type, passed as a query parameter, you can proceed as follows

(The encoding used in the client environment (ISO 8859-1) and the server (NLS_CHARACTERSET = WE8ISO8859P1) must match):

Without using Unicode:

- specify the required encoding for the OraSession.Options.Charset connection property

Using Unicode:

- set UseUniсode connection property : OraSession.Options.UseUniсode: = True

P.S. In case if, using our recommendations, you will not get the correct result, please provide us the used national encodings on the server and the client

Daniele Buttarelli
Posts: 56
Joined: Fri 02 Aug 2013 07:51

Re: TOraQuery: problem with € in parameter

Post by Daniele Buttarelli » Thu 15 Jun 2017 10:31

On the client:
NLS_LANG = ITALIAN_ITALY.WE8ISO8859P15

On the server:
SELECT * FROM V$NLS_PARAMETERS

NLS_LANGUAGE = ITALIAN
NLS_TERRITORY= ITALY
NLS_CURRENCY= ¤
NLS_ISO_CURRENCY= ITALY
NLS_NUMERIC_CHARACTERS= ,.
NLS_CALENDAR= GREGORIAN
NLS_DATE_FORMAT= DD-MON-RR
NLS_DATE_LANGUAGE= ITALIAN
NLS_CHARACTERSET= WE8ISO8859P15
NLS_SORT= WEST_EUROPEAN
NLS_TIME_FORMAT= HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT= DD-MON-RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT= HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT= DD-MON-RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY= ¤
NLS_NCHAR_CHARACTERSET= AL16UTF16
NLS_COMP= BINARY
NLS_LENGTH_SEMANTICS= BYTE
NLS_NCHAR_CONV_EXCP= FALSE

Daniele Buttarelli
Posts: 56
Joined: Fri 02 Aug 2013 07:51

Re: TOraQuery: problem with € in parameter

Post by Daniele Buttarelli » Thu 15 Jun 2017 14:25

A strange behavior is that same program compiled with Delphi 6 work correcly.

Daniele Buttarelli
Posts: 56
Joined: Fri 02 Aug 2013 07:51

Re: TOraQuery: problem with € in parameter

Post by Daniele Buttarelli » Mon 19 Jun 2017 11:56

Any news?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: TOraQuery: problem with € in parameter

Post by MaximG » Mon 19 Jun 2017 13:38

Please specify whether you tried one of the methods proposed by us. When specifying OraSession.Options.Charset := 'WE8ISO8859P15', the error in your environment is reproduced both in OCI Mode and in Direct Mode?

Daniele Buttarelli
Posts: 56
Joined: Fri 02 Aug 2013 07:51

Re: TOraQuery: problem with € in parameter

Post by Daniele Buttarelli » Mon 19 Jun 2017 13:45

When I specify OraSession.Options.Charset := 'WE8ISO8859P15', the error occours only in OCI Mode. In Direct Mode is ok.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: TOraQuery: problem with € in parameter

Post by MaximG » Mon 19 Jun 2017 14:41

Thank you for the information. We have reproduced the issue and will investigate its origin. We will inform you about the results shortly.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: TOraQuery: problem with € in parameter

Post by MaximG » Fri 23 Jun 2017 14:09

We investigated the problem and came to the following conclusions. The described behavior is determined by mismatch of Oracle and Windows system locale charsets:
Oracle server has the WE8ISO8859P15 charset, and if the Italian (Italy) locale is set on Windows, the charset will be ISO8859P1.
The euro symbol in WE8ISO8859P15 has the code 164, and in the ISO8859P1 charset the euro symbol has the code 128.

When sending the euro symbol value in the UTF-16 charset to the server, Oracle recodes into the WE8ISO8859P15 charset and the euro symbol receives the code 164,
and when the data is refetched, Windows cannot display it correctly, because in the WE8ISO8859P1 charset the symbol with the code 164 is not the euro symbol at all.

You can solve the problem in three ways:

1. Set the WE8ISO8859P1 charset on the server
2. Assign a parameter as OraQuery.ParamByName ('...'). AsAnsiString - that will avoid transcoding UTF16 -> WE8ISO8859P15
3. Correct in ODAC source code (the DBAcccess.pas module) the following functions:
procedure TDAParam.SetAsString(const V ... DIF} end;

Daniele Buttarelli
Posts: 56
Joined: Fri 02 Aug 2013 07:51

Re: TOraQuery: problem with € in parameter

Post by Daniele Buttarelli » Mon 02 Oct 2017 07:50

I tried solution 3 (by modifiing DBAccess.pas).
It work correctly if I use AsString.
However It doesnt' work if I use Value.
Example:
OraQuery1.ParamByName( 'pVCHAR1' ).Value := 'Test 1 €';

Can you help me?
Thanks.

Daniele Buttarelli
Posts: 56
Joined: Fri 02 Aug 2013 07:51

Re: TOraQuery: problem with € in parameter

Post by Daniele Buttarelli » Thu 05 Oct 2017 07:06

Up!

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: TOraQuery: problem with € in parameter

Post by MaximG » Tue 10 Oct 2017 15:19

Thank you for the information. We will investigate this behavior. We will inform you about the results shortly.

Daniele Buttarelli
Posts: 56
Joined: Fri 02 Aug 2013 07:51

Re: TOraQuery: problem with € in parameter

Post by Daniele Buttarelli » Mon 23 Oct 2017 06:41

Do you have any news?
Thanks.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: TOraQuery: problem with € in parameter

Post by MaximG » Fri 27 Oct 2017 16:10

We continue investigating the described issue and hope to get fast results. We will let you know at once.

Daniele Buttarelli
Posts: 56
Joined: Fri 02 Aug 2013 07:51

Re: TOraQuery: problem with € in parameter

Post by Daniele Buttarelli » Mon 27 Nov 2017 16:36

Any news?
Thanks.

Post Reply