Handling of Unicode Characters using Delphi 6

Question

I have a polling application developed in Delphi 6. It reads a file, parse the file according to specification, performs validation and uploads into database (SQL Server 2008 Express Edition)

We had to provide support for Operating Systems having Double Byte Character Sets (DBCS) e.g. Japanese OS. So, we changed the database fields in SQL Server from varchar to nvarchar.

Polling works fine in Operating Systems with DBCS. It also works successfully for non-DBCS Operating systems, if the System Locale is set to Japanese/Chinese/Korean and Operating system has the respective language pack. But, if the Locale is set to english then, the database contains junk characters for the double byte characters.

I performed a few tests but failed to identify the solution.

e.g. If I read from a UTF-8 file using a TStringList and save it to another file then, the Unicode data is saved. But, if I use the contents of the file to run an update query using TADOQuery component then, the junk characters are shown. The database also contains the junk characters.

PFB the sample code:

var
    stlTemp : TStringList;
    qry : TADOQuery;
    stQuery : string;
begin
    stlTemp := TStringList.Create;
    qry := TADOQuery.Create(nil);
    stlTemp.LoadFromFile('D:\DelphiUnicode\unicode.txt');
    //stlTemp.SaveToFile('D:\DelphiUnicode\1.txt'); // This works. Even though 
    //the stlTemp.Strings[0] contains junk characters if seen in watch

    stQuery := 'UPDATE dbo.receivers SET company = ' + QuotedStr(stlTemp.Strings[0]) +
        ' WHERE receiver_cd = N' + QuotedStr('Receiver'); 
    //company is a nvarchar field in the  database
    qry.Connection := ADOConnection1;
    with qry do
    begin
        Close;
        SQL.Clear;
        SQL.Add(stQuery);
        ExecSQL;
    end;
    qry.Free;
    stlTemp.Free
end;

The above code works fine in a DBCS Operating system.

I have tried playing with string,widestring and UTF8String. But, this does not work in English OS if the locale is set to English.

Please provide any pointers for this issue.

Answer 1

In non Unicode Delphi version, The basics are that you need to work with WideStrings (Unicode) instead of Strings (Ansi).

Forget about TADOQuery.SQL (TStrings), and work with TADODataSet.CommandText or TADOCommand.CommandText(WideString) or typecast TADOQuery as TADODataSet. e.g:

stlTemp: TWideStringList; // <- Unicode strings - TNT or other Unicode lib
qry: TADOQuery;
stQuery: WideString; // <- Unicode string

TADODataSet(qry).CommandText := stQuery;
RowsAffected := qry.ExecSQL;

You can also use TADOConnection.Execute(stQuery) to execute queries directly.

---

Be extra careful with Parametrized queries: ADODB.TParameters.ParseSQL is Ansi. If ParamCheck is true (by default) TADOCommand.SetCommandText->AssignCommandText will cause problems if your Query is Unicode (InitParameters is Ansi).

(note that you can use ADO Command.Parameters directly - using ? chars as placeholder for the parameter instead of Delphi's convention :param_name).

---

QuotedStr returns Ansi string. You need a Wide version of this function (TNT)

---

Also, As @Arioch 'The mentioned TNT Unicode Controls suite is your best fried for making Delphi Unicode application. It has all the controls and classes you need to successfully manage Unicode tasks in your application.

In short, you need to think Wide :)

Answer 2

1. You did not specified database server, so this investigation remains on our part. You should check how does your database server support Unicode. That means how to specify Unicode charset for the database and the tables/column/indices/collations/etc inside it. You have to ensure that the whole DB is pervasively Unicode-enabled in every its detail, to avoid data loss.

2. Generally you also should check that your database connection (using database access library of choice) also is unicode-enabled. Generally Microsoft ADO, just like and OLE, should be Unicode-enabled. But still check your database server manual how to specify unicode codepage or charset in the connection string. non-Unicode connection may also result in data loss.

3. When you tell you read some unicode file - it is ambiguous. What ius unicode file ? Is it UTF-8 ? Or one of four flavours of UTF-16 ? Or UTF-7 ? Or some other Unicode Transportation Format ? Usual windows WideChar roughly corresponds to legacy UCS-2 and is expected be BOM-stripped Intel-Endian flavour of UTF-16. http://msdn.microsoft.com/en-us/library/windows/desktop/ms221069.aspx

4. If the file is surely that flavour of UTF-16, then you can load it using Delphi TWideStringList or Jedi CodeLibrary TJclWideStringList. Review you code that you never work with your data using string variables - use WideString everywhere to avoid data loss.
   Since D6 was one of buggiest releases, i'd prefer to ensure EVERY update to Delphi is installed and then install and use JCL. JCL also provides codepage transition functions, that might be more flexible than plain AnsiStringVar := WideStringVar approach.
   For UTF-8 file, it can be loaded by TWideStringList class of JCL (but not TJclWideStringList).

5. When debugging, load lines of the list to WideString variable and see that their content is preserved.

6. Don't write queries like that. See http://bobby-tables.com/ Even if you do not expect malicious cracker - you can yourself make errors or meat unexpected data. Use parametrized queries, everywhere, every time! EVER!
   See the example of such: http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/delphivclwin32/ADODB_TADOQuery_Parameters.html
   Check that every SQL VARCHAR parameter would be ftWideString to contain Unicode, not ftString. Check the same about fields(columns).

7. Think if legacy technologies can be casted aside since their support would only get harder in time.

   7.1. Since Microsoft ADO is deprecated (for exampel newer versions of Microsoft SQL Server would not support it), consider switching to 'live' data access libraries. Like AnyDAC, UniDAC, ZeosDB or some other library. Torry.net may hint you some.

   7.2. Since Delphi 6 RTL and VCL is not Unicode-ready, consider migrating your application to TNT Unicode Components, if you'd manage to find their free version or purchase them. Or migrating to newer Delphi releases.

   7.3. Since Delphi 6 is very old and long not-supported and since it was one of buggiest Delphi releases, consider migrating to newer Delphi versions or free tools like CodeTyphoon or Lazarus. As a bonus, Lazarus started moving to Unicode in its recent beta builds, and it is possible that by the end of migration to it you would get you application unicode-ready.

   7.4 Migration might be excuse and stimulus for re-factoring your application and getting rid of legacy spaghetti.