Type Mappings

When using SQL Drivers to access an external database like Microsoft SQL Server or IBM DB2, we have to handle type mappings.

The DataFlex database API knows a fixed number of data types, as shown in the following table:

DATAFLEX TYPES

DF_ASCII	ASCII
DF_BCD	Numeric
DF_DATE	Date
DF_DATETIME	DateTime
DF_TEXT	Text
DF_BINARY	Binary

SQL-based databases have their own native types. Some of them are very similar to the DataFlex types, others can be quite different. The number of SQL native types is usually larger than the number of DataFlex types.

When accessing a SQL database from a DataFlex program, the SQL native types must be mapped to one of the DataFlex types.

This mapping can take 2 directions:

Mapping SQL type to DataFlex type
Mapping DataFlex type to SQL type

Mapping SQL Type to DataFlex Type

SQL to DataFlex type mapping happens when a table is opened from a DataFlex program. The table requests the SQL native type from the database and map it to one of the DataFlex types.

This is also used when connecting to an existing table with the Connect Wizard in the Studio.

The DataFlex SQL Server Driver recognizes all native SQL Server types on open. All types are mapped to an appropriate DataFlex type. Data can be read from and written to all SQL Server types.

The DataFlex DB2 Driver recognizes all native DB2 types on open. All types are mapped to an appropriate DataFlex type. Data can be read from and written to all DB2 types.

For details on all SQL to DataFlex type mappings for SQL Server and DB2, please see those pages.

In some situations, the DataFlex type can be changed by a setting in the table.int file.

It is also possible to change the length on the DataFlex side by specifying a different length in the table.int file.

Mapping DataFlex Type to SQL Type

DataFlex to SQL type mapping happens when creating new columns in a table through the driver. This is usually done in the Studio's Table Editor or in Database Builder. When creating new columns in a table, the developer can choose the DataFlex type, and the native SQL type defaults from the type mapping. The developer can change either type in the Table Editor.

Conversion from DataFlex embedded database to SQL can also be considered a restructure.

The mapping of a DataFlex type to a SQL type is configurable. The mappings can be configured by specifying the mapping in the driver configuration file (MSSQLDRV.INT, DB2_DRV.INT, ODBC_DRV.INT) or through corresponding database API attributes.

It is also possible to specify a default map schema. A default map schema defines DataFlex to SQL mappings for all types. This can be used for backward compatibility with earlier version of the driver, or to develop for a specific version of SQL Server.

Note that all configurable DataFlex to SQL mappings are only used when creating new columns through the driver. The mapping configuration settings have no effect on existing columns. Existing columns keep their existing SQL type and are not changed by a restructure.

Configuring DataFlex to SQL Mappings

Type mappings can be specified in the driver configuration file (MSSQLDRV.INT, DB2_DRV.INT, ODBC_DRV.INT) or at runtime through attributes:

Example:

The following example shows how to specify to map DF_DATE to SQL_DATE and DF_DATETIME to SQL_DATETIME2 in the MSSQLDRV.INT file.

; MAP_DFDATE_TO_SQLTYPE: The SQL Server type when creating new DF_DATE columns

; Allowed values:

; date

; datetime

MAP_DFDATE_TO_SQLTYPE date

; MAP_DFDATETIME_TO_SQLTYPE: The SQL Server type when creating new DF_DATETIME columns

; Allowed values:

; datetime2

; datetime

MAP_DFDATETIME_TO_SQLTYPE datetime2

To set the mappings at runtime , the attributes defined in cli.pkg can be used:

Example:

Use cli.pkg

Get DriverIndex "MSSQLDRV" to iDriverId

Set_Attribute DF_DRIVER_MAP_DFDATE_TO_SQLTYPE of iDriverId to 'date'

Set_Attribute DF_DRIVER_MAP_DFDATETIME_TO_SQLTYPE of iDriverId to 'datetime2'

Open Salesp

Get_Attribute DF_DATABASE_ID of iDriverId iServerId to hDatabase

Set_Attribute DF_DATABASE_MAP_DFDATE_TO_SQLTYPE of iDriverId hDatabase to 'date'

Set_Attribute DF_DATABASE_MAP_DFDATETIME_TO_SQLTYPE of iDriverId hDatabase to 'datetime2'

Using Default Map Schemas

A default map schema defines DataFlex to SQL mappings for all DataFlex types. This can be used for backward compatibility with earlier version of the driver, or to develop for a specific version of SQL Server.

Map schemas can only be used with the DataFlex SQL Server Driver.

Map schemas can be specified in the driver configuration file (MSSQLDRV.INT) with the DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA keyword.

Map schemas can be specified at runtime with the DF_DATABASE_DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA or DF_DRIVER_DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA attributes.

SQL Server Map Schemas

For SQL Server, the following default map schemas can be used:

MAP_DF_TO_SQL_TYPE_UNICODE // default

MAP_DF_TO_SQL_TYPE_SQL2012

MAP_DF_TO_SQL_TYPE_SQL2008

MAP_DF_TO_SQL_TYPE_SQL2005

MAP_DF_TO_SQL_TYPE_SQL2000

The following table shows the DataFlex to SQL type mapping for the SQL Server map schemas.

DataFlex Type	SQL2000	SQL2005	SQL2008	SQL2012	UNICODE
DF_ASCII	Char	Char	Char	Char	nvarchar
DF_DATE	Datetime	Datetime	Date	Date	date
DF_DATETIME	Datetime	Datetime	Datetime2	Datetime2	datetime2
DF_TEXT	Text	Varchar(max)	Varchar(max)	Varchar(max)	nvarchar(max)
DF_BINARY	Binary/image	Varbinary(max)	Varbinary(max)	Varbinary(max)	varbinary(max)

If no default mapping schema is specified, the MAP_DF_TO_SQL_TYPE_UNICODE mapping schema is used.

There is no specific mapping setting for SQL Server 2014, 2016, 2017 or 2019. Since these versions support the same types as SQL Server 2012, the map schema can be set to SQL2012.

The per-SQL Server version mapping schemas can be used to develop for a specific SQL Server version.

Default mappings can be overwritten per type with the DF_TO_SQLTYPE settings.