ansiToUtf8 doesnt work with converted Delphi XE projects from Delphi 7?

0
votes

There is a Oracle 9 db with CL8MSWIN1251 codepage setting and Delphi XE with some edit fields.

I need to convert project made in Delphi 7 to Delphi Xe project to use and able to see UTF8 data.

So I opened dpr file using Delphi XE and it converted automatically to dproj. But when I try to insert into db some data, I got question symbols ("?") instead of some utf8 chars. I tried to convert data from Edits (Tedit) using ansitoUTF8(edit1.text). And it doesn't work. Then I started a new project in XE, quickly added some edits to check ansitoutf8 function and it works well and inserts data to same db and into same table.

There is one rule: I cannot change codepage setting in db. 

begin
  adoquery1.close;
  adoquery1.sql.text:='insert into table (data1,data2) values ('+#39+ansitoUTF8(edit1.text)+#39+','+#39+ansitoUTF8(edit2.text)+#39+')';
  adoquery1.execsql;
end;

Any suggestions?

2
delphiutf-8delphi-7oracle9idelphi-xe
Are you aware that sql.text is a string and thus resembles a UnicodeString in D2009? Whatever you do before, it will be converted to unicode when assigned to sql.text. You may get around this by using parameters for the query (which I would recommend anyway), setting the DataType to ftString. – Uwe Raabe
You can't change the database codepage, but are the tables under your control? Check the nvarchar type: download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90120/… – user160694

2 Answers

1
votes

CL8MSWIN1251 is not an UTF-8 code page, is Oracle implementation of Windows 1251 (Cyrillic) codepage. Delphi XE uses always UTF-16. Oracle database and client are able to perform character set conversion. You should set the NLS_LANG parameter of your client/session properly. If you don't want to change the client code page, you can issue an ALTER SESSION SET NLS_LANGUAGE = ... at logon to change it for that session only. For an XE Unicode application with an Oracle 9i database, you can use AL16UTF16 on the client side to tell client data are in UTF-16 format and let Oracle perform its conversions.

PS: code like 

#39+ansitoUTF8(edit1.text)+#39+...

is the best way to introduce SQL injection vulnerabilities. You should use bind variables, or at least use functions like QuotedStr/AnsiQuotedStr that will ensure strings stays string and don't become SQL commands in the wrong hands.

1
votes

There is a backward compatibility break in Delphi XE with AnsiToUTF8() function, in fact in the Utf8Encode() function it calls.

In Delphi XE, it returns a RawByteString kind of string. So you'll have to force the result type into UTF8String. Like this: UTF8String(ansitoUTF8(...)

But I guess this perhaps is not your issue.

In your code:

adoquery1.sql.text:='insert into table (data1,data2) values ('+#39+ansitoUTF8(edit1.text)+#39+','+#39+ansitoUTF8(edit2.text)+#39+')';

The sql.text expression is a UnicodeString, so you'll have explicit conversion made by the Delphi XE compiler in your concatenation expression.

Does the following line work?

adoquery1.sql.text:='insert into table (data1,data2) values ('+#39+edit1.text+#39+','+#39+edit2.text+#39+')';

It should work under XE, and the conversion into UTF8 should be made by the VCL from raw UnicodeString.