Query returns unreadable characters

[wdemooij]

I'm facing a problem whith the results of a query to be handled by an application.
Sometimes (!) a query that should give text as 'A' as result, returns things like 'Á' or '¿'.

The application connects to a Oracle 8.1.7.0 database whith an 8.1.7.4. ODBC.

Whe've encountered this problem on different types of OS an different pc's.

I need to find a way to get rid of this 'bug', please help...

W. de Mooij

 

[SantaMufasa]

WDemooij,

This is probably not a 'bug', but instead is a feature. Here's why. The "strange" results you are seeing (like 'Á' or '¿') mean that the data exists in the database as CHR(193) ['Á'] instead of CHR(65) ['A']. The reason that some client PCs interpret the CHR(193) as 'A', others as 'Á', and still others as '¿', can result from interactions among the character set definitions on 1) the Oracle db server, 2) the machine (example: some Unix server) upon which the Oracle server resides, and 3) the client machine (example: some Windows PC) that displays the results passed to it from the database, via the db server.

The scenario is that a client PC with a very multi-national character code set is the inpute device for your application. Someone inputs a text string containing 'Á'; the application stores the 'Á' in the database; then someone using a PC with a rather narrow code set queries the data and sees '¿' since their character set does not interpret codes above ASCII(127).

If this sounds like an explanation for the behavior you are seeing, I can post an Oracle PL/SQL function that can display the rowid, field_name, and characters above ASCII 127, if you wish.

You can confirm these seemingly strange behaviours among different PCs by issuing this DOS prompt command from each of the PCs in question: "dhcp". The response tells the "Active Code Page" number for that PC. You'll be amazed at how varied the responses can be among your organization's PCs !

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:21 (10Dec03) GMT, 14:21 (10Dec03) Mountain Time)

 

[avjoshi]

Dave,

Did you mean chcp?

dhcp wont give you any thing.

Anand.

 

[wdemooij]

Thanx Santa,

You reply opens a new view on this problem for me. What bothers me, is that the field in the database contains an 'A'.

The application selects data which is put in an array to be handled for exporting data as strings to a textfile.

The result in the textfile is normally 'A', still in some cases the 'Á' is written. At this moment we have no idea how to reproduce this fault, it just happens sometimes.

For example, a textfile with ± 6000 lines has only one wrong character in a single line.

I will check on the DHCP settings as you suggested.

W. de Mooij

 

[SantaMufasa]

Anand,

If you saw how chubby my little fingers are, you would know why it came out wrong. Yes, it should be "chcp". Thanks for the catch.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:48 (10Dec03) GMT, 14:48 (10Dec03) Mountain Time)

 

[SantaMufasa]

W.

Can you please try an experiment for me? Can you please do a "SELECT ASCII(substr(your_field_name_here, start_chr,1) from your_table_name;" like in the example, below:

col a heading "Display|Char." format a7
col b Heading "ASCII|Char." format 999
select substr(text,1,1)a, ascii(substr(text,1,1))b from yada;

Display ASCII
Char.   Char.
------- -----
Á         193
A          65

Notice the differing ASCII values for 'Á' and 'A'. So if you could please post the results of your query, that would be excellent.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:03 (10Dec03) GMT, 15:03 (10Dec03) Mountain Time)

 

[wdemooij]

I will try your experiment when i'm back at 'the office' next Friday, we might be in different timezones (23:11 GMT+1) but tomorrow is my day off .

I'll let you know.

W.

 

[wdemooij]

Dave,

Here are the results:

The codepage is the same on all clients (850)

The result of the query
select distinct(substr(deelsysteem,1,1)) as D
, ascii(substr(deelsysteem,1,1)) as ASCII
from transres


D ASCII
- ---------
A 65
B 66
D 68
G 71
H 72
J 74
M 77
V 86

There seems to be no occurence of 'unwanted' characters in the database.

My workaround at the moment will be like this:
Double the selection (building two arrays), compare the arrays, if the two arrays differ in content the selections are made again.
Any suggestions or helpfull tips are more than welcome

W. de Mooij

 

[ddrillich]

W. de Mooij,

I would suggest to scan your database and find out whether the offending characters are indeed in the DB. You can implement the scanning strictly via SQL or combine it with a programming language.
I did it in the past with tcl which I doubt can be useful to you.
If you do find these characters in the DB, you can proceed and protect your database against insertion of these characters. That can be done via application code or maybe by using an Oracle trigger.
If you don't find them in the DB, then maybe the display device is responsible for them.

Good Luck,
Dan