Parameter value from TUIBQuery gets truncated when read back for Database.Charset=csUTF8

171 Views Asked by At

I am working on an application built in Delphi 2010 that uses UIB to connect to a Firebird 2.5 database. The application has been running using the default character set for a long time, i.e. nobody gave character sets any special thought and it has simply been working. Currently I am trying to make it work correctly with UTF-8 data.

In doing so I have hit upon a problem with TUIBQuery and parameterized queries. When using Database.Charset=csUTF8 and setting a parameter value for a CHAR(n)-field and retrieving it before executing the query the value is truncated.

Unfortunately some of my code writes and reads parameter like this in a number of places and therefore dies an ugly death.

To isolate and demonstrate the problem I created a simple fresh database with DEFAULT CHARACTER SET UTF-8 and a table like this:

  CREATE TABLE TEST (
    CHARFIELD CHAR(20),
    VARCHARFIELD VARCHAR(20)
  );

I set up the application to connect to the database using TUIBDatabase and TUIBTransaction. Then I created a TUIBQuery-instance, set SQL to a parameterized INSERT-statement into this table, and set the parameters:

  Query := TUIBQuery.Create(NIL);
  Query.Transaction := Transaction;
  Query.SQL.Text := 'INSERT INTO TEST (CHARFIELD, VARCHARFIELD) VALUES (:CHARFIELD, :VARCHARFIELD)';
  Query.Prepare(True);

  s:= 'ABC';

  Query.Params.ByNameAsString['CHARFIELD'] := s;
  Query.Params.ByNameAsString['VARCHARFIELD'] := s;

When I now read the parameter-values back like this:

  s := Query.Params.ByNameAsString['CHARFIELD'];
  s := Query.Params.ByNameAsString['VARCHARFIELD'];

The results are correct for Database.Charset=csNone. But when I instead specify DataBase.Charset=csUTF8 the value for CHARFIELD is truncated to 'A' instead of 'ABC'. The value for VARCHARFIELD is fine. The behaviour is independent of the actual data, I do not have to actually use non-ASCII-characters to provoke it, as the sample shows.

Calling ExecSQL() on the query works correctly and INSERTs the data as expected in both cases.

I have uploaded sourcecode to my simple test program as UIB_UTF8_Test.zip.

Does someone here have any idea what I may be doing wrong and how to do it right?

0

There are 0 best solutions below