Link to home
Start Free TrialLog in
Avatar of Choli
Choli

asked on

German Umlaut in WHERE and INSERT Statement

Hi all

Got a strange Problem with Oracle 8.1.7

I have a DB-Server and 8.1.7 Client both set to character set US7ACII. The Data in the DB can have german 'umlaut' (ä,ö,ü) in the data, usually VARCHAR2 fields.

It's no Problem just to read data from the DB, means the 'umlaut' s will be displayed correctly if they're allready in the DB. (Conversion was made with native connection, there was no problem with the 'umlaut' s)

If I try to search records with an argument with 'umlaut' there will be no result. If I replace the 'umlaut' with an ?, I get the correct Result (with 'umlaut' s in the result, exaclty where I expect it to be)

If I try to write new recordsets, the 'umlauts' are replaced by their origin character (ö --> o, ä --> a, ü --> u).

If I take the Microsoft ODBC for Oracle, it works fine but instead has problemes with too long queries.

Questions:

- Is there an option to set that the 'umlauts' are accepted also in WHERE and in INSERT? (st. diffrent from installing the db newly, that's no option...)
- Is there a solution with the microsoft ODBC that has no problems with long queries?

thanx for quick answer, many points are waiting for a fast anser! (I will increase points if you provide a fast solution, which I can use to really solve my real world problem!)

cheers

Choli
Avatar of seazodiac
seazodiac
Flag of United States of America image

Choli:

Take a look at this thread, I have answered the same question before in this forum:

https://www.experts-exchange.com/questions/20742241/Accent-insensitive-queries-in-Oracle.html
Avatar of Choli
Choli

ASKER

Hi Seazodiac

mmh... it's not exactly the same problem because I don't want a 'accent-insensitve' search but a correct 'accent-sensitive' search...:

- OK, it is one way to search case insensitive, but I'll be able to search exactly....
- There is something more about the problem: ORACLE should also save the 'umlauts' in it's correct form, the values are used e.g. for letters, where it's important to have the correct character.

any other idea or an explanation to the answer why it covers the problem anyway?

Cheers

Choli
- OK, it is one way to search case insensitive, but I'll be able to search exactly....

----->That's right, you can use it for exact search too.

- There is something more about the problem: ORACLE should also save the 'umlauts' in it's correct form, the values are used e.g. for letters, where it's important to have the correct character.

---->the fact that you create your Database in US7ACII stops oracle from storing 'umlauts' in its correct form. I am afraid that you did not read the entire answer.

I copies and pasted the QA from the old post:

## 1) will use of NVARCHAR2 allow me store A with an umlaut

Not if the national character set is US7ASCII. You need WE8ISO8859P1 or WE8MSWIN1252. But I would actually recommend using VARCHAR2 with the database character set WE8MSWIN1252 (Windows clients) or WE8ISO8859P1 (Unix clients)
Avatar of Choli

ASKER


Hmm... still not satisfied...

- OK, it is one way to search case insensitive, but I'll be able to search exactly....

----->That's right, you can use it for exact search too.
****> how? As I understand, the search will just find a and ä with that example. I just wanna have ä....


---->the fact that you create your Database in US7ACII stops oracle from storing 'umlauts' in its correct form. I am afraid that you did not read the entire answer.

We are working that way for years now, and it worked fine till XP and ORACLE ODBC.

Using  Microsoft ODBC for ORACLE it works. Using ODBC native connection, it works. I did a whole migration and all the umlauts are in that DB. I can read them out and show them.


Cheers

Choli

Leider US7ASCII kann nicht speichern in dem Datenbank deutsche umlaut  Buchstaben.

US7ASCII can not store umlauts in the database. This is the source of all your troubles. You have to use UTF8 or other character sets. Also on the client machine you have to set that character set in the Registry if you use Oracle Client.
Avatar of Choli

ASKER

Hi schwertner

haven't seen you for a while ;-) (Ok, I havn't been here for a while...)

Got the same information from ORACLE. The main problem is that the Oracle ODBC-Driver tries to convert the caraters to unicode. The Microsoft driver just passes the data trough. On the Database, Oracle saves unknown characters anyway with 8 bits altough the characterset is 7 bit.

As I see, to be on the secure side, we have to update the DB to 8 bit, which is not an easy task, i'm affraid.

CU

Stefan
I have the instructions how to change the character set of 8i to new one on the fly. Of course you have to backup the database before this to secure the experiment.

Another strategy is to make export, to create a new DB and to do import. Also have good experience to do this.
The best choice for the new character set is UTF8 and derivates. Also migration to 9i is a good idea.
Avatar of Choli

ASKER

hi schwertner

hmmm.... it's quite a big database withe some million entris. What about the time for running an update? Anyway how to get the instruction? some extra points?   ;-)  

I'm not sure if exporting the data and re-import them is suitable, again the question is about the time... and what is the thing with 9i? Is the problem solved there?

thanx 4 anser

choli
Upgrade will cause some downtime.
but EXP-IMP will NOT. you can do this while the database is up and running.

if configured properly, EXP/IMP goes pretty quickly and  smoothly. of course, the best thing is that you don't have to make a copy or backup of working database, while you are building new one.

You do not need to move to 9i only for the umlauts. If you set in the enw 8i installation character set to UTF8 you will have no troubles. Migration to 9i is because it is new version and better of course.

The instruction to change the character set will be sent to you if you send me an email jschwert99@hotmail.com. It is risky, so make an backup ane export before applying.

Export/import is a good solution. there are some small traps, but I just prepare a migration strategy for Germany using import/export and am well informed how to avoid the troubles.
Avatar of Choli

ASKER

As I'm not the system engineer for that system, I can't exactly control or push what version they have to use. But of course I'm urging them to convert to 8 Bit, probaly really UTF8, but they have to check that out first.

I'm not sure about the time slot we have for an export - import but I think it's the only way.

By the way, I found the problem with the Microsoft driver. It is not the number of columns but they don't support types as BLOB or CLOB. So we had to change the field-type to a large VARCHAR2. But as mentioned before it's too risky to work on an unsupported or even corupt dataset.

schwertner, what about closing and splitting this issue and reopen another one for the migration strategy?
ASKER CERTIFIED SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Choli:

In fact, you may not have to migrate your database.

First, let's check out what CHARSET and NCHARSET your database is really built upon.

at the SQL PROMPT,

SQL>>select name , value$ from sys.props$  where name like '%NLS_NCHAR%';

this will give your national characterset of your database.
what you may have is already UTF8, because mostly database is built upon that at the minimum.

suppose if you are already in UTF8, then all you need to do is to convert some columns in the tables to NCHAR or NVARCHAR2 type from CHAR or VARCHAR2,
then you are good to go...

Hope this helps