Understanding Mertech Files

The main files of interest in a driver installation that developers will customize are: mertech.ini, <table>.int. INT files (short for “Intermediate”) provide information to the runtime and driver about how to access a table and how to map certain features of that table into a DataFlex environment. The mertech.ini file is global and has settings that apply to the driver itself and to all tables.

INT Files

The Flex2SQL migration utility adds a driver prefix (for example, SQL_DRV:) to the root file name entry in the filelist and generates an intermediate file (<root_file_name>.INT) when a DataFlex file is converted to the target database. When an open command is issued for a file containing a driver prefix, the DataFlex runtime passes the open operation to the driver associated with the file.

When an open command is issued for a file containing an INT extension, the API searches the INT file for the DRIVER_NAME token, loads the appropriate driver and then passes control to the driver.

An INT file contains information required to allow DataFlex to connect to and open the converted SQL tables. INT options are listed below. A sample INT file follows.

Token

Description

DRIVER_NAME

Driver to load when opening this file.

SERVER_NAME

The name of the server with optional instance name (see Login Details).

DATABASE_SPACE_NAME

The database name.

DATABASE_NAME

The table name (see Login Details).

SCHEMA_NAME

The database owner (see Login Details).

NUMBER_DF_FIELDS

Number of fields in the DataFlex file.

PRIMARY_KEY

The index number for the field in the DataFlex file that is used to create a primary key on the target server.

PRIMARY_INDEX

Primary index in the DataFlex file.

TRANSLATE_OEM_TO_ANSI

Enable (1) or disable (0) OEM character translation within the driver. Defaults to 1 (Enabled).

OPTIMIZE

Fetch data a record (FOR_ONE_ROW) or a set of records (FOR_SET) at a time. When FOR_SET is specified, the driver limits the size of the query to the MAX_ROWS_TO_QUERY value. When FOR_ONE_ROW is selected, the driver generates a query that produces a result set containing only one record. Performance issues can arise if FOR_ONE_ROW is used. Defaults to FOR_SET.

MAX_ROWS_TO_QUERY

The number of records to be fetched in one network round-trip. Defaults to 10.

LOCAL_CACHE

Enable (YES) or disable (NO) storage of fetched records in local cache. Default to YES.

FIELD_NUMBER

The DataFlex column number. All subsequent INT entries apply to this field until another FIELD_NUMBER (or INDEX_NUMBER) entry is listed.

FIELD_INDEX

The DataFlex main index for the column.

FIELD_NATIVE_LENGTH

The number of bytes on the target database.

FIELD_LENGTH

The number of bytes in the DataFlex field.

FIELD_TYPE

The DataFlex field type for the column (DataFlex DF_FIELD_TYPE enum value).

FIELD_OVERLAP_START

The starting column for an overlap field.

FIELD_OVERLAP_END

The ending column (inclusive) for an overlap field.

FIELD_RELATED_FILE

Filelist number of a related parent table.

FIELD_RELATED_FIELD

Column number in the parent table that this column relates to.

INVERSE_KEYC

Name for the inverse key column to handle a case sensitive index or descending index segment.

INDEX_NUMBER

The DataFlex index number. All subsequent INT entries apply to this index until another INDEX_NUMBER entry is listed.

INDEX_NAME

As an alternative to specifying the segments of the index, after an INDEX_NUMBER entry, an INDEX_NAME entry can be added with the name of the index on the server specified that should be linked to the preceding INDEX_NUMBER. When used, this is the only option that needs to be specified. All the rest of the details will be loaded from the server. If Segment details are also specified, then Flex2SQL will operate in a “strict” mode in regards to indexes and require that the name AND index segment details match.

INDEX_NUMBER_SEGMENTS

Number of segments in this index

INDEX_NATIVE_CREATED

Index created by the Flex2SQL migration utility on the server.

INDEX_SEGMENT_FIELD

The field number of the index segment column.

INDEX_SEGMENT_CASE

Whether this index segment is case sensitive (USED, case sensitive, or IGNORED).

INDEX_SEGMENT_DIRECTION

The direction for this index segment (ASCENDING or DESCENDING).

The following is a sample INT file for a table SALESP on the SQL server TESTSSYSSQLEXPRESS in the database test in the default schema dbo:

DRIVER_NAME SQL_DRV
SERVER_NAME testsyssqlexpress
DATABASE_SPACE_NAME test
DATABASE_NAME SALESP
SCHEMA_NAME dbo
NUMBER_DF_FIELDS 5
PRIMARY_KEY 1
PRIMARY_INDEX 0
TRANSLATE_OEM_TO_ANSI 1
OPTIMIZE FOR_SET
MAX_ROWS_TO_QUERY 10
LOCAL_CACHE YES

FIELD_NUMBER 1
FIELD_INDEX 1
FIELD_NATIVE_LENGTH 6
FIELD_LENGTH 1021
FIELD_TYPE 7
FIELD_OVERLAP_START 16
FIELD_OVERLAP_END 17
FIELD_RELATED_FILE 5
FIELD_RELATED_FIELD 1
INVERSE_KEYC U_ID

FIELD_NUMBER 2
FIELD_INDEX 2

FIELD_NUMBER 3
FIELD_LENGTH 2
FIELD_NUMBER 5
FIELD_NATIVE_LENGTH 6

INDEX_NUMBER 1
INDEX_NUMBER_SEGMENTS 1
INDEX_NATIVE_CREATED 1
INDEX_SEGMENT_FIELD 1
INDEX_SEGMENT_CASE IGNORED
INDEX_SEGMENT_DIRECTION DESCENDING

INDEX_NUMBER 2
INDEX_NUMBER_SEGMENTS 2
INDEX_NATIVE_CREATED 1
INDEX_SEGMENT_FIELD 1
INDEX_SEGMENT_CASE IGNORED
INDEX_SEGMENT_FIELD 2
INDEX_SEGMENT_CASE IGNORED

Location of Login Details

The Flex2SQL migration utility allows you to choose whether login details (SERVER_NAME, DATABASE_NAME, and SCHEMA_NAME) are included in the INT file or not (Maintenance > Create Table or Restructure Table > Table tab and Tools > Preferences > Login tab).

If login details are included, these values are hardcoded for the table. If a token is not included in the INT file, then the assigned value comes from the user’s default information (DATABASE_NAME and SCHEMA_NAME), or from the server the user logged into (SERVER_NAME).

Note

If login details are included in the INT file and you move your tables to another server, database, or schema, the driver is no longer able to find the tables, and you have to recreate or manually edit the INT file.

MERTECH.INI Configuration File

The MERTECH.INI file has settings used by the Mertech DataFlex to SQL drivers. The placement of the MERTECH.INI is up to you. The safest place to put the file is right next to your application itself. Alternatively many developers choose to put the file into the BIN folder of the DataFlex runtime. Settings in this file are described below. By default, all available options are present in this file but are disabled (by using a semi-colon (;) at the beginning of the line).

A setting is assigned by entering a value after the equal sign (e.g. Server = sql.mycompany.local\MyInstance). Leaving a value empty means use the driver’s default value. Spaces are ignored before and after the equal sign. All settings and values are case insensitive.

Entries under a specific heading (e.g., heading [MSSQL] in the INI file) can only be used in that heading.

All settings are listed in the table below. Settings available for a particular driver are marked with an “X”. The value marked with an asterisk (*) is the default for the field.

Setting

MS SQL

ORACLE

PGSQL

MYSQL

Description

Server

X

X

X

X

The name of the server with optional instance name or pluggable database. Only MS SQL supports instances which are treated by the driver as a separate server. The format to specify and instance is <server>\\<instance>. Only Oracle supports pluggable databases. The format to specify a pluggable database is <server>/<pluggable_db>.

Example: sqlsrvsql2012

Database

X

X

X

The database name. Note that Oracle doesn’t the concept of a single server having multiple databases. Use the Server setting to specify a pluggable database for Oracle.

Example: modeldb

Port

X

X

Port number where the server is listening for communication. It would be unusual to need to specify or change the port number although you are able to do so if needed.

Default value for PostgreSQL = 5432 Default value for MySQL = 3306 Default value for MariaDB = 3306 Default value for MS SQL = 1433 Default value for Oracle = 1521

Schema

X

X

A schema is a logical grouping of objects owned by a database user. Both SQL Server and PostgreSQL support schemas.

Example: dbo

UseTrustedConnection

X

Use Windows “trusted” authentication. Usually comes from the INT file or login dialog box.

Values: <yes, *no>

TrustServerCertificate

X

Controls if the client will automatically trust the certificate sent by the server. Needs to be yes if the server is using the default self-signed certificate. The default of yes is less secure but shouldn’t require any changes from prior versions. This connection property is only available starting from build 17.0.9622 and will only work when using the v19 MSOLEDBSQL client.

Values: <*yes, no>

Encrypt

X

Controls if the client will use encrypted communication with the server. This setting defaults to no as older servers do not by default support encryption. This connection property is only available starting from version 17.0.9622 and will only work when using the v19 MSOLEDBSQL client.

Values: <yes, *no>

User

X

X

X

X

If not using a trusted connection, the login name for authentication.

Example: mydomainme

Password

X

X

X

X

Encrypted password associated with the User. Use the MdsEncryptPassword utility to get this value (for example, MdsEncryptPassword MyPassword).

Example: 78870b4d0a2122226983074d8d2a3b50

SuppressPopups

X

X

X

X

Should Error and Login dialog boxes be disabled? If set to yes, then no login dialog box pops up even if credentials are missing from the INI file, or if they are in error. This is useful when using WebApp server where no desktop is available for a popup dialog box. As an alternative to this setting, you can use the new driver Service Mode which also blocks popups and additionally records entries into the event log when a popup or error would have been displayed. Service mode is only available in v17.1+.

Values: <yes, *no>

MirrorServer

X

If the SQL Server has a mirror partner, the server name is provided here (with optional instance name). Mirroring is only supported for SQL Server 2012 at this point. High Availability support is now built into the driver with no settings needed using the latest MSOLEDBSQL client starting from version 17.0.9622.

Example: sqlsrvmirror2005

AutoReconnect

X

X

X

X

Should the driver try to re-establish stale and/or dead connections? If AutoReconnect is set to yes, the driver throws an exception when a query is issued on a stale or dead connection in the current transaction. The driver then attempts to re-establish the connection before the next query is issued on the connection in a new transaction. Not recommended unless the application can properly handle SQLExceptions.

Values: <yes, *no>.

AutoReconnectDialog

X

X

X

X

If AutoReconnectDialog is set to yes and a connection fails, the driver pops up a dialog box asking the user if it is okay to reconnect. This allows the user to reconnect the network cable or wait for the DSL to come back on. Not recommended in a web application. This dialog is blocked by Service Mode.

Values: <yes, *no>

ConnectionTimeout

X

X

X

Sets the amount of time (in seconds) a connection waits before it times out. Default value MSSQL = 15 Default value MYSQL = 15 Default value PGSQL = 0 (which means to use the operating system timeout setting)

SqlLockTimeOut

X

X

Sets the number of milliseconds a statement waits for a lock to be released.

Values:

  • * -1 = wait forever

  • 0 = no wait - return error immediately

  • <value in milliseconds>

IsolationLevel

X

Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server

Values: <READ_COMMITTED, *READ_UNCOMMITTED, REPEATABLE_READ, SERIALIZABLE, SNAPSHOT_LEVEL>

Note: Only valid for MS SQL.

SqlMaxCursors

X

X

X

Maximum number of cursors to keep open per server.

Default value = 100

OleDbOemTranslation

X

Enable/disable OEM character translation within the OLE-DB driver.

Values: <*yes, no>

UseRowCountInTransactions

X

X

X

Use the “rowcount” value during transactions?

The MAX_ROWS_TO_QUERY setting in the INT file controls how many rows are fetched at one time. Fetching multiple rows at a time can improve performance when reading records in a file. However, when multiple rows are fetched during a transaction, all these rows are locked. This causes unnecessary contention on the server if not all of the rows require updating. If contention is a concern for your database, set this option to no.

Values: <*yes, no>

ForceFieldsNotNull

X

X

X

X

If blank or zero is moved to a field, force it to have a value even if it is nullable. If set to yes, if a value is never moved to a field, the field gets 0/default value. If a value is moved to a field (even if its 0/default date), the field gets that value

Values: <yes, *no>

Trace_On

X

X

X

X

Location of the debug trace file. Specify the complete path and filename for the trace file. If no file is specified, no tracing occurs.

Example: c:\\flex2sql.tra

Trace_Level

X

X

X

X
Trace output level:

  • -1: Errors only

  • 0: Basic

  • 1: Detailed, 1st level

  • 2: Detailed, 2nd level

  • 3: Detailed, 3rd level``

Trace_Flush

X

X

X

X

To improve performance, flushing is turned off by default and the operating system normal file caching mechanism is used. If the application crashes, the last few lines of a trace are not written to the trace file. Enable flushing to cause each line to be immediately written to disk.

Values: <yes, *no>

TableCasing

X

X

X

X

Determines how the driver manages table and field names: maintain the current case, convert to uppercase, or convert to lower case.

Values: <*KeepCase, Uppercase, Lowercase>

AutoNumericRemapping

X

Automatically remap numeric data types to native types (e.g. smallint, int, bigint). Turn this setting off to always migrate numeric data as numeric/decimal.

Values: <*yes, no>

NativeOptimization

X

X

Manage the use of the Index hints optimization during a Query. Only use this if you’re having index performance issues.

Values:

  • Default : Off in MSSQL, on in Oracle

  • NoIndexHint : do not use index hints

  • UseIndexHint: use index hints

ForceDateFormatInit

X

X

X

X

Force date format initialization when stored procedure/function or trigger is executed. This is only needed if a stored procedure or trigger changes the date format.

Values: <yes, *no>

A sample MERTECH.INI file that is configured for use with MS SQL server:

; Mertech.ini
; This file has settings for the Mertech DataFlex to SQL drivers
; It should be placed in the same location as your driver and licens
; file (sql_drv.dll, mdsmysql.dll, sql_drv.cfg, mdsmysql.cfg, etc)

[MSSQL]
; The name of the server with optional instance name
; usually comes from the INT file or login dialog
; example: sqlsrv/MyInstance
Server = sql.mycompany.local/MyInstance

Database = acctplus

; If not using trusted connection, the login ID for authentication
; usually comes from the login dialog
; example: mydomain\me
User = mydomain\me

; Encrypted password associated with the User
; use the MdsEncryptPassword utility to get this value
; example: MdsEncryptPassword MyPassword

Password = 78870b4d0a2122226983074d8d2a3b50