Studio Connect Wizard

The Connect Wizard is now being used for three main purposes:

Connect: Connect to an existing SQL table that is currently not connected to DataFlex.
Refresh: Refresh an existing connected SQL table.
Custom: Customized connects, reconnects, and refreshes.

Connected Tables Status

A table is considered connected if the SQL table is already “in filelist.” There are different levels of being connected:

Fully Connected: There is a filelist entry which has an INT file, the INT file’s DRIVER_NAME matches the driver being used, and the INT file’s DATABASE_NAME and SCHEMA_NAME match the SQL Table’s names. Normally, the Filelist’s Logical_Name will match the table’s name, but that is not a requirement.
Partial INT Connected: This is the same as fully connected except the DRIVER_NAME does not match. You might get this if you attempt to connect to a table that is already connected via a different driver.
Filelist Connected: The SQL table’s name is the same as a filelist entry’s Logical_Name, but it does not have an INT file it can use. This might be an embedded table or some other strange condition, like an SQL table that is missing an INT file.
Not Connected: None of the above. This is typically what you’d get when newly connecting to a SQL database.

What Gets Changed

None of these options change the actual SQL table. These options only change the DataFlex side of a table’s definition. This means that the only changes made are in filelist.cfg and the various table INT files, which normally reside in your workspace’s data directory. This means:

If you make a backup of these files before you perform any of these connects, you can always restore your definitions back to that prior state.

You can change the connected tables status by altering the contents of the filelist, by editing INT files, and by removing INT files. Doing so would be considered an advanced technique, and one of the main goals of the Connect Wizard is to make these kinds of low-level changes unnecessary. However, there is no magic to this. If a table has a filelist entry which points to a valid INT file, its table connect status is fully connected. If you remove an INT file but keep an entry in the filelist, that table will be Filelist connected. If you remove an entry from the filelist, that table connect status will be not connected.

The Table Connect States

Connect

Connect to an existing SQL table that is currently not connected to DataFlex.

You use this option any time you want to connect to an SQL table that you have not yet connected to. This implies that you know nothing about the table other than what is defined in the SQL table. You have no filelist preference – it will just get assigned to the next free slot.

If the table’s connection status is anything other than not connected, it cannot be selected for connection.

This is a safe option in that it will not overwrite any entry or INT file that is already in the filelist.

(Current limitation is that there is no way to connect with a custom table name or to connect two tables from different sources that have the same table name.)

Refresh

Refresh an existing connected SQL table.

This option allows you to select tables that are already connected and refresh them. This can be used to reconcile any changes made to a table by a backend table designer or a script. This uses the information in the SQL Table and any existing information in the INT file to create a new synchronized INT and FD file definition.

The expectation is that any SQL table definition changes will be sensible, such as adding new columns, adding/changing indexes, or changing field lengths. Bigger changes may create inaccurate INT definitions. These can be corrected using the Studio’s table editor or by using the Custom connect option using the filelist slot but ignoring the existing INT information.

Using existing INT information can be important. That INT information often contains custom table definition information that cannot be obtained solely by connecting and looking at the SQL table’s definition. Examples of this are OEM/ANSI settings, custom DF field types, custom DF field lengths, mapping of index names to index numbers, definition of client-side indexes, and relationship information. Using a properly defined INT file provides a table definition that is more refined than what you would get with a simple connect.

One of the expectations of this option is that you can refresh a table at any time, and if there are no backend changes, there will be no INT changes. When in doubt, there should be no reason not to refresh.

(It will be our job to make a refresh as safe as possible.)

(Currently, this also updates connect IDs to whatever you’ve set them to. Should it?)

Custom

Customized connects, reconnects, and refreshes.

The Connect and Refresh options should make simple sense. You either connect to a currently unconnected table, or you refresh an already connected table. The Custom option, which is more complicated, can be used when one of the first two options cannot be used. Some examples of why you would use custom connect are:

You want to perform a connect on a table that is already in the filelist. Even if there is an INT file, you want to ignore it and start over while using the existing filelist slot. You might do this if:
Your INT is corrupted.
Your INT file is missing.
Table definition changes made by the backend table designer are extensive enough that the current INT definitions are best ignored.
Your initial connect made some bad choices (dummy dates, OEM/ANSI, etc.) and you’d like a do-over. (Using this option might be easier than removing those tables from the filelist and doing another connect.)
The tables and their INT files are currently connected using a different driver, and you wish to change drivers. In this case, you want to use as much of the information you can from the existing INT file and apply it to the new INT file.
Your tables are defined as embedded in the filelist, and you wish to switch over to existing SQL tables with or without existing INT files (kind of odd – but you never know).
You wish to change the server connection strings in INT files so that tables use a common connection ID or vice versa.
You want to do a refresh that checks for new foreign key relationships.

Of Note

The wizard lets you select a server via a server string or a managed connection. Whichever you choose will be used when writing out an INT file. When doing a refresh, this provides a way to change all of the tables over to connect IDs (or back). This might be a little heavy-handed, and we could add an option where an INT-based refresh would keep whatever server string it had. We didn't do this because we couldn't think of any reason why you wouldn't want this conversion to take place.
When connecting, we have options for OEM/ANSI and dummy dates. When refreshing, we use the information in the INT file.
There is a new issue when connecting to a table asking if you want SQL datetime to be mapped to DF Date or Datetime. Since we’ve used SQL datetime for dates for years, it is possible that a developer will want a datetime to date mapping (Woody had such an issue). When reconnecting or refreshing using an INT file, the table always uses the INT’s definition for this.
When connecting, a non-unique SQL index will get defined as a client-side unique index, making it usable. When refreshing, new non-unique indexes are ignored.
This should let you convert from our ODBC driver or the Mertech SQL driver to our MSSQL driver. It will use information from the INT file to handle a bunch of things like field type, relations, indexes, etc.
We may want to look into using related fields when the DF Field_Length has different settings for the related to and related from fields. This can happen when one INT file has a FIELD_LENGTH and the other does not. It's possible the runtime and the driver don't care, but the Studio (and maybe DBB) are not allowing these relationships to be defined. This needs to be researched.