Unicode with Oracle

I have an Oracle database using UTF8 character set for strings, and I want to insert data from Delphi. I am using a 3rd party control (DOA) for database access, trying to insert WideStrings into the database. I get the error
'ORA 12704 : Character Set Mis-match' when I post the request.

I am floundering in the dark here as I don't know whether the problem is Delphi, Oracle or DOA. Does anyone know how to get Delphi to use Oracle with UTF8 to give me a decent starting point.
sparkidoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LischkeCommented:
Hi sparkido,

the only idea I have is to disable any language translation on Oracle's side (if this is possible, I don't know Oracle). More important is to disable the language driver also in the SQL link (just clear it instead of 'ascii' ANSI or what ever).

Ciao, Mike
0
kretzschmarCommented:
hi sparkido,

can you insert records with sql-plus?
if yes,
then your oracle-client installation is correct and the problem is on doa/delphi side.
if no,
then you must adjust your client-installation.

i can do a test tomorrow, using doa-components on
oracle-8 and oracle 7.3 with d4 or d5.

tell me which versions you use (oracle/delphi/doa-version)
(i guess you use the doa-components from allround automations, which are the best i've seen)

to mike
doa-components don't use a bde-language-driver nor a sql-link,
its a full bde-replacement and much faster by a lower overhead.

meikl
0
LischkeCommented:
Aaah, thank you Meikl. I'll stay here and shut up to learn something new ;-))

Ciao, Mike
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

williams2Commented:
Hmmmm... I wonder how an installation should be build using Installshield Express as I'm about to do the exact same thing :-((((

Regards,
Williams
0
kretzschmarCommented:
hi sparkido,

you never telled me until yet, which versions you use nor if you can insert records with sql-plus.

i've found one point in the doa-components (which i've mentioned above), which will maybe a solution.

--- paste from helpfile

TOracleSession

Declaration

type TBytesPerCharacterOption = (bc1Byte, bc2Bytes, bc3Bytes, bc4Bytes, bcAutoDetect);

property BytesPerCharacter: TBytesPerCharacterOption;

Description

This property indicates to the session how many bytes is used to store 1 character. This depends on the the character set that is used when creating the database. Setting this value to bcAutodetect will cause the session to automatically query this information when necessary.

Warning: the default value of this property is bc1Byte, indicating that 1 character takes up 1 byte. For multi-byte character sets you must set this property to the correct value, or to bcAutodetect.

--- paste end

cannot do further advice until you inform me about your versions and your insert-try with sql-plus, because i've no starting-point.

meikl
0
sparkidoAuthor Commented:
Sorry if it takes a while to respond. I don't know where you all are, but I am in Scotland, so time differences etc...

Anyhow, I can insert data OK (I think), the problem is getting the DOA component to insert when I post.

For example, these work OK ...

CREATE TABLE test (char_col  CHAR(20),nchar_col nchar(20));

    INSERT INTO test VALUES ('Normal', N'Unicode');

    UPDATE TEST SET nchar_col = TRANSLATE('ÐÐ' USING NCHAR_CS)

But I can't put data into the table using post.
0
kretzschmarCommented:
hi sparkido,

have done a test using this code on
doa-components, and have no problem

procedure TForm1.Button1Click(Sender: TObject);
begin
  OracleQuery1.SQL.Text := 'CREATE TABLE test (char_col  CHAR(20),nchar_col nchar(20))';
  OracleQuery1.Execute;
  OracleQuery1.SQL.Text := 'INSERT INTO test VALUES (''Normal'', N''Unicode'')';
  OracleQuery1.Execute;
  OracleQuery1.SQL.Text := 'UPDATE TEST SET nchar_col = TRANSLATE(''ÐÐ'' USING NCHAR_CS)';
  OracleQuery1.Execute;
end;

same done with sql-navigator(powerquest) and sql-plus

all works properly, no special adjustments done. tested on oracle 8.1.5 server and delphi 5 with latest doa components version from allround automations.

try self the codesample above and tell me what happens

meikl
0
kretzschmarCommented:
hello?
0
sparkidoAuthor Commented:
Sorry,

    Got dragged onto something else. This works OK. I was hoping to use existing code, but if this is the only way to submit a query, I'll use this. Sumbit as an answer.

Sparks.
0
kretzschmarCommented:
hi sparks,

i don't know,
which existing code you have,
but anyway i'm glad
that you get it work.

good luck again

meikl ;-)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.