The general behavior of the DataFlex SQL Server Driver can be configured through the configuration file, MSSQLDRV.INT. The configuration file is read when the driver initializes.
Configuration files can be located anywhere in DFPATH. In general one configuration file per installation is enough. There are situations where there is a need to have different configurations for different deploy environments on one machine/network. In that case the configuration file should be placed in the deploy environment rather than in the overall DataFlex environment.
The example configuration file (MSSQLDRV.INT) is installed in the DataFlex Bin directory.
The keywords will be presented in the following format:
<Keyword>
Value <Possible values>
Associated Attribute <Attribute_Name> (<Type>)
<Keyword> The keyword to set in the intermediate file.
<Possible values> A list of values or a description of possible values for the keyword.
<Attribute_Name> The name of the database API attribute associated with the keyword.
(<Type>) The type of the associated keyword.
The supported keywords for the global intermediate file are:
0 or 1
None
Determines whether the driver attempts to auto_reconnect or not. The default value is 1 (Auto_Reconnect On)
Path to a valid directory
Sets up a directory to store cache files. By default, cache files will be stored in the same directory as the corresponding intermediate file.
Default specification
DF_DRIVER_DEFAULT_DEFAULT_ASCII
Sets up the default value that will be used when an ASCII field is created. Fields can be created during conversion or within a restructure operation.
Default specification
DF_DRIVER_DEFAULT_DEFAULT_BINARY
Sets up the default value that will be used when a Binary field is created. Fields can be created during conversion or within a restructure operation.
Default specification
DF_DRIVER_DEFAULT_DEFAULT_DATE
Sets up the default value that will be used when a Date field is created. Fields can be created during conversion or within a restructure operation.
Default specification
DF_DRIVER_DEFAULT_DEFAULT_DATETIME
Sets up the default value that will be used when a DateTime field is created. Fields can be created during conversion or within a restructure operation.
Default specification
DF_DRIVER_DEFAULT_DEFAULT_NUMERIC
Sets up the default value that will be used when a Numeric field is created. Fields can be created during conversion or within a restructure operation.
Default specification
DF_DRIVER_DEFAULT_DEFAULT_TEXT
Sets up the default value that will be used when a Text field is created. Fields can be created during conversion or within a restructure operation.
Integer value
DF_DATABASE_DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA, DF_DRIVER_DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA
Determines the default mapping schema setting for all DataFlex types.
Integer value
DF_DRIVER_DEFAULT_NULLABLE_ASCII
Sets up if ASCII fields allow null values by default. Null values are not allowed if the attribute is set to 0 (zero), all other integer values will allow null values.
Integer value
DF_DRIVER_DEFAULT_NULLABLE_BINARY
Sets up if Binary fields allow null values by default. Null values are not allowed if the attribute is set to 0 (zero), all other integer values will allow null values.
Integer value
DF_DRIVER_DEFAULT_NULLABLE_DATE
Sets up if Date fields allow null values by default. Null values are not allowed if the attribute is set to 0 (zero), all other integer values will allow null values.
Integer value
DF_DRIVER_DEFAULT_NULLABLE_DATETIME
Sets up if DateTime fields allow null values by default. Null values are not allowed if the attribute is set to 0 (zero), all other integer values will allow null values.
Integer value
DF_DRIVER_DEFAULT_NULLABLE_NUMERIC
Sets up if Numeric fields allow null values by default. Null values are not allowed if the attribute is set to 0 (zero), all other integer values will allow null values.
Integer value
DF_DRIVER_DEFAULT_NULLABLE_TEXT
Sets up if Text fields allow null values by default. Null values are not allowed if the attribute is set to 0 (zero), all other integer values will allow null values.
Integer value
DF_DRIVER_DEFAULT_RECORD_IDENTITY_HIDING
When old definition tables are opened - with a record identity column called DFRECNUM that is placed at he end of the record – this attributes determines if the record identity is visible or not. Setting the attribute to a non-zero value will hide every record identity column in every table.
ANSI, OEM
DF_DRIVER_DEFAULT_TABLE_CHARACTER_FORMAT
The default table character format to use when creating new tables.
Integer value
DF_DRIVER_DEFAULT_USE_DUMMY_ZERO_DATE
Sets up the default value of the DF_FILE_USE_DUMMY_ZERO_DATE attribute for new tables created in a structure operation. Dummy zero dates will not be used if set to 0, all other integer values will use dummy zero dates.
ID, ConnectString, Options
DF_DRIVER_CONNECTION_ID_OPTIONS
DF_DRIVER_CONNECTION_ID_STRING
DF_DRIVER_NUMBER_CONNECTION_IDS
Define a DataFlex Connection ID. Used to assign a logical name (Connection ID) to a database connect string.
DFConnectionId MyTestID, SERVER=(local);Trusted_Connection=yes;DATABASE=Northwind, 0
Integer value
Sets the error debug mode OFF if attribute is set to 0 (zero), all other integer values will switch the error debug mode ON. When the error debug mode is ON all errors generated by the database back-end will be displayed in a message box. This mode can be used in procedural environments where the screen space reserved to show error messages is often too small to show the complete text of the error message.
Positive integer value (including zero)
Sets the timeout (in milliseconds) for the find cache. The find cache timeout is the maximum time that is allowed between two find operations on records in memory from one table before they are considered outdated. The default value is 100 milliseconds.
Integer value
Switches ignoring warnings ON or OFF. When set to 0 (zero) warnings are reported as errors, all other integer values will cause the driver to ignore all warnings (nothing is reported). By default warnings are reported as errors.
Positive integer value
Sets the native size (in MegaBytes) of a column that defines the column as a “JIT” column. Columns of this size, and those that are bigger, will be fetched using the JIT binding mechanism in tables that have JIT binding switched on. The default value is 10 MegaBytes.
When JIT binding is used for a column, it means you can't rely on the data being in the record buffer as long as the column is not accessed. This may in some situations have unintended side effects.
A situation where this might happen is when making a duplicate record based on an existing record (cloning records). One method to do this is by setting the file status of the existing record to inactive, or by moving zero to recnum.
If the table has a JIT column and this column is not in the record buffer, the contents of the JIT column will not be written to the cloned record and thus will not be duplicated.
If this record cloning method is used for a table, and the table has JIT columns, JIT binding should be turned off.
Integer value, 1 or 0
Controls whether a login will be attempted during an open if the program is not already logged in on the connection.
If LOGIN_ON_OPEN is 1 when opening a table and there is no connection, the driver will attempt an automatic login.
If LOGIN_ON_OPEN is 0 when opening a table and there is no connection, the driver will not attempt to login, but generate an error.
SQL Data type: char, varchar, nchar, nvarchar
DF_DATABASE_MAP_DFASCII_TO_SQLTYPE, DF_DRIVER_MAP_DFASCII_TO_SQLTYPE
Determines which SQL native data type will be used on the SQL back end, when a new DataFlex ASCII column is created.
Existing columns will keep their existing SQL native data type during restructure. To change the native type of an existing column use DF_FIELD_NATIVE_TYPE.
See Type Mappings for more information.
SQL Data type: varbinary(max), image, binary
DF_DATABASE_MAP_DFBINARY_TO_SQLTYPE, DF_DRIVER_MAP_DFBINARY_TO_SQLTYPE
Determines which SQL native data type will be used on the SQL back end, when a new DataFlex Binary column is created.
Existing columns will keep their existing SQL native data type during restructure. To change the native type of an existing column use DF_FIELD_NATIVE_TYPE.
See Type Mappings for more information.
SQL Data type: datetime
DF_DATABASE_MAP_DFDATE_TO_SQLTYPE, DF_DRIVER_MAP_DFDATE_TO_SQLTYPE
Determines which SQL native data type will be used on the SQL back end, when a new DataFlex Date column is created.
Existing columns will keep their existing SQL native data type during restructure. To change the native type of an existing column use DF_FIELD_NATIVE_TYPE.
The following example shows how to specify to map DF_DATE to SQL_DATE in the MSSQLDRV.INT file.
; MAP_DFDATE_TO_SQLTYPE: The SQL Server type when creating new DF_DATE columns
; Allowed values: date, datetime
MAP_DFDATE_TO_SQLTYPE date
See Type Mappings for more information.
SQL Data type: datetime2, datetime
DF_DATABASE_MAP_DFDATETIME_TO_SQLTYPE, DF_DRIVER_MAP_DFDATETIME_TO_SQLTYPE
Determines which SQL native data type will be used on the SQL back end, when a new DataFlex DateTime column is created.
Existing columns will keep their existing SQL native data type during restructure. To change the native type of an existing column use DF_FIELD_NATIVE_TYPE.
The following example shows how to specify to map DF_DATETIME to SQL_DATETIME2 in the MSSQLDRV.INT file.
; MAP_DFDATETIME_TO_SQLTYPE: The SQL Server type when creating new DF_DATETIME columns
; Allowed values: datetime2, datetime
MAP_DFDATETIME_TO_SQLTYPE datetime2
See Type Mappings for more information.
SQL Data type: varchar(max), text, ntext, nvarchar(max)
DF_DATABASE_MAP_DFTEXT_TO_SQLTYPE, DF_DRIVER_MAP_DFTEXT_TO_SQLTYPE
Determines which SQL native data type will be used on the SQL back end, when a new DataFlex Text column is created.
Existing columns will keep their existing SQL native data type during restructure. To change the native type of an existing column use DF_FIELD_NATIVE_TYPE.
See Type Mappings for more information.
Boolean value
DF_DRIVER_MATCH_CLIENT_SERVER_VERSION
If this keyword is set to True, the driver will check if the version of the database client and server software match. If the client version is less than the server version, an error will be raised when connecting to the server.
If the setting is set to False (the default), versions will not be checked.
Integer value
DF_DRIVER_MINIMUM_CLIENT_VERSION
For SQL Server, the minimum client version can be set to:
8: SQL Server (SQL Server 2000 client)
9: SQL Native Client (SQL Server 2005 client)
10: SQL Server Native Client 10.0 (SQL Server 2008 client)
11: SQL Server Native Client 11.0 (SQL Server 2012 client)
12: DataFlex ODBC Driver 11 for SQL Server (SQL Server 2014 client)
13: DataFlex ODBC Driver 13 for SQL Server (SQL Server 2016 client)
17: DataFlex ODBC Driver 17 for SQL Server (SQL Server 2017 client)
18: DataFlex ODBC Driver 18 for SQL Server (SQL Server 2019 client)
In general it is best to use matching client and server versions. For this, the Match_Client_Server_Version driver configuration keyword can be used.
Using older client with newer server versions may sometimes cause unpredictable results.
A known issue is when using SQL Server date type. This type was introduced in SQL Server 2008 and is not recognized by older clients. An older client will return SQL dates as string (DF_ASCII), which can cause incorrect date values in the database.
Integer value
DF_DRIVER_REPORT_ACTIVE_COLUMN_ERRORS
Switches error reporting on active columns ON or OFF. The reporting is OFF if the attribute is set to 0 (zero), all other integer values will switch reporting ON. By default, active column errors are not reported.
Integer value
Switches reporting on cache read errors ON or OFF. The reporting is OFF if the attribute is set to 0 (zero), all other integer values will switch reporting ON. By default, cache read errors are not reported.
Integer value
Switches the login pop up panel ON or OFF. The panel pops up if the attribute is set to 0 (zero), all other integer values will ensure the panel does not pop up. By default the panel will pop up.
Integer value
DF_DRIVER_TRUNCATE_BINARY_ZEROES
Switches truncating of trailing binary zeroes ON or OFF. When set to 0 (zero) trailing binary zeroes are not truncated, any other integer value will truncate the trailing binary zeroes. By default trailing zeroes are not truncated.
Integer value
Switches the use of structure caching ON or OFF. Structure cache is OFF if the attribute is set to 0 (zero), all other integer values will switch structure caching ON. By default, structure cache is ON.
Integer value
DF_DRIVER_USE_CACHE_EXPIRATION
Switches the structure caching intermediate file expiration checking ON or OFF. Expiration check is OFF if the attribute is set to 0 (zero), all other integer values will switch reporting ON. By default, expiration checking is ON.
Integer value
Switches the translation of deadlock and lock timeout errors to DFERR_LOCK_TIMEOUT ON or OFF. Translation is off if set to 0 (zero, all other integer values will switch translation on. By default, the translation is on.
A configuration file that sets up date columns not to allow null values and to use the system date as default value looks like:
DEFAULT_NULLABLE_DATE 0
DEFAULT_DEFAULT_DATE {fn current_date()}