Working Without an INT File¶

A table can be opened without an INT file, provided a string containing table information is passed as an argument to the OPEN command. The string format is driver-specific. Parameters within curly braces are may or may not be required depending on your setup. The <numFields> option is to specify the number of columns the dataflex interface will have which might be lower than the number of columns in the SQL table.

Driver	Open Syntax
MS SQL	`Open "sql_drv:\\<server>{,port}{\<instance>}\<database>{<schema>}<table>{=<numFields>}" as <table>`
Oracle	`Open "ora_drv:\\<server>{:port}{\<pluggabledb>}\<owner>*<table>{=<numFields>}" as table`
PostgreSQL	`Open "mdspgsql:\\<server>{:port}{\<database>}{schema}<table>{=<numFields>}" as <table>`
MySQL	`Open "mdsmysql:\\<server>{:port}{\<database>}*<table>{=<numFields>}" as <table>`
MariaDB	`Open "f2s_md:\\<server>{:port}{\<database>}*<table>{=<numFields>}" as <table>`

Note

Prior to v17.1, MariaDB access was via the MySQL driver. Starting with v17.1 you must use the MariaDB driver.

Warning

Be aware that for some of the parameters, depending on the settings on the server, can be case-sensitive.

As an example, the following line can be used to open a table named Customer, from a local MS SQL Server, that is available in the pubs database, with dbo as the table owner (schema): Open "sql_drv:\\localhost\pubs*dbo*Customer" as customer

There are certain DataFlex attributes (for example, main index, file relationship, overlap fields, and field length) that are not defined when a table is opened without an associated INT file. You can use Set_Attribute commands to set these attributes and fully define the table as a DataFlex data file. Keep in mind that settings made using Set_Attribute are only valid while the table is open. As soon as you close the table, the settings are lost.

Setting Table Attributes Dynamically¶

The command block, Structure_Start_Dynamic / Structure_End_Dynamic, completely eliminates the need for an INT file. Instead, this command block allows the full table structure (which is generally kept in the INT file) to be passed to the driver during program execution.

Table settings are assigned inside the Structure_Start_Dynamic and Structure_End_Dynamic block using Set_Attribute commands. Set_Attribute commands called inside the block affect the data file structure that is kept in memory by the driver. No physical change to the table takes place.

Customization that can be done inside the dynamic setting block is generally the same customization that can be specified inside an INT file. Below are a few examples to show how this works. The examples replicate information from real INT files.

Note: Make sure that you update the Set_Attribute commands if the physical table structure is changed.

Example: Opening a System File¶

The following INT file was created during the ORDYS.DAT migration to MS SQL Server database.

DRIVER_NAME SQL_DRV
DATABASE_SPACE_NAME framework
DATABASE_NAME ordsys
SCHEMA_NAME dbo
SYSTEM_FILE YES
NUMBER_DF_FIELDS 3
PRIMARY_KEY 0
OPTIMIZE FOR_SET
MAX_ROWS_TO_QUERY 10
LOCAL_CACHE YES
FIELD_NUMBER 1
FIELD_LENGTH 6
FIELD_NUMBER 2
FIELD_LENGTH 6
FIELD_NUMBER 3
FIELD_LENGTH 6

To open the corresponding table without the INT file, while still retaining all the DataFlex file settings, we can do the following:

// Open the table without the INT file
Open "sql_drv:\\localhost\framework*dbo*ordsys=3" as ordsys

// set the DataFlex attributes using the dynamic settings
Structure_start_dynamic of ordsys
    Move ordsys.File_Number to iFileNumber
    Set_Attribute DF_FILE_IS_SYSTEM_FILE of iFileNumber To DFTRUE
    Set_Attribute DF_FIELD_LENGTH of iFileNumber 1 To 6
    Set_Attribute DF_FIELD_LENGTH of iFileNumber 2 To 6
    Set_Attribute DF_FIELD_LENGTH of iFileNumber 3 To 6
Structure_end_dynamic of ordsys

// At this point the Ordsys DataFlex data file has the same settings as
// if it was opened using an INT file

If you compare the information from the INT file with the information specified in the Set_Attribute commands calls, you see that although most of the attributes are defined, there are a few tokens from the INT file that are not specified. They are:

PRIMARY_KEY 0
OPTIMIZE FOR_SET
MAX_ROWS_TO_QUERY 10
LOCAL_CACHE YES

These attributes can also be set, but since these attributes are assigned their default values, the settings are not required.

Example: Opening a Table containing Index Definitions¶

Below is the INT file created when migrating the DAT file Salesp to an MS SQL Server database:

DRIVER_NAME SQL_DRV
DATABASE_SPACE_NAME framework
DATABASE_NAME salesp
SCHEMA_NAME dbo
NUMBER_DF_FIELDS 2
PRIMARY_KEY 1
OPTIMIZE FOR_SET
MAX_ROWS_TO_QUERY 10
LOCAL_CACHE YES
FIELD_NUMBER 1
FIELD_INDEX 1
FIELD_NUMBER 2
FIELD_INDEX 2
INDEX_NUMBER 1
INDEX_NUMBER_SEGMENTS 1
INDEX_SEGMENT_FIELD 1
INDEX_SEGMENT_CASE IGNORED
INDEX_NUMBER 2
INDEX_NUMBER_SEGMENTS 2
INDEX_SEGMENT_FIELD 2
INDEX_SEGMENT_CASE IGNORED
INDEX_SEGMENT_FIELD 1
INDEX_SEGMENT_CASE IGNORED

The following code opens this table and set its indexes segments as case insensitive segments using dynamic settings:

// Open the table without the INT file
Open "sql_drv:\\localhost\framework*dbo*salesp=2" as salesp

// Set the DataFlex attributes using the dynamic settings
Structure_start_dynamic of salesp
    Move Salesp.File_Number to iFileNumber
    Set_Attribute DF_FIELD_INDEX of iFileNumber 1 to 1
    Set_Attribute DF_FIELD_INDEX of iFileNumber 2 to 2

    // Index.1
    Set_Attribute DF_INDEX_NUMBER_SEGMENTS of iFileNumber 1 to 1
    Set_Attribute DF_INDEX_SEGMENT_FIELD of iFileNumber 1 1 to 1
    Set_Attribute DF_INDEX_SEGMENT_CASE of iFileNumber 1 1 to DF_CASE_IGNORED

    // Index.2
    Set_Attribute DF_INDEX_NUMBER_SEGMENTS of iFileNumber 2 to 2
    Set_Attribute DF_INDEX_SEGMENT_FIELD of iFileNumber 2 1 to 2
    Set_Attribute DF_INDEX_SEGMENT_CASE of iFileNumber 2 1 to DF_CASE_IGNORED
    Set_Attribute DF_INDEX_SEGMENT_FIELD of iFileNumber 2 2 to 1
    Set_Attribute DF_INDEX_SEGMENT_CASE of iFileNumber 2 2 to DF_CASE_IGNORED
    // Refresh the internal file structure
Structure_end_dynamic of salesp

Example: Opening a Table with Overlap Fields and Relationships¶

Below is the INT file created when migrating the DAT file Object to a MS SQL Server database:

DRIVER_NAME SQL_DRV
DATABASE_SPACE_NAME framework
DATABASE_NAME object
SCHEMA_NAME dbo
NUMBER_DF_FIELDS 15
PRIMARY_KEY 1
OPTIMIZE FOR_SET
MAX_ROWS_TO_QUERY 10
LOCAL_CACHE YES
FIELD_NUMBER 1
FIELD_INDEX 1
FIELD_RELATED_FILE 100
FIELD_RELATED_FIELD 1
FIELD_NUMBER 2
FIELD_INDEX 1
FIELD_NUMBER 3
FIELD_NATIVE_LENGTH 6
FIELD_INDEX 1
FIELD_NUMBER 5
FIELD_LENGTH 2
FIELD_NUMBER 6
FIELD_RELATED_FILE 150
FIELD_RELATED_FIELD 1
FIELD_NUMBER 8
FIELD_LENGTH 2
FIELD_NUMBER 10
FIELD_LENGTH 2
FIELD_NUMBER 15
FIELD_NAME obj_var_overlap
FIELD_OVERLAP_START 1
FIELD_OVERLAP_END 2
FIELD_INDEX 1
FIELD_RELATED_FILE 101
FIELD_RELATED_FIELD 15
INDEX_NUMBER 1
INDEX_NUMBER_SEGMENTS 3
INDEX_SEGMENT_FIELD 1
INDEX_SEGMENT_FIELD 2
INDEX_SEGMENT_FIELD 3
INDEX_SEGMENT_DIRECTION DESCENDING

Use the following code to open this Object table and set the overlap and relationship info:

// Open the table without the INT file
Open "sql_drv:\\localhost\framework*dbo*object=15" as Object

Structure_start_dynamic of object
    Move object.File_Number to iFileNumber
    Set_Attribute DF_FIELD_INDEX of iFileNumber 1 to 1
    Set_Attribute DF_FIELD_RELATED_FILE of iFileNumber 1 to 100
    Set_Attribute DF_FIELD_RELATED_FIELD of iFileNumber 1 to 1
    Set_Attribute DF_FIELD_INDEX of iFileNumber 2 to 1
    Set_Attribute DF_FIELD_INDEX of iFileNumber 3 to 1
    Set_Attribute DF_FIELD_NATIVE_LENGTH of iFileNumber 3 to 6
    Set_Attribute DF_FIELD_LENGTH of iFileNumber 5 to 2
    Set_Attribute DF_FIELD_RELATED_FILE of iFileNumber 6 to 150
    Set_Attribute DF_FIELD_RELATED_FIELD of iFileNumber 6 to 1
    Set_Attribute DF_FIELD_LENGTH of iFileNumber 8 to 2
    Set_Attribute DF_FIELD_LENGTH of iFileNumber 10 to 2
    Set_Attribute DF_FIELD_NAME of iFileNumber 15 to " obj_var_overlap "
    Set_Attribute DF_FIELD_TYPE of iFileNumber 15 to DF_OVERLAP
    Set_Attribute DF_FIELD_INDEX of iFileNumber 15 to 1
    Set_Attribute DF_FIELD_OVERLAP_START of iFileNumber 15 to 1
    Set_Attribute DF_FIELD_OVERLAP_END of iFileNumber 15 to 2
    Set_Attribute DF_FIELD_RELATED_FILE of iFileNumber 15 to 101
    Set_Attribute DF_FIELD_RELATED_FIELD of iFileNumber 15 to 15

    // Index.1
    Set_Attribute DF_INDEX_NUMBER_SEGMENTS of iFileNumber 1 to 3
    Set_Attribute DF_INDEX_SEGMENT_FIELD of iFileNumber 1 1 to 1
    Set_Attribute DF_INDEX_SEGMENT_FIELD of iFileNumber 1 2 to 2
    Set_Attribute DF_INDEX_SEGMENT_FIELD of iFileNumber 1 3 to 3
    Set_Attribute DF_INDEX_SEGMENT_DIRECTION of iFileNumber 1 3 to DF_DESCENDING

    // Refresh the internal file structure
Structure_end_dynamic of Object