Data Type Mapping (FireDAC)

Go Up to Setting Options (FireDAC)

FireDAC offers a flexible adjustable data type mapping system, which allows you to simplify the migration to FireDAC or optimize data representation.

General

The data type mapping allows you to map:

• result set column data types returned by a FireDAC driver to the data types preferred by the application.
• command parameters data types defined by an application to the driver supported data types.

The data type mapping is useful for:

• the creation of the data type schema compatible with other data access components when migrating the application from these components to FireDAC.
• the creation of the data type schema compatible across several supported DBMS when developing a cross-DBMS application.
• the mapping of the data types not supported by an application into the supported ones.
• the mapping of the generalized data types supported by a driver into the more specialized/convenient ones.
• the optimization of the memory consumption, by specifying the more optimal data types.

Let us consider SELECT of the numeric column from an Oracle table and how it may be mapped:

DDL	Driver data type	Preferred data type
NUMBER(2,0)	dtBcd, Precision=2, Scale=0	dtSByte
NUMBER(4,0)	dtBcd, Precision=4, Scale=0	dtInt16
NUMBER(8,0)	dtBcd, Precision=8, Scale=0	dtInt32
NUMBER(18,0)	dtBcd, Precision=18, Scale=4	dtCurrency

As you can see, the Oracle driver returns a unified data type (dtBcd/dtFmtBCD) for all possible NUMBER(X,Y) database types. But an application may prefer to use a more specialized/convenient data type, such as dtInt32. Also, FireDAC uses 34 bytes long TBcd data structure for dtBcd/dtFmtBCD data type, and 4 bytes long Integer for dtInt32.

Defining

FireDAC applies the mapping rules at a command preparation. After the command has been prepared, the rule changes have no effect. If the data type conforms to several rules, then only the first one is used. MaxStringSize, MaxBcdPrecision, MaxBcdScale properties are applied to the source data type before the mapping rules.

To define the data type mapping, an application must set FormatOptions.OwnMapRules to True and fill the MapRules collection. Each item in the collection is of the TFDMapRule class and represents a single mapping rule. In case of a result set column, each rule defines a transformation of a source data type, returned by a driver, into a target one, preferred by an application. In case of a command parameter, the rule defines a transformation of a target data type, specified by an application, into a source data type, supported by a driver. All rules, excluding the name-based ones, work bidirectionally for both cases.

Each rule is defined by the TFDMapRule properties:

Properties	Description
PrecMin/PrecMax	Defines the range of source data type numeric precision.
ScaleMin/ScaleMax	Defines the range of source data type numeric scale.
SizeMin/SizeMax	Defines the range of source data type string length.
SourceDataType	Source data type.
TargetDataType	Target data type.
NameMask	Column name mask.
TypeMask	Column data type mask.

If a precision, scale, or size is not used by the rule, then its value must be -1 (default value). If a source data type conforms to a certain rule, then a column data type will be defined using corresponding TargetDataType.

Example

To define mapping rules for the sample above, use the following code:

with FDConnection1.FormatOptions do begin
  OwnMapRules := True;
  with MapRules.Add do begin
    ScaleMin := 0;
    ScaleMax := 0;
    PrecMin := 0;
    PrecMax := 2;
    SourceDataType := dtBcd;
    TargetDataType := dtSByte;
  end;
  with MapRules.Add do begin
    ScaleMin := 0;
    ScaleMax := 0;
    PrecMin := 3;
    PrecMax := 4;
    SourceDataType := dtBcd;
    TargetDataType := dtInt16;
  end;
  with MapRules.Add do begin
    ScaleMin := 0;
    ScaleMax := 0;
    PrecMin := 5;
    PrecMax := 8;
    SourceDataType := dtBcd;
    TargetDataType := dtInt32;
  end;
  with MapRules.Add do begin
    ScaleMin := 4;
    ScaleMax := 4;
    PrecMin := 18;
    PrecMax := 18;
    SourceDataType := dtBcd;
    TargetDataType := dtCurrency;
  end;
end;