Table Restructuring¶

You can restructure and manage your database in a variety of ways:

• One way is to use the Flex2SQL Classic Migration Utility restructuring dialogs and your own restructuring code.

• Another option is to make SQL Server the main repository for your database structure and use available SQL modeling tools.

• You can also restructure and manage tables using DataFlex Studio or Database Builder.

It all comes down to what you treat as your source of truth. Either you can view the database server as the source of truth and synchronize the files DataFlex uses (INT’s and FD’s) to that or you can view your DataFlex files or code listing as the source of truth and use that to synchronize the structures on the database server.

Database Server as the Source of Truth¶

You can make the database server the source of truth for your database structure and use available SQL modeling tools to manage your database. Then, you can run the Flex2SQL Classic Migration Utility Generate .INT File from Table/View/Synonym feature to generate .INT and .FD files from the modified tables and bring your changes back into DataFlex.

Select Tools > Generate.INT > .INT File from Table/View/Synonym to display the Generate INT File dialog box.

The grid contains the following columns:

Column	Description
Check Box	Select the check box to mark the table for importing into DataFlex.
List icon	A green arrow over the list icon indicates the table already exists in DataFlex.
Table Name	The name of the SQL table on the server.
File Number	The existing (if the table already exists in DataFlex) or proposed (for a new table) file number.
INT File Name	The intermediate file name for this table.
FD File Name	The field offset definition file name for this table.

Warning

You can change the file numbers in the grid. However, we recommend not changing the files number for existing tables, or you may end up with duplicate tables in the filelist, but a single INT file.

Select a Fetch radio button on the right to choose the type of object to be retrieved from the DBMS.

The following import options are available:

Option	Description
Import SQL relationships	Select to import the SQL relationships into DataFlex. Be sure to check this option. Relationships need to be maintained in the INT file to ensure that DataFlex operates as expected.
Show INT File info view	Select to display the Create Intermediate File dialog box, which allows you to choose additional import settings. Additional settings are not usually required.
Insert into Filelist	Select to insert the imported tables into the DataFlex filelist.cfg file.
Generate FD File	Select to generate the FD file.

Warning

When a DataFlex file is migrated to an SQL backend, the Flex2SQL Classic Migration Utility creates the SQL Table using the same DataFlex field names and the proper SQL data types. The Flex2SQL Classic Migration Utility also creates an INT file that contains DataFlex information such as relationships, underlaps, special field lengths and data types. The INT file allows the driver to connect to the SQL table and to make sure that DataFlex works as you expect.

Recreating the INT file from a previously migrated table overwrites the current INT file and the DataFlex settings like underlaps, relationships, etc. are lost.

Note

Check the Import SQL relationships option to include the SQL relationships in the INT file. The Flex2SQL Relationship Synchronization option also allows you to retrieve the relationship information from the server into the .INT file.

Accessing Updated Tables in DataFlex¶

1. Start the Flex2SQL Classic Migration Utility and select Tools > Generate > Generate .INT File from Table/View/Synonym.

2. Fetch the desired tables or views from the SQL database.

3. Check the tables (or view) for which you want to import or update INT files (you can also use the Select buttons).

4. Click OK. The .INT files are generated and added to your Filelist with the driver prefix.

5. When prompted, select a Filelist slot for the file.

6. Open DataFlex Studio or Database Builder and select the file. Make sure the filename includes the driver prefix.

7. The Mertech Login dialog box is displayed. Enter the login information. After login, Database Builder shows the file structure information.

8. Generate the Data Dictionary files (.DD files) for the table.

9. Open DataFlex Studio and generate a view using the view wizard and the .DD generated in the previous step.

DataFlex as the Source of Truth¶

Mertech also supports restructuring and management of tables using DataFlex Studio or Database Builder. You can use DataFlex as the source of truth for the structure of your database. The Mertech drivers and DataFlex will ensure that all updates are reflected in the SQL backend, INT files, and any FD files.

The screenshot below shows a column being added to the Users table from the previously migrated order entry example.

Note

The DF_FILE_ROOT_NAME includes the Mertech driver prefix.

DataFlex 18.0 added support for declaring and editing native data types in the Table Editor. This means DataFlex developers can use the Table Editor to set the native (SQL) type for table columns instead of being limited to the default mapping from DataFlex types to native types.

For example, the Mertech driver natively supports GUID column types for some backends. The Table Editor now enables developers to set a column type to GUID even though DataFlex does not have a native GUID type. Prior to DataFlex 18.0, developers had to use the Flex2SQL Migration Utility to do this, or edit tables directly in the tools provided by the database vendor.

Flex2SQL v14.0 and higher fully support this new DataFlex feature.