The DataFlex Connectivity Kit for ODBC fully supports the DataFlex
restructure capabilities. You can use any DataFlex program based on these capabilities to maintain your table’s data definition. This includes Visual DataFlex Database Builder.
Of course you can use your database’s utilities to maintain the tables. In most cases this is the preferred way. Most changes made to table definitions outside of DataFlex will be “seen” by the Connectivity Kit automatically. Others require a manual edit of the intermediate file for the table in question. Changes that require additional edit operations are: index definition changes and trigger additions for example.
If structure caching is used (the default), be sure to delete the cache files (*.cch) after changing the table definition outside of Database Builder. For more information on structure caching see Chapter 6 – Structure caching.
Database Menu
Database related options can be found in the “Database” menu of Database Builder. Relevant options are discussed in this section.
Load database driver
To be able to work with the DataFlex Connectivity Kit for ODBC from within Database Builder, the Connectivity Kit must be loaded. This can be done by choosing “Load database driver” from the “Database “ menu in Database Builder. In the open file panel choose odbc_drv.dll.
Alternatively you can load the Connectivity Kit when Visual DataFlex starts by adding the line “4096=ODBC_DRV” to the dfini.cfg Visual DataFlex
configuration file. If this is the case, the Connectivity Kit will be loaded automatically whenever Database Builder starts. For more information on dfini.cfg see the Visual DataFlex documentation.
A third way to “load” the Connectivity Kit is by opening a table that is accessed through it.
Once the DataFlex Connectivity Kit for ODBC has been loaded, the “Database” menu of Database Builder will have 4 additional choices:
Database Bui
ld
er
Table” and “ODBC Administrator”.
Once a non DataFlex database driver is loaded, the “Database” menu will have 2 additional choices: “Remove .INT extension” and “Add .INT
extension”. These choices enable you to switch between DataFlex and non- DataFlex data quickly, provided both forms of the data are present.
Once a CLI based database driver is loaded (DB2, ODBC or SQL Server), the “Database” menu will have one additional choice: “OEM Ansi Wizard”. This choice will start the OEM Ansi conversion wizard.
Login
The login panel allows the user to login to a Data Source. In the “Driver” combo box choose the DataFlex Connectivity Kit for ODBC by selecting ODBC_DRV. If the choice is not available you can load the Connectivity Kit by using the “Load driver” button.
In the “Server” form enter the connection string for the Data Source you want to login to. For a detailed discussion of connection strings see Chapter 7 - Intermediate File. In the “User Name” form enter the user name and in the “Password” form the password for the database.
Database Bui
ld
er
Logout
The logout panel allows the user to logout from a Data Source. In the “Driver” combo box choose the DataFlex Connectivity Kit for ODBC by selecting ODBC_DRV.
In the “Server” form enter the connection string for the Data Source you want to logout from. For a detailed discussion of connection strings see Chapter 7 - Intermediate File.
Convert to ODBC, Convert to ODBC from script
The two conversion options are discussed in detail in Chapter 4 - Converting Data to ODBC.
Connect To ODBC Table
The connection option is discussed in detail in Chapter 5 - Connecting to Existing ODBC Data.
ODBC Administrator
The ODBC Administrator menu choice starts the ODBC Administrator. The Administrator allows you to create, modify and delete ODBC Data Sources. See Appendix B - ODBC Data Sources for more details on the ODBC Administrator.
Remove .INT extension
The Remove .INT extension menu option is intended to change filelist entries so they point to DataFlex data instead of an intermediate file. When chosen
Database Bui
ld
er
you will be presented with a filelist selection panel. Select the filelist entries you want to remove the INT extension from.
The option only edits the filelist. The physical intermediate files will not be removed.
Add .INT extension
The Add .INT extension menu option is intended to change filelist entries so they point to an intermediate file instead of DataFlex data. When chosen you will be presented with a filelist selection panel. Select the filelist entries you want to add the INT extension to.
The option only edits the filelist. The physical DataFlex files will not be removed, nor will the intermediate files be created.
OEM Ansi Wizard
The OEM Ansi Wizard menu option will start the OEM Ansi conversion
wizard. The wizard allows you to convert tables from using the OEM character format to Ansi character format (or vice versa). For more information on character formats see Chapter 8 - Character formats (OEM or Ansi).
File menu
Table manipulation options can be found in the “File” menu of Database Builder. Relevant options are discussed in this section.
New
To create a new table in a Data Source choose “New” from the “File” menu. To illustrate the way to create a new table we will follow the steps needed to create a new table called NewSample for Microsoft Access 2000.
Database Bui
ld
er
In the “Type” combo box choose the DataFlex Connectivity Kit for ODBC by selecting ODBC_DRV.
Choose the desired Filelist number for the new table in the “File Number” spin form.
Enter the desired rootname for the new table in the “Rootname” form. You can use the .int extension or the driver prefix (odbc_drv:). The name of table that will be created will be the same as the rootname entered here, without the .int extension or the driver prefix.
If all information has been entered, press “OK”. This will start a file panel for the new table.
Database Bui
ld
er
To specify the Data Source in which the table will be created fill the “Login” form with the connection string for the desired data source. For a detailed discussion of connection strings see Chapter 7 - Intermediate File.
On the “Fields” tab page the columns for the new table can be defined. We have created one column per DataFlex type plus one record identity column. For every column you can define the name, type, length, assign a main index, its nullability (if supported by the backend) and a default value (if supported by the backend). The Nullable and Default value columns will default to the settings in the configuration file, see Appendix C – Configuration file ODBC_DRV.INT for more details.
Database Bui
ld
er
On the “Index” tab page indexes for the new table can be defined. We specify an index for the record identity.
On the “Parameters” tab page table level settings can be specified. Most of the settings on the page are not relevant for ODBC tables. Only the “Record Identity” form and the “System File” and “Use dummy zero date”
checkboxes are of interest. All other settings are either DataFlex database specific or read only.
We set the record identity to 6 the number of the RID column we created on the “Fields” tab page.
Database Bui
ld
er
If all information for the new table has been entered, it can be saved. In our case we create a Microsoft Access 2000 table.
If we re-open the created table and compare the definition to the one we originally specified we see a few differences.
On the "Parameters” tab page the “File Revision” form is now filled with “ACCESS 04.00.0000”
The process described above has created the Access table and the associated intermediate file, NewSample.int.
DRIVER_NAME ODBC_DRV SERVER_NAME DSN=AccessBig DATABASE_NAME NEWSAMPLE PRIMARY_INDEX 1 TABLE_CHARACTER_FORMAT OEM USE_DUMMY_ZERO_DATE YES FIELD_NUMBER 2 FIELD_LENGTH 10 FIELD_PRECISION 0
Database Bui
ld
er
FIELD_NUMBER 3 FIELD_LENGTH 10 FIELD_NUMBER 6 FIELD_LENGTH 10 FIELD_PRECISION 0 FIELD_INDEX 1 INDEX_NUMBER 1 INDEX_NAME NEWSAMPLE001 INDEX_NUMBER_SEGMENTS 1 INDEX_SEGMENT_FIELD 6Open
Tables can be opened by choosing the “Open” option in the “File” menu. To open a table through the DataFlex Connectivity Kit for ODBC select a Filelist entry that points to an ODBC table.
Once the table is opened you can change its definition. See the Visual DataFlex documentation on Database Builder for more information.
Open as
Tables that are not in the Filelist can be opened by choosing the “Open as” option in the “File” menu.
In the “File Number” form type the number you want to use to open this table.
In the “Rootname” form type the table’s rootname. If we want to open the table that was created in the New section above we must enter
Database Bui
ld
er
Load DEF
The “Load DEF” option is intended to create a new table based on an existing definition that is stored in a DataFlex .DEF file. A .DEF file can be generated by Database Builder in the “Output DEF/FD” option.
In the “File Number” form type the number you want to use for the new table.
In the ”New file’s type” combo form choose the DataFlex Connectivity Kit for ODBC by selecting ODBC_DRV.
In the “Rootname” form enter the rootname of the new table. You must use the .int extension or the driver prefix (odbc_drv:). The name of the table that will be created will be the same as the rootname entered here, without the .int extension or the driver prefix.
After the information has been entered, press “OK”. This will start a file panel for the new table. The table will be created when the file panel is saved. This enables you to make adjustments to the definition of the table before actually creating it.
Maintenance menu
Database maintenance can be started from the Maintenance menu. Most options in the menu are specific for DataFlex data and will not have any effect on data accessed through the DataFlex Connectivity Kit for ODBC. The options that are DataFlex specific are: “Reindex”, “Cleanup”, “Attributes”, “Repair” and “Recompress”.
Copy records
Database Bui
ld
er
another. It will map the columns of the source table to the columns of the destination table by comparing the column names. Columns with identical names will be mapped.
This option can be useful when during conversion it is desired to change the converted table’s definition in the target database. A lot of database systems only allow table definition changes if no data is present in the table. In such cases it is possible to convert the definition of the table only, leave the original intact. Then use the target database utilities to change the table definition. After that use the “Copy records” option to convert the data.