A DataFlex driver in general must support certain functionality. Not all of that functionality is available for all target database formats. The intermediate file is the location where this type of information is stored. The contents of an intermediate file can be divided into three sections.
• A section that allows the Data Access API to determine the database driver to use
• A section that contains information needed before a table can be opened • A section that contains information needed after a table has been
opened
The general format of the intermediate file consists of lines of text separated by carriage return and line feed (CR/LF) characters. Each line may be up to 255 characters long and consists of a keyword and value pair or a comment. Keywords may be upper, lower or mixed case. Comments are preceded by the semicolon (;) character. In line comments are not supported. White space, defined as blank spaces and tab characters, is not significant and may be used for legibility.
The specific meaning and usage of intermediate file keywords are entirely dependent upon the specific database driver with the exception of the DRIVER_NAME keyword.
Every table that is accessed through the DataFlex Connectivity Kit for ODBC must have an intermediate file associated with it. The intermediate file specifies the location of the table by specifying a server, a database on the server and the table therein to connect.
Order of Keywords
Intermediate file keywords must be placed in the following order: a header, columns and indexes. The upcoming paragraphs will discuss all supported intermediate file keywords, the value they can be set to and the associated attribute, if any.
In
te
rme
dia
te
F
ile
<Keyword> (<Usage>)
Value <Possible values>
Associated attribute <Attribute_name> (<Type>)
Where
<Keyword> The keyword to set in the intermediate file.
(<Usage>) “Required.” Since most keywords are optional, this is not mentioned in the keyword’s description. It is only mentioned if a keyword is required, all others are optional. Optional keywords can be omitted; required keyword must be set.
<Possible values> A list of values or a description of possible values for the keyword
<Attribute_name> The name of the attribute associated with the keyword.
(<Type>) The type of the associated attribute. Associated Attributes
Some keywords have a so-called associated attribute. The behavior that is setup by such a keyword can also be setup by using the attribute. Attributes can be set by the Set_attribute command; their value can be queried by the Get_attribute command.
Setting up behavior through the intermediate file creates a global, persistent setting. Using the Set_Attribute command will result in a local setting only valid until the next Set_attribute command. It depends on your specific needs, which of the two ways should be used in a specific case.
The Primary_Index_Trigger keyword, for example, has an associated attribute DF_FILE_PRIMARY_INDEX_TRIGGER of type Boolean. There are two ways to switch the attribute on:
Primary_index_trigger YES ; Intermediate file line Set Attribute DF_FILE_PRIMARY_INDEX_TRIGGER of ;
MyTable.File_number To True
Alternatively, you can use the following to switch the attribute off: Primary_index_trigger NO ; Intermediate file line Set Attribute DF_FILE_PRIMARY_INDEX_TRIGGER of ;
In
te
rme
dia
te
F
ile
MyTable.File_number To FalseHeader
The keywords for the intermediate file header must be placed at the beginning of the intermediate file in the order they are discussed here. If an intermediate file does not have this information at the beginning of the file, the file and the associated table will fail to open.
Driver_Name (Required)
Value “ODBC_DRV”
Associated attribute DF_FILE_DRIVER (String)
The Driver_Name keyword is used by the Data Access API to determine the driver that must be used to open the table that is associated with the
intermediate file. The driver will be loaded if necessary. Normally, this
keyword has been parsed before the ODBC Client is called. The ODBC Client will then start parsing the rest of the intermediate file information. This
keyword must be the first keyword in any intermediate file.
Server_Name (Required)
Value Connection string
Associated attribute DF_FILE_LOGIN (String)
The Server_Name keyword must be set to a connection string. The connection string identifies the data source to connect to. The string can contain user information, including password, but that is not required. The string is made up of a number of Keyword=Value pairs separated by semicolons (;).
CONNECTION KEYWORD
DESCRIPTION OF THE VALUE
DSN Name of the data source to connect to.
UID A user id if needed by the data source.
PWD The password corresponding to the
user id name.
Configuration keyword Backend specific defined keyword. For a list of configuration keywords and
In
te
rme
dia
te
F
ile
CONNECTION KEYWORD
DESCRIPTION OF THE VALUE
their description see your database’s manual.
For example, if we want to connect to the table Department, in the data source Company, we would create an intermediate file, called dept.int, with the following content:
DRIVER_NAME ODBC_DRV SERVER_NAME DSN=Company DATABASE_NAME Department
Database_Name
1Value The name of the table to connect to Associated attribute None
The name of the table associated with the intermediate file. If this keyword is not set, the intermediate file name (without the .int extension) will be used.
Schema_Name
Value The name of the database owner
Associated attribute DF_FILE_OWNER (String)
The name of the schema the table belongs to. A schema is a collection of names or objects. A schema can contain tables, views, and triggers. Schemas provide a logical classification of objects in the database. If the keyword is not set, the user ID used to login to the data source is used as the schema name.
1 The Database_name keyword was created and designed before actually
connecting to SQL databases. This is why it has the “old” DataFlex meaning of table. In a future revision this may be adjusted to a more SQL oriented keyword.
In
te
rme
dia
te
F
ile
Table Keywords
The following keywords set attributes on table level. They must be set directly after the intermediate file header and before the column keywords (if any).
Dummy_Update_Column
Desktop databases usually do not support positioned updates. In these cases a different update mechanism, called SQLSetPos that allows for records to be locked is used. If the backend does not support exclusive locks on records, the Connectivity Kit will perform a “dummy update” to lock the record. This mechanism will ensure that records are locked when they are found while in a transaction. Since most ODBC drivers do not support table locking there is a difference between DataFlex locking and ODBC locking.
The dummy update mechanism updates a record directly after it has been found. The record identity column is set to the value just got from the find. This happens in the find logic. If the record identity column is not "updatable", it could be an auto increment for example; you can setup an alternative column by using the intermediate file keyword DUMMY_UPDATE_COLUMN. The Dummy_Update_Column keyword should be set to the number of the column you want to "dummy update". Column numbers start at 1.
A lot of data sources use a so-called optimistic locking strategy. DataFlex programs cannot handle this strategy. Most environments allow the user to change the locking strategy from optimistic to pessimistic. In order for DataFlex programs to correctly lock data, the locking strategy must be set to pessimistic locking!
For Access, one needs to setup pessimistic locking as described in the following article:
http://support.microsoft.com/support/kb/articles/Q225/9/26.ASP. At this moment there is no facility to determine how the backend in use supports locking. There are several ways this can be done depending on the backend in use. Possible behaviors are:
• Positioned updates. If the backend supports positioned updates, locking records is supported, no dummy updates are required.
• SQLSetPos + exclusive lock. Some back ends support the SQLSetPos logic with the possibility to lock a record exclusively. No dummy updates are required.
In
te
rme
dia
te
F
ile
locks on records, a dummy update will be done directly after a record has been found while in a transaction. In this case, the
DUMMY_UPDATE_COLUMN intermediate file setting is used to determine the column to use in the dummy update operation.
• None. Some back ends are read only and do not support locking at all. Tested environments are:
Environment Update behavior DB2 UDB v7.1 Positioned updates MS SQL 7.0 Positioned updates Sybase ASA 7 Positioned updates Oracle 8.0 Positioned updates Oracle 8i Positioned updates
MS Access 2000 SQLSetPos + dummy update
Get_RID_After_Create
Value YES, NO
Associated attribute DF_FILE_GET_RID_AFTER_CREATE (Boolean)
This keyword handles the behavior of the Connectivity Kit when the
Primary_Index_Trigger keyword is set to YES. After records are created in a table with a triggered primary index, the assigned record identity is moved to the record buffer used in DataFlex. Setting this keyword to NO will switch of moving the new identity to the record buffer. This eliminates a client server communication roundtrip, thus speeding up performance when creating records.
Note: Be aware that setting this keyword to NO can result in unwanted or erroneous behavior. Switching off the move behavior can speed up bulk creation of records. This keyword will be rarely used; normally you will only use this attribute from within the program logic where it will be used in massive creating of records. In such cases it will be switched off, then the records are created after which the attribute is switched back on.
In
te
rme
dia
te
F
ile
Max_Rows_Fetched
Value 0 ..Associated attribute DF_FILE_MAX_ROWS_FETCHED (Positive integer)
The number of records result sets of find operation on this table should be limited to. The default value is 0 (zero), which means that result sets will not be limited. All other positive integer values will limit the result set to that value. ODBC uses SQL as its language to manipulate data. SQL is a set oriented language. Every statement works on set(s) and result in a set. A DataFlex find command will be translated into its SQL counterpart, which is sent to the database server. The SQL statement will result in a set of rows that satisfy the condition of the statement for the find command. This result set can have no, one or more rows. In some cases, a result set will contain all rows of a table. Limiting the result set can improve performance. Unfortunately, there is no rule that can be followed when using this attribute. The best setting for the attribute depends on the program logic and may vary in different functional areas in one program. Normally, the best setting to use when experimenting with values for this attribute is 0, 1 and the number of records that are shown on the screen (in lists).
Primary_Index
Value 1.. highest index number defined for the table Associated attribute DF_FILE_RECORD_IDENTITY (Integer) The number of the identity index. The DataFlex API requires every table to have a record identity. This is a column in the table that uniquely identifies a row in the table. This column should be numeric and an index should be defined for it. For more information on record identity, see Chapter 10 - Record Identity.
NOTE: The DF_FILE_RECORD_IDENTITY attribute uses column numbers. The Primary_index intermediate file keyword uses index numbers. When setting up the primary index in the intermediate file the record identity field is set indirectly.
In
te
rme
dia
te
F
ile
Primary_Index_Trigger
Value YES, NOAssociated attribute DF_FILE_PRIMARY_INDEX_TRIGGER (Boolean)
Indicates that the record identity column is filled by the backend automatically when creating records. After records have been created, the Connectivity Kit will move the assigned number to the column by performing a “select (max(recordidentitycolumn)) from table” statement.
The default value for the keyword is NO.
Refind_After_Save
Value YES, NO
Associated attribute DF_FILE_REFIND_AFTER_SAVE (Boolean)
If triggers are defined on a table that fill one (or more) columns in a row that is created or updated, you may use this setting if you want these new column values to be available directly after the save operation. Setting this keyword will ensure that the record buffer is up to date. Setting this keyword to NO, the default, will switch re-fetching the row off. This eliminates a client server communication roundtrip, thus speeding up performance when creating records.
System_File
Value YES, NO
Associated attribute DF_FILE_IS_SYSTEM_FILE (Boolean)
Indicates if the table is a DataFlex system table. System tables in DataFlex are treated in a special way. They should contain one record and one record only. When a system table is opened, the first (and only) row in the table will be placed in the table’s record buffer.
DataFlex determines this attribute by the maximum number of records. In ODBC we cannot set a maximum number of rows. Be aware that the number of records in the table does not need to be one. This is determined by
In
te
rme
dia
te
F
ile
program logic, not by the Connectivity Kit. If there is more than one record, the record that will be placed in the record buffer is the first one according to the record identifier sort order.
Table_Character_Format
Value ANSI, OEM
Associated Attribute DF_FILE_TABLE_CHARACTER_FORMAT (String)
The format of the data in the table. This format can be set to Ansi or to OEM. DataFlex programs use data in OEM format. Most Windows applications expect data to be in Ansi format. If you want to access data from DataFlex and some other Windows tool, it may be required to store the data in Ansi format.
Be aware that setting this value by editing the intermediate file will only change the way data is presented and new data is stored. Existing data will not be converted from OEM to Ansi or vice versa. You need to run a conversion utility to convert existing data.
If you are creating new tables (when converting, by using Database Builder or doing a structure operation) the setting of the driver configuration keyword DEFAUL_TABLE_CHARACTER_FORMAT will be used to determine the initial setting of the attribute.
For more information on character formats see Chapter 8 - Character formats (OEM or Ansi).
Use_Dummy_Zero_Date
Value YES, NO
Associated Attribute DF_FILE_USE_DUMMY_ZERO_DATE (Boolean) Indicates if dummy zero dates must be used for date columns that do not allow null values. The setting has no effect on date columns that allow null values. When set to YES, the Connectivity Kit will translate the DataFlex zero date to the dummy zero date value “0001-01-01”. This avoids problems that can arise with sorting on indexes that use the date columns. For more information see Chapter 11 - NULL Values and Defaults.
In
te
rme
dia
te
F
ile
Column Keywords
The following keywords set attributes on column level. They must be set directly after the table keywords and before the index keywords (if any). Column keywords are grouped per column. If you want to set a column keyword, the column must be identified first (Field_Number), and then you can set one or more column keywords for that particular column. The keywords will apply to the last Field_Number that was specified.
Most of the keywords discussed will be automatically set by the Connectivity Kit and will never be added to the intermediate file. The length of a column for example, is defined in ODBC and normally this is not changed on the
DataFlex side. In some situations, you may want to change the attribute on the DataFlex side only. In those cases, you should use the keywords or associated attributes to setup the desired value.
Field_Index
Value 0 .. highest index number defined for the table
Associated attribute DF_FIELD_INDEX (Integer)
This keyword will set the main index attribute for a column. If this attribute is not set in the intermediate file, the DF_FIELD_INDEX attribute will be set to the first index the column appears in as a segment. See the DataFlex documentation for more information on main indexes.
Field_Length
Value 1 .. Maximum length for the type of the column
Associated attribute DF_FIELD_LENGTH (Integer)
The Connectivity Kit will get the lengths defined in ODBC. If you want to define a different length in DataFlex from the length in ODBC, you should use this setting. It defines the length of the column (together with Field_Precision). This keyword is normally used to make sure that two relating columns have the same length. This can be a problem when relating between tables of different back ends. For example, DataFlex will report the length of a numeric field as a multiple of 2. ODBC supports lengths for numeric data that is not a
In
te
rme
dia
te
F
ile
multiple of 2. If you want to relate between an ODBC numeric column of length 3 and a DataFlex field of length 4, you can adjust the field length of the ODBC column in DataFlex only.
Field_Name
Value The name of the column as reported by DataFlex
Associated attribute DF_FIELD_NAME (String)
The name of a column as reported by the Connectivity Kit. The keyword is generally used to setup overlap column names. It can also be used to setup a different name for a DataFlex environment from the name in ODBC.
There are column names that are legal in the data source but not in DataFlex. If you want to access a table with such a column, use the Field_Name
keyword to set it to a legal DataFlex name. An example of this type of name would be File_Number.
Field_Number
Value 0 .. Maximum number of columns in a table. Associated attribute DF_FIELD_NUMBER (Integer)
The Field_Number keyword defines a column keyword group. All subsequent column keyword settings will apply to the column with the number specified. The column number will change when another Field_Number keyword is used.
It is not possible to separate settings under multiple column groups. Every new column number will overwrite any preexisting settings.
Field_Overlap_Start, Field_Overlap_End
Value 1 .. Number of columns in the table
Associated attribute DF_FIELD_LENGTH (Integer), DF_FIELD_OFFSET (Integer)
The Field_Overlap_Start and Field_Overlap_End keywords are used to define an overlap column. An overlap column is a logical column that “overlaps”