Special character §

[Peter Grob]

Hi,

I have a problem with string-data and special characters like §. I guess, its because of unicode.
We are upgrading from TD41 to TD63.
At first I select string data from database which is written with version 4.1 (more than one row).
Next I try to select one row of the result set with the character § in the where-clause
and the result of this select is 0.

Here is an example:
First select:
SELECT Text_Grp, Text_Name, Text_Bez FROM Textbausteine INTO :nText_Grp, :strText_Name, :strText_Bez
WHERE Text_Grp = :nSch_Text_Grp ORDER BY Text_Grp

Second select with special character in strText_Name:
SELECT Text_Name, Text_Bez, Text_Text FROM Textbausteine INTO :dfName, :dfBez, :strText
WHERE Text_Grp = :nText_Grp and Text_Name = :strText_Name

Any suggestions?

Peter

Database version 11.5

[wardies]

Confirmed here with TD63 SP1, Win 7 x64, Oracle 11.2 client and Oracle 9.2 server.

To test, I inserted a row into a varchar2 column of a table using Sql*Plus, e.g.

Code: Select all

SQL> create table test_ansi (dummy varchar2(10));
Table created.

SQL> insert into test_ansi values ('Test § 1');
1 row created.

SQL> commit;
Commit complete.

select * from test_ansi;
DUMMY
----------
Test § 1

Then I simply read that value back using some SQL:

Code: Select all

Call SqlConnect( ghSql )
Call SqlPrepareAndExecute( ghSql, "select dummy into :strTest from test_ansi" )
Call SqlFetchNext( ghSql, nResult )
Set strTest = strTest     <-- Set a breakpoint here and look at strTest!

The result is that strTest has the value: "Test õ 1"

What appears to be happening is that when something (Oracle client? TD 6.3?) maps Unicode<->ANSI for writing to and reading from the database, it assumes code page 1252. However, in actual fact it should be mapping to the code page used by the database server (in our case, the US/UK standard, code page 850).

We can see this in the reverse case too: if we try the following code snippet in TD6.3 SP1:

Code: Select all

Set strTest = "Test § 2"
Call SqlPrepareAndExecute( ghSql, "delete from test_ansi" )
Call SqlPrepareAndExecute( ghSql, "insert into test_ansi values (:strTest)" )
Call SqlCommit( ghSql )

Then when we select with Sql*Plus, we'd expect to see the correct value, mapped to our current code page... but in fact we see:

Code: Select all

SQL> select * from test_ansi;
DUMMY
----------
Test º 2

So this does appear to be a Unicode conversion bug but I'm not sure where in the encode/decode process it's going wrong.

[Mike Vandine]

Thanks for the confirmation.

I will get someone to have a look at this.

Best regards,

[VEspinal]

Hi Wardies:

TD 6.3 does not support Oracle 9.

Please see Team Developer 6.3 compatibility matrix:
http://www.guptatechnologies.com/Produc ... atrix.aspx

I've tested with TD 6.3 SP1, Win 7x64, Oracle 11.2 client and Oracle 12c and works for me.

Please see attached pics and small reprocases.

Thanks

Best Regards,
Victor Espinal

[wardies]

Hi Victor,

Thanks for testing. I back-ported your APT to TD4.2 and can confirm that there is no difference or incompatibility between inserting/selecting in either version (TD4.2 vs TD6.3). Sorry for the invalid report; I incorrectly assumed SQL*Plus would be a good proxy for TD4.2 apps.

So in fact this is not Oracle server version-related; it looks the same on Oracle 10g and 11g but the problem only occurs for me when using the command-line SQL*Plus on Windows. I'd expect this to be using the right code page character lookup tables but it seems to do bad conversions. I tried with Oracle's GUI product, SQL Developer, and it behaves the same as TD, which we should be safe in assuming is correct.

This does make me wonder how the OP created the data though; and what code pages they were using in the old TD4.1 apps. Maybe TD4.1 behaves differently to TD4.2 but it seems unlikely.

Thanks for pointing out the TD6.3 Oracle server support matrix btw; this is useful info. We will no doubt still have customers on Oracle 8i, 9 and 10 for a year or two yet but over time we will push them to upgrade to 11g/12c!

Best regards,
Marcel

[VEspinal]

You are very welcome Marcel.

Best Regards,
Victor Espinal