MSSQLDRV.INT
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 deployment 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>)
Where
<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 attribute associated with the keyword.(<Type>): The type of the associated keyword.
Keywords
The supported keywords for the global intermediate file are:
Auto_Reconnect
Value
0 or 1
Associated Attribute
None
Description
Determines whether the driver attempts to auto_reconnect or not. The default value is 1 (Auto_Reconnect On).
DF_DRIVER_CACHE_PATH
Value
Path to a valid directory
Associated Attribute
Description
Sets up a directory to store cache files. By default, cache files will be stored in the same directory as the corresponding intermediate file.
DF_DRIVER_DEFAULT_DEFAULT_ASCII
Value
Default specification
Associated Attribute
DF_DRIVER_DEFAULT_DEFAULT_ASCII
Description
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.
DF_DRIVER_DEFAULT_DEFAULT_BINARY
Value
Default specification
Associated Attribute
DF_DRIVER_DEFAULT_DEFAULT_BINARY
Description
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.
DF_DRIVER_DEFAULT_DEFAULT_DATE
Value
Default specification
Associated Attribute
DF_DRIVER_DEFAULT_DEFAULT_DATE
Description
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.
DF_DRIVER_DEFAULT_DEFAULT_DATETIME
Value
Default specification
Associated Attribute
DF_DRIVER_DEFAULT_DEFAULT_DATETIME
Description
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.
DF_DRIVER_DEFAULT_DEFAULT_NUMERIC
Value
Default specification
Associated Attribute
DF_DRIVER_DEFAULT_DEFAULT_NUMERIC
Description
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.
DF_DRIVER_DEFAULT_DEFAULT_TEXT
Value
Default specification
Associated Attribute
DF_DRIVER_DEFAULT_DEFAULT_TEXT
Description
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.
DF_DATABASE_DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA
Value
Integer value
Associated Attributes
DF_DATABASE_DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA, DF_DRIVER_DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA
Description
Determines the default mapping schema setting for all DataFlex types.
DF_DRIVER_DEFAULT_NULLABLE_ASCII
Value
Integer value
Associated Attribute
DF_DRIVER_DEFAULT_NULLABLE_ASCII
Description
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.
DF_DRIVER_DEFAULT_NULLABLE_BINARY
Value
Integer value
Associated Attribute
DF_DRIVER_DEFAULT_NULLABLE_BINARY
Description
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.
DF_DRIVER_DEFAULT_NULLABLE_DATE
Value
Integer value
Associated Attribute
DF_DRIVER_DEFAULT_NULLABLE_DATE
Description
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.
DF_DRIVER_DEFAULT_NULLABLE_DATETIME
Value
Integer value
Associated Attribute
DF_DRIVER_DEFAULT_NULLABLE_DATETIME
Description
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.
DF_DRIVER_DEFAULT_NULLABLE_NUMERIC
Value
Integer value
Associated Attribute
DF_DRIVER_DEFAULT_NULLABLE_NUMERIC
Description
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.
DF_DRIVER_DEFAULT_NULLABLE_TEXT
Value
Integer value
Associated Attribute
DF_DRIVER_DEFAULT_NULLABLE_TEXT
Description
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.
DF_DRIVER_DEFAULT_RECORD_IDENTITY_HIDING
Value
Integer value
Associated Attribute
DF_DRIVER_DEFAULT_RECORD_IDENTITY_HIDING
Description
When old definition tables are opened—with a record identity column called DFRECNUM that is placed at the end of the record—this attribute 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.
DF_DRIVER_DEFAULT_TABLE_CHARACTER_FORMAT
Value
ANSI, OEM
Associated Attribute
DF_DRIVER_DEFAULT_TABLE_CHARACTER_FORMAT
Description
The default table character format to use when creating new tables.
DF_DRIVER_DEFAULT_USE_DUMMY_ZERO_DATE
Value
Integer value
Associated Attribute
DF_DRIVER_DEFAULT_USE_DUMMY_ZERO_DATE
Description
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.
DF_DRIVER_CONNECTION_ID
Value
ID, ConnectString, Options
Associated Attributes
DF_DRIVER_CONNECTION_ID
DF_DRIVER_CONNECTION_ID_OPTIONS
DF_DRIVER_CONNECTION_ID_STRING
DF_DRIVER_NUMBER_CONNECTION_IDS
Description
Define a DataFlex Connection ID. Used to assign a logical name (Connection ID) to a database connect string.
Example
DFConnectionId MyTestID, SERVER=(local);Trusted_Connection=yes;DATABASE=Northwind, 0
DF_DRIVER_ERROR_DEBUG_MODE
Value
Integer value
Associated Attribute
Description
Sets the error debug mode OFF if the 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.
DF_DRIVER_FIND_CACHE_TIMEOUT
Value
Positive integer value (including zero)
Associated Attribute
Description
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.
DF_DRIVER_IGNORE_WARNINGS
Value
Integer value
Associated Attribute
Description
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.
DF_DRIVER_JIT_TRESHOLD
Value
Positive integer value
Associated Attribute
Description
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.
Special Note
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.
DF_DRIVER_LOGIN_ON_OPEN
Value
Integer value, 1 or 0
Associated Attribute
Description
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.
DF_DATABASE_MAP_DFASCII_TO_SQLTYPE
Value
SQL Data type: char, varchar, nchar, nvarchar
Associated Attribute
DF_DATABASE_MAP_DFASCII_TO_SQLTYPE, DF_DRIVER_MAP_DFASCII_TO_SQLTYPE
Description
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.
DF_DATABASE_MAP_DFBINARY_TO_SQLTYPE
Value
SQL Data type: varbinary(max), image, binary
Associated Attribute
DF_DATABASE_MAP_DFBINARY_TO_SQLTYPE, DF_DRIVER_MAP_DFBINARY_TO_SQLTYPE
Description
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.
DF_DATABASE_MAP_DFDATE_TO_SQLTYPE
Value
SQL Data type: datetime
Associated Attribute
DF_DATABASE_MAP_DFDATE_TO_SQLTYPE, DF_DRIVER_MAP_DFDATE_TO_SQLTYPE
Description
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.
DF_DATABASE_MAP_DFDATETIME_TO_SQLTYPE
Value
SQL Data type: datetime2, datetime
Associated Attribute
DF_DATABASE_MAP_DFDATETIME_TO_SQLTYPE, DF_DRIVER_MAP_DFDATETIME_TO_SQLTYPE
Description
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.
DF_DATABASE_MAP_DFTEXT_TO_SQLTYPE
Value
SQL Data type: varchar(max), text, ntext, nvarchar(max)
Associated Attribute
DF_DATABASE_MAP_DFTEXT_TO_SQLTYPE, DF_DRIVER_MAP_DFTEXT_TO_SQLTYPE
Description
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.
DF_DRIVER_MATCH_CLIENT_SERVER_VERSION
Value
Boolean value
Associated Attribute
DF_DRIVER_MATCH_CLIENT_SERVER_VERSION
Description
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.
DF_DRIVER_MINIMUM_CLIENT_VERSION
Value
Integer value
Associated Attribute
DF_DRIVER_MINIMUM_CLIENT_VERSION
Description
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 an 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.
DF_DRIVER_REPORT_ACTIVE_COLUMN_ERRORS
Value
Integer value
Associated Attribute
DF_DRIVER_REPORT_ACTIVE_COLUMN_ERRORS
Description
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.
DF_DRIVER_REPORT_CACHE_ERRORS
Value
Integer value
Associated Attribute
Description
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.
DF_DRIVER_SILENT_LOGIN
Value
Integer value
Associated Attribute
Description
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.
DF_DRIVER_TRIM_VARCHAR_VALUES
Value
Integer value (0 = False, 1 = True)
Associated Attribute
Description
Determines whether values are trimmed when stored in a SQL varchar column.
See Padding and Trimming in SQL Databases for further information.
DF_DRIVER_TRUNCATE_BINARY_ZEROES
Value
Integer value
Associated Attribute
DF_DRIVER_TRUNCATE_BINARY_ZEROES
Description
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.
DF_DRIVER_USE_CACHE
Value
Integer value
Associated Attribute
Description
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.
DF_DRIVER_USE_CACHE_EXPIRATION
Value
Integer value
Associated Attribute
DF_DRIVER_USE_CACHE_EXPIRATION
Description
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.
DF_DRIVER_USE_DF_LOCKERROR
Value
Integer value
Associated Attribute
Description
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.
Sample Configuration File
A configuration file that sets up date columns not to allow null values and to use the system date as the default value looks like:
DEFAULT_NULLABLE_DATE 0
DEFAULT_DEFAULT_DATE {fn current_date()}