Working with new SQL Server 2008 data types

DataFlex only supports five physical data types: NUMERIC, ASCII, DATE, TEXT and BINARY. The sixth, OVERLAP, is a logical type which maps to any one of the physical types. In the SQL world however, there are many different data types that the driver has to support. This is especially important when you are connecting to applications using the same SQL server as your DataFlex application but developed in other languages.

In Mertech's v10 and v11 drivers, we worked to ensure that we support all data types that can be mapped to a DataFlex data type. For example, in SQL 2008, new types like DATE, TIME, DATETIME2, DATETIMEOFFSET were added along with some new the Spatial Data types. So how do we map these new data types the SQL Server driver?

Let us start with the most important one DATE. Prior to SQL 2008, MS SQL Server only supported the DATETIME data type. The DataFlex Date type only contains the date part and not the year part. In order to support the dates properly in DataFlex and to ensure consistent finds, the driver would save all the dates with a time of 12:00:00:00 am. When it retrieved the data, the time part would be stripped. We did support retrieving the time part separately in a round about way.

With SQL Server 2008, Microsoft introduced a DATE which is closer to native DataFlex date type (without the time part) and Visual DataFlex introduced a DATETIME type which includes the time part. The driver handles the default mappings depending on the version of VDF and MS SQL Server you are using. The following list defines how the driver defaults are mapped for handling dates.

Other new data types (see table below) cannot be mapped directly to a DataFlex type, therefore they are Read Only and all map to DF_BINARY. Read Only means that you can read that Data in raw form into DataFlex but won't be able to write to them.