Converting Data

To convert data using any of the DataFlex SQL Drivers we recommend that you first create a Managed Connection (using the SQL Connection Manager in the Studio) for the workspace and then use the SQL Conversion Wizard from the Database menu in the Studio.

You can convert from any supported environment end to any other supported environment; for example you can convert Microsoft Access data to SQL Server.


In order to be able to convert data from one environment to another, the source data must comply with the rules and specifications of the target environment. If the source data does not follow the target database rules, it is impossible to convert the data. If such a case presents itself the only option is to adjust the source table definition and/or contents so it does comply with the target database rules. After adjusting the source table, the table can be converted. Adjusting a table’s definition may require adjusting programs that use the table.

Table Type

If the source table already is a standard table, it will be converted to a standard table.

The conversion wizard allows you to convert recnum tables to both standard and recnum tables. If the program that is using the source database uses the recnum programming style, the tables should be converted to recnum tables. If the program uses the RowId programming style, converting to standard tables is recommended. See Programming Styles for more information.

When converting to a recnum table a record identity column will automatically be inserted in front of the “normal” columns. The record identity column will be called “recnum” and is automatically hidden. The “recnum” column will have the identity behavior for the SQL Server and DataFlex DB2 Driver, for the DataFlex ODBC Driver this depends on the settings in the database configuration file.

Null & Default Values

The DataFlex SQL drivers use a configuration file to determine the “nullability” and default value of columns created in the conversion process. If no configuration file is found, the default settings will be used. You can specify a target setting per DataFlex type. For example, you can define that Numeric fields should be converted not to accept null values and use a default value of 0. See NULL Values and Defaults for more information.

If more control over the target settings is desired, convert the definition only and then adjust the “nullability” and default values to your specifications. After that, copy the data from the original table to the new converted table. This way a table that has column specific defaults can be created. The table definition can be adjusted through Database Builder or the SQL Server utilities.

We have found that null values can degrade finding performance considerably. It is recommended not to allow null values in indexed columns.

Table Character Format

DataFlex data in the embedded database is stored in OEM format. Non-DataFlex backends often expect the data to be stored in ANSI format. When defining the conversion options, you define the table character format to be used in the converted table.  For more information on this subject, see Table Character Format in DataFlex.


Converting Data to use DataFlex SQL Drivers


See Also

The N Prefix in SQL

DataFlex SQL Drivers

Understanding How Connectivity Works

Using Managed Connections

Connectivity and Development

Connecting to Existing Data

Using Embedded SQL

Connectivity Concepts