Data Type Mapping

Overview

Data Type Mapping is a flexible and easily customizable gear, which allows mapping between DB types and Delphi field types.

In this article there are several examples, which can be used when working with all supported DBs. In order to clearly display the universality of the Data Type Mapping gear, a separate DB will be used for each example.

Data Type Mapping Rules

In versions where Data Type Mapping was not supported, MyDAC automatically set correspondence between the DB data types and Delphi field types. In versions with Data Type Mapping support the correspondence between the DB data types and Delphi field types can be set manually.

Here is the example with the numeric type in the following table of a MySQL database:

CREATE TABLE DECIMAL_TYPES
(
  ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  VALUE1 DECIMAL(4, 0),
  VALUE2 DECIMAL(10, 0),
  VALUE3 DECIMAL(15, 0),
  VALUE4 DECIMAL(5, 2),
  VALUE5 DECIMAL(10, 4),
  VALUE6 DECIMAL(15, 6)
)

And Data Type Mapping should be used so that:

• the numeric fields with Scale=0 in Delphi would be mapped to one of the field types: TSmallintField, TIntegerField or TlargeintField, depending on Precision
• to save precision, the numeric fields with Precision>=10 and Scalе<= 4 would be mapped to TBCDField
• and the numeric fields with Scalе>= 5 would be mapped to TFMTBCDField.

The above in the form of a table:

To specify that numeric fields with Precision <= 4 and Scale = 0 must be mapped to ftSmallint, such a rule should be set:

var
  DBType: Word;
  MinPrecision: Integer;
  MaxPrecision: Integer;
  MinScale: Integer;
  MaxScale: Integer;
  FieldType: TFieldType;
begin
  DBType       := myDecimal;
  MinPrecision := 0;
  MaxPrecision := 4;
  MinScale     := 0;
  MaxScale     := 0;
  FieldType    := ftSmallint;
  MyConnection.DataTypeMap.AddDBTypeRule(DBType, MinPrecision, MaxPrecision, MinScale, MaxScale, FieldType);
end;

This is an example of the detailed rule setting, and it is made for maximum visualization.Usually, rules are set much shorter, e.g. as follows:

// clear existing rules
MyConnection.DataTypeMap.Clear;
// rule for DECIMAL(4,0)
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 0,      4, 0,     0, ftSmallint);
// rule for DECIMAL(10,0)
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 5,     10, 0,     0, ftInteger);
// rule for DECIMAL(15,0)
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 11, rlAny, 0,     0, ftLargeint);
// rule for DECIMAL(5,2)
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 0,      9, 1, rlAny, ftFloat);
// rule for DECIMAL(10,4)
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 10, rlAny, 1,     4, ftBCD);
// rule for DECIMAL(15,6)
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 10, rlAny, 5, rlAny, ftFMTBcd);

Rules order

When setting rules, there can occur a situation when two or more rules that contradict to each other are set for one type in the database. In this case, only one rule will be applied — the one, which was set first.

For example, there is a table in an MySQL database:

CREATE TABLE DECIMAL_TYPES 
(
  ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  VALUE1 DECIMAL(5, 2),
  VALUE2 DECIMAL(10, 4),
  VALUE3 DECIMAL(15, 6)
)

TBCDField should be used for NUMBER(10,4), and TFMTBCDField - for NUMBER(15,6) instead of default fields:

If rules are set in the following way:

MyConnection.DataTypeMap.Clear;
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 0,     9, rlAny, rlAny, ftFloat);
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 0, rlAny,     0,     4, ftBCD);
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 0, rlAny,     0, rlAny, ftFMTBCD);

it will lead to the following result:

But if rules are set in the following way:

MyConnection.DataTypeMap.Clear;
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 0, rlAny,     0, rlAny, ftFMTBCD);
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 0, rlAny,     0,     4, ftBCD);
MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 0,     9, rlAny, rlAny, ftFloat);

it will lead to the following result:

This happens because the rule

MyConnection.DataTypeMap.AddDBTypeRule(myDecimal, 0, rlAny,     0, rlAny, ftFMTBCD);

will be applied for the NUMBER fields, whose Precision is from 0 to infinity, and Scale is from 0 to infinity too. This condition is met by all NUMBER fields with any Precision and Scale.

When using Data Type Mapping, first matching rule is searched for each type, and it is used for mapping. In the second example, the first set rule appears to be the first matching rule for all three types, and therefore the ftFMTBCD type will be used for all fields in Delphi.

If to go back to the first example, the first matching rule for the NUMBER(5,2) type is the first rule, for NUMBER(10,4) - the second rule, and for NUMBER(15,6) - the third rule. So in the first example, the expected result was obtained.

So it should be remembered that if rules for Data Type Mapping are set so that two or more rules that contradict to each other are set for one type in the database, the rules will be applied in the specifed order.

Defining rules for Connection and Dataset

Data Type Mapping allows setting rules for the whole connection as well as for each DataSet in the application.

For example, such table is created in MySQL:

CREATE TABLE PERSON 
( 
  ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  FIRSTNAME VARCHAR(20),
  LASTNAME VARCHAR(30),
  GENDER_CODE VARCHAR(1),
  BIRTH_DTTM DATETIME
)

It is exactly known that the birth_dttm field contains birth day, and this field should be ftDate in Delphi, and not ftDateTime. If such rule is set:

MyConnection.DataTypeMap.Clear;
MyConnection.DataTypeMap.AddDBTypeRule(myDateTime, ftDate);

all DATETIME fields in Delphi will have the ftDate type, that is incorrect. The ftDate type was expected to be used for the DATETIME type only when working with the person table. In this case, Data Type Mapping should be set not for the whole connection, but for a particular DataSet:

MyQuery.DataTypeMap.Clear;
MyQuery.DataTypeMap.AddDBTypeRule(myDateTime, ftDate);

Or the opposite case. For example, DATETIME is used in the application only for date storage, and only one table stores both date and time. In this case, the following rules setting will be correct:

MyConnection.DataTypeMap.Clear;
MyConnection.DataTypeMap.AddDBTypeRule(myDateTime, ftDate);
MyQuery.DataTypeMap.Clear;
MyQuery.DataTypeMap.AddDBTypeRule(myDateTime, ftDateTime);

In this case, in all DataSets for the DATETIME type fields with the ftDate type will be created, and for MyQuery - with the ftDateTime type.

The point is that the priority of the rules set for the DataSet is higher than the priority of the rules set for the whole connection. This allows both flexible and convenient setting of Data Type Mapping for the whole application. There is no need to set the same rules for each DataSet, all the general rules can be set once for the whole connection. And if a DataSet with an individual Data Type Mapping is necessary, individual rules can be set for it.

Rules for a particular field

Sometimes there is a need to set a rule not for the whole connection, and not for the whole dataset, but only for a particular field.

e.g. there is such table in a MySQL database:

CREATE TABLE ITEM 
(
  ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  NAME CHAR(50),
  GUID CHAR(38)
)

The guid field contains a unique identifier. For convenient work, this identifier is expected to be mapped to the TGuidField type in Delphi. But there is one problem, if to set the rule like this:

MyQuery.DataTypeMap.Clear;
MyQuery.DataTypeMap.AddDBTypeRule(myChar, ftGuid);

then both name and guid fields will have the ftGuid type in Delphi, that does not correspond to what was planned. In this case, the only way is to use Data Type Mapping for a particular field:

MyQuery.DataTypeMap.AddFieldNameRule('GUID', ftGuid);

In addition, it is important to remember that setting rules for particular fields has the highest priority. If to set some rule for a particular field, all other rules in the Connection or DataSet will be ignored for this field.

Ignoring conversion errors

Data Type Mapping allows mapping various types, and sometimes there can occur the problem with that the data stored in a DB cannot be converted to the correct data of the Delphi field type specified in rules of Data Type Mapping or vice-versa. In this case, an error will occur, which will inform that the data cannot be mapped to the specified type.

For example:

But when setting rules for Data Type Mapping, there is a possibility to ignore data conversion errors:

MyConnection.DataTypeMap.AddDBTypeRule(myVarchar, ftInteger, True);

In this case, the correct conversion is impossible. But because of ignoring data conversion errors, Data Type Mapping tries to return values that can be set to the Delphi fields or DB fields depending on the direction of conversion.

Therefore ignoring of conversion errors should be used only if the conversion results are expected.