Additional dbExpress Migration Hints (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Migrating dbExpress Applications to FireDAC


General

Incompatible Properties and Methods

Several properties are removed automatically by FireDAC_Migrate_DBX.txt migration script, because FireDAC does not have analogues:

  • TSQLConnection:
    • AutoClone - FireDAC supports multiple active statements per connection for all databases;
    • LocaleCode - is not applicable;
    • UniqueID - is not applicable;
    • GetDriverFunc - FireDAC uses different drivers architecture;
    • LibraryName - FireDAC uses different drivers architecture;
    • ValidatePeerCertificate - is not supported.
  • TCustomSQLDataSet:
    • MaxBlobSize - FireDAC fetches a BLOB value always in full.

Several properties and methods should be removed or replaced by hands, because FireDAC does not have direct analogues and may break the application:

  • IConnectionAdmin:
    • GetDelegateDriverNames - FireDAC does not support "delegate" driver concept;
    • RegisterDriver / UnregisterDriver - a FireDAC driver is registered automatically when linked into application.
  • TSQLConnection:
    • GetCommandTypes - FireDAC command kinds are enumerated by TFDPhysCommandKind;
    • GetServerMethodNames - when FireDAC DataSnap connection is used, then use mkProcs meta info query to get list of server methods;
    • SetTraceEvent / TraceCallbackEvent - FireDAC has different tracing / monitoring API;
    • MaxStmtsPerConn - FireDAC allows unlimited number of statements per connection;
    • VendorLib - use TFDPhysXxxDriverLink.VendorLib property or FDDrivers.ini;
    • LoadParamsOnConnect / ParamsLoaded / LoadParamsFromIniFile - FireDAC has different connection definition API;
  • TCustomSQLDataSet:
    • GetCommandNames - FireDAC command names are enumerated by TFDPhysCommandKind and TFDPhysMetaInfoKind;
    • SetSchemaInfo, SchemaName, GetMetadata - use TFDMetaInfoQuery instead;
    • ParseSelectSql, ParseDeleteSql, ParseUpdateSql, ParseInsertSql - FireDAC does not support this API. Instead, you can use UpdateOptions.UpdateTableName, TField.ProviderFlags, etc.
  • TSQLMonitor - FireDAC has different tracing / monitoring API.

FireDAC emulates many dbExpress incompatible properties and methods. To enable emulation FireDAC_Migrate_DBX.txt migration script includes FireDAC.DBX.Migrate unit into your FireDAC application uses clause.

Note: To get full control, consider to replace emulated methods by native FireDAC methods.

Exception Handling

TDBXError is the dbExpress-specific exception class. FireDAC has an analogue class, the EFDDBEngineException class.

When handling the dbExpress exceptions, the programmer uses the Message or the ErrorCode properties to get an error type. FireDAC has the Kind property, which returns an enumerated value. See handling errors in FireDAC for more information.

For example, replace the following dbExpress code into FireDAC code:

  • For Delphi:
if (E is TDBXError) and (Pos('unique key violated', TDBXError(E).Message) > 0) then
  MetaBaseDBError(SMb_DataSetInvalidPKeyValue, E);
if (E is EFDDBEngineException) and (EFDDBEngineException(E).Kind = ekUKViolated) then
  MetaBaseDBError(SMb_DataSetInvalidPKeyValue, E);

Wait Cursor

For example, replace the following dbExpress code into FireDAC code:

  • For Delphi:
Screen.Cursor := crSQLWait;
try
  ......
finally
  Screen.Cursor := crDefault;
end;
uses
  FireDAC.Stan.Factory, FireDAC.UI.Intf;
  ......
var
  oWait: IFDGUIxWaitCursor;
  ......
  FDCreateInterface(IFDGUIxWaitCursor, oWait);
  oWait.StartWait;
  try
    ......
  finally
    oWait.StopWait;
  end;

Third-party Integration

Many third-party products, such as reporting tools or m-tier libraries, require a DAC adapter unit. Please, contact a third-party product vendor for information about how to get an adapter for FireDAC.

Low Level API

FireDAC does not have a dbExpress API / Data.DBXCommon analogue. The code that uses the dbExpress API / Data.DBXCommon should be coded again, using only the FireDAC API (DatS and Phys Layers API). See FireDAC (general overview) for more information about it. There is no direct solution.

Drivers and Connections

Setting Driver and Client Library

dbExpress has many driver linking parameters, such as DriverUnit, DriverPackageLoader, DriverAssemblyLoader, MetaDataPackageLoader, MetaDataAssemblyLoader, GetDriverFunc, LibraryName, LibraryNameOsx. None of them has analogue in FireDAC. Instead a FireDAC driver may be linked either statically, either dynamically using pre-build FireDAC packages. See deploying application with FireDAC for more information about this.

dbExpress allows you to specify VendorLib parameter in connection parameters. FireDAC does not support that. To configure a FireDAC driver set TFDPhysXxxDriverLink.VendorLib property value or use the FDDrivers.ini file. See configuring FireDAC drivers for more information about it.

For example, replace the following dbExpress code into FireDAC code:

  • For Delphi:
SQLConnection1.Params.Add('VendorLib=' + ExtractFilePath(Application.ExeName) + 'libmysql.dll');
FDPhysMySQLDriverLink1.VendorLib := ExtractFilePath(Application.ExeName) + 'libmysql.dll';

Setting a Connection

In dbExpress a stored set of the connection parameters is called named connection and it is saved in the dbxConnections.ini file. To manage these named connections an application you may use the IConnectionAdmin interface reference returned by GetConnectionAdmin. In FireDAC the stored set of connection parameters is called connection definition and it is saved in the FDConnectionDefs.ini file (see defining a connection in FireDAC). Use the FDManager component to manage connection definitions.

In dbExpress to load at run-time the connection parameters from a named connection (automatically), the application uses ConnectionName and LoadParamsOnConnect or LoadParamsFromIniFile. A FireDAC application sets only ConnectionDefName.

For example, replace the following dbExpress code into FireDAC code:

  • For Delphi:
SQLConnection1.ConnectionName := 'IBConn';
// loads parameters of 'IBConn' named connection from dbxConnections.ini
SQLConnection1.LoadParamsFromIniFile;
SQLConnection1.Connected := True;
// will load parameters of 'IBConn' connection definition from FDConnectionDefs.ini
FDConnection1.ConnectionDefName := 'IBConn';
FDConnection1.Connected := True;

Logging to a Database

dbExpress uses the standard VCL or FireMonkey login dialogs. FireDAC requires to use the TFDGUIxLoginDialog component and set TFDConnection.LoginDialog to this component (see establishing a connection in FireDAC for more information).

The TFDConnection.OnLogin event is incompatible with the TSQLConnection.OnLogin event parameters list.

You need to change the following handler declaration (after applying FireDAC_Migrate_DBX.txt) into FireDAC code:

  • For Delphi:
procedure TMyDataModule.dbLogin(Database: TFDConnection; LoginParams: TStrings);
begin
  LoginParams.Values['User_Name'] := 'me';
  LoginParams.Values['Password'] := 'pwd';
end;
procedure TMyDataModule.dbLogin(Database: TFDCustomConnection; LoginParams: TFDConnectionDefParams);
begin
  LoginParams.Values['User_Name'] := 'me';
  LoginParams.Values['Password'] := 'pwd';
end;

Controlling Transactions

dbExpress transaction control API supports multiple active but not nested transactions. For multiple transactions it uses ID based approach.

FireDAC supports multiple active and nested transactions (see Managing Transactions (FireDAC) for more information). For multiple transactions an application may use several TFDTransaction's connected to the same TFDConnection. For nested transactions the calls of StartTransaction / Commit / Rollback may be nested.

The FireDAC.DBX.Migrate unit emulates dbExpress transaction control API, but does not support multiple active transactions. To support multiple active transactions the code must be changed.

For example, replace the following code into FireDAC code:

  • For Delphi:
uses
  FireDAC.DBX.Migrate;
...
var
  oTx1: TFDDBXTransaction;
...
oTx1 := FDConnection1.BeginTransaction;
try
  ...
  FDConnection1.CommitFreeAndNil(oTx1);
except
  FDConnection1.RollbackFreeAndNil(oTx1);
  raise;
end;
FDTransaction1.StartTransaction;
try
  ...
  FDTransaction1.Commit;
except
  FDTransaction1.Rollback;
  raise;
end;

Tracing

dbExpress tracing capabilities are based on TSQLMonitor component and TSQLConnection.TraceCallbackEvent event handler. FireDAC tracing capabilities are based on the set of TFDMoniXxxClientLink components and MonitorBy=Xxx connection definition parameter (see Tracing and Monitoring (FireDAC) for more information about it.

Both libraries are not compatible in this area, so dbExpress tracing setup must be completely replaced by the FireDAC setup. For example, to enable tracing output for FireDAC connection use the following code:

  • For Delphi:
FDMoniRemoteClientLink1.Tracing := True;
FDConnection1.Params.Add('MonitorBy=Remote');
FDConnection1.Connected := True;

Connection Meta-information

dbExpress provides connection specific metadata properties through TSQLConnection.MetaData property. This property returns a reference to the TDBXDatabaseMetaData object. FireDAC provides similar metadata properties through TFDConnection.ConnectionMetaDataIntf property. This property returns a reference to the IFDPhysConnectionMetadata interface.

The TDBXDatabaseMetaData and IFDPhysConnectionMetadata API's are not compatible.

Datasets and Commands

General

The major differences of dbExpress and FireDAC datasets are:

  • dbExpress datasets are unidirectional. dbExpress dataset do not cache fetched records. An application cannot set a random position in a result set.
  • dbExpress datasets have very limited client-side features. They do not support sorting, filtering, ranges, aggregates, etc.
    • FireDAC datasets have advanced client-side features.
  • dbExpress datasets are read-only. They do not support record editing, automatic updates posting, cached updates, etc.
    • FireDAC datasets have advanced updating features.

To avoid above dbExpress limitations many dbExpress applications use the TDataSetProvider component and a TClientDataSet component. After migrating a dbExpress application to FireDAC, you need to delete the TDataSetProvider and TClientDataSet components and use only FireDAC datasets.

Compatibility

dbExpress datasets (the use of dbExpress datasets, TDataSetProvider and TClientDataSet) and FireDAC datasets have slightly different data processing. To force FireDAC datasets compatibility set FormatOptions.DataSnapCompatibility to True.

dbExpress datasets use positional parameters binding. That means, that each parameter from Params collection is matched to parameter markers in SQL command text by position, not by name. FireDAC uses named parameters binding by default.

You may have problems in the following cases:

  • the same named parameter marker occurs 2 or more times in SQL command text.
  • the order of parameters in Params collection differs from the order of parameter markers in SQL command text.

To resolve these problems, consider to:

  • change the SQL command text and make all parameter marker names unique.
  • set FireDAC dataset Params.BindMode to pbByNumber.

Persistent fields

The dbExpress application with persistent fields must be adjusted additionally.

dbExpress does not provide or use persistent fields Origin and ProviderFlags properties, nor the queries DB dictionary to get the unique identifying fields. FireDAC provides and uses persistent fields Origin and ProviderFlags properties. The FireDAC_Migrate_DBX.txt migration script strips all TField.Origin values from DFM files. When Origin is empty (as it is in the dbExpress application), FireDAC uses the field name. But the default value of ProviderFlags does not include pfInKey, and FireDAC will not query the database for primary key fields. So, it will fail to get unique identifying fields and you must take one of the additional actions:

TSQLTable

FireDAC TFDTable is analogue to the BDE's TTable and dbExpress's TSQLTable. We recommend you to replace all TSQLTable with TFDQuery right while migrating to FireDAC.

TSQLDataSet

FireDAC does not have direct TSQLDataSet analogue class.

There are several replacement options:

For example, replace the following dbExpress code into FireDAC code:

  • For Delphi:
SQLDataSet1.CommandType := ctQuery;
SQLDataSet1.CommandText := 'select * from tab';
SQLDataSet1.Open;
SQLDataSet2.CommandType := ctTable;
SQLDataSet2.CommandText := 'tab';
SQLDataSet2.Open;
FDQuery1.SQL.Text := 'select * from tab';
FDQuery1.Open;
FDTable1.TableName := 'tab';
FDTable1.Open;

For example, replace the following dbExpress code into FireDAC code:

  • For Delphi:
SQLDataSet1.SetSchemaInfo(stTables, '', '', '');
SQLDataSet1.Open;
SQLDataSet2.SetSchemaInfo(stColumns, 'TAB', '', '');
SQLDataSet2.Open;
FDMetaInfoQuery1.MetaInfoKind := mkTables;
FDMetaInfoQuery1.Open;
FDMetaInfoQuery2.ObjectName := 'TAB';
FDMetaInfoQuery2.Open;

See Also