UTF8

BIZ TMS Aurelius
1

Hi Wagner,

We have a requirement to deliver out application supporting Arabic. We use both Firebird and MS SQL and connect via Firedac. What changes are recommended to Aurelius to support this. It looks like what we need to do is:

TMS Aurelius

  • change our TAureliusDataset TStringField to be TWideStringField
  • change our entities' memo blob attribute from [DBTypeMemo] to [DBTypeWideMemo]
  • leave our entities' string fields as String not WideString
  • for MS SQL set Global Config setting MapStringToNationalChar to true
  • for Firebird configure the SQL generator's WideStringCharSet to UTF8

FireDac FDConnectionDef

  • Add CharacterSet=UTF8 to the Firebird connection def

Code Example

Here we are checking how FireDac is configured and configuring Aurelius accordingly

procedure TConnectionFireDac.ConfigureDatabase;
var
  ConnectionName: string;
begin
  if FindCmdLineSwitch( sConnectionName, ConnectionName, True ) then
    conGenWS.ConnectionDefName := ConnectionName;

  if SameText( 'MSSQL', conGenWS.ActualDriverID ) then
  begin
    {See https://support.tmssoftware.com/t/utf8-charset/7411}
    TGlobalConfigs.GetInstance.MapStringToNationalChar := True;
    conAurelius.SQLDialect                             := 'MSSQL';
  end
  else if SameText( 'FB', conGenWS.ActualDriverID ) then
  begin
    var
    Firebird3SQLGenerator                   := ( TSQLGeneratorRegister.GetInstance.GetGenerator( 'Firebird3' ) as TFirebird3SQLGenerator );
    Firebird3SQLGenerator.UseBoolean        := False;
    Firebird3SQLGenerator.UseIdentity       := True;
    Firebird3SQLGenerator.WideStringCharSet := 'UTF8';
    conAurelius.SQLDialect                  := 'Firebird3';
  end
  else
    raise Exception.CreateFmt( 'Unsupported database type %s', [ conGenWS.ActualDriverID ] );
end;

In particular we are not certain if we should change our String fields to WideString in our entities. In testing, we have found that the DatabaseManager will only flag Firebird's varchar fields as UTF8 if we change the entity field to be a WideString. However changing the FireDac connection def to be UTF8 gets around that and treats all strings as UTF8. For MSSQL we need the MapStringToNationalChar flag for the fields to be created as NVarChar.

All this appears to work as expected for us. The only fly in the ointment is that we have to manually change the aurelius datasets' field defs as the ones generated by the Aurelius BPL compiled from our entities always generates TStringField and not TWideStringField for our TAureliusDatasets.

Kind Regards

Steve

2

Hi Steve,

Thanks for your e-mail, actually it's a good reference for other customers trying to achieve this.
All your assumptions are mostly correct, and they can vary here and there depending on the setup.

One of the things that make difference is if your database already exists or if Aurelius is going to create the fields. And also the existing encoding of the database. So there is not 100% correct answer but yours seem ok.

Yes. Aurelius treats string fields as "Unicode" if they are mapped as WideString, or if MapStringToNationalChar is set to True. So in this case if this is still an issue for you, set MapStringToNationalChar to True also for Firebird. But if everything is working fine for you, that's ok too.

One additional thing I would add to your list is to always read/write Blobs using AsUnicodeString:

  Value := Entity.BlobProperty.AsUnicodeString;