Data Access Worldwide White Papers.

Converting DataFlex Files to
Microsoft SQL Server

A Data Access Worldwide White Paper
by Marcia Ferreira

March 2003
Last Edited: June 25, 2003

Overview

The goal of this document is to help you through the process of converting DataFlex files to Microsoft SQL Server tables, consolidating in one place the information necessary for the first steps of a migration process.

Ultimately, it should help you in the process by laying out guidelines on how to get you started and what to watch out for. This is a work in progress.

Contents

·         Overview

·         Converting Your DataFlex Data

-          Preparing for Conversion

-          Loading the Driver

-          Utilities Available for Conversion

-          Standalone Database Builder

-          Important Conversion Options

-          The Conversion Process

-          What Changes After Conversion

-          Logging in to the Database

-          Alias Files

-          Intermediate (INT) Files

-          Cache Files (CCH)

-          Overlap Fields

-          Relationships

-          Indexes

-          Troubleshooting Conversion

·         Additional Reading/Resources

Converting Your DataFlex Data

When you have DataFlex data files and would like to move them to a different database backend – in this case, Microsoft SQL Server – you need to convert your files.

Preparing for Conversion

Before you can convert your files, make sure you have backup of the original data files. After that:

-          Check if the service (MSSQLSERVER) is running on the server

-          Check if you can access the Microsoft SQL Server server

-          Make sure the directory where the data files are does not have limited access (i.e. not set ReadOnly or some security setting)

-          Check if the DataFlex environment is set correctly – i.e. If running Character Mode DataFlex, DFRUNCON run without errors; if running VDF, Database Builder starts without errors

-          Make sure the data files are readable from DataFlex

-          Make sure a conversion utility is available – e.g. installed with the Connectivity Kit (for Character Mode)

-          Make sure you can load the Connectivity Kit driver

-          Make sure the registration is still valid, especially if you had a previous build of the Connectivity Kit installed

Note: Preferably, have the latest build of the Connectivity Kit installed. You can check for updates at ftp://ftp.dataaccess.com/pub/products/connectivity/updates/released/mssql)

^ top

Loading the Driver

To be able to use converted files in your applications or any of the available utilities, you need to have the Connectivity Kit loaded. You can have this done automatically or programmatically.

To have the Connectivity Kit automatically loaded, you need to add the following line to your DFINI.cfg DataFlex file:

4096=MSSQLDRV

Note: During installation you are asked if you want the Connectivity Kit to be automatically loaded. If you chose that option, your DFINI already contains that line

In your programs, you have to use the Load_Driver command to load the Connectivity Kit and Unload_Driver to unload it, as in the example below:

Integer iNumDriversLoaded

Get_attribute DF_NUMBER_DRIVERS to iNumDriversLoaded

Showln “Number of Drivers loaded before LOAD: “ iNumDriversLoaded

LOAD_DRIVER “MSSQLDRV”

Get_attribute DF_NUMBER_DRIVERS to iNumDriversLoaded

Showln “Number of Drivers loaded after LOAD: “ iNumDriversLoaded

UNLOAD_DRIVER “MSSQLDRV”

Get_attribute DF_NUMBER_DRIVERS to iNumDriversLoaded

Showln “Number of Drivers loaded after UNLOAD: “ iNumDriversLoaded

Inkey windowindex

^ top

Utilities Available for Conversion

The conversion process can be done programmatically – i.e. the developer will write his own program to convert the data files – or automatically – i.e. using utilities provided by Data Access Worldwide.

The utilities available are:

-          Database Builder – a utility that comes with Visual DataFlex Development product. If not using VDF version 8.3 or higher, you should use the Standalone Database Builder

-          Standalone Database Builder –a version of Database Builder for non-Visual DataFlex 8.3 or higher Developers that can be used without the entire Visual DataFlex Development suite. It can be downloaded from ftp://ftp.dataaccess.com/pub/products/connectivity/tools

-          CKMGR – a utility for DataFlex Character Mode Developers that gets installed when the following options are selected at the Connectivity Kit Components screen: DataFlex Character Mode Client and Tools & Utilities

Note: The CKMGR utility gets installed with its source code when, besides the indicated installation options, you select Development Install. If you would like to create your own conversion utility, that can give you a heads start in the process

^ top

Standalone Database Builder

If you use the Connectivity Kit with DataFlex Character Mode, you might want to download the Standalone Database Builder. That will allow you to use a DataFlex tool to update or modify the structure of your converted files.

If your revision of Visual DataFlex is prior to 8.3, we recommend downloading the Standalone version of Database Builder.

With this version of Database Builder you cannot manipulate your data dictionaries, only file structures.

If you are running DataFlex Character Mode or Visual DataFlex 7, the first thing you will need to do is to point to the directory where your Filelist and INT files are. Use the File | Select Alternative Path option to do this.

^ top

Important Conversion Options

The conversion options are present in two different places:

-          On the conversion screen

-          In the configuration file (MSSQLDRV.INT)

The conversion screen (from either Database Builder or CKMGR) has default options already selected. You should use all the default options (especially “Always use Record Identity” option – this way indexes containing RECNUM will be converted as unique indexes in the backend).  The “Delete original after conversion” option we suggest you uncheck it if you would like to keep the original DataFlex data files as a backup. It is usually a good idea to have the original files in case a problem occurs during conversion.

The configuration file has the defaults to be used by the conversion utilities.  For example, if your data files should use the ANSI character format, you can specify DEFAULT_TABLE_CHARACTER_FORMAT ANSI in your configuration file and that will be the format suggested on the conversion screen. Other defaults can be configured in the configuration file and three are quite important:

-          DEFAULT_NULLABLE_<data type>

-          DEFAULT_USE_DUMMY_ZERO_DATE

-          USE_CACHE.

The DEFAULT_NULLABLE_<data type> (e.g. DEFAULT_NULLABLE_ASCII) dictates if columns of that data type will allow null value or not. For performance reasons, we recommend all columns be set to NOT nullable. Refer to the User’s Guide – “Null Values” chapter – for a complete explanation.

The DEFAULT_USE_DUMMY_ZERO_DATE maps the DataFlex “0” (zero) date to a value recognized by the backend database. That keyword will turn on or off this mapping – the recommended setting is on (setting the keyword to any value different from zero). Once dummy_zero_date is on, the value used to map zero dates is 1753-01-01 for MS SQL Server.

The USE_CACHE keyword switches the use of cache files on and off.  The use of cache files (extension CCH) is important to improve speed when opening tables. The cache file will contain the table structure, which makes the driver save a trip to the server, since it gets that information from local files.

Other keywords can be used in the configuration file. Refer to the User’s Guide for more information.

Note: The configuration file is loaded only once, when your application – or any application using the Connectivity Kit – is started. You can use the driver commands and attributes to retrieve and set values to those options. Refer to the User’s Guide – “Commands and Techniques” chapter – for a list of those attributes and commands

^ top

The Conversion Process

The conversion process is simple. Once you choose the utility to be used and have all set up (see Preparing for Conversion), just run the utility and enter the server, database and user information at the conversion screen and the tables will start being created at the backend database.

DataFlex control files, like FlexErrs, Abdata, DDdata, Codemast and Codetype, still need to be in DataFlex format, do not convert them. Knowledge Base 1262 - INFO: Converting FlexErrs, etc. to different databases  (http://www.dataaccess.com/KBPrint.asp?ArticleID=1262) has more information on this.

Note: In MS SQL Server “server” means your computer name and the “database” is the name of the database that will hold all the tables created in the backend. User should only be entered if you want your INT file to contain that information – refer to the User’s Guide “Commands and Techniques” chapter for more information.

Note: Character Mode users need to start CKMGR using DFRUNCON – and not DFRUN

Important! If you have large data files, you should choose to convert them using the dump and load option for it would be faster than standard conversion – see the User’s Guide – “Converting Data” chapter – for more information.

A log file, called MSSQLDRV.LOG, is created in the data directory – you can review the information from that file to check the details of your conversion – and the FD for the converted files are recreated.

Besides those files, you will see files with extension INT in the data directory. Those are the files used by the Connectivity Kit to get to the tables in the backend – more on this later.

^ top

What Changes After Conversion

After converting your DataFlex files you will have other files in your data directory, as mentioned above, and the entries in your Filelist will have an INT extension. The files that will be opened by DataFlex applications that use that Filelist will be the INT files.

Note: Another way of opening files is through the use of Driver Prefix. Opening files using the driver prefix method is usually faster than using straight INT files because the Connectivity Kit does not need to go through an extra step of opening the INT to get the driver to be used with that file. Knowledge Base 1840 - INFO: Open Methods when Using CLI Connectivity Kits (http://www.dataaccess.com/KBPrint.asp?ArticleID=1840) talks about both methods.

Through the INT files you gain access to the tables in the backend. If no user information is found in the INT file and your application does not use the login command to login to the database, the backend will display a dialog for you to provide that information at connection time.

Note: The login command should be placed in the main module of your system. Once logged in, all the other programs called by the main program should have access to the tables through the same connection as the main program.

The way your programs used transactions might need to be adjusted in order to improve performance or correct deadlocks. For more information, refer to the User’s Guide – “Transactions” chapter – and the white papers Transactions, Locking and DataFlex and Compatibility Issues Between DataFlex Databases and Databases Accessed Through CLI Based Connectivity Kits Version 2.1.

Other aspects of your programs may need to be adjusted – such as removing unnecessary finds, using file_mode to set files to read_only, etc. For more information, refer to Knowledge Base 1841 - INFO: Improving Performance of MSSQL (http://www.dataaccess.com/KBPrint.asp?ArticleID=1841)

All the converted tables will have an extra field added to the end of their structure, called DFRECNUM. An index on only this field, which will use the last index number available, is created. DFRECNUM will be the Record Identity field for the converted tables and its index will be the tables’ Primary Index.

If you have DataFlex Character Mode, you need to be aware that some things change when using the Connectivity Kit.

-          After you converted your files to a different database backend (like MS SQL Server), all applications have to be started with DFRUNCON, and not with DFRUN

-          Some utilities installed with the product will not work on converted files:

o        DFFILE

o        DFSORT

o        DFQUERY (DFQ can be used with converted files)

o        DFAUTO

-          File structure maintenance should be performed with either the database backend utilities or with Standalone Database Builder

^ top

Logging in to the Database

Database servers need to authenticate the user before you can gain access to the databases and tables in the server. So, when trying to open a table in the backend database, your application needs to provide a valid user and password in order to use the tables there available.

The automatic way of doing it is by including the necessary information in the INT file being used to open the table. That information is included in the INT file if, during conversion, you enter it in the conversion panel.

If you want to login from the application – and not have the user information visible in the INT file – you can use the Login command as shown below.

LOGIN “SERVER=MySQLServer;DATABASE=MyDB” “MyUserid” “MyPwd” “MSSQLDRV”

For more information, refer to the User’s Guide – “Commands and Techniques” chapter.

^ top

Alias Files

When you have alias files in your Filelist, you do not need to select them for conversion. The only thing you need to do is to edit their entry in the Filelist to contain the iNT extension in the rootname.

If you select the original file and its alias for conversion at the same time, the original file gets converted and the Filelist entry for the alias file gets automatically changed to contain the INT extension.

^ top

Intermediate (INT) Files

The INT files contain connection information and other items that describe the tables with details that DataFlex needs but could not be placed in the backend database.

The Connectivity Kit will automatically be loaded when opening an INT file.

^ top

Cache Files (CCH)

The cache files are created if the USE_CACHE keyword is set to any integer value different from 0. They will contain the table structure information. Those files – by default placed along with the INT files – should be updated every time:

-          The table structure changes

-          The build of the Connectivity Kit changes

-          The location of the files changes

-          A different Connectivity Kit will be used to access the same tables

If you need to update the CCH files, just delete them and they will automatically be recreated.

If you want to set a different directory to hold the CCH files, change the configuration file (MSSQLDRV.INT) to contain the keyword CACHE_PATH (e.g. CACHE_PATH c:\MyApp\Data\Cache\).

^ top

Overlap Fields

The keywords Field_Overlap_Start and Field_Overlap_End are used to describe overlap fields. DataFlex overlap fields are translated into logical columns in the table; they will not be physically part of the table structure in the backend.

Underlap information is not automatically added to the INT files, you need to edit the file yourself and add that information. Underlaps cannot be part of indexes, since the indexes will be on the whole columns and not part of them.

^ top

Relationships

The relationships between files will be described in the INT files using the keywords Field_Related_File and Field_Related_Field.

^ top

Indexes

Two types of indexes can be defined in the intermediate files (INT): SQL Server indexes and DataFlex Only indexes.

The SQL Server indexes are the ones that physically exist in the backend. During conversion, the utilities will try to create in the backend all indexes define in DataFlex.

The DataFlex Only are only defined  in the intermediate files, they do not exist in the database.

The set of keywords to identify either index is different (Index_Number + Index_Name for SQL Server indexes; Index_Number + Index_Number_Segments + Index_Segment_Field + Index_Segment_Direction for DataFlex indexes) and is described in the User’s Guide – “Intermediate File” chapter.

When an index exists in the backend, it will be available to be used by the database optimizer and will probably have better performance than an index only logically defined (DataFlex only index).

^ top

Troubleshooting Conversion

If you had problems during the conversion process, you may want to do the following:

-          Make sure all the steps from Preparing for Conversion were followed

-          Examine the file MSSQLDRV.LOG. It will probably contain more information on the error generated during conversion

-          Add the keyword ERROR_DEBUG_MODE to MSSQLDRV.INT and set it to a non-zero integer. This may give you a more descriptive error message when running the conversion utility

-          Use Data Access free support resources: Knowledge Base, White Papers and Newsgroups

^ top

Additional Reading/Resources

^ top

Contacting Data Access Worldwide

Data Access Worldwide
14000 SW 119 Ave
Miami, FL 33186
305-238-0012
Domestic Sales: 800-451-3539
Fax: 305-238-0017
email: sales@dataaccess.com
Newsgroup Server: news.dataaccess.com
Internet: http://www.dataaccess.com

Data Access Worldwide - Asia Pacific
Suite 5, 333 Wantirna Road, Wantirna VIC 3152 Australia
Phone: +61 3 9800 4233 f: +61 3 9800 4255
Sales: asiapacific@DataAccess.com
Support: support.asiapacific@DataAccess.com
Internet: http://www.DataAccess.com/AsiaPacific

Data Access Worldwide - Brasil
Av.Paulista, 1776 - 21st.Floor
São Paulo -SP - Brazil
CEP 01310-921
Phone: 5511-3262-2000
Fax 5511-3284-1579
Sales: info@dataaccess.com.br
Support: suporte@dataaccess.com.br
Internet: http://www.dataaccess.com.br

Data Access Worldwide - Europe
Lansinkesweg 4
7553 AE Hengelo
The Netherlands
Telephone: +31 74 - 255 56 09
Fax: +31 74 - 250 34 66
Sales: info@dataaccess.nl
Support: support@dataaccess.nl
Internet: http://www.dataaccess.nl

Data Access Technical Support
800-451-3539 / 305-232-3142
email: support@dataaccess.com
Visit our Support Home page to see all of our Support options: http://www.dataaccess.com/support

Data Access Technical Knowledge Base    http://www.dataaccess.com/kbase
Many answers to technical problems can be found online in the Data Access Technical Knowledge Base. Here, you can access the same live data that Data Access Worldwide technical support and development staff use to enter and track technical articles. 

Copyright Notice
This document is property of Data Access Corporation. With credit to Data Access Corporation for its authorship, you are encouraged to reproduce this information in any format either on paper or electronically, in whole or in part. You may publish this paper as a stand alone document within your own publications conditional on the maintenance of the intent, context, and integrity of the material as it is presented here.

DataFlex is a registered trademark of Data Access Corporation.
WebApp is a registered trademark of Data Access Corporation.

NO LIABILITY FOR CONSEQUENTIAL DAMAGES
To the maximum extent permitted by applicable law, in no event shall Data Access Corporation be liable for any special, incidental, indirect, or consequential damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or any other pecuniary loss) arising out of the use of or inability to use any information provided in this document, even if Data Access Corporation has been advised of the possibility of such damages. Because some states and jurisdictions do not allow the exclusion or limitation of liability for consequential or incidental damages, the above limitation may not apply to you.

^ top