Configuration Settings¶

Sample INT file¶

CREATED Sat Apr 19 18:05:00 2014 by SQLBTR 5.1.6402.1
DRIVER_NAME SQL_BTR
SERVER_NAME GATEWAY\\sqlexpress
DATABASE_SPACE_NAME tempdb
TABLE_NAME CLASS
SCHEMA_NAME dbo
FILEGROUP_TABLE
FILEGROUP_INDEX
FILEGROUP_TEXT
LOGICAL_RECORD_LENGTH 14
PAGE_SIZE 4096
FILE_FLAGS 512
FILE_DDF_FLAGS 64
PERMANENT_INT NO
MAX_ROWS_TO_QUERY 100
LOCAL_CACHE YES
NUMBER_DF_FIELDS 11
PRIMARY_INDEX 1
TRANSLATE_OEM_TO_ANSI 0
IGNORE_NULL_VALUES 1
TRIM_STRING_FIELDS 1

FIELD_NUMBER 1
FIELD_NAME ID
FIELD_NATIVE_TYPE 15
FIELD_NATIVE_LENGTH 4
FIELD_NATIVE_OFFSET 0
FIELD_NATIVE_FLAG 0
FIELD_DEFAULT_VALUE 0
FIELD_AUTOINCREMENT_TYPE YES
FIELD_INDEX 1

FIELD_NUMBER 2
FIELD_NAME NAME
FIELD_NATIVE_TYPE 0
FIELD_NATIVE_LENGTH 7
FIELD_NATIVE_OFFSET 4
FIELD_NATIVE_FLAG 1
FIELD_INDEX 2
INDEX_NUMBER 1

INDEX_NUMBER_SEGMENTS 1
INDEX_SEGMENT_FIELD 1
INDEX_SEGMENT_FLAG 258

INDEX_NUMBER 2
INDEX_NUMBER_SEGMENTS 2
INDEX_SEGMENT_FIELD 2
INDEX_SEGMENT_CASE IGNORED
INDEX_SEGMENT_FLAG 1298
INDEX_SEGMENT_FIELD 3
INDEX_SEGMENT_CASE IGNORED
INDEX_SEGMENT_FLAG 1282

NOTE: Only modify the INT file as instructed (for example, to change the PERMANENT_INT setting). Unauthorized changes to the field or index tokens may result in unpredictable application errors.

Sample mds.ini file¶

[SQL_BTR]
INT-Folder=..\IntFiles_SQL
Server=mssql\sql2008
Database=testdb
Schema=dbo
UseTrustedConnection=no
User=mydomain\\me
Password=4c8fe10aad3
Table-Prefix=proj1\_
Table-Postfix=
FilegroupTable=TABLES
FilegroupIndex=INDEXES
FilegroupText=LOBFIELDS

[ORA_BTR]
INT-Folder=..\IntFiles_ORA
Server=orclsrv
User=me
Password=4c8fe10aad3
Table-Prefix=proj1\_
Table-Postfix=

[PGS_BTR]
INT-Folder=..\IntFiles_PGS
Server=pgsqlsrv:5432
Database=testdb
User=me
Password=4c8fe10aad3
Table-Prefix=proj1\_
Table-Postfix=

NOTE: A sample mds.ini file can be found in the installation folder (<Program Files>\Mertech Data Systems\DB Drivers\Btrieve\sdk\samples).

Consolidating INT files on the client¶

In the scenario described under Segregating tables using a prefix or postfix (or Options to migrate a multi-directory UniPaaS application) where a similar set of data files is duplicated to multiple subfolders, the INT files in each folder would be the same. While this is not a problem, it can be desirable to consolidate the files for easier maintenance. You can use the INT-Folder setting to specify the path where a common set of INT files is stored. For example you could enter this information in the mds.ini file:

INT-Folder=..\\corporate_intfiles

Specifying the target backend¶

During the migration, the Convert Data Files dialog box displays so you can choose certain migration options. This dialog box contains three check boxes, Get Server Name from Login, Get Schema Name from Login and Get Database Name from Login.

Figure 22 Settings to save the target backend

These boxes are checked by default, meaning the driver obtains this information from the Server Login dialog box when the program is run. If these boxes are not checked, this information is written to the INT files and the driver uses the stored values at runtime.

NOTE: The Get Schema Name from Login option is not used in Oracle.

But what if you want to change one of these items later? You have to update each INT file individually. You can instead choose to store these settings in the mds.ini file. During the migration, leave these three boxes checked so the server name, database name, and schema are NOT written to the INT file and instead enter this information in the mds.ini file. For example:

Server=sqlsrv2\sql2012
Database=SQLDB
Schema=dbo

Automatic application login¶

By default, a Server Login dialog box pops up when the application starts. The user has the option to save the password and subsequently avoid the dialog box, but some ISVs prefer to not have the Server Login dialog box pop up at all.

The application may include a call to the B_SQL_LOGIN (10015) API or simply include the server login information in the mds.ini file. For example:

UseTrustedConnection=no
User=mydomain\me
Password=4c8fe10aad3

Segregating tables using a prefix or postfix¶

There are several scenarios when a developer might want to distinguish groups of tables in the target database, for instance, Accounts Receivable (AR_), Accounts Payable (AP_), and Engineering (EN_). The more often need for such a feature is when tables are duplicated into multiple subfolders, a set per company or fiscal year for instance. Usually, the DDFs for each folder would be identical – having the same list of table names. So trying to migrate these to the same database would cause a name space conflict.

To avoid the conflict in names, include either a prefix such as 1999_ or a suffix like _cust1. During the migration, the tables are created with this additional appendage so “Person” would become “1999_Person” or “Person_cust1”. For example you could enter this information in the mds.ini file:

Table-Prefix=CustA\_

Or

Table-Postfix =_CustA

NOTE: Oracle allows only 30 characters for SQL identifiers and considers all identifiers in a global namespace. Because of the global nature of the identifiers, the BTR2SQL driver creates indexes, sequences, and triggers using the table name plus a suffix (respectively: _Ixx, _Sxx, _Txx). Btrieve table names are allowed to be 20 characters. Adding the four characters for the other identifiers, is 24 characters – thus leaving only 6 characters for a prefix or a suffix string if all 20 are utilized for the table names. So be conscience of this number. As an example, using a prefix of C593_, consider a table defined in the DDFs as ALRESIDENTSERVICEPLA. The table will be created in Oracle as *C1593_ALRESIDENTSERVICEPLA *and its first index will be created as *C1593_ALRESIDENTSERVICEPLA_I01 *– a full 30 characters.

Using MS SQL Filegroups¶

The MS SQL Filegroups feature is used to group database objects and files together for allocation and administration purposes. Filegroups can be selected for tables, indexes, and text storage. The properties of all the files in a filegroup can be managed simultaneously, streamlining administration.

Filegroups also provide performance advantages. Placing data and index files on separate I/O paths means that I/O resources spent retrieving data from tables do not interfere with I/O resources searching through indexes. I/O resources spent performing updates, inserts and deletes are split between the tables and the indexes. For example you could enter this information in the mds.ini file:

FilegroupTable=TABLES
FilegroupIndex=INDEXES
FilegroupText=LOBFIELDS

Sample mds_global.ini¶

Each piece is optional.

[SQL_BTR]
;Tracing
TRACE_ON=c:\trace\sql_btr.tra
TRACE_LEVEL=0
TRACE_FLUSH=no
;Locking
SERVER_SIDE_LOCKING=yes
SQL_LOCKS=no

[ORA_BTR]
;Location of the debug trace file
TRACE_ON = c:\trace\ora_btr.tra
TRACE_LEVEL = 0
TRACE_FLUSH=no
;Locking
SERVER_SIDE_LOCKING=yes
SQL_LOCKS=no

[PGS_BTR]
;Location of the debug trace file
TRACE_ON = c:\trace\pgs_btr.tra
TRACE_LEVEL = 0
TRACE_FLUSH=no
;Locking
SERVER_SIDE_LOCKING=yes
SQL_LOCKS=no

A semi-colon (;) at the beginning of the line in the mds_global.ini file indicates that line is a comment and is ignored. A setting is assigned in mds_global.ini by entering a value after the equal sign (e.g., Trace_On = c:\trace\sql_btr.tra). Spaces are ignored before and after the equal sign. Leaving a value empty means use the driver’s default value. All settings and values are case insensitive.

Entries under a specific heading (e.g., heading [SQL_BTR]) are only used for that driver (e.g., driver MS SQL).

NOTE: A sample mds_global.ini file can be found in the installation folder (<Program Files>\Mertech Data Systems\DB Drivers\Btrieve\bin)

MdsSetSetting¶

The format for the call is:

MdsSetSetting(foldername, settingname, value)

• foldername is the full or relative path to the folder where the msd.ini file would reside (for example, “c:\myapp\ver4\data\cust5”). The folder does not have to exist, it is simply the virtual location for the mds.ini file. If the setting being changed is in the mds_global.ini file, specify NULL for foldername.

• settingname is any entry in the mds.ini file (for example, INT-Folder) or mds_global.ini file (for example (Trace_Level). Setting names are not case sensitive.

If a call to MdsSetSetting is made to enable TRACE_ON, that should be the last trace setting updated. If a trace is already active, it is stopped and a new file takes over using the latest trace settings.

• value is the value to be assigned to the setting. If a Password value is provided and it starts with 0x, it is assumed that the password was encrypted using MdsEncryptPassword.

There is no need to specify a Section Name (for example [SQL_BTR]). The assumption is that the setting is for the current driver. All parameters are enclosed in quotes, so the value itself should never include quotes.

Examples:

// (mds.ini) Set the password for data files opened in the cust5 folder
MdsSetSetting("c:\\myapp\\ver4\data\\cust5", "Password", "0xed8ee3e72436da3169")
// (mds_global.ini) Limit buffer output in the trace to just KeyBuffers
MdsSetSetting(NULL, "Trace_Buffers", "kb")

How does MdsSetSetting work?¶

In the example below global memory settings are highlighted in turquoise, folder-specific settings are highlighted in gray and new or updated settings are shown in red text.

1 When the application starts:

1. Registry values are read and stored.

Trace_On= c:\trace\sql_btr.tra, Trace_Level=0, TRACE_FLUSH=no, Server_Side_Locking=yes

b) Environment variable values are read and stored, overwriting any existing values.

Trace_On= c:\trace\sql_btr.tra,Trace_Level=1, TRACE_FLUSH=no, Server_Side_Locking=yes

c) mds_global.ini values are read and stored, overwriting any existing values.

Trace_On= c:\tracesql_btr.tra, Trace_Level=1,TRACE_FLUSH=yes, Server_Side_Locking=yes, Trace_Query_Times=yes

2 When the application calls MdsSetSetting (NULL, “Trace_Level”, “2”) to set a global value, any existing global value is overwritten.

Trace_On= c:\trace\sql_btr.tra,Trace_Level=2, TRACE_FLUSH=yes, Server_Side_Locking=yes, Trace_Query_Times=yes

3 When the application calls MdsSetSetting (“c:\myapp\ver4\data\cust5”, “Schema”,’cust5”) to set a folder‑specific setting:

a) If an mds.ini file exists in the specified directory (and the file was not already read when a create or open call was made) then the mds.ini file is read and the values are stored.

c:\myapp\ver4\data\cust5: Server=testserver, Database=testdb

2. MdsSetSetting is run and in memory values are overwritten.

c:\myapp\ver4\data\cust5: Server=testserver, Database=testdb,Schema=cust5

The values set by MdsSetSetting remain in effect until the process stops.

NOTE: Be careful changing values for Schema, Database, Table-Prefix and Table-Postfix using MdsSetSetting since the changes affect all subsequent create and open calls for the selected directory.

NOTE: If there is an mds.ini file on disk, settings in that file are NOT changed by a call to MdsSetSetting. Values set by MdsSetSetting are not stored on disk.

Example¶

Below is an example using MdsSetSetting to create a virtual mds.ini file and then using these settings to create and open a new table.

// First, execute an embedded sql call to create new schema 'cust5'

// Next, create a virtual mds.ini file for 'c:\myapp\ver4\data\cust5'
MdsSetSetting("c:\myapp\ver4\data\cust5","INT_Folder","c:\myapp\ver4\data\intfiles_mssql")
MdsSetSetting("c:\myapp\ver4\data\cust5","Server","testserver")
MdsSetSetting("c:\myapp\ver4\data\cust5","Database","testdb")
MdsSetSetting("c:\myapp\ver4\data\cust5","Schema","cust5")
MdsSetSetting("c:\myapp\ver4\data\cust5","User","testuser")
MdsSetSetting("c:\myapp\ver4\data\cust5","Password","password")

// Now, create and open Person.mkd using the virtual mds.ini settings
B_CREATE      "c:\myapp\ver4\data\cust5\Person.mkd"
B_OPEN        "c:\myapp\ver4\data\cust5\Person.mkd"

The INT_Folder setting in the virtual mds.ini directs the application to “c:\myapp\ver4\data\intfiles_mssql” where the Person_mkd.int file is stored.

A sample directory structure is shown below. The cust1, cust2, and cust5 folders (shaded in gray) do not actually exist, they are virtual folders, created in memory by MdsSetSetting to contain a virtual mds.ini file. The application must include calls to MdsSetSetting to create these virtual data folders before it opens or creates any tables for these schemas on the server.

c:\myapp
  ver4
    data
        mds.ini(int-folder=.\intfiles_mssql server=testserver schema=common)
        intfiles_mssql
          person_mkd.int
        cust1
          mds.ini(int-folder=.\intfiles_mssql server=testserver schema=cust1)
        cust2
          mds.ini(int-folder=.\intfiles_mssql server=testserver schema=cust2)
        cust5
          mds.ini(int-folder=.\intfiles_mssql server=testserver schema=cust5)

MdsGetSetting¶

You can retrieve global and folder specific settings programmatically with a call to MdsGetSetting. The format for the call is:

MdsGetSetting(foldername, settingname, return value)

All settings in the mds_global.ini file and all settings in the mds.ini file (except Password) can be retrieved. Additionally these global settings, displayed at the top of the trace file, can be retrieved:

• Driver_Info: for example, “SQLBTR 5.2.6900.99”

• Driver_Location: for example, “C:\myapp\w3btrv7.dll”

• Process_Info: for example, “C:\myapp\myapp.exe, Process ID 3628, WTS ID 0”

• Computer_Name: for example, “mycomputer”

• License_Info: for example, “Serial Number 2000 for 10 users registered to MY COMPANY”

• Current_Working_Folder: for example, “C:\MyApp”

• Ini_Location: for example, “C:\MyPath\mds_global.ini”   or  “C:\MyApp\data\mds.ini”