Unicode with Fb2.1 and D2009

[lams]

Hi,

I am using latest dbx driver for Ib and Firebird 2.1 with Delphi 2009.

My SQLConnection like this:

Code: Select all

  object conInterbase: TSQLConnection
    ConnectionName = 'Devart InterBase'
    DriverName = 'DevartInterBase'
    GetDriverFunc = 'getSQLDriverInterBase'
    LibraryName = 'dbexpida40.dll'
    LoginPrompt = False
    Params.Strings = (
      'DriverName=DevartInterBase'
      'DataBase=C:\DB\Crazy.fdb'
      'RoleName='
      'User_Name=sysdba'
      'Password=masterkey'
      'SQLDialect=1'
      'BlobSize=-1'
      'ErrorResourceFile='
      'LocaleCode=0000'
      'DevartInterBase TransIsolation=ReadCommited'
      'WaitOnLocks=True'
      'Charset='
      'CharLength=1'
      'EnableBCD=True'
      'OptimizedNumerics=True'
      'LongStrings=True'
      'UseQuoteChar=False'
      'FetchAll=False'
      'UseUnicode=True')
    VendorLib = 'gds32.DLL'
    Left = 24
    Top = 384
  end

In Devart readme, it say:

UseUnicode

Enables or disables Unicode support. Affects character data fetched from the server. When set to True all character data is stored as WideStrings and TStringField is replaced with TWideStringFiled.

This option is available for Delphi 2006 and higher IDE versions.

Default value of this option is True for Delphi 2009 and higher IDE versions, and False for Delphi 2006 and 2007.

But why I add all fields to my dataset, it show all fields are TStringField and TMemoField? Why not TWideStringField and TWideMemoField?

Of course it cannot show my Chinese characters in the database.

Any suggestion?

[Plash]

Probably the problem occurs because all fields in your database have NONE character set. The driver cannot be sure that such fields contains UTF8 data, so the driver does not decode data in this fields.

In this next build of DbxIda we'll change this behaviur. The driver will decode data in fields with NONE character set from UTF8 when the UseUnicode option is set to True. In this case TWideStringField fields will be created.

[lams]

Ok, I solve the widestring and widememo problem as I connect to the correct database with default charset is UTF-8. Now I have another issue:

When I try to update the Blob type 1 field, I got the follow error:

Dynamic SQL Error
SQL error code = -303
feature is not supported
blob and array data types are not supported for move operation

Here is my database script:

SET SQL DIALECT 1;

CREATE DATABASE 'c:\db\crazyutf8.fdb' PAGE_SIZE 4096 DEFAULT CHARACTER SET UTF8

/* Table: USERS, Owner: SYSDBA */

CREATE TABLE USERS
(
USERID VARCHAR(30) NOT NULL,
PASSWD VARCHAR(20) NOT NULL,
SALUTATION VARCHAR(6),
FIRSTNAME VARCHAR(40),
LASTNAME VARCHAR(40),
NOTES BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
PRIMARY KEY (USERID)
);

Here is my Delphi code:

Code: Select all

  object SQLQuery1: TSQLQuery
    MaxBlobSize = -1
    Params = 
    SQL.Strings = (
      'Select * from Users')
    SQLConnection = conDevart
    Left = 128
    Top = 40
  end

  object conDevart: TSQLConnection
    ConnectionName = 'DEVART INTERBASE'
    DriverName = 'DevartInterBase'
    GetDriverFunc = 'getSQLDriverInterBase'
    LibraryName = 'dbexpida40.dll'
    LoginPrompt = False
    Params.Strings = (
      'drivername=DevartInterBase'
      'Database=C:\DB\CrazyUTF8.fdb'
      'User_Name=sysdba'
      'Password=masterkey'
      'sqldialect=1'
      'blobsize=-1'
      'localecode=0000'
      'devartinterbase transisolation=ReadCommited'
      'waitonlocks=True'
      'charlength=1'
      'enablebcd=True'
      'optimizednumerics=True'
      'longstrings=True'
      'usequotechar=False'
      'fetchall=False'
      'useunicode=True')
    VendorLib = 'gds32.DLL'
    Left = 32
    Top = 208
  end

procedure TForm1.btnExeClick(Sender: TObject);
begin
  SQLQuery1.Close;
  SQLQuery1.SQL.Text := 'Update Users Set Notes = :Notes';
  SQLQuery1.ParamByName('Notes').DataType := ftBlob;
  SQLQuery1.ParamByName('Notes').ParamType := ptInput;
  SQLQuery1.ParamByName('Notes').AsString := Memo1.Text;  // The memo.text are some Chinese characters
  SQLQuery1.ExecSQL();
end;

But if I do this, everything works fine:

Code: Select all

procedure TForm1.btnExeClick(Sender: TObject);
begin
  SQLQuery1.Close;
  SQLQuery1.SQL.Text := 'Update Users Set Notes = '" + Memo1.Text + '"';
  SQLQuery1.ExecSQL();
end;

Any suggestion?

Cheers,

[Plash]

You should use the ftWideMemo data type for the NOTES parameter. In this case IBDAC will encode a value of this parameter to UTF8 before sending it to the server.

[lams]

After I change parameter datatype as ftWideMemo, I got the same error:

Dynamic SQL Error
SQL error code = -303
feature is not supported
blob and array data types are not supported for move operation

Also I try open the table by ClientDataSet, and update the Notes field under DBGrid, then call ApplyUpdates(-1), then I got the follow error:

raised exception class EIBCError with message 'Cannot transliterate betwen character sets'

[/code]

All these tests are working quite well when I use ADOConnection with IBProvider firebird oledb driver. So I am quite sure this is Devart driver issue, not Firebird database problem.

[Plash]

There is a bug with ftWideMemo parameters. We'll fix it in the next build of DbxIda.

Note that you should not use AsString to assign a value to the parameter because DataType will be automatically changed to ftString. You should use the Value property:

Code: Select all

  SQLQuery1.ParamByName('Notes').DataType := ftWideMemo; 
  SQLQuery1.ParamByName('Notes').ParamType := ptInput; 
  SQLQuery1.ParamByName('Notes').Value := Memo1.Text;