Changes in Connectivity Kit Type Handling
When using Connectivity Kits to access an external database like MS SQL Server or 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
| DataFlex Type | Description |
|---|---|
| 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, while 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 two directions:
- Mapping SQL type to DF type
- Mapping DF type to SQL type
Mapping SQL Type to DF Type
SQL to DF type mapping occurs when a table is opened from a DataFlex program. The table will request 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 Connectivity Kit for SQL Server will now recognize all native SQL Server 2008/2012 types on open. This includes the new date and time types introduced in SQL Server 2008. All types will be mapped to an appropriate DataFlex type. Data can be read from and written to all SQL Server types.
The Connectivity Kit for DB2 will now recognize all native DB2 10.1 types on open. All types will be mapped to an appropriate DataFlex type. Data can be read from and written to all SQL Server types.
The help contains tables that show all SQL to DataFlex type mappings for SQL Server and DB2.
Mapping DF Type to SQL Type
DF to SQL type mapping occurs when creating new columns in a table through the Connectivity Kit. This is usually done with Table Editor or Database Builder. When creating new columns in a table, the DataFlex type is entered, and this will be mapped to the SQL native type when the table is created or restructured.
Conversion from DataFlex embedded database to SQL can also be considered a restructure.
Earlier versions of the Connectivity Kit would map each DataFlex type to a fixed SQL native type. With Connectivity Kit version 6, each DataFlex type can be mapped to different SQL types by configuration settings and attributes.
Changes in SQL to DF Mapping
SQL to DataFlex type mapping happens when the Connectivity Kit opens a backend table. During the open, the SQL backend type of the columns will be mapped to a DF type.
Note that the opposite mappings (DF to SQL) happen only during restructure. When creating new columns, the type will be determined by these mappings.
The backend table can be a table that is originally created by the CK, but it can also be an external table not created by the CK, for example, created by a SQL backend tool or it could be part of a third-party application.
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 int file.
Changes in DF to SQL Mapping
Mapping of DF type to SQL type occurs when the CK performs a restructure, for example, when adding a new column or changing the type of a column. Conversion from DataFlex to SQL can also be considered a restructure.
In CK6, the mapping of a DF type to a SQL type has been made 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 attributes.
It is also possible to specify a default map schema. A default map schema will define DF to SQL mappings for all types. This can be used for backward compatibility with earlier versions of the Connectivity Kit or to develop for a specific version of SQL Server.
Note that all configurable DF to SQL mappings will only be used when creating new columns through the Connectivity Kit. The mapping configuration settings will have no effect for existing columns. Existing columns will keep the SQL type they already have and will not be changed by a restructure.
New Configuration File Keywords
The following configuration file keywords have been added. These keywords can be specified in the mssqldrv.int and db2_drv.int configuration files.
DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA
MAP_DFDATE_TO_SQLTYPE
MAP_DFDATETIME_TO_SQLTYPE
MAP_DFASCII_TO_SQLTYPE
MAP_DFTEXT_TO_SQLTYPE
MAP_DFBINARY_TO_SQLTYPE
For the ODBC Connectivity Kit, a different set of configuration keywords has been added. Since the ODBC Connectivity Kit can connect to different database backends with different native types, an ODBC type must be specified in the odbc_drv.int.
MAP_DFDATE_TO_ODBCTYPE
MAP_DFDATETIME_TO_ODBCTYPE
MAP_DFASCII_TO_ODBCTYPE
MAP_DFTEXT_TO_ODBCTYPE
MAP_DFBINARY_TO_ODBCTYPE
New Attributes
The DF to SQL mappings can also be specified at runtime with the set_attribute command. The following attributes have been added.
DF_DRIVER_DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA
DF_DATABASE_DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA
DF_DRIVER_MAP_DFDATE_TO_SQLTYPE
DF_DRIVER_MAP_DFDATETIME_TO_SQLTYPE
DF_DRIVER_MAP_DFASCII_TO_SQLTYPE
DF_DRIVER_MAP_DFTEXT_TO_SQLTYPE
DF_DRIVER_MAP_DFBINARY_TO_SQLTYPE
DF_DATABASE_MAP_DFDATE_TO_SQLTYPE
DF_DATABASE_MAP_DFDATETIME_TO_SQLTYPE
DF_DATABASE_MAP_DFASCII_TO_SQLTYPE
DF_DATABASE_MAP_DFTEXT_TO_SQLTYPE
DF_DATABASE_MAP_DFBINARY_TO_SQLTYPE
Only for the ODBC Connectivity Kit, the following attributes have been added:
DF_DRIVER_MAP_DFDATE_TO_ODBCTYPE
DF_DRIVER_MAP_DFDATETIME_TO_ODBCTYPE
DF_DRIVER_MAP_DFASCII_TO_ODBCTYPE
DF_DRIVER_MAP_DFTEXT_TO_ODBCTYPE
DF_DRIVER_MAP_DFBINARY_TO_ODBCTYPE
DF_DATABASE_MAP_DFDATE_TO_ODBCTYPE
DF_DATABASE_MAP_DFDATETIME_TO_ODBCTYPE
DF_DATABASE_MAP_DFASCII_TO_ODBCTYPE
DF_DATABASE_MAP_DFTEXT_TO_ODBCTYPE
DF_DATABASE_MAP_DFBINARY_TO_ODBCTYPE
Configuring DF 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 Salsep
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 will define DF to SQL mappings for all DF types. This can be used for backward compatibility with earlier versions of the Connectivity Kit or to develop for a specific version of SQL Server.
Map schemas can only be used with the SQL Server Connectivity Kit. Map schemas can be specified in the Connectivity Kit configuration file (mssqldrv.int) with the DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA keyword.
Or map schemas can be specified at runtime with the DF_DRIVER_DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA or DF_DATABASE_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_SQL2000
MAP_DF_TO_SQL_TYPE_SQL2005
MAP_DF_TO_SQL_TYPE_SQL2008
MAP_DF_TO_SQL_TYPE_SQL2012
The following table shows the DF to SQL type mapping for the SQL Server map schemas.
| DataFlex Type | SQL2000 | SQL2005 | SQL2008 | SQL2012 |
|---|---|---|---|---|
| DF_ASCII | Char | Char | Char | Char |
| DF_DATE | Datetime | Datetime | Date | Date |
| DF_DATETIME | Datetime | Datetime | Datetime2 | Datetime2 |
| DF_TEXT | Text | Varchar(max) | Varchar(max) | Varchar(max) |
| DF_BINARY | Binary/image | Varbinary(max) | Varbinary(max) | Varbinary(max) |
If no default mapping schema is specified, the SQL2000 mapping schema will be used. These mappings were also used with earlier Connectivity Kit versions.
Changing the mappings may have consequences for existing databases and applications. See below at Backward Compatibility.
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.
If no default mapping schema is specified, the SQL2000 mapping schema will be used.
See Also
Connectivity Kits Version 6