Multi-Company Setups

It is common in the DataFlex and Pervasive environments to create files with the same name residing in different directories. For example the C:\data directory might contain subdirectories for ACME company (C:\Data\Acme\) and Widget company (C:\Data\Widget\). Each company subdirectory might contain a list of sales people (salesp.dat).

Consolidating Directories.jpg

The problem on the SQL side is that different tables with the same names will collide if stored in the same database. Mertech provides several ways to resolve this problem. You can

  • Migrate to multiple SQL Servers or databases

  • Migrate to different schema names

  • Attach a custom prefix or postfix to the table names

While all of these options solve the problem, you end up with as many SQL tables as you had .DAT files. It would be nice to be able to consolidate the data into one table.

Create the Table and Copy the Data

The following steps can be taken in Flex2SQL to consolidate matching files from multiple data folders:

Note

This assumes that the table structures in the different folders are identical.

  1. Add a new field to each file that can be used to identify the data source. Since the table is still a DAT file at this point, use the DF Studio or Database Builder to add the field. We would add COMPANY_ID to each .DAT file in the above example.

  2. Populate the new field to uniquely identify the source. We would put the value “ACME” in the new COMPANY_ID field in the c:dataACMEsalesp.dat file, and “Widget” in the COMPANY_ID field in the second folder.

    Note

    Please contact Mertech if you would like input into how to automate this process based on your particular scenario.

  3. Migrate the first file to your SQL Server (Filelist, select file, Convert Database). This creates the SQL table and copies the data from the first file into the table.

  4. Generate an .INT file for a matching file in another folder (Filelist, select file, Tools > Generate .INT > Generate INT from .DAT). This will create an .INT file in your data directory, and allow you to access the table that was migrated earlier in SQL

  5. Copy data from the .DAT to the SQL table (right-click the file, Copy Data from .DAT to Table).

Repeat steps 4 and 5 for any additional matching files in other data folders. You now have one SQL table that contains the data from all the copies of the given table, with a field that identifies the source for each row.

Update your Application

Now that the files have been merged into one SQL table, there needs to be a way to easily distinguish data from one original file to the others with no or minimal changes to the DataFlex application.

The DF_FIELD_PROGRAMMATIC_DEFAULT attribute is defined to store a default value for a given field. The driver will use this default value during inserts if no explicit value is moved to the field buffer. Locate the point in your application where you allow the user to select the working data folder. Once a new data folder has been selected, issue a call to:

SET_ATTRIBUTE DF_FIELD_PROGRAMMATIC_DEFAULT OF <TABLE>.FILE_NUMBER <FIELD NUMBER> TO <VALUE>

In our case: SET_ATTRIBUTE DF_FIELD_PROGRAMMATIC_DEFAULT OF SALESPS.FILENUMBER 1 TO “ACME” Where field number 1 is COMPANY_ID, and the user selected data folder is C:\Data\Acme\.

When DF_FIELD_PROGRAMMATIC_DEFAULT is set, if no value is moved to the field marked as the programmatic default field, the value given in the attribute is saved to the field when a new record is created. In our example, any new data inserted when working in the C:\data\ACME\ folder will have “ACME” stored in the COMPANY_ID field. This is without making any other changes to your code.

The SQL_SET_CONSTRAINT macro command has been extended to constrain finds to only return data belonging to this new programmatic default field.

Example: To constrain finds to only return data belonging to “ACME”

Set_Attribute DF_FIELD_PROGRAMMATIC_DEFAULT of salesp.file_number 1 to "ACME"
SET_SQL_CONSTRAINT of salesp From_Programmatic_Defaults
SQL_CONSTRAINT of salesp to ACTIVATE
Repeat
    Find Gt salesp by Index.2
    Showln salesp.last_name " - " salesp.state
Until (not(Found))

This ensures that data belonging to other companies is not retrieved. Again, this is without modifying any source code outside of where tables are opened and the working directory is set.