Product Overview

BTR2SQL User's Guide
Product Overview

Mertech Data System’s ISAM to SQL Database Connectivity (ISDBC) drivers provide a comprehensive solution that allows Btrieve-based applications to work with multiple backends without the need to recompile or rewrite code. Using the Mertech drivers, an application currently working exclusively with a Btrieve or Pervasive.SQL backend can work with a Microsoft SQL Server, Oracle, MySQL, or PostgreSQL database in a matter of hours!

The BTR2SQL product bundle consists of:

· Migration Utility: GUI utility to migrate Btrieve/Pervasive.SQL files using DDFs

· Mds Migrate Table: Command-line tool to migrate Btrieve/Pervasive.SQL files using DDFs

· Migrate UniPaaS Data: Command-line tool to migrate a uniPaaS XML formatted application

· Mds Migrate File: Command-line tool to migrate Btrieve/Pervasive.SQL files when DDFs cannot be produced.

· Database driver: DLL to replace the Btrieve Engine to execute database operations on the SQL backend

· SDK: Additional functionality that is available in the driver

· Plus trace tools and utilities to validate DDFs and the data migration

NOTE: The Btrieve Engine is contained in wbtrv32.dll or a later version called w3btrv7.dll. Mertech provides replacements for both dlls.

Figure 1 How the Database Driver DLL Works

Companies using applications written for the Btrieve database engine are finding significant challenges when they convert to the newer, more robust SQL database engines. Mertech’s middleware products are designed to:

· Alleviate the costs associated with migrating applications working against existing Btrieve data sources to MS SQL Server, Oracle, MySQL, and PostgreSQL databases.

· Provide developers with an efficient, platform-independent solution using existing code.

· Create the opportunity for the developer to immediately leverage existing applications with an MS SQL Server, Oracle, MySQL, or PostgreSQL backend.

· Accomplish all of these tasks without compromising the stability or speed of the application and without requiring any source code changes.

· Translate calls made to the Btrieve data manager API into SQL statements that work with the target SQL backend.

Related Topics

Supported Database Drivers
Installed Files
License Registration

Copyright© 2007 - 2015 Mertech Data Systems, Inc, All rights reserved.


Supported Database Drivers

Supported Database Drivers

BTR2SQL for Microsoft SQL Server

If you use Microsoft SQL Server, then use the Mertech BTR2SQL for SQL Server drivers. The BTR2SQL for SQL Server native database driver for MS SQL Server uses an OLEDB programming interface to access the MS SQL Server database. This direct connection to the server does not require any ODBC drivers or DSN entries.

NOTE: BTR2SQL for SQL Server supports MS SQL Server 2005 thru MS SQL Server 2014, including LocalDB.

LocalDB was introduced in SQL Server 2012. It is a new version of SQL Express that is dedicated to developers. LocalDB is easy to install, requires no management but offers that same programming support as SQL Server Express. LocalDB can also be used in the production environment if it fits the application needs. The default server name for MS SQL Server LocalDB is (localdb)\v11.0.

BTR2SQL for MySQL

If you use MySQL, then use the Mertech BTR2SQL for MySQL drivers. The BTR2SQL for MySQL native database driver uses the MySQL C programming interface to access the MySQL Server database. This direct connection to the server does not require any ODBC drivers or DSN entries.

NOTE: BTR2SQL for MySQL supports MySQL 5.1 thru MySQL 5.7.

BTR2SQL for Oracle

If you use Oracle, then use the Mertech BTR2SQL for Oracle drivers. BTR2SQL for Oracle uses Oracle Call Interface (OCI) to access the Oracle database. BTR2SQL for Oracle communicates directly with the Oracle server through Oracle’s network interface, SQL*Net.

NOTE: BTR2SQL for Oracle supports Oracle 9.x thru Oracle 12c.

BTR2SQL for PostgreSQL

If you use PostgreSQL, then use the Mertech BTR2SQL for PostgreSQL drivers. The BTR2SQL for PostgreSQL native database driver uses the PostgreSQL C programming interface to access the PostgreSQL Server database. This direct connection to the server does not require any ODBC drivers or DSN entries.

NOTE: BTR2SQL for PostgreSQL supports PostgreSQL 9.1 thru PostgreSQL 9.4.

Related Topics

Product Overview


Installed Files

Installed Files

The following files are installed by default in the directories shown under <Program Files>\Mertech Data Systems\DB Drivers\Btrieve. The files identified are the relevant files. Additional files are also installed.

Directories and files accessible from a shortcut in the Mertech’s ISDBC Drivers for Btrieve group of the Windows Start menu are marked with an *.

Directory | Contains
bin\ | The migration utility, command-line migration tools, drivers and the license files.
* | btr2sql.exe | Btrieve to SQL GUI Migration Utility
* | DDFValidator.exe | Command-line tool to verify DDFs definitions
 | libmysql.dll | Lock plugin for MySQL
 | libpq.dll | Primary front-end library for PostgreSQL.
 | mds_gobal.ini | Configuration files with settings that can be used as an alternative to registry and environment variables
 | MdsEncryptPassword.exe. | Command-line tool to generate an encrypted password
 | MdsMigrateFile.exe | Command-line tool to migrate Btrieve files (as a last resort) when DDFs are not available
* | MdsMigrateTable.exe | Command-line tool to migrate Btrieve files using DDFs
* | MigrateUniPaasData.exe | Command-line tool to migrate a uniPaaS XML format application
 | MigrateUniPaasData.ini | Configuration file used by MigrateUniPaasData.exe
* | MigrationValidator.exe | Command-line tool to verify transfer from Btrieve to SQL server
* | MigrationValidator_uniPaas.exe | Command-line tool to verify a uniPaaS migration
 | MigrationValidator_unipaas.ini | Configuration file used by MigrationValidator_uniPaas.exe
 | mys_btr.cfg | The MySQL license
 | mys_btr.dll | Mertech migration and MySQL database driver dll
 | ora_btr.cfg | The Oracle license
 | ora_btr.dll | Mertech migration and Oracle database driver dll
 | pgs_btr.cfg | The PostgreSQL license
 | pgs_btr.dll | Mertech migration and PostgreSQL database driver dll
 | sql_btr.cfg | The MS SQL license
 | sql_btr.dll | Mertech migration and MS SQL database driver dll
* | TraceControl.exe | Driver trace control tool
clients\pgsql | Required dependencies for the PostgreSQL client library
deploy\ * | Mertech replacement for Btrieve Access dlls (wbtrv32.dll, an earlier version of the dll, and w3btrv7.dll, name of the dll starting with Pervasive.SQL2000). Drivers are specific to the target backend and are contained in the mssql, mysql, oracle and pgsql subdirectories. · wbtrv32.dll: Replacement for Btrieve wbtrv32.dll · w3btrv7.dll: Replacement for Btrieve w3btrv7.dll NOTE: The mysql subdirectory also contains the dll, ini file and documentation for mds_app_lock.
help\ | Online help, a Quick Start Guide and Video.
* | BTR2SQLQuickStartVideo.exe | Video explaining how to migrate a Btrieve application using the BTR2SQL GUI Migration Tool when DDFs are available.
* | BTR2SQL.chm | The compiled online help for BTR2SQL. Provides context sensitive help for the GUI Migration Utility, a description of the BTR2SQL SDK, and information about the BTR2SQL command-line migration tools.
* | Quick Start Guide.pdf | An overview of the migration process using the BTR2SQL GUI Migration Tool.
sdk\ * | The documentation, include files, and samples for the BTR2SQL software development kit (SDK). Also contains a sample mds.ini file in the samples subdirectory and source code and executable for MdsMigrateTable in the migration subdirectory.

Product Overview


License Registration

License Registration

If you have already purchased the ISDBC drivers, then you have received a registration file in a ZIP file along with instructions. If you are evaluating the product, a limited license is already installed. If you want to request a fully functional license file, please contact Mertech Data Systems, Inc. at https://www.mertechdata.com/contact-us/.

The ZIP file you receive also contains a license file: sql_btr.cfg for MS SQL Server, mys_btr.cfg for MySQL, ora_btr.cfg for Oracle, pgs_btr.cfg for PostgreSQL. The file must be in the Windows PATH. The license is required by the Migration Utility, so place a copy of the CFG file in the installation folder (<Program Files>\Mertech Data Systems\DB Drivers\Btrieve\bin).

The license file is also required at runtime by your application. See 3. Substitute the Btrieve Access dlls for information to replace the Pervasive dlls — we recommend that you place a copy of the license file in the same folder with the access dll and your application exe.

Product Overview


What’s New

What’s New

This section describes the updates that have been made to the BTR2SQL product starting with version 3.

Related Topics

Version 5.2
Version 5.1
Version 5.0
Version 4.5
Version 4.2
Version 4
Version 3


Version 5.2

Version 5.2

MSSQL

Server Support

Support is available for MS SQL Server 2014 and LocalDB.

MySQL

Server Support

Support is available for MySQL 5.7.

DATETIME and TIMESTAMP

DATETIME and TIMESTAMP fields are stored as their associated SQL type, which combines a time of day with fractional seconds (requires that MySQL client library version 5.6.4 or above be installed).

BigInt

8-byte integer fields map to the BigInt SQL type by default (instead of Binary). This is faster and displays better in SQL tables. If the Integer(8) field is a descending index and all 64 bits are required, you will need to change the SQL type from BigInt to Binary.

mds_app_lock.ini

A sample mds_app_lock.ini file can be found in the installation folder: <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\deploy\mysql.

Oracle

Server Support

Support is available for Oracle 12c.

Lock Conflict

The extremely small namespace Oracle allowed in the DBMS_LOCK package (30 bits) can lead to a lock conflict. A custom lock package is provided that expands the namespace. This should eliminate false lock errors.

PostgreSQL

Server Support

Support is available for PostgreSQL 9.4.

GUI Migration Utility

Attach and Detach

Attach and detach options were added to the BTR2SQL GUI Select Database dialog box to allow users to specify the database file location. (MSSQL)

Command-line Migration Tools

MdsMigrateFile

A new command-line tool, MdsMigrateFile, is installed. MdsMigrateFile can be used to migrate Btrieve files, as a last resort, when DDFs are not available.

MigrateUniPassData

Several improvements have been made to the MigrateUniPassData command-line tool, which performs migrations for Magic applications.

APIs

New APIs, MdsSetSetting and MdsGetSetting, are available to programmatically set and retrieve global (mds_global.ini) and folder specific (mds.ini) settings.

The Btrieve APIs B_RENAME_FILE and B_DELETE_FILE are supported.

Support was added for NextInChunk and AppendToChunk sub functions of the B_GET_DIRECT and B_CHUNK_UPDATE functions.

When B_UPDATE or B_DELETE are called, a Status 80 is returned if any of the fields has changed since the last read.

General

A new setting in mds_global.ini, PERVASIVE_DLL_NAME, allows you to specify the name and optionally the path of the Btrieve access dll to be used for reading Btrieve data during migration.

The default for the IDENTIFIER_CASE setting was changed from "Upper" to "Original" (MSSQL and Oracle).

Shortcuts were added for easy access to the deployment folder (where the Btrieve replacement dlls are located) and to the SDK.

New blog posts and white papers are available to describe multi-migration scenarios.

Examples to show how to use Embedded SQL to create and drop databases and users are provided in sdk\Samples\MdsApis\C\MdsApiSamples.cpp.

What’s New


Version 5.1

Version 5.1

License

BTR2SQL v5.1 comes with a fully functional license that is valid for 14 days after installation.

Support for Btrieve 6.15

A more explicit error message displays if Btrieve dlls are not accessible.

PostgreSQL client library

The required dependencies for the PostgreSQL client library are installed in <Program Files>Mertech Data Systems\DB Drivers\Btrieve\clients\pgsql and can be copied to the <winsys32> directory.

FUJITSU COBOL

A new Btrieve Type, B_TYPE_NUMERICSAF (100) was added, enabling the Mertech drivers to properly map the Fujitsu COBOL signed data type.

Support for Microsoft SQL Server Filegroups

The MS SQL Filegroups feature is used to group database objects and files together for allocation and administration purposes. The BTR2SQL GUI Migration Utility, mds.ini file, command-line migration tools and migration API were modified so separate Filegroups can be selected for tables, indexes, and text storage. Choosing separate Filegroups can streamline administration and increase data access speed.

Migration Utility

A Quick Start Guide and Video are provided to facilitate getting started as a new BTR2SQL user.

Three new fields were added for to the Convert File dialog box for assignment of MS SQL Filegroups.

SQL

Locking

Two settings in the INI file, SERVER_SIDE_LOCKING and SQL_LOCKS (new) control how locking is implemented.

Execution

The dataBuffer parameter was added to the B_SQL_EXECUTE command so a call to B_SQL_PREPARE is no longer required before executing embedded SQL statements.

Support for SQL Case Identifier

A new setting in the INI file, IDENTIFIER_CASE, controls how table and field names are handled.

Tracing

Startup Tracing

Startup tracing was added to the mds_app_lock plugin for MySQL and PostgreSQL. This server-side trace alerts to problems with the mds_app_lock plugin during the SQL server startup.

Trace Query Times

A new setting in the INI file, TRACE_QUERY_TIMES, allows the query time output to be suppressed during tracing. The default for this setting is No.

CACHE

A new setting in the INI file, CACHE_GETEQUAL, determines whether a record is fetched from local cache.

What’s New


Version 5.0

Version 5.0

Server Support

Supported SQL Server Version Embedded in the License File

The driver itself is no longer tied to a specific SQL server version. Instead, the supported SQL server version (minimum and maximum) is embedded in the license file. This means you can upgrade to the latest SQL server versions without changing your driver.

PostgreSQL

Version 5.0 introduces PostgreSQL 9.1 to the list of supported servers.

MSSQL 2012

Version 5.0 adds support for Microsoft SQL Server 2012 Edition.

SQL Native Client

By default the driver now looks for Microsoft SQL Server 2012 Native Client and then falls back to version 2008 or 2005 if version 2012 is not available.

Proper Locking Support in a High-Speed Multi-Process Environment

The SQL update command includes the call sequence lock, update, unlock. Although all the calls execute in the same network request, the lock and unlock are not tied to the transaction - they are performed immediately whereas the updated data is not available until the commit is performed.

The SQL update command was modified so that the unlock occurs after the transaction commit. This resolves problems observed in some high-speed multi-process environments.

Tracing

Startup Trace

A startup trace is now available to record errors detected during the early stages of driver initialization (before the regular trace begins), which can help to track down tracing and license file issues.

Migration Utility

Improved GUI Interface

The Migration Utility was rewritten to use the .NET framework resulting in an improved interface.

Support for Selecting Data Type Mappings from Btrieve to SQL

BTR2SQL v5.0 allows you to modify the field-type mappings used during migration. General preferences and table-specific settings are supported. A drop-down list of field-types ensures that only suitable mappings can be selected. Most of the time it is not necessary to change the assignment made by BTR2SQL, but there may be instances when this is useful. For example, you can map a string field to a date/time field to provide meaningful interpretation of date/time information.

Improvement in Oracle Data Migration Using DIRECT PATH Load

SQL*Loader provides two methods for loading data: conventional path load and direct path load. A conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. A direct path load parses the input data according to the description given in the loader control file, converts the data for each input field to its corresponding Oracle column data type, and builds a column array structure (an array of <length, data> pairs). You can now choose the direct path load, which is faster than the conventional path load.

Renumbering Indexes

Indexes are automatically renumbered to eliminate gaps after an index is dropped. This feature may be particularly useful for Magic developers, where adding and dropping indexes occurs more frequently.

Improved Command-line Tools

A new command-line tool, MigrateUniPaasData, was added for direct migration from uniPaaS XML applications to the SQL backend. A second tool, MigrationValidator_uniPaas, was also added to verify this migration.

What’s New


Version 4.5

Version 4.5

MySQL

Version 4.5 introduces MySQL 5.1 to the list of supported servers.

Btrieve API Support

Manual assignment for autoinc fields

Normally an AUTOINC field is automatically assigned a value by the server – the application places a zero in the dataBuffer. Sometimes it is necessary for the application to give a specific value to such a field – the application fills the dataBuffer with the desired value. This is now supported.

Support for DOS apps

There are a few applications still left based on the DOS OS. If your application is one of these, you are using Pervasive’s DOS driver, which forwards the DOS interrupt call to the 32-bit access dll. Replacing the Pervasive w3btrv7.dll with the Mertech version will cause the application to utilize our SQL access. The process of migration and deployment are the same as for a Windows 32-bit application except you must also deploy the Pervasive DOS driver.

Migration

Utilize the index list in Btrieve file

Previously, the indexes defined in Index.DDF were used to create the indexes on the SQL server. In many cases, these indexes did not accurately match those in the Btrieve file causing various issues at runtime. To be more compliant with what the application expects from the driver, the migration now utilizes the key definitions in the Btrieve file instead of those in the DDFs. If the Btrieve file is not available during migration, the DDFs will be used.

Supporting >8K record sizes

The SQL backends do not support a record size greater than 8K. In order to accommodate the rare Btrieve record larger than 8k, the migration process will use varchar(max) and varbinary(max) data types rather than varchar and varbinary. It is suggested to split the file into smaller records since utilizing these large data types comes with a performance overhead.

SDK

"Selective Column Fetching" (B_SET_FIELD_FETCH)

B_SET_FIELD_FETCH allows the application to control which fields are retrieved during a B_GET or B_STEP function. This is a simple way to add huge performance benefits to sections of the application that read large records but only require a few of the fields.

MdsPrepareDB

A migration function that allows you to setup the server and/or database with needed objects (stored procedures, views, etc.) for running your Btrieve application with the Mertech driver.

MdsEncryptPassword

If your application creates data folders on the fly (for a new customer for instance), you will probably want to create a new mds.ini file in the folder with server-specific credentials. The password in the INI is encrypted and given in hex format. To convert a plain text password to the required format, call MdsEncryptPassword (or B_ENCRYPT_PASSWORD).

mds_global.ini

A new INI file is introduced as an alternative to the registry and environment variables. All prior settings are now available in the INI file. The location of the mds_global.ini file is up to you. The first mds_global.ini found on the usual PATH is used. We recommend placing the mds_global.ini file in the same location as the access dll and license (.cfg) file.

Misc

Lowered memory consumption

Structure sizes and dynamic memory usage were reduced to lower the overhead of the driver.

Lower record size on server

Where possible and depending on the server backend, some data types on the SQL server have been reduced in size and utilize a more native data type. For instance, MDS_RECNUM changed from NUMBER(28) to a BIGINT. This will reduce the data storage and retrieval time on the server. Note, existing tables will remain with the previous definition.

Geometric Progression of cache

Applications often do a scan of a file or a portion of the file while at other times only individual records are read. In order to make both scenarios as fast as possible, the caching mechanism in the driver watches the series of function calls and determines when the application is doing a table scan. As this progresses, the driver increases the record cache to provide the fastest access to the records needed.

Tools

Trace Control Tool installed

Have trouble finding just the right registry spot to set the trace file up? We now install a Trace Control tool that provides easy access to the settings.

Improved screen updates during migration

Small improvements to the interface during migration provide better and timely feedback so you are not stuck wondering if the tool is hung.

More checks in DDF Validator

The DDF Validation tool adds more checks to help ensure your migration is a smooth success.

License Registration

The license registration tool is removed. Rather than typing in a long registration code, you will be sent a license CFG file via email. All license codes are also always available for download from our website using your Mertech Data Systems Purchasing Account.

What’s New


Version 4.2

Version 4.2

Server Support

Oracle 11g

The 11g OCI client library is used internally now. This fixed a few issue found in the older library when accessing newer OCI dlls. The driver is also tested with the 11g server for full support.

UTF Character Set in Oracle

If Oracle database is using UTF character set, the migration will now use NVARCHAR2 instead of VARCHAR2.

Tracing

TRACE_BUFFERS

This new value (registry or environment variable) can contain any combination of 'pb', 'kb', 'db', or 'sql'. Many times you want to see the activity of the application without logging the large Data buffers. Setting TRACE_BUFFERS=’kb,sql’ will cause the trace to only log the Key Buffer and SQL commands so the trace file will be much smaller.

Btrieve API Support

Expanded Legacy NULL support

Legacy null support expanded to properly filter records based on the null "value" provided in the Btrieve index definition. An extra field is added to the table ending in "_isnull" and this field is then included in the SQL index.

Improved support for Titan controls

Titan utilizes some "special" values in fields that are not normally valid as a float or date for instance. The driver checks for these values and reacts accordingly.

B_DATETIME (type 30)

B_DATETIME is now fully supported.

Milliseconds in B_TIMESTAMP

The string value stored in the SQL database is now expanded to include milliseconds and its format is changed to military style (YYYY-MM-DD-hh-mm-ss.nnn) so it sorts properly as a string.

Btrieve Version Number

Sometimes an application checks the version of Btrieve to validate the API support or to narrow the range of tested scenarios. The Mertech driver always reports itself as version 7.9 in the B_VERSION call so some applications refuse to run. To override the information returned, you may add a registry entry: HKLM\SOFTWARE\[Wow6432Node\]Mertech Data Systems\DB Drivers\BTR\<Driver>\v4

Add a new key "BtrieveVersion".

Add a string value — call it the name of the process and the value is the desired version. For instance, "wbexec32.exe"=9.11.

Indexes defined with different lengths

Btrieve allows different lengths to be defined for the same "field" in multiple indexes. So you might have one index defined as 100 bytes for the address field but another index only defines 20 bytes. This kind of partial definition is not supported by most SQL backends and thus not supported by the driver. The index will be defined as the full length of the field. This may affect such operations as GET_EQUAL.

Related to this, it is possible to have a large field (a string of say 1000 bytes). The indexes can only define 255 bytes at most. In this instance, the "field" will be split into two when migrated to SQL. The original field name is used for a string of 255 and another field is added with "_extra" appended to the name to hold the remainder of the data.

B_SET_DIR and B_GET_DIR

These two simple functions are implemented now.

Overlapping fields

The DDFs allow multiple fields to be defined for the same portion of a record. It is somewhat common to define one binary field that covers several smaller fields or to have one index segment defined for multiple fields rather than multiple segments (one for each field). This approach causes several problems.

When the dataBuffer is split out to individual fields, the data will be duplicated into SQL – the smaller fields and also the overlapping field. This is wasteful and depending on the exact definitions of the fields, may have conversion errors into the SQL data types.

When the dataBuffer is pieced back together for a read, there are two ways of looking at the data so the conversions could get corrupted.

An error will be given during migration for tables defined in this manner. To avoid the situation, the developer should decide which definitions are most correct. Normally, this means removing the definition of the overlapping field in favor of the smaller more accurate definitions.

Misc

Use HKEY_CURRENT_USER

HKEY_LOCAL_MACHINE is now only used for the initial information obtained during installation – for instance, the location where the product was installed. All other information is stored and retrieved from HKEY_CURRENT_USER. For instance, trace settings are now found at HKEY_CURRENT_USER\Software\Mertech Data Systems\DB Drivers\BTR\MySql Driver\v4 for the MySQL driver.

View SQL Button

A view needs to be created on the Oracle server during migration. The migration tool will pop up a dialog box asking for the SYS password in order to run the needed SQL command. Most administrators will not give you the password and will instead want to run the SQL themselves. So, the dialog box offers a button which will show you the SQL. Pass this SQL off to your Oracle administrator to be run.

SDK

Migration API

MdsMigrateOneTable, MdsMigrateAllTablesInDdfs, MdsMigrateAllTableInConfig

These new APIs give you full control over the migration process and allow you to automate it for seamless deployment.

A command-line migration tool is also provided which has more flexibility and automation than the GUI tool. Source is provided as a sample of the migration API.

Improved DOCs

The documentation has been broken into logical sections or "modules". Overview and introduction pages are added to give you more information about the group of functions. And the information has been reviewed and expanded upon where needed. Let us know if you need more details for any of the functions.

What’s New


Version 4

Version 4

Server Support

MSSQL 2008

Version 4 adds support for MSSQL 2008 and removes support for MSSQL 2000.

SQL Native Client

Version 3 of the driver used the OLE-DB client distributed with MDAC. Version 4 now utilizes the newer "SQL Native Client" (2008 or 2005). This newer client provides the same interface but has improvements and fixes. It can be downloaded from Microsoft in the Feature Packs available here (http://msdn.microsoft.com/en-us/data/aa937733.aspx)

Support for new MSSQL 2008 data types DATE and DATETIME2

These two types have a larger range and thus better support the Btrieve Date. If the 2008 client is used during migration, these new types are chosen by default. If the 2005 client is used, the migration and runtime driver will treat date columns as the older DateTime type with 01-01-1753 minimum value.

Migration sets MSSQL database to use snapshot isolation

Snapshot isolation has always been needed for proper transaction control; the migration is being proactive now to ensure this happens. During runtime, the trace file will show a warning if it detects the database is not in snapshot mode.

ALTER DATABASE <db> SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE <db> SET READ_COMMITTED_SNAPSHOT ON

Tracing

Controlling trace in the application

Several APIs were added to the SDK to allow the application to have control over the trace file output and to log messages into the trace.

& See the SDK documentation Modules tab > Driver Trace Control for details

Buffered file I/O utilized for the trace output

Buffering the output significantly increases the speed of tracing. A level 1 trace is almost an unnoticeable impact on the application’s performance now.

Because the output is buffered, looking at the trace while the application is running will not show you the last few operations. And if the application exits unexpectedly, the last part of the trace will be lost – making it difficult to see what might have caused the crash. You can disable the buffered I/O by setting TRACE_FLUSH to "ON" in the registry or environment variable (along with the usual TRACE_ON and TRACE_LEVEL).

Trace file automatically breaks at 512MB size

A new file is started with a number added to the end of the filename when the file size reaches 512MB. As the trace grows you will thus see "mds.tra", "mds_1.tra", "mds_2.tra", etc. This allows you to periodically remove pieces so the hard drive doesn’t fill up and makes viewing a section of the trace easier as most editors won’t handle 4GB files.

"Slow" queries are reported

Each query that is run is timed. If the query takes longer than a predefined time slice, it is reported as slow. You can search the trace file for "Performance:" to find these entries. Generally queries to find data will run in less than 0.01 seconds with an occasional "burp". If you find queries taking many times longer, this could indicate a problem with an index.

Btrieve API Support

True-NULLs

Version 4 now includes the "null indicator segment" field in the SQL database so this extra value is properly maintained in the dataBuffer.

Full support for Btrieve Client ID

Each "client" receives a new context inside of the driver including a separate login to the server, transaction support, cursors, etc.

Override flag for B_CREATE to force driver to ignore an existing INT file

Version 4 introduces a flag allowing you to ignore the existing INT file:

B_CM_OVERWRITE_IGNORE_INT_FILE= -6

Opposite this, perhaps you want the driver to always use the definition in the INT. Two more flags force this behavior (the FILESPEC in the dataBuffer is completely ignored):

B_CM_OVERWRITE_FORCE_INT_FILE =-99

B_CM_NOOVERWRITE_FORCE_INT_FILE=-100

& See Creating tables on the fly for an example using the B_CM_OVERWRITE settings

Support for extra data types defined in fieldext.ddf

Version 4 now reads this information and makes a wiser choice for the type used in SQL.

B_EXTENDED_VERSION

It may be interesting, or even needed, for your application to know if the Pervasive version or the Mertech version of the access dll is loaded and you may want to know the version of the Mertech driver (B_VERSION reports 7.9 always). Or perhaps you need some version information for the SQL server currently connected. B_EXTENDED_VERSION can do all of this. Calling this function against the Pervasive dll returns a status 1 (unknown operation) making it easy to distinguish the two.

What’s New


Version 3

Version 3

Performance and Usability Improvements

Version 3 represents Mertech’s continuing commitment to create products that will completely fulfill your application's needs. We have added several new features to the application API to improve performance and functionality. We have also added many new tools to help build the Data Definition Files (DDFs) and validate a successful migration.

Windows 16-bit support

Although Windows 3.1 is now a thing of the past, there are still many applications running that were written for 16-bit Windows. These applications use the Pervasive w1btrv7 dll instead of w3btrv7. An introductory version of this dll is now included in the Version 3 installation. At this time, it is only supported on Windows NT and above. It is not supported if the application uses the Chunk APIs. The dll uses thunking to forward the 16-bit calls to the w3btrv7 dll, so both dlls are required. You can test it by using the old Btrieve function executor, wbexec.exe.

Driver prefix no longer needed in DDF files

The Driver Prefix was initiated in the DataFlex product line. DataFlex uses a prefix in the table location to determine which driver to load for data access. When the Btrieve product was introduced, the same concept was used. Either sql_btr: or ora_btr: is prepended to the file location column in FILE.DDF (table list). Btrieve does not use or need this and it caused problems in applications that depend on DDFs.

Therefore, we removed the driver prefix. Instead of sql_btr: in front of the file location in BTR2SQL, a checkmark now appears on the file's icon in the File Selection grid. This checkmark indicates that the file has been migrated to an SQL table. It is easier to see and you no longer need to use the Add/Remove Driver Prefix buttons.

MS SQL Server 2005 support

Support for SQL Server 2005 has been included within BTR2SQL Version 3 to allow functionality within existing or future deployments to SQL 2005.

Improved database migration tool

Several small issues have been fixed or enhanced on the Migration Utility. Continued improvement of this tool is a priority for us in the months ahead.

Performance improvements

Overall improvements in find and update performance, cleaning up of some code that caused unexpected application aborts.

Documentation improvements

We have moved from PDF format for distribution to compiled help file and html.

Mertech SDK for BTR2SQL

In the past, the Mertech SDK has simply consisted of the Btrieve API — the Btrieve SDK, sub the Mertech w3btrv dll. Version 3 implements several of the operations previously ignored, specifically the Chunk operations. We have also introduced some new functions that your applications can use to enhance performance and to allow on-the-fly table manipulation. For detailed explanations of all of these functions and examples, please see the SDK documentation. The documentation for the SDK and examples with project files are installed in the <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\sdk\ subdirectories.

New functions to handle Chunks

Btrieve is famous for the ability to store large amounts of data quickly. Before the term BLOB was widely used, Btrieve supported extremely large records which could hold any object, picture, file, etc. The data length and buffer is limited to 16-bits, which means that the API only supports about 64k. In order to access larger data records, Chunks were invented. The application inserts or fetches the fixed portion of a record, which is always less than 64k, using the standard B_Insert and B_Get/Step. Then the application uses the Put and Get Chunk APIs to insert and read the rest of the data in small pieces.

Many applications have used this feature to store pictures and other objects in their database, so B_Get_Chunk(23) and B_Update_Chunk(53) operations have been long requested. Nothing in your application needs to change. If you were previously unable to use the Mertech driver due to the lack of Chunk support, give Version 3 a try.

Bulk inserts using the new FAST_INSERT functions

B_Insert is fast, but when you use an SQL database at the backend, there are more efficient ways to insert large numbers of records. This is especially true when the records themselves are large.

B_BEGIN_FAST_INSERT, B_FAST_INSERT and B_END_FAST_INSERT have been added to support this new method. Native SQL APIs are used to let you take advantage of the fastest loading method for the backend in use. As of March 2006, only MS SQL Server is supported.

While these are new operations not supported by Btrieve itself, they are implemented through the standard BTRV/BTRCALL function interface. Consult the SDK documentation for detailed examples and usage.

The Fast Insert operation supports a callback mechanism to insert large records. This is similar to the Update Chunk functions discussed above. Fast Insert allows the application to insert the fixed portion of a record and then it feeds small pieces of the variable portion until the record is complete. This works well if the application is reading the data from a stream; read 16k from the stream, append 16k to the data record do again. However, this is still not the fastest method of getting data into the SQL backend.

Since the Fast Insert operations are new, the full 32-bit data length is allowed. Thus, the application can read the entire record including the variable portion (BLOB) prior to the insert, and then pass the entire data buffer, up to 2G, to the Fast Insert operation. The speed of this method is phenomenal compared to a B_Insert and multiple UpdateChunk calls. A sample project under the sdk\samples directory shows a comparison of insert speeds.

Schema manipulation

Perhaps your application needs to create temporary data files during operation or add some new tables over time. You may need some new columns in a file. In the past, your only option was to update the schema in the DDFs and then perform another migration of the affected files.

Now, the application can use a set of new functions similar to Pervasive's DTI functions; MdsAddTable(), MdsRenameTable(), MdsDropTable() and MdsAddFields().

The functions do what their names imply. They are documented in the mds_btrieve.h header file located in the <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\sdk\include directory.

Embedded SQL

You may occasionally want the application to communicate with the SQL backend directly in order to access features that previously did not exist in the Btrieve API.

The new Embedded SQL APIs were created to remedy this situation. They are transacted through the normal BTRV/BTRCALL API using additional Operation code constants that have values of 10000 and above. For example, you can use B_SQL_EXECUTE to execute an SQL command and B_SQL_FETCH to retrieve the results of the query.

New validation tools

Additional customer feedback involved the need to validate the steps of the migration process. You have many tables, fields, and even more rows of data. How do you know the DDFs are defined correctly and that the data was successfully migrated to the SQL backend?

DDF Validator

Building DDFs for your Btrieve database can be the largest undertaking of moving the application to SQL. This is listed as a prerequisite rather than a step in the migration process. Without DDFs, your data is just a conglomeration of meaningless bytes. Searching for headers and sifting through possibly thousands of field names, data types, sizes, flags, etc. can be exasperating and error-prone. Even when you think that you have fixed everything, a small quirk may remain that doesn't appear unless you read every record and check each field.

This is exactly what the DDF Validation Tool does. It uses a set of tests to verify that the definitions that you created match the actual records in the file.

Even if your applications have contained DDFs for years, it is possible that a bad value may have been inserted into the tables over time, which can cause problems when you move the data to SQL. We recommend that you at least run the DDF Validator on a subset of the data rows to make the transition as smooth as possible.

Migration Validator

This tool validates the actual data migration. It uses the standard Btrieve API to read rows from Btrieve and from SQL simultaneously and then compares the data buffer returned. Any mismatch displays in the output.

What’s New


Before You Begin

Before You Begin

Before you begin the migration process, make sure that your database client is able to connect to the database server. If you are going to perform the migration on the same machine where the database server is installed, then all the pieces you need to establish the connection are already in place. However, if you are connecting to a database server from a client machine, then you must make sure that you have the client libraries installed and the client configured to connect to the server. For example, with Microsoft SQL Server, you will need to use the Client Network Utility to set up server alias and connection protocol. With Oracle, you will use the SQL*Net Configuration assistant. See those products for more details.

After your client and server are set up properly and are able to communicate, then you can proceed with the migration.

Related Topics

Prerequisites
Database Limitations
DDFs
Validate Data Definition Files using DDF Validator
Lock Plugin for MySQL
Hardware Considerations
Utilizing Function Executor


Prerequisites

Prerequisites

IMPORTANT: Be sure the appropriate 32-bit client is installed and that the client and server version numbers match and are supported by BTR2SQL. You can develop 64-bit applications, and if so 64-bit libraries are available from Mertech. However, the Migration Utility requires the 32-bit client to be installed. If a 64-bit client is installed the Migration Utility may close unexpectedly when you attempt to login.

Btrieve 6.15 or Pervasive.SQL 7.0
(or greater)

You need a Btrieve engine during data migration to read the original data. This may be the workgroup engine, a server engine, or a client with the server engine hosting the data on another computer.

See the Pervasive documentation for information on installation and configuration.

Btrieve data files with schema defined in Pervasive.SQL DDFs

Data Definition Files (DDFs) define the layout, or schema, of the tables in the Pervasive.SQL database. The Migration Utility requires the DDFs to migrate the data to SQL. See the Pervasive documentation and many other resources and tools available online for further information on building DDFs if they are not included in your database.

Alternately, you can use MigrateUniPaasData, the Mertech command-line migration tool that works with Magic uniPaaS applications where the source code is stored in XML format.

Microsoft SQL, MySQL, Oracle, PostgreSQL

MS SQL Server, Oracle, MySQL, and PostgreSQL are the four SQL backends currently supported by the ISDBC for Btrieve drivers. One of these backends must be installed and running. The client software must be configured on the computer where the migration and the application are run. Refer to your database server documentation for details.

MS SQL Server

· The DB_OWNER role should be set for the User ID identified for the migration process.

· For the fullest support of data types, the SQL Server 2012 Native Client should be used. Alternatively, the 2008 or 2005 Native Client can be used. (Download the Native Client from the Feature Pack available here: http://msdn.microsoft.com/en-us/data/aa937733.aspx)

· An MSSQL client is included with the BTR2SQL installation.

Oracle

· Using Oracle's Security Manager, add the Select Any Table privilege to the user ID used for the migration process.

· Requires OCI.dll, which may have other dependencies. All of these should be included with the client software.

· Missing Oracle dependencies may be resolved by installing the Visual C++ 2010 Redistributable package. (https://www.microsoft.com/en-US/download/details.aspx?id=8328 )

MySQL

· The User ID used during migration must have SUPER rights; or the MySQL server must be 5.1.6 or above.

· A client libmysql.dll is available in <Program Files>Mertech Data Systems\DB Drivers\Btrieve\bin. The version of the installed client must match the version of the server.

PostgreSQL

· The server must be 9.1 or above.

· PostgreSQL does not support storing Zero (null) bytes in a string. If your data needs to include a Zero byte, you must include the Binary flag on the field in the DDFs (bit 12 / 0x1000 on field flags).

· The PostgreSQL citext module provides the case-insensitive character string type CITEXT, which allows for case insensitive fields without the need for inverse key columns.

· Dlls are installed in <Program Files>Mertech Data Systems\DB Drivers\Btrieve\clients\pgsql and can be copied to the <windir>System32 directory. The version of the installed client must match the version of the server.

.NET Framework 3.5

The version 5 migration tool was rewritten to use the .NET framework resulting in an improved interface. The version 5 migration tool requires .NET Framework 3.5. The installation automatically installs .NET 3.5 if it is not already installed, except on Windows 8.

NOTE: Windows 8 includes .NET 4, but not the required 3.5 version. When you run an application that requires an older .NET version, Windows 8 displays a dialog box asking you to download it.

Before You Begin


Database Limitations

Database Limitations

In some cases, the SQL servers have different limits or data type ranges than supported by Btrieve. Most applications are not affected by the difference, but it is good to be aware of these differences.

The largest difference is in record size. Btrieve supports up to about 64k in the fixed portion of the record; however, most SQL servers max out around 8K. The driver tries to compress records larger than 8K by converting large text fields to a blob form — for instance, it will use varchar(max) instead of varchar on MSSQL which is stored in a different manner and takes up very little space in the original record.

Microsoft SQL Server

Max 1024 columns

Max 8060 bytes per record

MSSQL 2005 limits dates to a lower range of 1753-01-01.

Oracle

Max 1000 columns

MySQL

InnoDB is the supported storage engine with the driver.

Max 1000 columns

Max 8000 bytes per record

Max 64 indexes and 16 segments per index

PostgreSQL

Maximum 250 - 1600 columns depending on column types

Before You Begin


DDFs

DDFs

If you are a Btrieve user, then you are probably aware of the challenges that you face when you try to integrate the old and new applications that do not have Data Definition Files – the infamous DDFs. These files are absolutely essential to describe the entire layout of data, or schema, within the Btrieve files. But it can be frustrating and intensely time-consuming when you try to build DDFs manually. Sifting through C header files for definitions of the records, deciding which data type is required for each field in the database, and a myriad of other details consume hours of valuable resource time, and the end result may still be plagued with inconspicuous errors.

Fortunately, Pervasive Software has developed and provided their new tool DDF Builder with Pervasive.SQL. Many other tools of this type are also available on the internet to help you with this daunting task. However, even though these tools can be remarkably helpful, identifying the definitions for a 300-table database can still be prone to errors. Initial visual inspection of the revised data in the Control Center may be misleading. It is easy to miss a field, a misaligned offset, or the wrong data type, such as zstring instead of string.

NOTE: Mertech also provides MigrateUniPaasData, a command-line migration tool that works with Magic uniPaaS applications where the source code is stored in XML format.

Related Topics

Before You Begin
Handling variant records
The IN DICTIONARY clause
Nullable columns and the True-Null extra byte
Restart the Pervasive.SQL engine after modifying table definitions


Handling variant records

Handling variant records

Variant records live up to their name. They can display in various ways, depending on some known flag or tag. In C, this is known as a union and typically has a value in the first part of the record that dictates the appearance of the rest of the record. Before the advent of SQL and relational databases, this kind of storage was popular and Btrieve is quite famous for supporting Variant records.

Let’s examine a simple example in C:

struct Correspondence
{
char name[30];
BDATE date;
int type;  // 1=email, 2=mail,
3=fax
union {
char emailAddr[150];
AddrInfo addressInfo;  //struct size is 300
char faxNum[15];
};
}; //Correspondence

EmailAddr, addressInfo, and faxNum share the same space in the record and therefore cannot be defined as separate fields. When Pervasive introduced SQL access to the data, one way to see this data was to define three different tables, all using the same MicroKernel file. All three tables start with name, date, and type. One table also includes emailAddr, the second table includes addressInfo, and the third includes faxNum. When you use SQL to access the data, you must include an extra Where clause. For example, if you want all of the mail records, you might execute SELECT * from Coor_Mail where type=2.

Currently, the Mertech drivers do not provide any special handling to make this functionality operate. If you give the Migration Utility the command to migrate all three tables, then you will have the same records in each of the three tables. The records that do not belong will have garbage in the columns within the union portion. You need to allow this data to be used in a fully-SQL backend and to treat the union portion as binary data.

Think of the record as follows:

struct Correspondence
{
char name[30];
BDATE date;
int type;  // 1=email,
2=mail, 3=fax
unsigned char unionData[300];
}; //Correspondence

The union is replaced with an array of bytes. This does not need to be done in the C code; it is just another way to view the record. However, you will cause the Pervasive.SQL table to appear as shown in the above example. Note the size of the binary field. It must be at least as large as the largest piece in the union.

Therefore, use syntax similar to this:

CREATE TABLE Correspondence IN DICTIONARY
USING 'correspondence.mkd' (
name VARCHAR(29) not null,
deliveryDate DATE not null,
recType INTEGER not null,
unionData BINARY(300) not null
)

DDFs


The IN DICTIONARY clause

The IN DICTIONARY clause

When you create DDFs for existing MicroKernel files, you must be careful not to overwrite the existing file or its indexes. As shown above in the variant record example, the IN DICTIONARY clause can be used. This lets the Pervasive.SQL engine update only the DDF files and not create the associated data file.

The same logic applies to indexes. For example:

CREATE INDEX idxRecType IN DICTIONARY ON Correspondence (recType)

DDFs


Nullable columns and the True-Null extra byte

Nullable columns and the True-Null extra byte

Starting with Pervasive.SQL 2000, a new type of Null Indicator was introduced called True Null. Refer to Key Attributes in the Pervasive.SQL Programmer's Guide for details. In short, when a table is created with True Nulls, each nullable column is shifted by one byte. This byte is used as a Boolean value and indicates whether the column is null or has a real value.

For example:

CREATE TABLE SomeNulls ( C1 integer, C2 integer ) needs a C struct as follows:

struct SomeNullsRec {
 bool nullC1; int C1;
bool nullC2; int C2
};

If you are creating table definitions for existing data files, it is unlikely that they have this extra Boolean byte. Therefore, make sure that you include the not null clause on each column as demonstrated in the variant record example.

CREATE TABLE NoNulls
( C1 integer not null, C2 integer not null )

or:

SET TRUENULLCREATE=OFF;
CREATE TABLE LegacyNulls
( C1 integer, C2 integer )

DDFs


Restart the Pervasive.SQL engine after modifying table definitions

Restart the Pervasive.SQL engine after modifying table definitions

Some versions of the SQL engine within PSQL may remember older definitions of tables after you have modified them. Some definitions may appear to be inaccurate when you visually examine the data in the Control Center. If you suspect that this is the case, then we recommend that you restart the PSQL engine after each major modification to the table structures.

DDFs


Validate Data Definition Files using DDF Validator

Validate Data Definition Files using DDF Validator

Mertech’s DDF Validation Tool was developed to make sure that the DDFs have been defined accurately. It searches for common mistakes, such as the total length of fields that do not match the Btrieve record size for the file and for files that have not been defined. It also checks for anomalies in the names of the columns, indexes, etc. After checking for table and column level issues, it reads every row from each table in the database and examines each field. When an error is detected, a message displays providing the error code and a suggestion to help you fix the problem.

Optimally, you should run the DDF Validation tool prior to migrating your data to SQL. If you have already migrated the tables, you can convert them again if problems are encountered with the DDFs.

We recommend that you run a validation even if you have had DDFs for years. The validation does not take long to run, but you may want to limit the row scanning to save some time.

Any time that you make changes to the DDFs, we recommend that you run the DDF Validation Tool on the affected tables.

Related Topics

Before You Begin
DDF Validator command-line syntax
DDF Validator errors
Sample output from the DDF Validator tool


DDF Validator command-line syntax

DDF Validator command-line syntax

Select Mertech’s ISDBC Drivers for Btrieve | DDF Validator from the Windows Start menu. Since this is a command-line utility, a Command Prompt is automatically opened.

Run the DDFValidator script by typing the following at the command prompt:

DDFValidator [-dblocation <DDF Folder>] [-log <log file name>]
[-table <table name>] [-max-warnings n] [-max-rows n]

[-ignore <error list>] [-?]

-dblocation: The full path to the Pervasive.SQL DDFs that define the database schema. If this is not provided, the current directory is assumed.

-log: Send the report (XML format) to the specified file. When -log is not included, the XML is sent to stdout. When -log is provided, the XML is sent to the file and only the error messages display so you can see that the tool is working and not hung.

‑table: By default, all tables are tested. The ‑table parameter allows you to narrow the tests by specifying one or more tables. Use semicolons to separate the list; or include the ‑table parameter multiple times.

-max‑warnings: Stops processing after <n> errors.

-max‑rows: Allows you to narrow the testing time by reducing the number of rows checked. If your table has 10 million rows, it is likely that after approximately the first million, the rest of the rows will validate the same way.

-ignore: Error codes to ignore - separated by semicolons. If you receive many of the same warning messages and you have verified that it is not really a problem for your database, then you can filter it out to avoid littering the output.

Parameters with brackets [] are optional. Surround names with quotes if needed (space in the name).

Example: Test the Pervasive.SQL Demodata database; send report to XML file:

DDFValidator -dblocation "c:\pvsw\demodata" -log DemoDataTest.xml

Validate Data Definition Files using DDF Validator


DDF Validator errors

DDF Validator errors

Database

· (4000) More than one table defined for the same Btrieve file.

· (4001) Btrieve file found in database folder that is not defined in the DDFs.

Table

· (3000) Number of rows read did not match the number reported by B_STAT.

· (3001) Table, Column, or Index name is invalid for SQL.

· (3002) Obsolete.

· (3003) Not all of the record is defined by fields.

· (3004) Record length is too large or small.

· (3005) ACS / ISR is not supported by the Mertech drivers at this time.

Columns

· (2000) Column defined outside record length.

· (2001) NumericSTS data type is not currently support by the ISDBC drivers.

· (2002) Length of field is too large or small for the data type.

· (2003) Two or more fields are overlapping.

Indexes

· (5000) A column appears more than once in an index definition.

· (5001) A Key is defined in the Btrieve file but not in the DDFs.

· (5002) An index is defined in the DDFs but not in the Btrieve file.

Rows

· (1000) Field defined as ZString/String/LString but appears to have binary data.

· (1001) Value not recognizable for the defined data type.

· (1002) BIT columns should only be 0 or 1 (normally).

· (1003) Field defined as ZString but did not have a null terminator.

· (1004) Characters beyond the terminator character of a string.

May be bytes from adjacent field due to alignment problem.

· (1005) Value for Null-Indicator-Segment (NIS) should normally be either 0 or 1.

· (1006) Length byte for LString is longer than defined field length.

· (1007) Timestamp value looks suspicious. While almost any value is "valid", a really old or in the future timestamp may mean trouble.

General errors

· (9000) Unexpected Btrieve error.

· (9001) This tool does not support ... (a data type, etc.).

· (9002) Btrieve returned an unexpected data length value.

Validate Data Definition Files using DDF Validator


Sample output from the DDF Validator tool

Sample output from the DDF Validator tool

<report>
<timestamp time="Mon Jan 02 13:27:17 2006" />
<database location="D:\DDFVALIDATOR\TESTDATA\">
<warning severity="1" code="4001">
"Btrieve file 'NotATable.btr' found in database location but is not defined in the table list."
<suggestion> "Should this file have a table definition?" </suggestion>
</warning>
<warning severity="2" code="4000">
"Table T1 and T2 are both defined as Btrieve file D:\DDFVALIDATOR\TESTDATA\t1.mkd. This is illegal."
<suggestion> "Remove one of the table definitions." </suggestion>
</warning>
<table name="T10" recordSize="42" flags="64" numRecords="1">
<location path="D:\DDFVALIDATOR\TESTDATA\T10.mkd" />
<column name="In va#l*id" type="4-TIME" offset="1" size="4" flags="4">
<warning severity="1" code="3001" column="In va#l*id">
"Column name is not a valid SQL identifier. This may cause problems with SQL queries later."
<suggestion> "Names should only contain a space, underscore, or alpha-numeric characters" </suggestion>
</warning>
</column>
<rows>
<warning severity="3" code="1001" recordNum="1" column="In va#l*id">
"Error reading data - Bad time [145:01:145.1]."
<suggestion> "Check the offset and length of the field." </suggestion>
</warning>
<warning severity="2" code="1000" recordNum="1" column="C1">
"Field has binary data. '1' found at offset 1 in the field."
 <suggestion>
"This may mean the field should be defined as Binary instead of Text."
</suggestion>
</warning>
<warning severity="3" code="1006" recordNum="1" column="C2">
"Length byte indicates value (103) which is larger than defined field size of 42"
<suggestion> "Either the data is corrupt or the field is misaligned." </suggestion>
</warning>
</rows>
<results recordsTested="1" validRecords="0" />
</table>
<table name="T4" recordSize="0" flags="0" numRecords="0">
<location path="D:\DDFVALIDATOR\TESTDATA\t4.mkd" />
<column name="c1" type="1-INTEGER" offset="1" size="4" flags="4">
<warning severity="3" code="2000" column="c1">
"Column defined beyond Btrieve record size."
<suggestion> "Columns must fit within defined record length." </suggestion>
</warning>
</column>
<column name="field_0" type="0-STRING" offset="0" size="10" flags="0">
<warning severity="3" code="2003" column="field_0">
"Column overlaps with 'field_1'. This is illegal for SQL access."
 </warning>
</column>
<warning severity="2" code="3003">
"Some portions of the record are not defined by a column."
 <suggestion> "Ensure fields are defined for the entire Btrieve record." </suggestion>
</warning>
<results recordsTested="0" validRecords="0" />
</table>
</database>
<results tablesTested="16" recordsTested="30" validRecords="13" />
</report>

Validate Data Definition Files using DDF Validator


Lock Plugin for MySQL

Lock Plugin for MySQL

Btrieve’s ability to lock records outside of a transaction cannot be duplicated directly with standard SQL. To support this functionality on MySQL, Mertech has developed a plugin dll with a set of functions used at runtime. This dll must be installed on the MySQL server and the functions must be registered before the application can run properly.

NOTE: The plugin must be installed to ensure proper locking in multi-user scenarios. If the application will only be used in a single-user installation, the plugin is not needed. For a single-user installation you can set Server_Side_Locking to No in the mds_global.ini file. Locking will occur on the client only and the warning for the missing plugin will not be displayed.

1. Copy the mds_app_lock.dll to the plugin_dir

The correct version of mds_app_lock.dll should be copied to the directory specified by the MySQL plugin_dir variable. The variable definition can be found by executing SHOW VARIABLES LIKE "%plugin_dir%". This directory is usually "<Program Files>\MySQL\MySQL Server n.n\lib\plugin".

If you are running a | Copy | To "%plugin_dir%"
32-bit version of MySQL | <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\deploy\mysql\mds_app_lock.dll | <Program Files>\MySQL\ MySQL Server n.n\ lib\plugin
64-bit version of MySQL | <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\deploy\mysql\ x64\ mds_app_lock.dll | <Program Files>\MySQL\ MySQL Server n.n\ lib\plugin

2. Make sure libmysql.dll is available on the system PATH

Copy the version of libmysqll.dll included with your MySQL installation to a location on the system path. The recommended location is the Windows\System32 folder, unless you are running a 32-bit version of MySQL on a 64-bit version of Windows, then libmysql.dll should be installed in the Windows\SysWOW64 folder.

If you are running a | Copy | To
32-bit version of MySQL on a 32-bit machine | <Program Files>\MySQL\MySQL Server n.n\lib\libmysqll.dll | Windows\System32 folder
32-bit version of MySQL on a 64-bit machine | <Program Files>\MySQL\MySQL Server n.n\lib\libmysqll.dll | Windows\SysWOW64 folder
64-bit version of MySQL on a 64-bit machine | <Program Files>\MySQL\MySQL Server n.n\lib\libmysqll.dll | Windows\System32 folder

NOTE: Installing libmysql.dll in the MySQL plugin directory or in the application directory does not resolve this problem because neither directory is on the Windows system path.

3. Create the mds_app_lock.ini file

An mds_app_lock.ini file should be created in the same directory as the mds_app_lock.dll. A sample mds_app_lock.ini file can be found in the installation folder (<Program Files>\Mertech Data Systems\DB Drivers\Btrieve\deploy\mysql).

Below are the INI file tokens:

Server // MySQL server name (required)

User // MySQL user name (required and must have PROCESS privilege)

Password // Encrypted user password (required, see EncryptMdsPassword.exe)

Database // Database to connect otherwise information_schema will be used

Port // Connection port, otherwise 3306 will be used

TRACE_ON // Location and filename of trace file

TRACE_LEVEL // -1 to 3: the level of information to record in the trace file

TRACE_FLUSH // Whether each line is flushed to disk or cached for later output

The plugin, which is installed on the MySQL Server, is loaded at startup. The plugin requires a valid connection to the MySQL database to determine if a session that owns a lock is still running or if orphan locks were left behind. Therefore, the Server, User, and Password tokens are required and the User must have the PROCESS privilege.

4. Register the functions

Use the following statements to register the functions on the MySQL server:

CREATE FUNCTION mds_get_applock RETURNS INTEGER SONAME 'mds_app_lock.dll';

CREATE FUNCTION mds_get_applock_version RETURNS STRING SONAME 'mds_app_lock.dll';

CREATE FUNCTION mds_get_applock_version_build RETURNS STRING SONAME 'mds_app_lock.dll';

CREATE FUNCTION mds_get_list_applocks RETURNS STRING SONAME 'mds_app_lock.dll';

CREATE FUNCTION mds_release_applock RETURNS INTEGER SONAME 'mds_app_lock.dll';

CREATE FUNCTION mds_release_all_applocks RETURNS INTEGER SONAME 'mds_app_lock.dll';

CREATE FUNCTION mds_release_list_applocks RETURNS INTEGER SONAME 'mds_app_lock.dll';

Updating the mds_app_lock.dll

MySQL only allows replacement of the DLL if any existing references are deleted from the database. In the event you need to update the dll:

1. Execute the DROP script:

DROP FUNCTION IF EXISTS mds_get_applock;

DROP FUNCTION IF EXISTS mds_get_applock_version;

DROP FUNCTION IF EXISTS mds_get_applock_version_build;

DROP FUNCTION IF EXISTS mds_get_list_applocks;

DROP FUNCTION IF EXISTS mds_release_applock;

DROP FUNCTION IF EXISTS mds_release_all_applocks;

DROP FUNCTION IF EXISTS mds_release_list_applocks;

2. Copy the new DLL to the directory indicated by the MySQL %plugin_dir% variable.

3. Run the registration script again.

Troubleshooting mds_app_lock plugin

1. The following error message displays when you start BTR2SQL and login to MySQL.

Install the proper version of mds_app_lock.dll in the MySQL plugin directory.

2. The following error message displays when you run the script to create the mds lock functions.

Error Code: 1126. Can't open shared library 'mds_app_lock.dll' (errno: 126 The specified module could not be found.)

Install libmysql.dll in the proper location.

3. You encounter other errors running the lock registration script or you experience locking problems when you run your application in a multi-user environment.

Make sure the mds_app_lock.ini file is properly configured, that the user has the PROCESS privilege and that the mds_app_lock.ini is installed in the plugin directory. Rerun the lock registration script.

4. To obtain additional information, examine the startup trace file mds_app_lock.tra in the %temp% directory.

Before You Begin


Hardware Considerations

Hardware Considerations

Utilize RAID 1+0

1+0 offers the best balance of performance and data integrity.

Defragment the server drives

If you have never defragmented a drive, you will be amazed at the improvement in access speed.

Have at least 2G of RAM

More memory means better performance. The usual amount of data often accessed should fit in the server's cache with room to spare for optimal performance.

Turn off the Mertech driver trace

When initially testing the application, you may have used the driver trace. Be sure to turn this off when you are finished. Leaving this turned on causes slowdown in performance.

Place Data Index and Transaction Logs on different physical hard drives

Place Data Index and Transaction Logs on different physical hard drives on the server to balance the drive access.

Pre-allocate database or tablespace

Pre-allocate database or tablespace according to the current size of the Pervasive.SQL database if the backend allows it. Leave room for growth and once again, defragment the drive. Both of these steps help ensure that the tables are not fragmented across the hard disk.

Set the Recovery Model to BULK LOGGED

Set the Recovery Model to BULK LOGGED for Microsoft SQL Server.

Changes to my.ini

Configuring MySQL to utilize as much memory on the server as possible will usually boost performance. There are many resources available on optimizing MySQL. One entry of particular impact is query_cache_size; depending on the initial configuration chosen, the default for this value might be very low.

Also, set innodb_flush_log_at_trx_commit = 0

Before You Begin


Utilizing Function Executor

Utilizing Function Executor

Whatever language your application is written in and whether it utilizes a high-level COM object, a class wrapper, or the BTRV function directly, each operation eventually is funneled into the BTRCALL (or BTRCALLID) function exported from w3btrv7.dll or wbtrv32.dll.

Pervasive provides a low-level tool for executing Btrieve commands called Function Executor. Since the tool runs directly off of w3btrv7.dll calling the standard BTRCALLID function, it is a good troubleshooting tool when an application is receiving an unexpected error or you would like to experiment with a particular scenario.

A simple technique to discern the difference between Btrieve and SQL behavior is to create two disk folders. In one, place wbexec32.exe (from pvsw\bin) and the original w3btrv7 dll. In the second folder, place another copy of the wbexec32 along with the Mertech w3btrv and sql_btr.cfg, ora_btr.cfg, mys_btr.cfg, or pgs_btr.cfg. Then create shortcuts to each of these and you can selectively execute Btrieve API calls against Btrieve and against SQL.

NOTE: Function Executor is just like any Btrieve application and will thus require the SQL client to be installed and on the system PATH (see Prerequisites).

Using the Function Executor History window, you can save a sequence of API calls and then play them back later or ask it to repeat them any number of times. During playback, there is a small arrow in the lower right of the dialog box which will access a record of time to execute the calls. This can be used for quick performance comparisons.

Before You Begin


Step by Step Migration using the GUI Migration Utility

Step by Step Migration using the GUI Migration Utility

Take the following steps to migrate your existing Btrieve or Pervasive.SQL database to a Microsoft SQL Server, Oracle, MySQL or PostgreSQL database using the GUI Migration Utility.

NOTE: If your source code is stored in XML format use MigrateUniPaasData, the Mertech command-line migration tool that works with Magic uniPaaS applications.

NOTE: Before you begin see Validate Data Definition Files using DDF Validator .

Related Topics

1. Migrate Data to SQL backend using the Migration Utility
2. Verify the Migration
3. Substitute the Btrieve Access dlls
4. Run Your Application
5. Deploy the Application
6. Optimize the Application for SQL


1. Migrate Data to SQL backend using the Migration Utility

1. Migrate Data to SQL backend using the Migration Utility

In order to run your application with the target database, you must first migrate your database to the target database using the Migration Utility. The database Migration Utility creates the table, copies the data, and creates the required indexes by using the entries in the DDF files. That is why an accurate DDF is essential to the overall application migration process.

Suggestion: Assuming Pervasive.SQL and the data reside on a machine other than the new server, run the migration process on the Pervasive.SQL machine. This splits up the processing requirements of SQL and the data conversion.

An example of migrating tables in the Pervasive.SQL Demodata database to an MS SQL Server backend is given in this section. The migration steps for MySQL, Oracle, and PostgreSQL are similar; only the driver name and the login information are different.

Related Topics

Step by Step Migration using the GUI Migration Utility
Login to the target database
Select and migrate files
What happens during the migration?


Login to the target database

Login to the target database

1. Select Mertech’s ISDBC Drivers for Btrieve | Migration Utility from the Windows Start menu.

The Login dialog box appears, as shown in Figure 2.

Figure 2 Login Dialog Box

2. Select the Mertech driver you are using — MS SQL Server, MySQL, Oracle, or PostgreSQL.

Note: If an error occurs when you select the driver (for example "Error loading driver!") or the BTR2SQL Migration Utility closes unexpectedly, the most likely cause is that there is a problem with the SQL client installation or a BTR2SQL driver is not in the expected location. Mertech driver dlls (sql_btr.dll for MS SQL, mys_btr.dll for MySQL, ora_btr.dll for Oracle or pgs_btr.dll for PostgreSQL) must be found on the Windows PATH and are usually located at <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\bin.

3. In the Server Name field, type the name of the server to which you are logging in to. If you are using SQL Server, click the button next to the text box to automatically search for available servers on the network.

4. In the User Name field, type the name of the user. This is only needed if you are using SQL Server Authentication to logon to the server. If you are using Windows authentication, you can click Trusted Connection. Please note that Trusted Connection must be set up properly by your network administrator to allow you access to the SQL Server instance.

5. In the Password field, type the password for the user (not needed for trusted connection).

6. Select the Database you want to connect to. The default database for the user is selected automatically. Click the button next to the text box to display the Select Database dialog box.

7. Click Login to log into the server.

1. Migrate Data to SQL backend using the Migration Utility


Select and migrate files

Select and migrate files

NOTE: See Setting Migration Preferences to choose global options for tracing, field mappings, field default values and more.

IMPORTANT: Oracle users see Analyzing Oracle Storage Requirements before starting the migration.

1. Select File | Select File.DDF… and open your DDF file. The File Selection dialog box lists all of the entries in your DDF. At this stage, you still need your Btrieve engine to read the database files.

2. Select a single file or multiple files to migrate by highlighting the entries in the dialog box.

3. Right-click> to display the popup menu.

Fig 02 Select Files

Figure 3 File Selection Dialog Box (with right-click popup menu)

4. Select Convert to driver. The Convert Data Files dialog box appears (the format of the dialog box depends on whether one of more files is selected).

Fig 03 Convert Data Files

Figure 4 Convert Data Files Dialog Box

5. Click OK to begin migrating the data files.

1. Migrate Data to SQL backend using the Migration Utility


What happens during the migration?

What happens during the migration?

The GUI Migration Utility creates the tables and associated indexes and copies the records into the table on the SQL server. The utility also generates an .INT file that corresponds to each migrated data file. This auxiliary file is stored in the same directory as the data files. The .INT file contains schema information that the ISDBC driver will use when accessing the data in SQL.

The icon in the list changes to reflect the migration. An icon with an arrow pointing to the right [image] indicates that a file has been migrated to the target backend.

NOTE: At this point, MS SQL, MySQL, Oracle, and PostgreSQL tools will be used to access the data. However, after you test the application against the SQL backend, you may discover that the table definitions are misaligned. If this is the case, then it is best to use the same DDF editor that you used previously to correct the schema, and then you can migrate the affected table(s) to SQL again.

1. Migrate Data to SQL backend using the Migration Utility


2. Verify the Migration

2. Verify the Migration

Run Mertech’s Migration Validator to validate the migration process. The Migration Validator uses the standard Btrieve API to read rows from Btrieve and from SQL simultaneously and compare the data buffer returned. A message displays in the output if any mismatch has been found.

Step by Step Migration using the GUI Migration Utility


3. Substitute the Btrieve Access dlls

3. Substitute the Btrieve Access dlls

In order for the application to access the new data on the SQL database, the Mertech versions of the Btrieve access dlls (wbtrv32.dll or w3btrv7.dll) must be used. The easiest way to do this is to copy the two dlls from the installation folder to the location where the application EXE is run. Choose the appropriate installation subfolder depending on your target SQL backend: <Program Files>Mertech Data Systems\DB Drivers\Btrieve\deploy\subfolder. If you are not sure if your application uses wbtrv32.dll or w3btrv7.dll, then copy both.

There are several locations where you can put the Mertech drivers, but the application must be able to find the Mertech version of the dll using the standard Windows dll search order. Typically the Mertech version of wbtrv32.dll or w3btrv7.dll is placed in the directory with the application executable. Windows searches here for dependencies first, so this guarantees that the correct version is located. If the driver dll is not placed in the directory with the application, it must be found by Windows dll search. This is slightly different on each version of Windows, but in general, this is the search order:

· The application’s executable directory

· The system directory (%windir%\system32, or %windir%\SysWOW64)

· The 16-bit system directory (%windir%\system)

· The Windows directory (%windir%)

· The current directory

· The directories that are listed in the PATH environment variable. Note that this does not include the per-application path specified by the App Paths registry key. The App Paths key is not used when computing the dll search path.

Important: The license file also must be found in the system PATH. We recommend that you place the sql_btr.cfg, ora_btr.cfg, mys_btr.cfg, or pgs_btr.cfg file in the same folder as the access dll. For example, if your application executable file is located in c:\myapp\bin and you will be accessing data in Oracle:

Copy <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\deploy\oracle\w3btrv7.dll and ora_btr.cfg to c:\myapp\bin.

Replace the dlls that currently reside in the PVSW\BIN folder. This ensures that the Mertech dll is used. However, many of the Pervasive tools will no longer function. For instance, if the table definitions need to be corrected, the original Pervasive dlls must be restored. Be sure that you save a copy of the original dlls before you replace them.

Step by Step Migration using the GUI Migration Utility


4. Run Your Application

4. Run Your Application

Once the correct dll is substituted, run your application. The Server Login dialog box, shown in Figure 5, displays and indicates that the program is now accessing the new backend.

Fig 04 Server Login

Figure 5 Server Login Dialog Box

At this point, the application, without modification, should be sending and receiving data from the newly chosen backend. The original MicroKernel files, the Pervasive.SQL engine and any Btrieve/DDF tools are no longer needed.

As an example, an application written for the Pervasive Demodata database issues the B_OPEN API to open c:\pvsw\demodata\billing.mkd. The Mertech version of the Btrieve access dll is loaded and used for the B_OPEN because the Mertech dll is located in the application's folder. The Mertech driver finds the billing_mkd.INT file, reads it, and then displays a login dialog box, which allows the user to input a user id and password for the selected SQL backend. If this dialog box does not display, either the Mertech dll was not found or the user id and password were previously saved.

& See also Specifying the target backend

You may choose to shut down the Pervasive services and stop the workgroup engine. The application should continue running because the data is now being provided by the new SQL server.

Step by Step Migration using the GUI Migration Utility


5. Deploy the Application

5. Deploy the Application

If your application will not be accessing Btrieve and SQL data simultaneously when the application is deployed, the Pervasive components, located in the pvsw directory, are no longer needed. Rather than including the entire Pervasive.SQL installation, the Mertech dll (w3btrv7 or wbtrv32) is installed in the application directory. No registry keys or COM registration is required. The Btrieve data files and DDFs are also not needed.

The SQL client (MS SQL Server Native client, MySQL client, Oracle OCI client, or PostgreSQL client) must be installed on the system running the application.

The .INT files must also be copied to the system running the application. The application will continue doing a B_OPEN on billing.mkd, for instance, even though billing.mkd does not exist. The Mertech driver dll will read billing_mkd.int instead.

& See Scenarios using the GUI Migration Utility for an example of a typical directory structure

Step by Step Migration using the GUI Migration Utility


6. Optimize the Application for SQL

6. Optimize the Application for SQL

Mertech provides a rich set of extended SDK and optimization tools to allow for post-migration optimization using embedded SQL and the ability to call stored procedures and functions to call server-side components. Using stored procedures and functions allows you to implement enterprise-wide business rules across your separate applications.

Embedded SQL gives you the power of SQL (using the Btrieve API) to create highly constrained data-sets quickly without the need to write extensive client-side code. You can get rid of hundreds of lines of client-side code by effectively using of a few lines of embedded SQL code.

Using server-side components and embedded SQL is not required, but it can reduce the network traffic by keeping the processing on the more powerful servers and transferring only the data that you need to the client-side.

Another enhancement that can save much network traffic is the reduction of data read. Most Btrieve applications are programmed to read the entire record whether it needs 1 or all of the data fields. Reading all of the fields is inefficient and thus the SDK provides a set of calls to restrict the list of fields needed. The field list can be changed at any time to provide the fastest access time during particularly time-sensitive operations.

& See Also BTR2SQL SDK

Step by Step Migration using the GUI Migration Utility


GUI Migration Utility Components

GUI Migration Utility Components

The BTR2SQL GUI Migration Utility assists developers during and after a data migration. It contains all the features that a developer needs to migrate an existing Btrieve application to the target backend. Mertech designed the user-friendly, intuitive user interface with the assumption that you do not require detailed knowledge of the target backend.

BTR2SQL works with the files listed in the FILE.DDF. You must select a file before you can perform any database-related operation on the file. You can select either a single file or multiple files to perform the operation in a single or batch mode.

NOTE: The GUI migration utility requires DDFs. The MigrateUniPaasData command-line migration tool works with Magic uniPaaS applications where the source code is stored in XML format.

Related Topics

Menu bar
Toolbar
Table Menu
Reports Menu
Help Menu
Main Screen Display
Setting Migration Preferences
Convert Data Files


Menu bar

Menu bar

The menu bar contains all the commands necessary to migrate a Btrieve application to an SQL backend.

Menu Contains commands to

File Select the FILE.DDF, connect to the target server, login, or choose global migration settings.

Table Perform the data migration, browse Btrieve or migrated tables, perform index maintenance, define an owner for a data file, or create a text file for high-speed migration.

Reports Examine the migration and trace reports.

Help Obtain information about the BTR2SQL migration utility.

GUI Migration Utility Components


Toolbar

Toolbar

The toolbar contains icons that can be used instead of a menu command for commonly used functions. Hover the mouse over an icon to determine the icon’s function.

Figure 6 Toolbar

For example, instead of choosing File > Select Database from the menu bar, you can click the Select Server Database icon.

GUI Migration Utility Components


Select File.DDF

Select File.DDF

Allows you to select the Btrieve FILE.DDF that contains the Btrieve data file to be converted.


Select Database

Select Database

MS SQL Server, MySQL, and PostgreSQL support multiple instances of the database engine running concurrently on the same computer. The Select a Database option allows the user to select a specific database.

NOTE: In order to create, delete, or manipulate MS SQL Server table structures through BTR2SQL, the schema id (the user) must be either be a "dbo" (db_owner) or have rights to create and delete tables in the selected database.

Attach

Connects database data and log files to an instance of SQL Server and brings the database online (MS SQL Server only). This means that you can distribute a database as standard files by detaching the database from the server, copying the database files to another computer, and then attaching to them using that computer’s installation.

MDF file

SQL Server databases use two files - a master database (MDF) file, known as the primary database file, which contains the schema and data, and a LDF file, which contains the logs. Browse to select an existing MDF file. The name must include the full path. Any associated LDFs (log files) and NDFs (secondary data files) must be in the same folder.

Database Name

Enter a database name (optional). If no database name is entered, the name given when the database was created is used. The MDF file name is typically the same as the database name.

Detach

Takes the database offline and removes it from the SQL Server instance to which it was attached. The database data and log files remain intact and you can then attach the database at a later point to another SQL Server instance (MS SQL Server only).


Login

Login

Allows you to login to the target database. In order to carry out a successful migration, BTR2SQL must logon to the target database and also must have access to the Pervasive/Btrieve engine to read the Btrieve/Pervasive data files.

The login server name, user name, and password and must be properly set up. Contact your database/network administrator or consult the documentation of the target database.

A BTR2SQL Quick Start Video and Quick Start Guide are provided to facilitate getting started as a new BTR2SQL user. Both can be accessed by selecting Mertech’s ISDBC Drivers for Btrieve from the Windows Start menu, through commands on the Migration Utility Help menu and from the Migration Utility Login dialog box.

& For a detailed description see Login to the target database


Preferences

Preferences

The Preferences dialog box provides options for global migration settings, the location of migration reports, and the location of trace files, assignment of default values for NOT NULL field types, and updates to data-type mappings used during migration.

& For a detailed description see Setting Migration Preferences


Exit

Exit

Terminates the BTR2SQL program


Table Menu

Table Menu

The Table Menu is a dynamic menu. The displayed commands depend on whether one or more files are selected in the File Selection dialog box. Commands on the Table menu allow you to perform the database conversion, create, drop and truncate tables, browse Btrieve and SQL data, verify and update index definitions, specify a Btrieve owner name, create a text file for SQL*Loader (Oracle only), and generate .INT files and SQL scripts for creating tables.

Commands available on the Table menu are the same commands that are accessible on the popup menu when you right-click the File Selection dialog box.

& For a detailed description see Convert Data Files.

GUI Migration Utility Components


Reports Menu

Reports Menu

The Reports menu is used to clear or display the migration report and to view the driver trace file.

Related Topics

GUI Migration Utility Components
Migration Report
Clear Migration Report
Driver Trace


Migration Report

Migration Report

The Migration Report is a summary report that displays any errors that may have occurred during the last migration, the license and version of the migration tool, a table creation report, an index creation report, and a data migration report. You can generate multiple migration reports in one file for viewing multiple migration reports and then clear the report to start a new migration report (see Setting Migration Preferences).

At the top of the report, the file displays the driver version and the license number. Next, the file displays the date and time the report was generated and the user name of the user who generated the report. A sample migration report is shown in Figure 27.

Select Report | Migration Report.

Mertech Data Systems, Inc

Database Migration Report

SQLBTR 5.0.5914.0

Licensed to: Evaluation License

------------------------------------------------------------------------------

Report Generated on: 12/18/2012

At: 10:24:30 AM

By: sa

For Server: GATEWAY\sqlexpress Database/Tablespace: tempdb

DDF Location: C:\ProgramData\Pervasive Software\PSQL\Demodata

------------------------------------------------------------------------------

REPORT SUMMARY

Errors: No errors

No Errors in Table Creation

No Errors in Index Creation

No Errors in Data Migration

TABLE CREATION REPORT

------------------------------------------------------------------------------

Table Name Status Error Log

------------------------------------------------------------------------------

CLASS ** CREATED **

INDEX CREATION REPORT

------------------------------------------------------------------------------

Table Name Index No. Status Error Log

------------------------------------------------------------------------------

CLASS Index.1 ** CREATED **

Index.2 ** CREATED **

DATA MIGRATION REPORT

------------------------------------------------------------------------------

Table Name Total Records Records Migrated Status Remark

------------------------------------------------------------------------------

Class 211 211 ** PASS **

---------------------------------- END MIGRATION -----------------------------

Figure 27 Sample Migration Report

Troubleshooting


Clear Migration Report

Clear Migration Report

Clears the current file containing the migration report(s).

Reports Menu


Driver Trace

Driver Trace

View the driver trace file generated during migration.

Reports Menu


Help Menu

Help Menu

In addition to this User’s Guide, a Quick Start Guide and Quick Start Video are available from the Windows Start menu under the Mertech’s ISDBC Driver for Btrieve heading. Additional documentation is also available on the Mertech web site.

Related Topics

GUI Migration Utility Components
BTR2SQL Help F1
Index
Quick Start Guide
Quick Start Video
Mertech on the Web
About


BTR2SQL Help F1

BTR2SQL Help F1

Opens the Contents tab of the BTR2SQL User’s Guide. Press F1 at any time to navigate to the help topic specific to the currently displayed screen.

Help Menu


Index

Index

Opens the Index tab of the BTR2SQL User’s Guide.

Help Menu


Quick Start Guide

Quick Start Guide

Provides information to facilitate getting started as a new BTR2SQL user. The guide is in pdf format.

Help Menu


Quick Start Video

Quick Start Video

Provides information to facilitate getting started as a new BTR2SQL user. The video is a self-running executable that does not require any special software to be installed.

Help Menu


Mertech on the Web

Mertech on the Web

You can download the latest BTR2SQL version, get product news, FAQs, online support, and the blogs. It’s well worth your time to get acquainted with this site.

Help Menu


About

About

Provides version and copyright information about BTR2SQL.

Help Menu


Main Screen Display

Main Screen Display

After selecting the FILE.DDF, the display lists of all of the data files in the FILE.DDF. This is the main area for completing all tasks related to data migration. You can perform actions on one or more data files.

This dialog box allows you to identify and select files you want to migrate. The icon next to the file name indicates whether or not the file has been migrated. An icon without an arrow indicates that the file is still in the native Btrieve format while an icon with a green arrow shows that a table has been migrated to the target RDBMS. The status line at the bottom of the dialog box displays the number of files currently selected and the target server and database.

Fig 06 Select Files

Figure 7 File Selection Dialog Box

All

Selects all entries in the list

None

De-selects all selected files in the list

Invert

Toggles the entries. All marked entries become unmarked and all unmarked entries become marked.

Valid Btrieve Files

Selects all of the data files in the FILE.DDF that have a valid and accessible Btrieve data file.

Valid SQL Tables

Selects all of the data files that have been migrated and checks for errors in accessing tables on the SQL backend.

GUI Migration Utility Components


Setting Migration Preferences

Setting Migration Preferences

Select File | Preferences to choose global migration settings, the location of migration reports, and the location of trace files. Values entered here are stored in the registry and remain after the tool exits.

Fig 05 Preferences

Figure 8 Preferences Dialog Box

Overwrite Existing Tables Without Warning

If this option is selected, a table with the same name is overwritten during migration. By default, the option is turned off, and you are prompted before a table is overwritten.

Erase Table Before Migration

If this option is selected, the contents of the target table are erased before the contents of the Btrieve data file are copied. By default, this option is turned off. If this option is NOT selected, rows from the Btrieve file are appended to the existing data.

Require Btrieve Data Files

If this option is checked, BTR2SQL skips a table if the data file is not found.

Prompt For Btrieve Owner If Needed

During migration if the program detects a file that requires owner information, it prompts the user for it. If this option is unchecked, BTR2SQL skips the operation for that file.

Migrating DataFlex for Btrieve Files

This option is for those using DataFlex for Btrieve (the modified runtime version) and looking to migrate to SQL Server. Empty table definitions are first created using Mertech's Flex2SQL migration utility and then data is copied over at a high speed using BTR2SQL into the newly created table. The migration tool reads the auto-increment RECNUM column value, which is the last field in tables created by DataFlex for Btrieve and copies the value as is to the RECNUM identity column, the first column in the newly created SQL table.

Migration Report File Name

This is the name of the migration report file — a summary of the migration process. You can access this report by selecting Reports | Migration Report.

Press the button button to browse for the location where you want to save the migration report file.

Append to Existing Migration Report

The latest report is appended to an existing one. If this option is not checked, the existing migration report is deleted.

Migration Trace File

When you select this option, BTR2SQL generates a trace log file for all of the calls at the driver (low) level that occur during migration. You can view this file by selecting Reports | Driver Trace.

NOTE: Leaving this trace file ON all of the time could slow down the performance of the migration process because each driver operation is logged into the trace file.

File Name

Press the button button to browse for the location where you want to save the migration trace file.

Level

Use the drop-down arrow to choose the trace level. There are multiple "levels" of tracing, from -1 to 3, depending on the amount of detail needed.

& See Runtime Driver Trac for a description of each level

Runtime (debug) Trace File

Sets the TRACE_ON registry setting in HKCU\Software\Mertech Data Systems\DB Drivers\Btrieve\BTR2SQL\<backend>\v3. If TRACE_ON is enabled, then all calls by the driver to and from the application will be logged in the file name specified. This option must be set with caution in order to not adversely affect performance.

NOTE: Depending on the TRACE_LEVEL setting, leaving the trace file ON all of the time may slow down your application and generate a very large output file.

Related Topics

GUI Migration Utility Components
Set Field Defaults
Set Data Type Mapping


Set Field Defaults

Set Field Defaults

Allows you to set default values for ASCII, NUMERIC, DATE and TIME field types. Default values are used if a value is not provided when data is inserted into a table. NOT NULL attributes can be set for a given field type that will persist for all tables during migration. When a field is created as NOT NULL, Oracle and MS SQL Server databases expect a default value. You can specify the default values or let the drivers handle it. If not specified, the driver assigns a date value of 01/01/1753 for MS SQL Server and 01/01/0001 for Oracle. Numeric fields have a default of 0 and ASCII fields a single space. Time fields have a default of 00:00:00.

Fig 10 Field Default Values

Figure 9 Field Default Values

NOTE: PostgreSQL does not support storing Zero (null) bytes in a string. If your data needs to include a Zero byte, you must include the Binary flag on the field in the DDFs (bit 12 / 0x1000 on field flags).

When is a field NOT NULL?

Fields that are part of an index are always created as NOT NULL. BTR2SQL will not allow you to change this because this directly affects FIND performance when using indexes based on those fields. If a field is in the NULL state and a SELECT statement is generated for that field, both Oracle and MS SQL Server perform a full table scan of the data file, which slows down performance. You can click on the check boxes under the NOT NULL column to set a field that is not part of any index to NOT NULL.

NOTE: If fields are selected as NOT NULL, SQL databases require that those fields always have a value during an INSERT statement. If the program field receives no value, the driver automatically inserts a default value. You can control these defaults during migration by specifying the defaults for a particular field. For indexed fields, you can change preset defaults provided by BTR2SQL.

NOTE: Field default values can be specified at the global level by selecting File| Preferences from the menu bar. Field default values can be set for an individual migration from the Convert Data Files dialog box.

Setting Migration Preferences


Set Data Type Mapping

Set Data Type Mapping

Allows you to modify the field-type mappings used during migration. The drop-down list of field-types ensures that only suitable mappings can be selected. Most of the time it is not necessary to change the assignment made by BTR2SQL, but there may be instances when this is useful. For example, you can map a string field to a date/time field to provide meaningful interpretation of date/time information.

NOTE: Data type mapping values can be specified at the global level by selecting File| Preferences from the menu bar. Data type mapping values can also be set for an individual file by selecting the Fields tab in the Convert Data Files dialog box.

Setting Migration Preferences


Convert Data Files

Convert Data Files

<Right-click> on any file/files in the File Selection dialog box and choose one of the options available from the popup menu. Procedures for these menu options are described on the following pages. The displayed menu depends on whether one or multiple files are selected.

One File Selected

Fig 07 File Options Popup

Multiple Files Selected

Figure 10 File Options - Popup Menu

The first option, Convert to database, creates the table, copies the data and then adds indexes, all in one operation. It can operate on one file at a time or multiple files. The Convert Data Files dialog box is different depending on whether one file or multiple files are selected.

One File Selected

Multiple Files Selected

Figure 11 Convert (Data) Files Dialog Box

NOTE: When multiple files are selected for migration, all selection options are available on the main tab. The fields and indexes are migrated as defined in the DDFs. When you select OK to convert multiple files, the program migrates each file separately, instead of as a batch process.

& See also Options Tab, Fields Tab, Indexes Tab, Server-Specific Tab , Copy Data to Table

Related Topics

GUI Migration Utility Components
Options Tab
Fields Tab
Indexes Tab
Server-Specific Tab
Table Operations
Browse Operations
Index Maintenance
Specify Btrieve Ownership
Analyzing Oracle Storage Requirements
Create Text File for Loader
Analyze after Loader Execution
Generate


Options Tab

Options Tab

Figure 12 Convert File – Options Tab

Get Server Name from Login

If you uncheck this option, the server name is stored in the INT file during migration. If you leave this option checked, the server name is NOT stored in the INT file during migration. At runtime, the driver looks for the server name in 1) the INT file, 2) the mds.ini file. If the server name is not stored in either file, the driver displays the Login dialog box.

Get Schema Name from Login

If you uncheck this option, the schema name is stored in the INT file during migration. If you leave this option checked, the schema name is NOT stored in the INT file during migration. At runtime, the driver looks for the schema name in 1) the INT file, 2) the mds.ini file. If the schema name is not stored in either file, the driver displays the Login dialog box. Not used in Oracle.

Get Database Name from Login

If you uncheck this option, the database name is stored in the INT file during migration. If you leave this option checked, the database name is NOT stored in the INT file during migration. At runtime, the driver looks for the database name in 1) the INT file, 2) the mds.ini file. If the database name is not stored in either file, the driver displays the Login dialog box.

& See Specifying the target backend for additional information

Migrating DataFlex for Btrieve Files

This option is used in conjunction with Mertech Flex2SQL tool for migrating Btrieve files to SQL Server.

& See Migrating DataFlex for Btrieve Files in the Preferences dialog box to set this as a global option

OEM to ANSI Translation

When checked, converts the OEM character set (used by DataFlex) to the ANSI character set.

OK

Selecting OK starts the conversion process.

& See also Fields Tab, Indexes Tab, Server-Specific Tab

Convert Data Files


Fields Tab

Fields Tab

The Fields tab allows you to change the data type mapping assigned to a column when the table is created on the SQL server. The Fields tab also lets you enter a default value for fields defined as NOT NULL. Fields created as NOT NULL need a default value that you provide if a value is not provided by the program or if the field is left blank.

Figure 13 Convert File – Fields Tab

Name

Displays the alphanumeric identifier for the field (the column name).

Type

Displays the Btrieve data type assigned to the field.

SQL Type

Displays the default field-type mapping that will be used when creating the SQL table during migration. You can modify this setting.

& See Set Data Type Mapping to set the mapping as a global preference

Binary

Allows you to tag string fields as binary during migration. Check this option if a string field contains non-ASCII values (<32 or >128).

Offset

Displays the field offset in the record buffer.

Size

Displays the field length in bytes.

Dec

Used for numeric fields, displays the number of decimal spaces the field can contain.

Null

Indicates whether NULL values are allowed in the field. You can change this setting, however, fields participating in an index cannot be NULL.

Default

Allows you to enter a default value to be used for a NOT NULL field when the field is left blank.

& See also Options Tab, Indexes Tab, Server-Specific Tab

Convert Data Files


Indexes Tab

Indexes Tab

The indexes tab shows details of all keys and the fields that comprise the keys.

Figure 14 Indexes Tab

Field Name

Displays all the fields that make up the currently selected key.

Desc

Check to indicate this field is descending.

Case

Check to indicate this field is case insensitive.

NOTE: The PostgreSQL citext module provides the case-insensitive character string type CITEXT that allows for case insensitive fields without the need for inverse key columns.

Ignore Null Values

Check if NULL values should be ignored, otherwise the default value will be assigned to a NOT NULL field.

Properties and Storage (Oracle Only)

Primary Key

Check to indicate this index is a primary key. All SQL databases perform exceptionally well with a correctly selected Primary Key. By default, the driver sets Index 1 in your Btrieve file as the primary key on the server. You can choose any index, as long as it is unique.

Unique

Check to indicate this index is unique.

NOTE: All Mertech drivers support auto-increment fields on the server-side. However, because of the limitation on MS SQL Server, it allows only one auto increment field per table. If you have an auto increment field and you expect that table to be accessed from other non-Btrieve programs, be sure to enable the auto-increment trigger for the table.

Initial

Displays the size of the first extent for the index tablespace on the Oracle server.

Next

Displays the size of the next extent to be allocated when the initial space is full.

% Free

Displays the space reserved in each of the cluster's data blocks for future expansion. The value of the parameter is expressed as a whole number and interpreted as a percentage.

& See also Options Tab , Fields Tab, Server-Specific Tab

Convert Data Files


Server-Specific Tab

Server-Specific Tab

The server-specific (MSSQL and Oracle in the example) tab contains settings that determine how the data is loaded.

Oracle users run the Analyzing Oracle Storage Requirements option and then use the drop-down list in the Oracle tab to assign different locations for the data and indexes for the currently selected tables.

Figure 15 MSSQL Tab

Figure 16 Oracle Tab

& See also Options Tab, Fields Tab, Indexes Tab

Convert Data Files


Table Operations

Table Operations

The following table operations are available from the Table menu and from the popup menu displayed when you right-click file(s) in the File Selection dialog box.

Copy Data to Table

This option copies records from a Btrieve data file to a table that has already been created using Mertech’s BTR2SQL tool. Use this only if the table exists on the server. The following copy options can be selected:

· Lock Table: Check to lock the table and prevent updates to the table while the data is being copied to the SQL server.

· Erase Table: Check to erase the contents of the target table before the contents of the Btrieve data file are copied. If this option is NOT checked, rows from the Btrieve file are appended to any existing data.

· Bulk Copy Operations: Migrating records to the database is much more efficient using a bulk copy operation. MSSQL lets you determine whether to commit updates based on the number of rows per batch or kilobytes per batch.

Create Table

Creates an empty table and associated indexes without migrating the data.

Select this option when you want to use SQL*Loader for high-speed data migration or for creating blank tables that are later populated by the application.

Drop Table

Deletes a table in the target database for each data file that is selected in the FILE.DDF.

Truncate Table

Erases the contents of the table in the backend database that has the same name as the data file currently selected in the FILE.DDF.

Convert Data Files


Browse Operations

Browse Operations

Browse Btrieve Data

Displays the data for the highlighted file. The data is read from the native Pervasive / Btrieve file. You can also. You are prompted for the Pervasive database name. Enter the logical name for the Pervasive database that contains the selected file (for example, Demodata).

NOTE: The OLEDB provider for Pervasive.SQL must be installed to access the Btrieve files. The OLEDB provider is needed only for the data browsing function.

Browse SQL Table

Displays the data for the highlighted table. Retrieves data from the migrated SQL table.

Fig 13 Browse

Figure 17 Browse Data Dialog Box

NOTE: You can also double-click a filename to browse the data. If the file has not yet been migrated, the Btrieve file is opened. If the file was already migrated, the SQL table is opened.

Convert Data Files


Index Maintenance

Index Maintenance

This option allows you to check the index definition of each file, whether or not the indexes have been created properly, and to re-create or drop indexes on a file.

The most common reason why a Btrieve program does not perform optimally after migration to an SQL database backend is because the indexes were not properly created or not created at all. The driver fails to create indexes for a number of reasons, but the two most important are duplicate data and running out of disk space. For example, a Btrieve table might contain dates like 12/12/99 and 12/12/1999 in two separate records. Since BTR2SQL converts all dates to four-digits during migration, such data causes a unique constraint violation if this date field is part of an index.

For this option to work properly, make sure that your Btrieve files are present for comparing the database structures.

Fig 14 Index Mnt

Figure 18 Index Maintenance

1. Select all the files in the File Selection dialog box.

2. <Right-click> on a file and select Index Maintenance.

BTR2SQL searches each Btrieve file and compares it to the index created on the server. If the index is not created, BTR2SQL displays it with a red check mark next to it.

If the index was not created, you can drop and re-create the table or drop and recreate the index using the Index Maintenance dialog box.

Convert Data Files


Specify Btrieve Ownership

Specify Btrieve Ownership

In Btrieve, you can define an owner for a data file for security purposes. BTR2SQL cannot access these files unless the owner (password) is specified while opening these files. For migration purposes, BTR2SQL requires that you specify the owner name for these files to successfully migrate the data to the SQL Server.

The File Selection dialog box, shown in Figure 7, displays a lock icon for the files requiring the owner specification.

1. In the File Selection dialog box, select a file with lock next to it.

2. <Right-click> and select Btrieve | Specify Owner Names…

The Specify Btrieve Ownership dialog box appears.

Figure 19 Specify Btrieve Ownership

3. Type the owner’s information in the Owner column.

4. Press OK.

Number

Number assigned to the file in FILE.DDF. This column is non-editable.

File Name

Name given to the Btrieve file. This column is non-editable.

Owner

Files owner information. The specified owner information is verified. If it does not match, you are prompted again to enter the information for that file.

Convert Data Files


Analyzing Oracle Storage Requirements

Analyzing Oracle Storage Requirements

IMPORTANT: This feature should be used before ANY data migration is done. Running out of disk space during data migration creates a situation where Btr2SQL does not properly create or migrate data.

This option dynamically calculates the server storage requirement for each individual table based on the Btrieve file sizes. The sizes indicated are in bytes. If the tablespace is not large enough, the tablespace is highlighted in red. In these cases, you can use Oracle’s Storage Manager utility to increase tablespace sizes.

NOTE: You must have rights to alter the tablespaces, even the ones to which you are migrating. You can change user rights using Oracle’s Enterprise Manager.

1. In the File Selection dialog box, select a Btrieve file(s) to be migrated to the SQL backend.

2. <Right-click> and select Oracle Specific Options | Analyze Storage Requirements.

Figure 20 Analyze Storage Requirements (Oracle Only)

Tablespaces

Tablespace

All tablespaces the current user can access.

Available (bytes)

Total remaining space for the tablespace. You can compare this to the Required Size of the tablespace to see if there is enough space.

Required Size (bytes)

The space required by the Btrieve files selected. This turns red if the required size is larger than the available size.

Totals

Remaining server-side space in bytes and the space required by the currently selected files.

Btrieve Data Files

Name

The name assigned to the selected Btrieve file.

Data Tablespace

The tablespace currently assigned for the migrated Btrieve data.

Recommendation: It is recommended that you create index and data tables on two separate tablespaces, preferably on separate hard drives. You can choose different tablespaces for index and data tables, and assign individual tables to different tablespaces using the Oracle tab in the Convert File dialog box.

Size (bytes)

The space required on the server-side for the Btrieve table data.

Index Tablespace

The tablespace currently assigned for the migrated Btrieve indexes.

Size (bytes)

The space required on the server-side for the Btrieve index data.

Totals Data

Total space designated for data tablespace.

Totals Index

Total space designated for index tablespace.

Convert Data Files


Create Text File for Loader

Create Text File for Loader

SQL*Loader is the fastest way of populating new Oracle tables from a Btrieve file. BTR2SQL generates a text file from the Btrieve data file and also generates all the necessary control scripts for running SQL*Loader. At the end of this process, a file called loader.bat is generated that contains commands to run the loader scripts (generated by SQL*Loader) for each file. This loader can be run in client mode or server mode depending on whether or not the machine you are executing the loader scripts on is also the server. If not, you must select the client mode.

Figure 21 Create Text File for Loader (Oracle Only)

1. In the File Selection dialog box, select the Btrieve file(s) to be migrated to the SQL backend.

2. <Right-click> and select Oracle Specific Options | Create Text File for Loader.

Batch File Location

Create One File

If this option is checked, BTR2SQL creates a single batch file to load all the selected files. If this option is not checked, BTR2SQL creates individual batch files to load the data. Use the Browse button to select the location for the batch file.

SQL Loader Option

Client Execution

Indicates whether BTR2SQL executes the loader on the client or server machine. If the machine from which you are running the SQL*Loader is a client machine, then select the Client Execution option. This automatically puts the login information in the loader.bat file. For faster loading, Mertech recommends that you use the server mode option and run the loader.bat directly on the server, if possible.

You must run Analyze after Loader Execution after loading the data into tables using SQL*Loader.

Loader File Name

The name assigned to the SQL*Loader file.

In the Grid

For every file selected in the File Selection dialog box:

Table Name

The assigned table name.

Directory

Path to the selected Btrieve file.

File Name

The name of the Btrieve file.

Data File

The name that will be assigned to the text file if the exporting option is checked.

Direct

The load type. If checked, then the load type is direct. If unchecked, then the load type is conventional. A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. Typically, direct load is faster than conventional load.

Export

Indicates whether or not you want to export data. If this option is checked, then BTR2SQL generates text files containing the data.

Convert Data Files


Analyze after Loader Execution

Analyze after Loader Execution

SQL*Loader is a specialized way of populating a table. SQL*Loader utility is run separately or asynchronously from BTR2SQL. Certain information pertinent to the Btrieve database and, consequently, to the Btrieve program (i.e. auto-increment sequence) needs to be adjusted before a Btrieve program can function properly. BTR2SQL goes through each file and compares the number of records migrated for each file to the number of records in Btrieve. If it finds a mismatch in the number of records, BTR2SQL identifies the records that did not match.

The log file generated by SQL*Loader, called <filename>.log, usually contains the information pertaining to which record(s) were NOT migrated. BTR2SQL does not analyze the information or logs that it generates. In order to ensure that the loader migrated all data correctly, this option must be run. This option also corrects the auto-increment sequences.

NOTE: If you do not execute Analyze after Loader Execution after you migrate the database using SQL*Loader, then sequences and certain system statistics will be off and your application will not be able to perform FIND operations correctly.

1. In the File Selection dialog box, select all the files that were migrated to Oracle.

2. <Right-click> on a file and select Oracle Specific Options | Analyze after Loader Execution.

Adjust Sequence Definition

This option adjusts the start value of the sequence created. The sequence is the order of the values in the auto-increment column.

NOTE: When SQL*Loader is used to populate a table, the auto-increment values have to be adjusted. This needs to be done manually by using the Analyze after Loader Execution option.

Enable Trigger to Handle Auto-Increment and Inverse Key

This option enables a trigger that handles auto-increment fields and the inverse key on the specified table. If BTR2SQL created a table with the auto-increment column and you are using Mertech’s drivers with your Btrieve program, Mertech’s drivers automatically handle auto increment values. However, if you access and change the same table by a non-Btrieve program, then the auto-increment sequence is disturbed. To avoid this, use this option to enable a trigger that automatically fires when a record is added, deleted, or updated and perform the necessary operations for auto-increment handling.

To support descending index segments and case sensitive indexes, the drivers make use of a special key called inverse key. BTR2SQL creates inverse keys during data migration. Since this feature is specific to the driver, any updates to index fields in a migrated table using Mertech’s drivers require that you also update the inverse correctly, much the same as in auto-increment handling. Oracle and MS SQL Server use a trigger for maintenance of the inverse key values in the index. Through BTR2SQL, you can enable or disable this trigger. If the trigger is in place on a table, then the driver does not insert its own values and allows the values from the trigger to be used. There is only one trigger that handles both auto-increment and inverse key handling.

1. In the File Selection dialog box, select the file to have the trigger enabled.

2. <Right-click> and select Oracle Specific Options | Enable Trigger to Handle Auto-Increment and Inverse Key.

Handling of AutoInc Fields

· AutoInc fields always retain their value during migration.

· AutoInc fields are managed the same way whether records are added using B_INSERT or B_FAST_INSERT: If the value of an AutoInc field is zero the next sequential number is assigned, otherwise the value provided in the dataBuffer is used.

Disable Trigger to Handle Auto-Increment and Inverse Key

This feature disables the trigger that Oracle and MS SQL Server use to handle the maintenance of inverse keys in the index of a data file.

1. In the File Selection dialog box, select the file to have the trigger disabled.

2. <Right-click> and select Oracle Specific Options | Disable Trigger to Handle Auto-Increment and Inverse Key.

Convert Data Files


Generate

Generate

Table Definition Files from Btrieve/Pervasive.SQL

This option creates the .INT file for each data file selected. The .INT file contains information about the structure of a specific data file.

1. Select a file(s) in the File Selection dialog box.

2. <Right-click> and select Generate | Table Definition Files from Btrieve.

& See also INT files.

Generate SQL Script for Creating Tables

This option generates SQL scripts for creating tables and indexes. This allows more control and security over the process of creating tables. The output is sent to a file, <filename.SQL> that can then be run from SQL*Plus in Oracle or ISQL in MS SQL Server.

1. Select a file(s) in the File Selection dialog box.

2. <Right-click> and select Generate | SQL Script for Creating Tables.

3. Select the location for the SQL script.

4. Press OK.

Convert Data Files


Configuration Settings

Configuration Settings

There are three files used by BTR2SQL drivers to ensure that your Btrieve application operates as expected.

Name | Source | Location | Contains
INT files (filename.INT) | Migration Utility (or command-line migration tool) creates one INT file in the Data Directory for each migrated table | Data Directory or the location specified in the mds.ini file | General settings selected during migration and the Btrieve table structure
mds.ini file | Installation Wizard installs a sample in: <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\sdk\samples | Data Directory | Location of the INT files, schema, server and database name, login information, table pre and postfix values
mds_global.ini file | Installation Wizard installs a sample in: <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\bin | The drivers will search for this file on the %PATH%. | Global values like trace, lock and performance settings

Related Topics

INT files
mds.ini file
mds_global.ini file
Setting global and folder specific values programmatically
Precedence of configuration settings


INT files

INT files

An INT file contains all of the information necessary to allow the BTR2SQL driver to connect to, open, or recreate a converted SQL table. A partial INT file is shown below. General settings are listed first. These settings are based on information you select during migration. The field (FIELD_NUMBER 1..n) and index (INDEX_NUMBER 1..n) definitions follow. These definitions provide a picture of the file. At runtime the data is accessed on the server using this picture.

NOTE: The INT files can be different depending on the SQL server (and even the version of the server) chosen during migration. So it is not reliable to generate INT files for one server make/model/version and then target a different server version.

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.

Configuration Settings


mds.ini file

mds.ini file

The mds.ini file contains the location of the INT files, schema, server and database name, login information, and table pre and postfix values. The Btr2SQL drivers use the mds.ini file in conjunction with the INT files at runtime. Some of the tokens in the mds.ini file are also used during migration:

· Migration Utility: uses the INT-Folder, Table-Prefix, and Table-Postfix tokens (for examples, refer to Scenarios using the GUI Migration Utility).

· MDSMigrateTable and MigrateUniPaasData: use all of the tokens. Command-line tokens override those in the mds.ini file (for examples, refer to Options to migrate a multi-directory UniPaaS application).

One copy of the mds.ini file can be placed in each folder where the application’s data files are usually stored. The mds.ini file contains the following tokens. All the tokens are optional.

Token | Description | Allows you to
INT-Folder | Path to the INT files | Consolidate INT files in one directory
Server | The server name | Identify the target backend for a group of files rather than storing the information in each INT file or obtaining the information at runtime
Database | The database name
Schema | The schema name (not used in Oracle)
UseTrustedConnection | Rely on Windows authentication, yes or no. The default is no. (MSSQL only) | Store login information so a Login dialog box is not required
User | User name. If UseTrustedConnection is yes, the User and Password values are ignored
Password | A 128-bit encrypted value obtained with the command-line tool MdsEncryptPassword, which can be found in the bin folder of the Mertech driver installation
Table-Prefix | Text to prepend to the table name during migration | Create unique table names when migrating multiple directories with the same files names to the target server
Table-Postfix | Text to append to the table name during migration
FilegroupTable | Filegroup for table data (MSSQL only) | Specify a filegroup for table data, indexes, or for text, ntext, image, xml, varchar(max), nvarchar(max), and varbinary(max) fields.
FilegroupIndex | Filegroup for indexes (MSSQL only)
FilegroupText | Filegroup for large fields (MSSQL only)

A sample mds.ini file is shown below. Entries under a specific heading (e.g., heading [SQL_BTR]) are only used for that driver (e.g., driver MS SQL). The driver’s default value is used when a value is left empty. Spaces are ignored around the equal sign. All settings and values are case insensitive. A more detailed description of each setting is provided in the next section.

Related Topics

Configuration Settings
Sample mds.ini file
Consolidating INT files on the client
Specifying the target backend
Automatic application login
Segregating tables using a prefix or postfix
Using MS SQL Filegroups


Sample mds.ini file

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=

[MYS_BTR]

INT-Folder=..\IntFiles_MYS

Server=mysqlsrv:3306

Database=testdb

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).

mds.ini file


Consolidating INT files on the client

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

mds.ini file


Specifying the target backend

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.

ConvertFile not checked.png

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

mds.ini file


Automatic application login

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

mds.ini file


Segregating tables using a prefix or postfix

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.

mds.ini file


Using MS SQL Filegroups

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

mds.ini file


mds_global.ini file

mds_global.ini file

While the mds.ini file controls information specific to each data folder, mds_global.ini contains settings with a broader scope. The location of the mds_global.ini file is up to you. The first mds_global.ini file found on the usual Path is used. We recommend placing the mds_global.ini file in the same location as the access dll and license (.cfg) file.

These locations are searched in order:

· Current folder

· Folder where the dll was loaded from

· Folder where the exe was loaded from

· System Path

· Driver Installation bin folder

All mds_global.ini settings are listed in the table below. Settings are grouped by category: Tracing, Locking, Performance and Miscellaneous. The value marked with an asterisk (*) is the default.

Setting

Description

Tracing

Trace_On

Location of the debug trace file.

Specify the complete path and filename for the trace file. If the entry is empty, no tracing occurs.

Example: c:\trace\sql_btr.tra

Trace_Level

Trace output level.

Values:

· -1: Errors only

· 0: Basic

· 1: Detailed, 1st level*

· 2: Detailed, 2nd level

· 3: Detailed, 3rd level

& See also Runtime Driver Trac

Trace_Overwrite

Overwrite existing trace file?

If this option is no, the trace file name is augmented with a number each time so it does not overwrite previous traces.

Values: <yes, *no>

Trace_Flush

Flush each line to disk?

For speed, flushing is turned off by default and the operating system normal file caching mechanisms are used. However, if the application crashes, the last few lines of trace will not be output to the trace file. Enable flushing to get around this problem.

Values: <yes, *no>

Trace_Buffers

The Btrieve buffers to include in the trace output.

Values are:

· sql (SQL statements, SQL statements are encrypted)

· pb (Position Block)

· kb (Key Buffer)

· db (Data Buffer)

· fb (Field Buffers)

Values can be combined, separated with a space or comma. For example to only include SQL statements and key buffer:

Trace_Buffers = sql kb

The default is to trace all buffers.

Trace_Gap_Allowed

Threshold (in seconds) for "slow" operations.

A large gap in the trace output timestamps can mean a potential performance penalty. When the driver notices such a gap, it adds a warning in the trace output (preceded by "Performance:"). A pattern of these warnings may pinpoint a bottleneck.

Default value: 0.80

Trace_Query_Times

Report time for queries?

Each Prepare and Execute of a query is reported in milliseconds when this option is turned on.

<yes, *no>

Trace_Mem_Stat

Monitor memory consumption and changes at beginning and end of each BTRV call.

This is only active at trace level 1 or above. The value is a combination of bit flags:

· 0: (Default)

· 1: Show current memory used. Shows "MemUsage :" in trace

· 2: Include C Runtime information. Shows "MemState :" in trace

· 4: Show a difference in memory consumption from one BTRV call to the next

Locking

Server_Side_Locking

Enforce Btrieve style locking ?

When Server_Side_Locking is set to yes, Btrieve style locking is enabled, and application locks are sent to the server where they are managed by the Mertech driver. This prevents other applications that go through the Mertech driver from accessing locked records. It does not prevent outside code from modifying records.

When Server_Side_Locking is set to no, locking still occurs on the client, but the lock is not sent to the server so concurrency is not supported. For a single instance of an application, the local information is enough and avoids the installation of the mds_app_lock plugin for MySQL.

NOTE: See also SQL_Locks below.

Values: <*yes, no>

SQL_Locks

Enable SQL locks?

Enabling Server_Side_Locking does not utilize SQL record locks, which means other processes may update records outside of the Btrieve application. It also means, a deadlock is possible since the server-side locks do not have a method to detect this.

Enabling SQL_Locks adds standard record locks, but requires that the Btrieve application request a transaction. If your application is guaranteed to always use transactions, it is recommended to turn off Server_Side_Locking and turn on SQL_Locks.

SQL_Locks adds overhead and thus impacts performance.

Values: <*yes, no>

Performance

Query_Threshold

Threshold (in seconds) for a "slow" query.

The driver keeps an eye out for queries that are returning slowly and flags these in the trace log. Look for "Performance:" in the trace to find them. Slow query times could indicate a problem with an index.

You have the option of adjusting the query threshold for your server network and server configuration. A negative time causes query times to not be reported at all.

Default time for being “slow” is 0.01 seconds

Use_Fast_Stat

Include the record count in the B_STAT?

The B_Stat function is used frequently by Btrieve applications after a B_Open to obtain basic file and key format information. This call includes the current record count. Obtaining the current record count in SQL is relatively slow and is not normally required by the application.

If Use_Fast_Stat is enabled, the record count is returned as Zero.

Values: <yes, *no>

Performance_Counter_Report

Location of the performance report.

Specify the complete path and filename for the performance report. The performance report includes counters and timings of each Btrieve function call made segregated by file. This can be useful in tracking down the slowest operations and may, for instance, highlight an index problem

Example: c:\trace\sql_btr.perf

Cache_GetEqual

Cache records for GetEqual and GetDirect.

Setting Cache_GetEqual to no (the default) forces a B_GET_EQUAL or B_GET_DIRECT call to always refresh the data from the SQL server, even if that particular row is already in the cache. This is useful for scenarios when a particular row is polled repeatedly for changes. Setting Cache_GetEqual to yes fetches the record from cache and may improve performance if the same record is requested multiple times.

Values: <yes, *no>

Miscellaneous

Btrieve_Version

Set the Btrieve version.

Sometimes an application checks the version of Btrieve to validate the API support or to narrow the range of tested scenarios. The Mertech driver always reports itself as version 7.94 in the B_VERSION call so some applications refuse to run.

Use this setting to override this number.

Default value: 7.94

Identifier_Case

Character Case of identifiers in SQL.

Controls how table and field names are handled. Values are:

· Upper: Convert to upper case

· Lower: Convert to lower case

· Original: Maintain the original case*

PERVASIVE_DLL_NAME

Allows you to specify the name and optionally the path of the Btrieve access dll used for reading Btrieve data during migration

Default value: wbtrv32.dll

Related Topics

Configuration Settings
Sample mds_global.ini


Sample mds_global.ini

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

[MYS_BTR]

;Location of the debug trace file
TRACE_ON = c:\trace\mys_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)

mds_global.ini file


Setting global and folder specific values programmatically

Setting global and folder specific values programmatically

You can set global and folder specific settings programmatically thru a call to MdsSetSetting.

Related Topics

Configuration Settings
MdsSetSetting
MdsGetSetting


MdsSetSetting

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:

a) 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:\trace\sql_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

b) 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)

Setting global and folder specific values programmatically


MdsGetSetting

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"

Setting global and folder specific values programmatically


Precedence of configuration settings

Precedence of configuration settings

Global settings can be set programmatically, in the mds_global.ini file, as environment variables and in the registry (HKEY_CURRENT_USER\SOFTWARE\Mertech Data Systems\DB Drivers\BTR\Driver\version). If a value is not found, the driver uses an internal default. Setting options are listed top to bottom, in descending order of precedence.

· Runtime call to MdsSetSetting

· INT file

· mds.ini file

· mds_global.ini file

· Environment variables

· Registry settings (HKCU then HKLM)

· Driver defaults

Settings in the INT file override settings in the mds.ini file. This allows you to perhaps migrate one important table to another server or database. During migration, check the Get DatabaseName from Login option for all but the one table. Uncheck the option for the one table of interest. Doing so will cause the database name to be written only to the one INT file. For all but this one table, the Database setting in the mds.ini file will be used.

If the INT file and mds.ini file are both stored in the data directory, the INT-Folder setting in the mds.ini file is ignored and settings in the INT file and mds.ini file are merged. If a setting appears in both files (for example, the target back end tokens), the values in the INT file take precedence.

NOTE: Only one mds.ini file is allowed per data directory.

In the example below the mds.ini file contains INT-Folder=..\INTFILES:

C:\MyApp\Archives

GLOBAL_DAT.int

PROJ_DAT.int

RESOURCE_LIB.int

INTFILES\

BILLING_MKD.int

CLASS_MKD.int

DEPT_MKD.int

ENROLLS_MKD.int

PERSON_MKD.int

1999\

mds.ini

PERSON_MKD.int

When B_OPEN is called | The INT File read
C:\MyApp\Archives\1999\Person.mkd | C:\MyApp\Archives\1999\Person_mkd.int
C:\MyApp\Archives\1999\Billing.mkd | C:\MyApp\Archives\INTFILES\Billing_mkd.int

Configuration Settings


Scenarios using the GUI Migration Utility

Scenarios using the GUI Migration Utility

This section describes migration scenarios that can be used to consolidate data directories, maintain data segregation, perform load balancing and more.

Related Topics

A Basic Migration
Distributing Tables to a Different Server, Database, or Schema
Using a Custom Prefix or Postfix to Distinguish Tables
A Real-life Example
Pros and Cons of each Migration Option
Recap


A Basic Migration

A Basic Migration

The Mertech Migration Utility creates an SQL server database from a Btrieve/Pervasive.SQL transactional database to allow Pervasive developers to offer all the features available on SQL servers without changing source code or compromising on performance.

A sample Pervasive data directory is shown below.

C:\MyApp\DATA

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

PURCHASING.MKD

SALESPEOPLE.MKD

The DDF files contain the database file names (FILE.DDF), field names including offsets and data types (FIELD.DDF) and indexes (INDEX.DDF). The following steps are taken to migrate a Btrieve/Pervasive.SQL transactional database to an SQL database.

The developer:

1. Launches the Migration Utility

2. Logs in by entering the target server name, user name and database name.

3. Selects the FILE.DFF containing the tables to be migrated to the target database.

The Migration Utility:

1. Reads the DDF files to determine the structure for the migrated tables.

2. Uses this information along with the login data to create the tables on the SQL server.

3. Copies the existing data to the new tables.

4. Stores the table structure in intermediate (INT) files.

The same data directory after migration

C:\MyApp\DATA

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

MARKETING_MKD.INT

PURCHASING.MKD

PURCHASING_MKD.INT SALESPEOPLE.MKD

SALESPEOPLE_MKD.INT

Tables created in the target SQL database

MARKETING

PURCHASING

SALESPEOPLE

What happens at runtime?

When you launch the BTR2SQL Migration Utility, a Login dialog box prompts for a server name, user name and database name. These entries determine the target backend for the migrated files.

Similarly, when the operator runs the application, a Server Login dialog box displays to again obtain the target backend information. When the application issues a B_OPEN to open a file (for example MARKETING.MKD), the Mertech driver locates the associated INT file (MARKETING_MKD.INT) in the data directory and uses the login information along with the table structure stored in the INT file to connect to and open the converted SQL table.

& See also Consolidating INT files on the client and Automatic application login

Creating tables on the fly

In addition to using the INT file to connect to and open a converted table, the Mertech driver also uses the INT file to create tables at runtime.

When an application issues a B_CREATE, the driver looks to see if an INT file already exists for the file. If there is an existing INT file, the driver compares its contents with the index definitions in the new create structure. If they look like a match, the driver uses the definitions in the INT file to create the table on the server. This allows you to have pre-existing INT files (with full field definitions) for temp tables for instance.

There may be times when you do not want an existing INT file to be used, in that case you can set the keynum value for B_CREATE:

B_CM_OVERWRITE_IGNORE_INT_FILE= -6

Opposite this, perhaps you want the driver to always use the definition in the INT. Two more flags force this behavior and the FILESPEC in the dataBuffer is completely ignored:

B_CM_OVERWRITE_FORCE_INT_FILE =-99

B_CM_NOOVERWRITE_FORCE_INT_FILE=-100

To prevent an INT file from being deleted when a table is dropped, you can set the PERMANENT_INT token in the INT file to YES (the default setting is NO).

PERMAMENT_INT YES

& See also B_CREATE API

Common folder layouts

It is common in the Pervasive environment to create files with the same name residing in different folders. For example the C:\MyApp\DATA directory might contain subdirectories for the central, east and south regions of a company. Each subdirectory might contain the marketing, purchasing and sales person information for that region.

C:\MyApp\DATA

CENTRAL\

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

PURCHASING.MKD

SALESPEOPLE.MKD

EAST\

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

PURCHASING.MKD

SALESPEOPLE.MKD

SOUTH\

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

PURCHASING.MKD

SALESPEOPLE.MKD

The problem when migrating these files to the SQL side is that different tables with the same names will collide if stored in the same database. Mertech provides several ways to resolve this problem. You can:

1. Migrate each folder to a different SQL server. By definition, there will be multiple databases (refer to Distributing Tables to a Different Server, Database, or Schema).

serv1.db1.dbo.purchasing

serv2.db1.dbo.purchasing

2. Migrate each folder to a different database on the same server (refer to Distributing Tables to a Different Server, Database, or Schema).

serv.db1.dbo.purchasing

serv.db2.dbo.purchasing

3. Migrate each folder to a different schema name, or user on Oracle (refer to Distributing Tables to a Different Server, Database, or Schema).

serv.db1.user1.purchasing

serv.db1.user2.purchasing

4. Migrate all folders to the same server, database, and schema but attach a custom prefix to the table names (refer to Using a Custom Prefix or Postfix to Distinguish Tables).

serv.db1.dbo.cust1_purchasing

serv.db1.dbo.cust2_purchasing

Scenarios using the GUI Migration Utility


Distributing Tables to a Different Server, Database, or Schema

Distributing Tables to a Different Server, Database, or Schema

The Mertech drivers work seamlessly in a distributed environment with multiple local or remote SQL Servers. Consider the following scenarios:

1. You choose to leave some files in Pervasive, while migrating others to an SQL backend. This could be an interim step during the migration process to test the migration. Or, a decision may have been made to leave private tables in Pervasive on the local machine, while common tables are stored on a central SQL Server.

2. In a second scenario, branch offices may have a local SQL Server, and also connect to a central master SQL Server, to access certain data that needs to be shared across branches.

3. Another choice might be made to distribute files on different physical hard drives on the server to balance the drive access.

4. Finally, security settings may dictate your choice of where tables are stored.

The Mertech drivers support all of these scenarios.

Figure 23 Distributed Environment

1. Start the Migration Utility and login.

2. Select the required File.DDF.

3. Select the individual files for this target database.

4. <Right-click> and choose Convert to Database.

5. Uncheck the Get Server Name from Login, Get Schema from Login and Get Database Name from Login check boxes so this information is stored in the INT files.

6. Click OK.

7. To migrate files to a different server:

a. choose File | Login and select a different server.

b. If required, choose a new FILE.DDF.

c. Select the individual files for this server.

d. <Right-click> and choose Convert to Database.

e. Uncheck the Get Server Name from Login, Get Schema from Login and Get Database Name from Login check boxes so this information is stored in the INT files.

f. Click OK.

8. To migrate additional files to a different database on the same server:

a. Choose File | Select Database and pick a different database from the Select Database dialog box.

b. If required, choose a new FILE.DDF.

c. Select the individual files for the new database.

d. <Right-click> and choose Convert to Database.

e. Uncheck the Get Server Name from Login, Get Schema from Login and Get Database Name from Login check boxes so this information is stored in the INT files.

f. Click OK.

9. To migrate files to a different schema:

a. choose File | Login and provide different credentials.

NOTE: The selected user must have object creation rights.

b. If required, choose a new FILE.DDF.

c. Select the individual files for the new schema.

d. <Right-click> and choose Convert to Database.

e. Uncheck the Get Server Name from Login, Get Schema from Login and Get Database Name from Login check boxes so this information is stored in the INT files.

f. Click OK.

Other options

The BTR2SQL Migration Utility allows you an easy way to distribute an MS SQL database by selecting separate Filegroups for tables, indexes, and text storage (Convert File dialog box | MSSQL tab). Choosing separate Filegroups can streamline administration and increase data access speed.

The BTR2SQL Migration Utility also allows Oracle users to choose different tablespaces for index and data tables (Convert File dialog box | Indexes tab).

You can also choose to migrate some tables to one tablespace and other tables to another tablespace using the scripting option:

1. Use the BTR2SQL Table | Generate | SQL Script for Creating Tables option.

2. Modify the generated script as needed.

3. Run the modified script to create the tables.

4. Use BTR2SQL Table | Copy Data option to copy the data.

Scenarios using the GUI Migration Utility


Using a Custom Prefix or Postfix to Distinguish Tables

Using a Custom Prefix or Postfix to Distinguish Tables

Instead of using a different server, database, or schema as in the previous scenario, you might want to create separate tables for each region. To avoid the conflict in names, you can include either a prefix such as SOUTH_ or a suffix like _EAST. During the migration, the tables are created with this addition so "SALESPEOPLE" becomes "SOUTH_SALESPEOPLE" or "SALESPEOPLE_SOUTH".

You specify the prefix or suffix in the data directory configuration file, mds.ini. The mds.ini file contains a number of settings to help automate and consolidate information needed by the BTR2SQL driver at runtime. Mds.ini is placed in the folder where the application’s data files are usually stored and works in conjunction with the INT files that have replaced the original Btrieve data files.

The Table-Prefix and Table-Postfix settings in the mds.ini file can be used to create unique table names.

The example Pervasive directory before migration

C:\MyApp\DATA

CENTRAL\

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

PURCHASING.MKD

SALESPEOPLE.MKD

EAST\

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

PURCHASING.MKD

SALESPEOPLE.MKD

SOUTH\

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

PURCHASING.MKD

SALESPEOPLE.MKD

1. Place a copy of the mds.ini file in each data directory.

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

2. Edit the mds.ini file to add the required prefix or suffix for the data directory. For example, edit the mds.ini file in the CENTRAL data directory to add the table prefix "CENTRAL_".

[SQL_BTR]

INT-Folder=

Server=

Database=

Schema=

UseTrustedConnection=

User=

Password=

Table-Prefix=CENTRAL_

Table-Postfix=

3. Migrate the files in each data directory to your SQL Server.

a. Open the Migration Utility, login and select the associated FILE.DDF.

b. Select the Pervasive files to be migrated.

c. <Right-click> and choose Convert to Database

d. Click OK.

This 1) creates the SQL tables with the entered prefix 2) copies the existing data into the SQL tables, and 3) generates the intermediate files (.INT).

The same data directories after migration

C:\MyApp\DATA

CENTRAL\

mds.ini

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

MARKETING_MKD.INT

PURCHASING.MKD

PURCHASING_MKD.INT

SALESPEOPLE.MKD

SALESPEOPLE_MKD.INT

EAST\

mds.ini

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

MARKETING_MKD.INT

PURCHASING.MKD

PURCHASING_MKD.INT

SALESPEOPLE.MKD

SALESPEOPLE_MKD.INT

SOUTH\

mds.ini

FIELD.DDF

FILE.DDF

INDEX.DDF

MARKETING.MKD

MARKETING_MKD.INT

PURCHASING.MKD

PURCHASING_MKD.INT

SALESPEOPLE.MKD

SALESPEOPLE_MKD.INT

Tables created in the target SQL database

CENTRAL_BILLING

CENTRAL_PURCHASING

CENTRAL_SALESPEOPLE

EAST_BILLING

EAST_PURCHASING

EAST_SALESPEOPLE

SOUTH_BILLING

SOUTH_PURCHASING

SOUTH_SALESPEOPLE

Specifying the path to the INT files

In the scenario mentioned above, 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.

When you migrate to SQL, you would deploy this structure (plus your application and Mertech driver):

C:\MyApp\DATA

CENTRAL\

mds.ini

MARKETING_MKD.INT

PURCHASING_MKD.INT

SALESPEOPLE_MKD.INT

EAST\

mds.ini

MARKETING_MKD.INT

PURCHASING_MKD.INT

SALESPEOPLE_MKD.INT

SOUTH\

mds.ini

MARKETING_MKD.INT

PURCHASING_MKD.INT

SALESPEOPLE_MKD.INT

Or, you can add an INT-Folder entry to the mds.ini file to tell the driver where to find the common set of INT files, and instead deploy something like this:

C:\MyApp\DATA

INTFILES\

MARKETING_MKD.INT

PURCHASING_MKD.INT

SALESPEOPLE_MKD.INT

CENTRAL\

mds.ini

EAST\

mds.ini

SOUTH\

mds.ini

What this has done is eliminate the duplicated INT files. You now only have one folder to maintain for the INT files. You still need each data folder as you would in Btrieve, but you just need the mds.ini file in it with the appropriate INT-Folder and Table-Prefix entries.

[SQL_BTR]

INT-Folder=..\INTFILES

Server=

Database=

Schema=

UseTrustedConnection=

User=

Password=

Table-Prefix=CENTRAL_

NOTE: Instead of creating CENTRAL, EAST and SOUTH directories for the mds.ini files, you can use the MdsSetSetting API to create virtual mds.ini files in memory for each folder. For example:

MdsSetSetting("C:\MyApp\DATA\CENTRAL","INT_Folder","..\INTFILES")

MdsSetSetting("C:\MyApp\DATA\CENTRAL","Table-Prefix"," CENTRAL_")

& See also Setting global and folder specific values programmatically

What happens at runtime?

When an application issues the B_OPEN API to open a file (for example MARKETING.MKD), the Mertech driver opens the mds.ini file in the data folder and uses the path defined by the INT-Folder entry to locate the associated INT file (..\INTFILES \MARKETING_MKD.INT). The driver reads the INT file and uses the information to connect to and open the converted SQL table.

Scenarios using the GUI Migration Utility


A Real-life Example

A Real-life Example

A Btrieve application maintains a master catalog of office supplies and creates customer-specific catalogs on the fly at runtime. The application was migrated to MS SQL Server and the deployed data directory and database look like this.

Data directory

C:\MyApp\DATA

INTFILES\

MASTER_DAT.INT

The MS SQL database

MASTER

The MASTER_DAT.INT file has the read-only attribute checked and the PERMANENT_INT token set to YES.

Customer "A" requests a supply list specific to his business. The application creates a new folder, CustomerA

C:\MyApp\DATA

INTFILES\

MASTER_DAT.INT

CUSTOMERA\

The application also creates an mds.ini file in that folder.

C:\MyApp\DATA

INTFILES\

MASTER_DAT.INT

CUSTOMERA\

mds.ini

The mds.ini file contains the INT-Folder entry to show where the INT file is that contains the catalog structure. The mds.ini file must also provide credentials for accessing the server and some way to distinguish this catalog from other catalogs.

NOTE: The mds.ini file should be the only file in the CUSTOMERA folder. If the INT file is placed in the CUSTOMERA folder, the mds.ini INT-FOLDER entry is ignored.

This example mds.ini file uses Table-Prefix to distinguish this table from others. A different schema or user could also be used.

[SQL_BTR]

INT-Folder=..\INTFILES

Server=testserver

Database=testdb

Schema=dbo

Table-Prefix=CustA_

NOTE: Instead of creating the CUSTOMERA directory containing an mds.ini file, you can use the MdsSetSetting API to create a virtual mds.ini file in memory for CUSTOMERA. For example:

MdsSetSetting("C:\MyApp\DATA\CUSTOMERA","INT_Folder","..\INTFILES")

MdsSetSetting("C:\MyApp\DATA\CUSTOMERA","Table-Prefix"," CustA_")

& See also Setting global and folder specific values programmatically

When the application issues a B_OPEN (C:\MyApp\DATA\CUSTOMERA\MASTER.DAT):

· The driver opens the mds.ini file in the CUSTOMERA subdirectory and is redirected by the INT-Folder entry to the MASTER_DAT.INT file in the ..\INTFILES subdirectory.

· The driver reads the MASTER_DAT.INT file and tries to open testserver/testdb.CustA_MASTER

· The table does not exist so BTRIEVE Status 12 (File does not exist) is returned.

The application executes a B_CREATE

· The driver repeats the same logic to locate the INT file (actually, the mds.ini and INT file are obtained from memory cache).

· The driver creates the CUSTA_MASTER table based on the definitions in the MASTER_DAT.INT file.

Data directory

C:\MyApp\DATA

INTFILES\

MASTER_DAT.INT

CUSTOMERA\

mds.ini

The SQL database

MASTER

CUSTA_MASTER

NOTE: If you want to use a unique schema/user or database instead of a table prefix for each catalog, you need to execute the SQL commands to do this before accessing tables from the new data folder. BTR2SQL includes an embedded SQL API.

& See also B_CREATE API

Scenarios using the GUI Migration Utility


Pros and Cons of each Migration Option

Pros and Cons of each Migration Option

We have looked at several different ways to migrate tables to an SQL server. Below are some of the pros and cons of each.

Migrate each folder to a different server

serv1.db1.dbo.Hours
serv2.db1.dbo.Hours

or a different database

serv.db1.dbo.Hours
serv.db2.dbo.Hours

PROs

· An easy way to perform load balancing by distributing the data across multiple computers, CPUs, hard disks, etc.

· You can assign different user credentials to the different servers or databases.

CONs

· Transactions will not cross multiple "customers" since the driver will have a unique connection to the database for each data folder.

· There is more administrative overhead associated with backing up and maintaining the additional hardware and software.

· When tables are migrated to multiple databases, the driver must have a separate set of connection(s) open for each database and must switch context each time a file from a different database is accessed. This can have an impact on the server. The context switching can also add performance overhead on the client depending on the order of calls and how frequently the switch is made.

Migrate each folder to a separate schema (users on Oracle)

serv.db1.user1.Hours
serv.db1.user2.Hours

PROs

· There is a clean distinction between "sets" of data.

· You can assign different user credentials to each schema.

· The list of tables in each schema is as small as possible.

CONs

· There is a little more administrative overhead maintaining the additional schemas.

Migrate each folder using a different table prefix or suffix

serv.db1.dbo.East_Hours
serv.db1.dbo.Central_Hours

PROs

· This option is the easiest to implement.

· The same approach works in all backends.

CONs

· The table list can become very large.

· It is harder to assign separate user access control.

· Oracle's 30 character limit on identifiers can severely hinder this option.

Consolidate the tables during migration

serv.db1.dbo.Hours

PROs

· You have the fewest number of tables to maintain.

· The same approach works in all backends.

CONs

· The application code must be updated.

· There is no separate user access control.

In summary, adding a table prefix or suffix during migration is the easiest and most-used migration option. However, if you have a lot of tables in each folder, the list on the server can be a bit overwhelming. For example, if you start with 300 data files and you have four customer folders, you end up with 1200 tables.

If your application requires different credentials on the server for different customer sets, then separating the tables by user/schema might be the easiest route.

Scenarios using the GUI Migration Utility


Recap

Recap

During migration, the Migration Utility reads DDF files and stores corresponding table structures in table-specific INT files. The Migration Utility can also store the table path and other server credentials in the INT files.

The developer can create a configuration file, mds.ini, to provide additional access information specific to each data folder.

At runtime, the Mertech driver looks for the INT file and/or mds.ini file in the data directory and uses information in these files to connect to and open the SQL tables.

By selecting the appropriate conversion options you can use the BTR2SQL Migration Utility to distribute, segregate your migrated database.

Scenarios using the GUI Migration Utility


Migrating Files using the Command-line

Migrating Files using the Command-line

A command-line tool, MdsMigrateTable, can be used instead of the BTR2SQL GUI Migration Utility to migrate an existing Btrieve application to work with an SQL server.

Related Topics

MdsMigrateTable
Migration Validator


MdsMigrateTable

MdsMigrateTable

You can use MdsMigrateTable to do all the work (create tables, copy data, create indexes), or you can create the tables and indexes and copy the data with another tool, or only create an SQL script to create the tables.

MdsMigrateTable reads the Btrieve table definitions from the Pervasive Data Definition Files (DDFs).

Command-line syntax

Select Mertech’s ISDBC Drivers for Btrieve | Mds Migrate Table from the Windows Start menu. Since this is a command-line utility, a Command Prompt is automatically opened

Run the MdsMigrateTable script by typing the following at the command prompt:

MdsMigrateTable tablelist operation ddf [configuration parameters]

Tablelist determines which Btrieve tables are involved in the migration process. The tablelist parameter is required.

Tablelist | Description
OneTable | Perform the selected task on one Btrieve table. Use the /table parameter to pass the table name.
AllTables | Perform the selected task on the entire Btrieve database.
AllConfig | Perform the selected task on the defined Btrieve tables. Use the /config parameter to pass the name of the XML document that defines the configuration for the migration.

Operation determines which piece(s) of the migration to perform. The operation parameter is required.

Operation | Description
CreateTable | Only create the tables, indexes and INT files on the server.
CopyData | Only copy the Btrieve data to the SQL server (indexes are dropped, data is copied, then the indexes are restored). Assumes the tables were already created on the SQL server.
Full | Create the tables, indexes and INT files and copy the data.
IntOnly | Only create the INT files.
ScriptOnly | Only generate the SQL script that can be used to create the tables.

NOTE: INT files are created in the same location as the data files unless they are redirected to another folder by the mds.ini file.

ddf is the full or relative path to the data dictionary files (DDFs). The ddf parameter provides the source for the conversion and is required.

/ddf <DDF Folder>

Configuration parameters are settings used during the migration process. Configuration parameters are optional unless otherwise indicated.

Configuration Parameter | Description
/server <servername> | Destination SQL server and optional port number where the server is listening for communication. Always use the same SQL server version as the end-user. For instance, migrating to MS2008 utilizes different data types than MS2005.
/database <dbname> | Destination database.
/schema <schema> | Destination database schema. Normally the database owner.
/user <username> | User name for server login (empty for trusted connection).
/password <password> | Password for server login.
/table <tablename> | Name of table to migrate (as listed in the DDFs). /table is required if tablelist is OneTable.
/dll <Mertech migration dll> | The path and filename of the Mertech migration dll (sql_btr.dll, mys_btr.dll, ora_btr.dll, or pgs_btr.dll) used to access the SQL backend. If the full path is not specified, the dll is loaded from the system PATH.
/config <xml config filename> | XML document that defines the configuration for the migration. If NULL, the default settings are used. /config is required if tablelist is AllConfig.
/trace <trace filename> | Path and filename for the trace output.
/level <trace level> | Level of tracing (-2, -1, 0, 1, 2, 3, 4).
/nopause | Do not wait for a key press at end.

NOTE: If the server, target database, and/or login credentials are not provided, the mds.INI file, registry settings, or Login dialog box provide these details.

XML Config File

The /config parameter can be used to pass the name of an XML config file that contains settings that affect the conversion process. For instance, whether to remember the server name in the INT file, the Oracle tablespace settings, or whether a field is nullable. If no /config parameter is provided the driver's default settings and the information obtained from the DDFs and the data file itself are used.

You can use the XML config file to override default conversion settings. You only need to include the pieces to be changed. For example, if you want to change one of the fields to not be nullable even though the DDFs are flagged as nullable you might provide the following XML config file.

<Btrieve2SqlMigration Driver="SQL_BTR">

<Table Name="Updates">

<Fields>

<Field Name="C1" Nullable="n"/>

</Fields>

</Table>

</Btrieve2SqlMigration>

A Table entry with no table Name specified overrides the default settings for ALL tables. A Table entry with a specific Name indicated overrides the default settings. So you can have a no table name entry with OverwriteExistingTable=yes and then for table "person", specify OverwriteExistingTable=no.

If you are migrating a uniPaaS application and the source is stored in XML format, you can generate the XML config file automatically using the MigrateUniPaasData command-line tool. You can then edit the generated file to meet your needs. You can also create the XML config file manually.

NOTE: Refer to the <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\sdk\samples\migration folder for sample XML files and the DTD, which contains the legal building blocks for the XML config file.

Migrating Files using the Command-line


Migration Validator

Migration Validator

Run MigrationValidator after data is migrated to verify that everything was correctly transferred from the Btrieve application to the SQL server. Migration Validator checks that the basic table structure and indexes match and then reads all the records from Btrieve and from SQL to verify the data was successfully stored.

A message displays in the output if any mismatch is found.

Prerequisites

1. BTR2SQL or some other tool has copied all of the data from the Btrieve database into the desired SQL backend.

2. The data has not been modified in either version of the database. Otherwise, the tool will incorrectly report failures, such as mismatched data or incorrect number of rows.

3. The Pervasive version of w3btrv7.dll is expected to be present in the system PATH. You can accomplish this most efficiently by copying the original dll from your pvsw\bin folder into the folder where MigrationValidator.exe is located.

Command-line Syntax

Select Mertech’s ISDBC Drivers for Btrieve | Migration Validator from the Windows Start menu. Since this is a command-line utility, a Command Prompt is automatically opened.

Run the Migration Validator script by typing the following at the command prompt:

MigrationValidator <database location> [-dll <Mertech dll>] [-table <tablelist>] [-maxRows <n>] [/varOnly] [/debug]

database location: Is required and indicates the path to the DDFs. The DDFs are read for schema information, then each table in the list is opened and tested.

-dll: Specifies the full path and filename (wbtrv32, w3btrv7, or sql_btr) of the dll used to access the SQL backend. If no dll is specified, sql_btr.dll is loaded from the system PATH.

-table: Lists the table(s) to be tested. By default, all tables are tested. When you include the -table parameter, you can specify one or more tables to limit the test. Use semicolons to separate the list, or include the ‑table parameter multiple times.

-maxRows: Allows you to shorten the testing time by reducing the number of rows checked.

/varOnly: Only tables with variable length records are tested if this parameter is included.

/debug: Additional processing information is displayed if this parameter is included.

Example:

MigrationValidator c:\mydata\demodata -dll "C:\Program Files\Mertech Data Systems\DB Drivers\Btrieve\bin\ora_btr.dll" -table person;billing;class -maxRows 2000

Migrating Files using the Command-line


Migrating Magic uniPaaS Applications

Migrating Magic uniPaaS Applications

UniPaaS (now called Magic xpa Application Platform) from Magic Software Enterprises is a powerful application platform that simplifies the process of building and deploying business applications. Although this platform has been available for many years and supports SQL databases, Btrieve tends to be the database of choice for Magic developers.

Mertech provides the MigrateUniPaasData command-line tool as a part of the BTR2SQL product bundle. MigrateUniPaasData converts uniPaaS XML applications to an SQL backend without compromising the stability or speed of the application and without requiring any source code changes. MigrateUniPaasData does this by reading the application source files (datasources.xml, datasourcesindex.xml, magic.ini and models.xml) to create the SQL database and then storing the SQL table schema in INT files that the Mertech BTR2SQL drivers use to connect to and open the converted SQL tables.

Figure 24 MigrateUniPaasData

NOTE: If your uniPaaS application is not stored in XML format, export the DDFs and use the Migration Utility or the command-line tool MdsMigrateTable to perform the migration.

How migration works

The migration process consists of three steps:

1. Create the SQL tables: MigrateUniPaasData reads the application source files and creates the tables on the server.

2. Create the INT files: MigrateUniPaasData creates one INT file for each converted data file. The INT file describes the table structure.

3. Load the data on the server: MigrateUniPaasData copies data from the existing files to the newly created SQL tables.

Related Topics

MigrateUniPaasData Command-line Tool
MigrateUniPaasData.ini File
Options to migrate a multi-directory UniPaaS application
Final steps in the UniPaas migration process
What happens after your UniPaaS application is deployed?
Migration Validator Tool for uniPaaS Applications


MigrateUniPaasData Command-line Tool

MigrateUniPaasData Command-line Tool

MigrateUniPaasData converts uniPaaS XML applications to an SQL backend. It allows full migrations as well as maintenance tasks like exporting new INT files. MigrateUniPaasData can also generate the SQL script used to define a new database on the SQL server.

Command-line syntax

Select Mertech’s ISDBC Drivers for Btrieve | Migrate UniPaaS Data from the Windows Start menu. Since this is a command-line utility, a Command Prompt is automatically opened.

Run the Migrate UniPaaS Data script by typing the following at the command prompt:

MigrateUniPaasData [operation] [configuration parameters] source

Operation determines the type of migration that is performed. A full migration is performed if no operation is specified.

Operation | Description
CreateTable | Only create the table(s) on the SQL server.
CopyData | Only copy the data to the SQL server.
Full | Create tables and copy the data (default).
XML [<xml filename>] | Generate XML file for use with MdsMigrateTable.
IntOnly | Only generate the .INT files.
ScriptOnly | Only generate the SQL script to create the schema.

Configuration parameters are settings used during the migration. Configuration parameters can also be provided in the MigrateUniPaasData.ini File. Settings entered on the command-line override settings in the MigrateUniPaasData.INI file.

Configuration Parameter | Description
-dll <Mertech migration dll> | Location and filename of the Mertech migration dll to use (sql_btr.dll, ora_btr.dll, mys_btr.dll, pgs_btr.dll)
-datalocation <path to data files> | Folder where data file are located; INT files are written here.
-magicini <magic.ini location> | Folder and filename for the magic.ini file.
-server <servername> | Destination SQL server and optional port number where the server is listening for communication. Always use the same SQL server version as the end-user. For instance, migrating to MS2008 utilizes different data types than MS2005.
-database <dbname> | Destination database.
-schema <schema> | Destination database schema. This name is normally the same as database owner.
-user <username> | User name for server login (empty for trusted connection).
-password <password> | Password for server login. The password in the INI file is encrypted with MdsEncryptPassword.
-table <tablename> | Only perform the migration operation on this table.
-filegroupTable <filegroup> | Specify a filegroup for table data (MSSQL only).
-filegroupIndex <filegroup> | Specify a filegroup for indexes (MSSQL only).
-filegroupText <filegroup> | Specify a filegroup for text, ntext, image, xml, varchar(max), nvarchar(max), and varbinary(max) fields (MSSQL only).
-trace <trace filename> | Path and filename for the trace output.
-level <trace level> | Level of tracing (-1, 0, 1, 2, 3).
-nopause | Do not wait for a key press at end.
-debug | Send extra trace information to the screen during the migration operation.
-silent | Do not send status information to the screen (only errors).
-RequireBtrieveFile | Skip table if the data file is not found.
-MarkIntAsPermanent | Mark the INT as 'permanent' (read-only).

While the INI file supports yes/no for flag values, the command-line does not. So, for instance, "‑RequireBtrieveFile" means "yes". Omitting "-RequireBtrieveFile" means "no".

NOTE: If the server, target database, and/or login credentials are not provided, the INI file, registry settings, or Login dialog box provide these details.

Source is the name of source folder containing datasources.xml and models.xml. The source folder should include the complete path information and full filename including the file name extension.

[drive:] <source folder>

Migrating Magic uniPaaS Applications


MigrateUniPaasData.ini File

MigrateUniPaasData.ini File

The MigrateUniPaasData command-line tool uses settings in the MigrateUniPaasData.INI file to determine the paths to dlls and data folders, name of the target server, database, and schema, and other migration options. The MigrateUniPaasData.INI file should be placed in the same directory as the MigrateUniPaasData tool.

NOTE: A sample MigrateUniPaasData.ini file can be found in the installation folder (<Program Files>\Mertech Data Systems\DB Drivers\Btrieve\bin).Settings are assigned in MigrateUniPaasData.INI file by entering the setting, followed by an equal sign, followed by the value (for example, Server=mssql\sql2008). All settings and values are case insensitive. Spaces are ignored before and after the equal sign. Leave a value empty to use the tool's default value for that setting. A semi-colon (;) at the beginning of a line indicate the line is a comment.

A list of the available settings is given below. Comments inside the MigrateUniPaasData.INI file provide additional information about each of these settings.

NOTE: Surround names with quotes if there are spaces in the name.

Setting | Description, Example, <default>
dll | Full or relative path to the dll to use for the target server. dll=”C:\Program Files (x86)\Mertech Data Systems\DB Drivers\Btrieve\bin\sql_btr.dll”
Server | Name (and optional port number) of the target server. Server=mssql\sql2008
Database | Name of the target database. Database=MyAppDB
Schema | Name of target database schema. This name is normally the same as database owner. Schema=c001
User | User name for server login (empty for trusted connection). User=sa
Password | Password for server login. password=db35195a59a4553c
FilegroupTable | Filegroup name for table data (MSSQL only) FilegroupTable= TABLES
FilegroupIndex | Filegroup name for indexes (MSSQL only) FilegroupIndex= INDEXES
FilegroupText | Filegroup name for large fields (MSSQL only) FilegroupText = LOBFIELDS
DataFolder | Complete or relative path to data files. DataFolder=C:\MyApp\MyData\Company1
MagicIni | Complete or relative path and filename for magic.ini. The magic.ini file is used to resolve variable names (enclosed in %) and to verify which tables are handled by the Btrieve gateway. MagicIni=C:\magic\Users\me\Magic.ini & See additional information below.
IgnoreTableSymbol | Skip table names beginning with this string. This prefix character identifies deprecated or unused tables that should not be migrated. IgnoreTableSymbol=~
RemovePathFromFilenames | If set to yes, all path information is removed from the Btrieve filename found in "Data Source Name." RemovePathFromFilenames = yes <no>
IllegalIdentifierChars | Characters in this list (table names, column names, etc.) are replaced by the ReplacementChar. Double quote is always included in IllegalIdentifierChars. IllegalIdentifierChars = .,;:`'" /|\#%@~!$^&*(){}[]<>?-+= <.,;:`'\" /|\\#-%@~!$^&*(){}[]<>?-+=>
ReplacementChar=_ | Character to be used to replace illegal characters. Double quote is not allowed. ReplacementChar=_ <_>
IdentifierMaxLen | Maximum length of an identifier (table names, column names, triggers etc.). IdentifierMaxLen = 50 <30>
TRACE_ON | Location of the trace file. TRACE_ON=c:\trace\MigrateMyApp.tra
TRACE_LEVEL | Level of trace output (-1,0,1,2,3) TRACE_LEVEL=2
TRACE_FLUSH | Force a file flush after each trace output TRACE_FLUSH=no
RequireBtrieveFile | Selects whether tables should be created if there is no associated Btrieve data file. Except in rare cases, this option should always be Yes. RequireBtrieveFile=yes <yes>
MarkIntAsPermanent | Set to yes to make the INT file read only. MarkIntAsPermanent=no <no>
AllowFieldsBeyondRecLen | Set to yes to allow the fixed record length to be expanded. AllowFieldsBeyondRecLen=no <no>
IgnoreNullValues | Set IgnoreNullValues to no for a slight increase in performance under SQL if an indexed value equals the defined Null value. IgnoreNullValues = yes <yes>
RememberServerName | Set RememberServerName to yes to store the server name in the INT file. RememberServerName=no <no>
RememberDatabaseName | Set RememberDatabaseName to yes to store the database name in the INT file. RememberDatabaseName=no <no>
RememberSchema | Set RememberSchema to yes to store the schema name in the INT file. RememberSchema=no <no>
EraseTableBeforeCopy | Set EraseTableBeforeCopy to no to allow an append for a CopyData operation. EraseTableBeforeCopy = yes <no>
OverwriteExistingTable | If OverwriteExistingTable is no, during a Full migration, the operation fails if the table already exists on the server. OverwriteExistingTable = yes <no>
OemTranslation | Set to yes if the character data in the Btrieve files is stored in DOS/OEM format, and you want to do OEM to ANSI conversion so the data looks correct on SQL. OemTranslation = no <no>
[Logical Names] | List variable definitions not resolved or to override in magic.ini. For example: CUST = FLstore & See additional information below.
[Owner Names] | An owner name is like a password for a Btrieve data file. The migration utility cannot open the file for reading without this password. When needed, specify the filename and owner name here. MYFILE1.btr = OWNERNAME1 MYFILE2.btr = OWNERNAME2
[Duplicate Tables] | In some applications, the same file is referenced by multiple entries. When this is true, we do not want to migrate the tables multiple times, since this would create several tables with duplicated data. For example, if you have 132 as 'Person', 1132 as 'Persons', and 1341 as 'People' all pointing to person.btr, then you would list 1132 and 1341 as duplicate tables. The information after the '=' is considered a comment. This information is not used. For example; 1132 = DD Person 1341 = HH Person
[Temp Tables] | List the table numbers of temporary tables to be skipped during the migration. The information after the '=' is considered a comment. This information is not used. For example: 37 = My Temp Table 1 [%TEMPPATH%table1.T%TERM%] 1127 = Some deprecated table [%PATH%\oldtable.btr] & See additional information below.

MagicIni

uniPaaS variable configuration information is entered in the Studio and stored in the magic.ini file. This includes a definition for all the data files used in the project.

Figure 25 Data File Definitions

This also includes logical names, which provide a way to specify the location of physical files.

Figure 26 Logical Names

At runtime, when the user logs into a certain customer (for example, FL), the %CurrentCust% variable in the magic.ini file is updated to reflect the exact location (for example, FLstore). Future access to a customer table (for example, Customers.btr) is then directed to the FLstore directory (c:\SampleMultiCompanyLayout\FLstore\Customer.btr).

During migration, MigrateUniPaasData reads the %CurrentCust% variable and uses this information to identify the location for a folder being migrated. Since the runtime information is not available during migration, the %CurrentCust% variable must be properly set in the magic.ini file prior to migrating each folder.

Alternatively, you can provide this value in the [Logical Names] section of the MigrateUniPaasData.ini. See example below.

[Logical Names]

MigrateUniPaasData resolves logical names using the magic.ini file. However some names are determined at runtime (see example in the MagicIni section) and therefore are not available during the migration. Those values can instead be placed in the [Logical Names] section of the MigrateUniPaasData.ini file.

For example:

[Logical Names]

CurrentCust = FLstore

When a value appears in both the magic.ini file and the MigrateUniPaasData.ini file, the value in the MigrateUniPaasData.ini file overrides the value in magic.ini.

[Temp Tables]

The [Temp Tables] section is where you list the tables to be omitted from the migration. This includes tables to skip when migrating a specific directory, for example, when migrating the CORPORATE directory you want to skip all of the regional tables. This also includes temporary tables that don’t need to be migrated. Enter the table ID, followed by an optional comment to help you identify the table.

For example:

[Temp Tables]

100 = Temp Table1

Migrating Magic uniPaaS Applications


Options to migrate a multi-directory UniPaaS application

Options to migrate a multi-directory UniPaaS application

It is common for developers to use a multi-directory layout where general information is stored in a global corporate directory and separate directories are created to keep customer-specific data. The customer-specific directories often contain duplicate filenames (for example Customers.btr).

The question arises what is the best way to migrate this type of database to an SQL backend. On the SQL side, different tables with the same names collide if you attempt to create them in the same database. MigrateUniPaasData allows you to choose several ways to resolve this problem. You can:

· Migrate each directory to a different server

· Migrate each directory to a different database

· Migrate each directory to a different schema (user on Oracle)

· Choose a unique table prefix (or postfix) for each directory

This section examines these migration options, gives example configuration settings, and describe pros and cons of each selection.

Creating the migration files

You can specify the configuration settings for MigrateUniPaasData:

· Using command-line arguments

· In an mds.ini file

· In an MigrateUniPaasData.ini file

NOTE: Settings entered on the command-line override settings in the ini files

For our examples, we provide all the settings in the mds.ini and MigrateUniPaasData.ini files. Placing settings in the mds.ini file means they can be used by the drivers at runtime.

The diagram below shows our starting layout on the left. In order to perform the migration, we will create several batch files and several ini files in each directory.

Creating the MigrateUniPassData batch files

Two batch files are created in each of the CORPORATE, FLstore and GAstore directories. One performs the migration and the other verifies that the migration was successful. The content of the batch files is the same in all three directories.

_DoMigrate.bat

Runs the MigrateUniPassData migration utility and outputs results to Migration.log.

set path=C:\Program Files (x86)\Mertech Data Systems\DB Drivers\Btrieve\bin;%path%

MigrateUniPaasData full . >_Migration.log

cmd /k

_DoValidate.bat

Runs the MigrationValidator_uniPaas verification utility after the migration is complete and outputs results to Validation.log.

set path=C:\Program Files (x86)\Mertech Data Systems\DB Drivers\Btrieve\bin;%path%

MigrationValidator_uniPaas . >_Validation.log

cmd /k

Creating the MigrateUniPassData mds.ini files

An mds.ini file is also created in each data directory. Settings in the mds.ini files control how tables are created and accessed on the SQL server. The mds.ini file is the key to successfully migrating a multi-directory layout.

& For a description of the mds.ini file tokens see mds.ini file

Example mds.ini files are shown below. These files demonstrate the use of the INT_Folder token to consolidate the INT files in two folders, one for corporate tables and one for the regional store tables. These files also show the settings to be used for each of the migration options. Settings in the example files are listed under the [SQL_BTR] heading meaning they are specific to Microsoft SQL Server.

Select the mds.ini files settings for the migration option that best suits your environment.

NOTE: Instead of creating CORPORATE, FLstore and GAstore directories for the mds.ini files as shown in the examples, you can use the MdsSetSetting API to create virtual mds.ini file in memory for each directory. For example:

MdsSetSetting("C:\SampleMultiCompanyLayout\CORPORATE","INT_Folder", "..\intfiles_corporate")

MdsSetSetting("C:\SampleMultiCompanyLayout\CORPORATE","Server", "sqlsrv1\sql2012")

& See also Setting global and folder specific values programmatically

Related Topics

Migrating Magic uniPaaS Applications
Option 1: Migrate each directory to a different server
Option 2: Migrate each directory to a different database
Option 3: Migrate each directory to a different schema (user on Oracle)
Option 4: Choose a unique table prefix (or postfix) for each directory


Option 1: Migrate each directory to a different server

Option 1: Migrate each directory to a different server

PROs

· An easy way to perform load balancing by distributing the data across multiple computers, CPUs, hard disks, etc.

· You can assign different user credentials to the different servers.

CONs

· Transactions will not cross multiple "customers" since the driver will have a unique connection to the database for each data directory.

· There is more administrative overhead associated with backing up and maintaining the additional hardware and software.

mds.ini files in:

CORPORATE Directory | FLstore Directory | GAstore Directory
[SQL_BTR] INT-Folder=..\intfiles_corporate Server=sqlsrv1\sql2012 Database=SQLDB Schema=dbo UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3 | [SQL_BTR] INT-Folder=..\intfiles_regions Server=sqlsrv2\sql2012 Database=SQLDB Schema=dbo UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3 | [SQL_BTR] INT-Folder=..\intfiles_regions Server=sqlsrv3\sql2012 Database=SQLDB Schema=dbo UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3

Options to migrate a multi-directory UniPaaS application


Option 2: Migrate each directory to a different database

Option 2: Migrate each directory to a different database

PROs

· An easy way to perform load balancing by distributing the data across multiple computers, CPUs, hard disks, etc.

· You can assign different user credentials to the different databases.

CONs

· Transactions will not cross multiple "customers" since the driver will have a unique connection to the database for each data directory.

· There is more administrative overhead associated with backing up and maintaining the additional hardware and software.

· When tables are migrated to multiple databases, the driver must have a separate set of connection(s) open for each database and must switch context each time a file from a different database is accessed. This can have an impact on the server. The context switching can also add performance overhead on the client depending on the order of calls and how frequently the switch is made.

mds.ini files in:

CORPORATE Directory | FLstore Directory | GAstore Directory
[SQL_BTR] INT-Folder=..\intfiles_corporate Server=sqlsrv1\sql2012 Database=SQLDB Schema=dbo UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3 | [SQL_BTR] INT-Folder=..\intfiles_regions Server=sqlsrv1\sql2012 Database=SQLDBFL Schema=dbo UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3 | [SQL_BTR] INT-Folder=..\intfiles_regions Server=sqlsrv1\sql2012 Database=SQLDBNY Schema=dbo UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3

Options to migrate a multi-directory UniPaaS application


Option 3: Migrate each directory to a different schema (user on Oracle)

Option 3: Migrate each directory to a different schema (user on Oracle)

PROs

· There is a clean distinction between "sets" of data.

· You can assign different user credentials to each schema.

· The list of tables in each schema is as small as possible.

CONs

· There is a little more administrative overhead maintaining the additional schemas.

mds.ini files in:

CORPORATE Directory | FLstore Directory | GAstore Directory
[SQL_BTR] INT-Folder=..\intfiles_corporate Server=sqlsrv1\sql2012 Database=SQLDB Schema=dbo UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3 | [SQL_BTR] INT-Folder=..\intfiles_regions Server= sqlsrv1\sql2012 Database=SQLDB Schema=dbo1 UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3 | [SQL_BTR] INT-Folder=..\intfiles_regions Server= sqlsrv1\sql2012 Database=SQLDB Schema=dbo2 UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3

Options to migrate a multi-directory UniPaaS application


Option 4: Choose a unique table prefix (or postfix) for each directory

Option 4: Choose a unique table prefix (or postfix) for each directory

PROs

· This option is the easiest to implement.

· The same approach works in all backends.

CONs

· The table list can become very large.

· It is harder to assign separate user access control.

· Oracle's 30 character limit on identifiers can severely hinder this option.

mds.ini files in:

CORPORATE Directory | FLstore Directory | GAstore Directory
[SQL_BTR] INT-Folder=..\intfiles_corporate Server=sqlsrv1\sql2012 Database=SQLDB Schema=dbo UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3 Table-Prefix=COR | [SQL_BTR] INT-Folder=..\intfiles_regions Server= sqlsrv1\sql2012 Database=SQLDB Schema=dbo UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3 Table-Prefix=FL | [SQL_BTR] INT-Folder=..\intfiles_regions Server= sqlsrv1\sql2012 Database=SQLDB Schema=dbo UseTrustedConnection=no User=mydomain\me Password=4c8fe10aad3 Table-Prefix=GA

Options to migrate a multi-directory UniPaaS application


Final steps in the UniPaas migration process

Final steps in the UniPaas migration process

Creating the MigrateUniPaasData.ini files

A MigrateUniPaasData.ini file is also created in each data directory. Settings in the MigrageUniPaasData.ini file help to determine how definitions in the application source files are interpreted. A sample MigrateUniPaasData.ini file is installed in the <Programfiles>\Mertech Data Systems\DB Drivers\Btrieve\bin\ directory.

& For a description of settings see MigrateUniPaasData.ini File

MigrateUniPaasData.ini files in:

CORPORATE Directory | FLstore Directory | GAstore Directory
MagicIni = ..\app\magic.ini [LOGICAL NAMES] CurrentCust = CORPORATE [Temp Tables] 37 = Customers 42 = Orders 100 = Temp Table1 101 = Temp Table2 | MagicIni = ..\app\magic.ini [LOGICAL NAMES] CurrentCust = FLstore [Temp Tables] 5 = Insurance 8 = Vendors 100 = Temp Table1 101 = Temp Table2 | MagicIni = ..\app\magic.ini [LOGICAL NAMES] CurrentCust = GAstore [Temp Tables] 5 = Insurance 8 = Vendors 100 = Temp Table1 101 = Temp Table2

Creating the MigrationValidator_uniPaas.ini files

The MigrationValidator_uniPaas.ini file in each directory contains settings used during the validation. Settings should be the same as those in the MigrateUniPaasData.ini file.

& See also Migration Validator Tool for uniPaaS Applications

One more IMPORTANT consideration

A key factor to the success of the migration is the existence of ALL the data files to be migrated. While the migration utility can determine most of the file structure from the application XML files, the utility needs to examine the actual data file to get it right.

Since Magic creates files on the fly, there are often lots of data files that are missing from the project directories. To ensure all files are present, you might want to create a script that accesses each file, causing Magic to create an empty data file if the file does not exist.

Migrating and deploying your UniPaaS application

Follow these steps to migrate and deploy your application.

1. Run the _DoMigrate_.bat file to perform the migration. Examine the Migration.log file.

2. Run the_DoValidate_.bat file to confirm that the migration was successful. Examine the Validation.log file.

3. Copy the deployment directory to the production site.

The deployment directory structure from our example should look like this.

· The batch files (_DoMigrate.bat and _DoValidate.bat) are no longer needed.

· The migration ini files (MigrateUniPaasData.ini and MigrationValidator_uniPaas.ini) are no longer needed.

· The data files (.btr) are no longer needed.

· The INT files are in dedicated directories, one for corporate (intfiles_corporate) and one for regional stores (intfiles_regions). The driver uses these files at runtime to determine the structure of migrated tables.

· Each data directory has an mds.ini file that contains the location of the INT files, target server, database, schema, login information and (optional) table prefix. The drivers use the mds.ini files (along with global settings in the mds_global.ini file) at runtime.

NOTE: Alternately, instead of creating data directories to store the mds.ini files, you can create the mds.ini files in memory using the MdsSetSetting API.

4. Replace the Btrieve access dlls, wbtrv32.dll and/or w3btrv7.dll that currently reside in the PVSW\BIN directory with the Mertech drivers in the C:\Program Files\Mertech Data Systems\DB Drivers\Btrieve\deploy\ <subdirectory> directory.

NOTE: Save a copy of the original Pervasive dlls before you replace them. Many of the Pervasive tools require these dlls. For instance, if the table definitions need to be corrected, the original Pervasive dlls must be restored.

Migrating Magic uniPaaS Applications


What happens after your UniPaaS application is deployed?

What happens after your UniPaaS application is deployed?

When your application is loaded, the BTR2SQL driver initializes and reads global settings from the mds_global.ini file. These settings turn on/off tracing and set other performance and locking parameters that affect the entire application.

When your application accesses a table, uniPaaS issues a Btrieve command B_OPEN to open a file (for example FLstore\Orders.btr); the BTR2SQL driver:

1. Looks for the associated INT file (Orders_btr.int) in the FLstore directory.

2. The INT file is not there, so the driver looks for an mds.ini file.

3. The driver opens the mds.ini file (FLstore\mds.ini), which contains:

· The INT file location: INT-Folder = ..\intfiles_regions

· The target backend: Server = sqlsrv1\sql2012, Database=SQLDB, Schema=dbo

· Login information: User =mydomain\me, Password=4c8fe10aad3

· Table-Prefix =FL

NOTE: This example assumes you chose the table-prefix option to segregate the directories.

4. The driver reads the Orders_btr.INT file and uses the information to open the dbo.FL_Orders table in the sqlsrv1 database.

Related Topics

Migrating Magic uniPaaS Applications
Creating tables on the fly
Deleting tables
Creating a new regional store


Creating tables on the fly

Creating tables on the fly

If your application creates tables on the fly, the Mertech driver will automatically handle this without any need to modify your source code. When a B_CREATE command is issued, the Mertech driver reads the data definition from the corresponding INT file and creates the empty table on the server based on this definition.

& See also B_CREATE API

Related Topics

What happens after your UniPaaS application is deployed?


Deleting tables

Deleting tables

Magic programmers usually issue a DBDEL command to delete a file. The Magic runtime processes DBDEL by issuing a file delete command through the Windows operating system and not through the Btrieve API. This means that the Mertech drivers are not able to trap DBDEL calls.

Mertech created an SDK and exposed driver functionality including commands that allow Magic programmers to easily remove tables from the SQL backend. If your application deletes files on the fly through the OS, you will need to modify that code to use the B_DROP_FILE or B_TRUNCATE_FILE commands instead. These commands are implemented using the CallDLLS function, described at:

http://ftp.magicsoftware.com/www/help/MG9/expres_1/HDK3B9C3A80.htm

B_DROP_FILE

#define B_DROP_FILE (B_OPEN + 3000)

Drops the SQL table and removes the INT file. The XML definition remains in the application source files.

Parameters

[in] | posBlock | Optional - If the posBlock of an open file is passed, close it, drop the table and delete the INT file.
 | dataBuffer | N/A
 | dataLength | N/A
[in] | keyBuffer | Optional - If no posBlock is passed, pass the full or relative path and filename of the MicroKernel file in the key Buffer - just like a B_OPEN.
 | keyNumber | N/A

Example

CallDLLS ('w3btrv7.BTRCALL','2444A112',3000,0,0,0,Trim([table_name]), ASCIIChr(Len(Trim([table_name]))), ASCIIChr(0)) = 0

NOTE: To prevent an INT file from being deleted when a table is dropped, you can set the PERMANENT_INT token in the INT file to YES (the default setting is NO). This is useful if for example, you want to drop and then later recreate a file using the same structure. Refer to the BTR2SQL User’s Guide of additional information.

B_TRUNCATE_FILE

#define B_TRUNCATE_FILE (B_OPEN + 2000)

Deletes all the records in the file.

Parameters

[in] | posBlock | Optional - If the posBlock of an open file is passed, truncate the file and leave it open
 | dataBuffer | N/A
 | dataLength | N/A
[in] | keyBuffer | Optional - If no posBlock is passed, pass the full or relative path and filename of the MicroKernel file in the key Buffer - just like a B_OPEN
 | keyNumber | N/A

Example

CallDLLS ('w3btrv7.BTRCALL','2444A112',2000,0,0,0,Trim([table_name]), ASCIIChr(Len(Trim([table_name]))), ASCIIChr(0)) = 0

&
See also Additional Btrieve APIs

Related Topics

What happens after your UniPaaS application is deployed?


Creating a new regional store

Creating a new regional store

It is likely that your UniPaaS application will add new directories (regional stores) as time goes by. Take the following steps to do this.

Using a table-prefix

1. Create the directory for the new store (for example, NYstore).

2. Create an mds.ini file in that directory.

[SQL_BTR]

INT-Folder=..\intfiles_regions

Server= sqlsrv1\sql2012

Database=SQLDB

Schema=dbo

UseTrustedConnection=no

User=mydomain\me

Password=4c8fe10aad3

Table-Prefix=NY

3. Now, accessing the tables will cause uniPaaS to issue a Btrieve B_CREATE:

B_CREATE(NYstore\Customers)

B_CREATE(NYstore\Orders)

The driver reads the mds.ini file in the NYstore data directory and uses the INT_Folder token to locate the INT files. The driver uses the table template in the INT file to create the new tables with the NY_ prefix.

NOTE: In the example above, the mds.ini file is stored on disk in a separate directory for each store. Instead, you can use the MdsSetSetting API to create virtual mds.ini files. For example:

MdsSetSetting("C:\SampleMultiCompanyLayout\NYstore","INT_Folder", "..\intfiles_regions")

MdsSetSetting("C:\SampleMultiCompanyLayout\NYstore","Server", "sqlsrv1\sql2012")

MdsSetSetting("C:\SampleMultiCompanyLayout\NYstore","Database", SQLDB")

MdsSetSetting("C:\SampleMultiCompanyLayout\NYstore","Schema", "dbo")

MdsSetSetting("C:\SampleMultiCompanyLayout\NYstore","UseTrustedConnection", "no")

MdsSetSetting("C:\SampleMultiCompanyLayout\NYstore","User", "mydomain\me")

MdsSetSetting("C:\SampleMultiCompanyLayout\NYstore","Password", "0x4c8fe10aad3")

MdsSetSetting("C:\SampleMultiCompanyLayout\NYstore","Table-Prefix", "NY")

& See also Setting global and folder specific values programmatically

In a separate database

1. Create a new SQL database (for example, SQLDBNY). This can be done using SQL modeling tools or in your application by calling the BTRV/BTRCALL api B_SQL_EXECUTE (documented in the SDK) to execute a CREATE DATABASE command.

strcpy(query, "CREATE DATABASE SQLDBNY");

bStat= BTRCALL(B_SQL_EXECUTE,&cursor1,&query,&(dataLen=(BTI_WORD)strlen(query)),NULL,0,0);

& Examples to show how to use Embedded SQL to create and drop databases and users are provided in <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\sdk\Samples\MdsApis\C

2. Create the directory for the new store (for example, NYstore).

3. Create an mds.ini file in that directory.

[SQL_BTR]

INT-Folder=..\intfiles_regions

Server= sqlsrv1\sql2012

Database=SQLDBNY

Schema=dbo

UseTrustedConnection=no

User=mydomain\me

Password=4c8fe10aad3

4. Now, accessing the tables will cause uniPaaS to issue a Btrieve Create the new tables using B_CREATE:

B_CREATE(NYstore\Customers)

B_CREATE(NYstore\Orders)

The driver reads the mds.ini file in the NYstore data directory and uses the INT_Folder token to locate the INT files. The driver uses the table template in the INT file to create the new tables in the SQLDBNY database.

Related Topics

What happens after your UniPaaS application is deployed?


Migration Validator Tool for uniPaaS Applications

Migration Validator Tool for uniPaaS Applications

Run MigrationValidator_uniPaas after data is migrated to verify that everything was correctly transferred. MigrationValidator_uniPaas checks that the basic table structure and indexes match and then reads all the records from Btrieve and SQL and performs a memory compare of the data buffer.

If the compare fails, MigrationValidator_uniPaas does a smart compare of each field (for instance, floats are notorious for being different after going through the various layers of manipulation, so an epsilon comparison is used).

A message displays in the output if any mismatch is found.

Command-line Syntax

Select Mertech’s ISDBC Drivers for Btrieve | Migration Validator for UniPaaS from the Windows Start menu. Since this is a command-line utility, a Command Prompt is automatically opened

Run the Migration Validator for UniPaaS script by typing the following at the command prompt:

MigrationValidator_uniPaas [configuration parameters] source

Configuration parameters are settings used during the verification.

Configuration Parameter | Description
-dll <Mertech migration dll> | Location and filename of the Mertech migration dll to use (sql_btr.dll, mys_btr.dll, ora_btr.dll, pgs_btr.dll). Specifies the path and filename of the dll used to access the SQL backend. If it is specified, give the full path to the dll; otherwise, sql_btr.dll is loaded from the system PATH.
-datalocation <data file folder> | Folder where data files are located. The datalocation parameter is required to indicate the path to the INT files.
-magicini <magic.ini location> | Folder and filename for magic.ini file.
-server <servername> | Name of the SQL server and optional port number where the server is listening for communication.
-database <dbname> | Name of the target database.
-schema <schema> | Name of target database schema. This name is normally the same as database owner.
-user <username> | User name for server login (empty for trusted connection).
-password <password> | Password for server login.
-table <tablename> | By default, all tables are tested. When you include the -table parameter, you can specify one or more tables to narrow the tests. Use semicolons to separate the list, or include the ‑table parameter multiple times.
-trace <trace filename> | Path and filename to send trace output
-level <trace level> | Path and filename for the trace output.
-nopause | Do not wait for a key press at end.
-debug | Send extra trace information to the screen during the migration operation.
-silent | Do not send status information to the screen (only errors).
-maxRows <n> | Maximum number of rows to test for each table. Allows you to shorten the testing time by reducing the number of rows checked. If your table has 10 million rows, it is likely that after the first million or so, the rest of the rows will validate the same way
/varOnly | Only test tables with variable length records

Source is the name of source folder location of datasources.xml and models.xml, including the file name extension and any necessary path information.

[drive:] <source folder>

Surround names with quotes if needed (space in the name).

Example:

MigrationValidator_uniPaas c:\mydata\demodata -dll "C:\Program Files\Mertech Data Systems\DB Drivers\Btrieve\bin\ora_btr.dll" -table person;billing;class -maxrows 2000

NOTE: Configuration parameters can also be provided in the MigrationValidator_unipaas.ini file. A sample file is included in the C:\Program Files\Mertech Data Systems\DB Drivers\Btrieve\bin directory. Settings in the MigrationValidator_unipaas.ini file should be the same as settings used during migration in the MigrateUniPaasData.ini file.

Migrating Magic uniPaaS Applications


Command-line Migration Scenarios

Command-line Migration Scenarios

An initial migration is the simplest scenario and is performed at least once for the development environment and for customers who have an existing installation to be migrated. Simply run a full (default) migration of the entire database with the appropriate paths to dlls and data folders, name of the target server, database, and schema, and other migration options specified.

First-time migration

First-time migration with either of the command-line tools is accomplished in a matter of hours without the need to recompile or rewrite code. You can run a full migration:

MdsMigrateTable AllTables Full /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll

MigrateUniPaasData Full –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

Or, you can break up the procedure to allow the database administrator more control over the migration process.

Separating the Steps with MdsMigrateTable

1. Create the tables on the server:

MdsMigrateTable AllTables CreateTable /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll

Or, you can export an SQL script to be run on the server directly. This method is usually preferred by database administrators.

MdsMigrateTable AllTables ScriptOnly /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll

2. Create the intermediate (INT) file for each Btrieve file that is to be converted to the target database. The intermediate file contains information used at runtime by the Mertech database driver.

MdsMigrateTable AllTables IntOnly /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll

3. Load the data on the server:

MdsMigrateTable AllTables CopyData /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll

Or, another process can be used to load an initial set of data for deployment.

4. Save a copy of the INT files and take a snapshot of the database. This becomes version 1 of your deployment and will be used in the future for updates.

Separating the steps with MigrateUniPaasData

1. Create the tables on the server:

MigrateUniPaasData CreateTable –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

Or, you can export an SQL script to be run on the server directly. This method is usually preferred by database administrators.

MigrateUniPaasData ScriptOnly –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

2. Create the intermediate (INT) file for each Btrieve file that is to be converted to the target database. The intermediate file contains information used at runtime by the Mertech database driver.

MigrateUniPaasData INTOnly –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

3. Load the data on the server:

MigrateUniPaasData CopyData –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

Or, another process can be used to load an initial set of data for deployment.

4. Save a copy of the INT files and take a snapshot of the database. This becomes version 1 of your deployment and will be used in the future for updates.

Schema update

A common scenario is for a developer to make changes to the database on his own machine and commit the changes to the SQL server in the development environment. The problem is how to deploy the changes to customer sites so no data is lost and everything remains in sync. The developer must send the following information to a customer site before updates can go live:

· His new application

· Updated INT file(s)

· SQL script(s) for restructuring the database

Schema Update with MdsMigrateTable

1. Migrate the updated schema to a v2 database in the development environment.

MdsMigrateTable AllTables CreateTable /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll /config c:\myapp\MyApp.xml

2. Use an SQL Diff tool to compare the v1 and v2 databases and to create a script that can update the v1 database to the v2 database (for instance, to add new tables, columns, indexes, etc.)

3. Generate new INT files.

MdsMigrateTable AllTables IntOnly /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll

4. Deploy the new application and updated INT files to the customer site and run the SQL script to update the server.

Schema Update with MigrateUniPaasData

1. Migrate the updated schema to a v2 database in the development environment.

MigrateUniPaasData CreateTable –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

2. Use an SQL Diff tool to compare the v1 and v2 databases and to create a script which can update the v1 database to the v2 database (for instance, to add new tables, columns, indexes, etc.)

3. Generate new INT files.

MigrateUniPaasData INTOnly –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

4. Deploy the updated INT files to the customer site and run the SQL script to update the server.

How the command-line tools can work together

You can use the MigrateUniPaasData utility to generate an XML script to pass to MdsMigrateTable, which can do the work later. Separating the steps (schema extraction and migration) allows you to tweak the configuration or perform the data migration without access to the uniPaaS source. For example:

1. Use MigrateUniPaasData to export an XML definition of the database.

MigrateUniPaasData XML MyApp.xml -datalocation c:\myapp\data -magicini c:\unipaas\users\me\magic.ini

2. Modify the XML file if you want (to perhaps trim down the list of tables in the file).

3. Use MdsMigrateTable tool to continue the migration, creating the tables and INT files and copying the data.

MdsMigrateTable AllTables CreateTable /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll /config MyApp.xml

4. Use MdsMigrateTable tool to copy the data.

MdsMigrateTable AllConfig CopyData /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll /config MyApp.xml

MigrateUniPaasData |  | MDSMigrateTable
XML Source | Þ XML scriptÞ (edit script) | Create tables Create indexes Create INTs Copy data


Migrating Files when DDFs are not Available

Migrating Files when DDFs are not Available

MdsMigrateFile is a command-line tool that is provided as a last-resort proof of concept approach to see that an application can run against SQL. MdsMigrateFile is useful in a situation where no schema definition is available for a Btrieve file. MdsMigrateFile runs a B_STAT command on the file to determine the file statistics and index structure. MdsMigrateFile then issues a B_CREATE against the SQL backend, defining fields based on the reported index structure. Once the table is created, MdsMigrateFile copies the records. MdsMigrateFile produces tables that work with the Mertech drivers to make the application functional, but typically the data is not usable in SQL by other applications or tools. MdsMigrateFile is installed in the <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\bin directory.

MdsMigrateFile Command-line Tool


MdsMigrateFile Command-line Tool

MdsMigrateFile Command-line Tool

Command-line syntax

Run MdsMigrateFile by typing the following at a command prompt:

MdsMigrateFile.exe BtrieveFileName [configuration parameters]

NOTE: The BtrieveFileName can include wildcards.

Configuration Parameter | Description
/owner <ownername> | Owner name associated with the Btrieve file. Multiple /owner <ownername>parameters can be provided and MdsMigrateFile will cycle through the names attempting to find the correct one.
/server <server name> | Destination SQL server and optional port number where the server is listening for communication. Always use the same SQL server version as the end-user. For instance, migrating to MS2008 utilizes different data types than MS2005.
/database <dbname> | Destination database.
/schema <schema> | Destination database schema.
/user <username> | User name for server login (empty for trusted connection).
/password <password> | Password for server login.
/dll <Mertech migration dll> | The path and filename of the Mertech migration dll (sql_btr.dll, mys_btr.dll, ora_btr.dll, or pgs_btr.dll) used to access the SQL backend. If the full path is not specified, the dll is loaded from the system PATH. Defaults to sql_btr.dll if no dll is provided.
/force-int-usage | When this token is included, MdsMigrateFile uses the field definitions in the INT file (rather than those returned by B_STAT) to create the SQL table. If the BtrieveFileName is Billing.MKD, the expected INT file is Billing_MKD.INT.
/validate-data | When this token is included, the migrated data is read back and compared to the original file.
/trace <trace filename> | Path and filename for the trace output.
/level <trace level> | Level of tracing (0, 1, 2, 3, 4, 5, or 6). Trace level 2 is usually sufficient for user debugging.

For example:

"C:\Program Files (x86)\Mertech Data Systems\DB Drivers\Btrieve\bin\MdsMigrateFile.exe" BILLING.MKD /server DEV-PC\SQLEXPRESS /database TestDB /trace mytrace /level 2

NOTE: If the server, database, and/or login credentials are not provided, the mds.ini file, registry settings, or Login dialog box provide these details.

NOTE: During the migration, INT files are created in the same location as the Btrieve file unless they are redirected to another folder by the mds.ini file.

Comparison to conversion using the GUI Migration Utility

Below are two SQL table structures for the Pervasive Demodata file Billing.MKD. The structure on the left was created using the GUI Migration Tool (MdsMigrateTable can also be used). The structure on the right was created using MdsMigrateFile.

GUI Migration Utility | MdsMigrateFile

Notice the difference in the columns. When the GUI Migration Utility is used, field names, field sizes, and data types match the information provided in the DDFs. When MdsMigrateFile is used, columns are strictly based on the index structure returned by the B_STAT command. Fields that are not indexed are combined into FILLER fields.

Below are the results of a query to retrieve records from the Billing file that was created using the GUI Migration Utility.

Below are the results of a query to retrieve records from the Billing_MKD file that was created using MdsMigrateFile.

Improving the conversion process

MdsMigrateFile is not the recommended method for migrating files.

If you know the file structure, you can greatly improve the conversion process by:

· Creating the DDFs and then using the BTR2SQL GUI Migration Utility or MdsMigrateTable to migrate the file.

or

· Copying an INT file for a similar file and renaming it to match your filename (Billing_MKD.INT in this example). Then, running MdsMigrateTable using the /force-int-usage token. For example:

"C:\Program Files (x86)\Mertech Data Systems\DB Drivers\Btrieve\bin\MdsMigrateFile.exe" BILLING.MKD /server DEV-PC\SQLEXPRESS /database TestDB /force-int-usage

NOTE: Contact Mertech Technical Support for assistance with this conversion process.

Migrating Files when DDFs are not Available


BTR2SQL SDK

BTR2SQL SDK

Mertech BTR2SQL comes with a software development kit (SDK) that gives you access to extended Btrieve functionality. The SDK contains a runtime dll and migration dll for each backend (MS SQL, MySQL, Oracle or PostgreSQL). The SDK also contains header files to define the constants and structures required for advanced programming and it contains detailed documentation and examples.

The BTR2SQL SDK is installed in the <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\sdk directory.

The SDK documentation can be viewed in your browser by selecting the SDK shortcut in the Mertech’s ISDBC Drivers for Btrieve group of the Windows Start menu (or by opening index.html in the<Program Files>\Mertech Data Systems\DB Drivers\Btrieve\sdk\docs directory).

The SDK documentation contains:

Tab | Provides
Main Page | Overview of the SDK.
Modules | Hyperlinks to information about the types, definitions, constants, structures, and functions (grouped by category) in the SDK.
Classes: Class List | Classes, structures, unions and interfaces with brief descriptions.
Classes: Class Members | An alphabetical list of all class members with links to the classes they belong to and where they are defined.
Files | A description of each header file in the sdk/include subdirectory. Note: Other header files are available in the sdk/samples subdirectory.
Examples | Example code that can be viewed in the browser. Note: Other code samples can be found in the sdk/samples directory.

Additional documentation for the SDK is provided in this chapter.

Related Topics

Runtime dll with extended APIs
Migration dll
Supported Btrieve API Functions
Additional MDS status codes
Extended DDF Data Types
B_CREATE API
SQL Login and Direct Query Execution
Fast Inserts
Driver Trace Control
Schema Functions (DTI Function Support)
Migration Functions
Additional Btrieve APIs
Accessing Both Btrieve and SQL Backends


Runtime dll with extended APIs

Runtime dll with extended APIs

During the migration process, you replace the Btrieve runtime dll (wbtrv32.dll or later version called w3btrv7.dll) with the Mertech runtime dll of the same name. The Mertech runtime dll codes the standard Btrieve operations (for example, B_OPEN) so that they will work with the selected SQL backend. This means your application can access the SQL backend without any changes to your existing code or BTRCALLs.

In addition to the standard Btrieve operations, the Mertech runtime dll includes extended APIs to:

· Create temporary files or files based on existing templates

· Use the Embedded SQL APIs to run SQL queries directly to the SQL backend

· Call the Fast Insert APIs that use native SQL backend functionality to get data into a table as fast as possible

· Start, stop and control trace output

· Add, delete, rename, or modify existing tables

· Determine whether the Btrieve or SQL backend is in use, determine the version of the Mertech driver that is installed, get and set runtime settings, encrypt passwords

How do I use the extended APIs?

The runtime dll is dynamically linked to your application when the application is loaded, so the extended APIs are automatically available and accessible just like the standard Btrieve operations through the BTRV/BTRCALL function interface. Simply use the provided constants (or the operation code) and structures defined in the mds_btrieve header. For example:

#include <mds_btrieve.h>

MDS_SQL_LOGIN loginInfo = {"WIN-P6C\\SQLEXPRESS", "btrtest", "mertech", "btrtest", ""};

status = BTRCALL(B_SQL_LOGIN, NULL, &loginInfo, &(dataLen=sizeof(loginInfo)), NULL, 0, 0);

Direct call interfaces are also available for some of the extended APIs. For example you can encrypt a password by calling:

#include <mds_btrieve.h>

status = BTRCALL(B_ENCRYPT_PASSWORD, NULL databuf, &datalen, keybuf, 0, 0);

Or, you can call MdsEncryptPassword directly:

#include <mds_btrieve.h>

status = MdsEncryptPassword(password, encpw, encpwlen);

BTR2SQL SDK


Migration dll

Migration dll

The migration dll (sql_btr.dll, mys_btr.dll, ora_btr.dll, or pgs_btr.dll) includes all the standard and extended Btrieve functions plus functions to write your own migration interface.

How do I use the migration APIs?

In order to use the migration APIs, the migration dll must be dynamically linked at runtime using LoadLibrary and the function addresses must be obtained using GetProcAddress. This is done for you in the BtrieveFile class, which is a simple wrapper class capable of loading any dll. The BtrieveFile class is defined in BtrieveFile.h. An MS SQL code extract is shown below. For additional examples, see the samples\migration subdirectory.

#include <BtrieveFile.h>

void useMdsMigrateOneTable()

{

BtrieveFile* pBF = new BtrieveFile("sql_btr.dll");

MDSRESULT status;

static const char ddfLocation[] = "c:\pvsw\demodata\";

static const char tableName[] = "billing";

status = pBF->MdsMigrateOneTable(ddfLocation, tableName, MDS_MIG_FULL, NULL);

} //useMigrateOneTable()

BTR2SQL SDK


Supported Btrieve API Functions

Supported Btrieve API Functions

BTR2SQL fully supports all Btrieve API functions with the exception of the functions listed below, which are not required in a relational database.

Functions that are not supported

Constant | Opcode | Explanation
B_EXTEND | 16 | Split an existing Btrieve data file over an additional drive. Operation is ignored, status 0 is returned.
B_SET_OWNER | 29 | Assign owner name and optionally encrypt Btrieve data file. Operation is ignored, status 0 is returned. Owner names can be implemented through SQL security by porting a file to a different login.
B_CLEAR_OWNER | 30 | Clear owner name for a Btrieve data file. Operation is ignored, status 0 is returned.
B_CONTINUOUS | 42 | Place the specified file in or remove the file from continuous operation mode, for use in system backups. Operation is ignored, status 0 is returned.
B_LOGIN | 78 | Login to a Btrieve database. Operation is not supported, returns B_INVALID_FUNCTION(1). The BTR2SQL sdk has a new API, B_SQL_LOGIN (10015), to login to an SQL server.
NOWRITE_WAIT | 500 | This operation bias code is ignored.

BTR2SQL SDK


Additional MDS status codes

Additional MDS status codes

MDS_errGeneric = 18000 ///Unknown

MDS_errNotLoggedIn = 18001

MDS_errTableNotFound = 18002

MDS_errInvalidTableName = 18003

MDS_errInvalidColumnNumber = 18004

MDS_errInvalidColumnName = 18005

MDS_errInvalidDataType = 18006

MDS_errDuplicateColumnName = 18007

MDS_errInvalidDataSize = 18008

MDS_errInvalidColumnOrder = 18009

MDS_errInvalidIndexName = 18010

MDS_errStringTooShort = 18011

MDS_errDirectoryError = 18012

MDS_errDuplicateTable = 18013

MDS_errISRNotSupported = 18014

MDS_errInvalidDatabaseName = 18015

MDS_errXmlMissingElement = 18016 ///< Required Element missing from XML

MDS_errXmlMissingAttribute = 18017 ///< Required Attribute missing from XML

MDS_errXmlIncorrectDriver = 18018 ///< XML indicated an incorrect driver target

MDS_errXmlInvalidValue = 18019 ///< Invalid value for an XML attribute

BTR2SQL SDK


Extended DDF Data Types

Extended DDF Data Types

Btrieve natively supports about 28 basic data types. fieldext.ddf was introduced to augment this list and support a number of other interesting types. BTR2SQL reads this information and makes a wiser choice for the type used in SQL. For instance, in addition to the standard B_DATE type, there are several Julian based date formats and dates in various string forms. All of these are migrated to a real DATE type in SQL instead of a generic string or integer as defined in field.ddf. Pervasive's pedtconv.dll is utilized to do the data conversions. This dll can be found in the Pervasive SDK downloaded from their website.

& See also SDK documentation: Modules tab > Btrieve Types

BTR2SQL SDK


B_CREATE API

B_CREATE API

The B_CREATE syntax was expanded so your application can provide the name of a pre-existing INT file to use as a pattern when creating a new table on the SQL server.

Background

The original B_CREATE syntax is:

status = BTRCALL(B_CREATE, NULL dataBuffer, &dataLength, keyBuffer, 0, 0);

Where keyBuffer is <filename>

The only information available to the driver for creating a new file was the filename and indexes. The driver had to examine the indexes to determine there was a field of a particular data type and size at some specific offset in the record. However, the driver could not determine the names of all of the fields, so you might end up with a table such as:

CREATE TABLE cust1_dat (mds_field1 int, mds_filler1 binary(10), mds_field2 varchar(50), mds_filler2 binary(500))

Auto template option

B_CREATE was later expanded to make use of an existing int file (filename.int).

When an application issues a B_CREATE, if an INT file already exists for the file, the driver compares its contents with the index definitions in the new create structure. If they match, the driver uses the definition in the INT file to create the table on the server. If the INT file does not look compatible, it is ignored and you are stuck with the MDS_FIELDx and MDS_FILLERx field names.

Several flags control the way the auto template option works. The ‘flag’ becomes the keyNumber for the operation.

Flag | Description
B_CM_OVERWRITE_IGNORE_INT_FILE= -6 | Overwrite the existing table; ignoring the definition in the existing INT file
B_CM_OVERWRITE_FORCE_INT_FILE = -99 | Overwrite the existing table; force usage of the existing INT file (ignore FILESPEC in databuffer)
B_CM_NOOVERWRITE_FORCE_INT_FILE= -100 | Do not overwrite the existing table; force usage of existing INT file (ignore FILESPEC in dataBuffer)

The B_CREATE syntax using the auto template option is:

status = BTRCALL(B_CREATE, NULL dataBuffer, &dataLength, keyBuffer, keyNumber, 0);

Where keyNumber is one of the above flags.

INT File template option

The latest enhancement to B_CREATE expands the contents of keyBuffer to include the new filename (as before), the template filename, and the table name to be created on the server. Now you can specify an INT file for an existing file that has a pattern you want to use to create the new file. For example you can create bar.btr with the same structure (but different table name) as foo.btr.

The new B_CREATE syntax is:

status = BTRCALL(B_CREATE, NULL dataBuffer, &dataLength, keyBuffer, keyNumber, 0);

Where keyNumber dataBufferis one of the above flags and keyBuffer is:

<filename> | <template filename> | <table name>

If a pipe character ( ‘|’ ) appears in the keyBuffer, <template filename> is assumed to be the next (required) string. The second pipe and <table name> are optional. If not included, the driver defaults to filename_ext for the table name.

A space on either side of the pipe is ignored. Quotes around any sub-string are removed. Quotes around the entire string are not supported. Quotes on the interior of any sub-string causes an invalid filename error.

All forms of filenames are supported for both the filename and template parts of the string. This includes just a filename (relative to the “current directory”), current directory relative paths (.pathfilename or ..pathfilename), root relative paths (pathfilename), full paths (c:datafilename) and UNC paths (serversharepathfilename).

Notes

· An INI file in the folder where the template filename is specified is read. So it is possible for the template INT file to be in a different folder than what is directly specified for the template file.

· If the template filename does not exist, a status 12 is returned.

· A new INT file is created for the new filename with contents that match the template INT but using the new table name.

· If the new filename INT exists when the create call is made, it is ignored/erased and the template INT is used just as if the new file INT had not been there.

· If the new filename INT exists and is marked as Permanent, a FileAlreadyExists error is returned.

· The template INT should be marked as Permanent.

Example 1

Your Magic (or non-Magic) application has a DB table “custmain.mcf” with table name “Customers Main”. Custmain.mcf exists in C:MyAppData and C:MyAppDatamds.ini has an entry: INT-Folder=..intfiles

During migration to SQL, table “Customers Main” and C:MyAppIntFilescustmain_mcf.int are created.

The application later wants to access a new customer table with the same definition as “Customers Main”. C:MyAppData is the current folder at runtime.

1. The application opens ‘cust1.dat | custmain.mcf | Cust – John Smith’.

2. C:MyAppIntFilescust1_dat.int does not exist so the driver returns status 12.

3. Magic then issues a B_CREATE passing the same path string as before.

4. The driver reads C:MyAppIntFilescustmain_mcf.int into the usual internal file classes and updates the table name to “Cust – John Smith” and the physical name to “cust1.dat”.

5. The driver then issues a call to Create Table to on SQL backend, creates C:MyAppIntFilescust1_dat.int, and returns success.

6. Magic reissues the open and continues successfully.

Example 2

You can also use this new B_CREATE option in a multi-xxx (company, customer, site, etc.) environment where you utilize the same schema in multiple folders (one for each customer for instance). Scenario

c:\

myapp

data

intfiles_mssql(all files marked as Permanent and OS Read/Only attribute set)

people_btr.int

jobs_btr.int

salaries_btr.int

intfiles_ora(all files marked as Permanent and OS Read/Only attribute set)

people_btr.int

jobs_btr.int

salaries_btr.int

cust1

mds.ini(says intfiles=..intfiles_mssql and ..intfiles_ora, prefix=cust1_)

cust2

mds.ini(says intfiles=..intfiles_mssql and ..intfiles_ora, prefix=cust2_)

cust3

mds.ini(says intfiles=..intfiles_mssql and ..intfiles_ora, prefix=cust3_)

When either B_OPEN or B_CREATE references one of the customer files (for example, “c:myappdatacust1people.btr”), the driver looks at the INI file to determine where the INT files are located and then opens the INT file for the required server type (for example, “c:myappdataintfiles_mssqlpeople_btr.int”).

So, rather than having thousands of INT files in each of the customer folders (all having the same contents), the INI file directs the driver to a common location for the INTs.

Creating temporary files

When the EOY Report is generated, various data is collected into a temporary file. The definition for this table, ReportTemplate.mkd, is in the DDFs. Use the Migration Utility to generate the corresponding INT file, ReportTemplate_mkd.int.

When the application executes a B_CREATE(“eoyreport_2014090901011545.mkd | ReportTemplate.mkd | EOYReportData_2014090901011545″), the Mertech driver reads ReportTemplate_mkd.int, creates a new table EOYReportData_2014090901011545 and generates a new INT file, eoyreport_2014090901011545_mkd.int.

Later, when B_DROP_TABLE is called, the INT file and the table on the SQL backend are removed.

Avoid OS calls

Some applications manipulate the data files directly with OS calls by copying an existing data file and deleting the file when done. These direct operations are not supported by the drivers. Instead, you must fully rely on the B_CREATE API to create the temporary table and B_DROP_TABLE to remove the table when it is no longer needed.

& See also Additional Btrieve APIs

Multi-user scenarios

Extra precaution must be taken to avoid multiple processes from using the same temporary file simultaneously. With Btrieve, the temp file could be created locally and thus each client could have a dedicated file. Now that the table is created in a shared SQL resource, multiple clients have access to the same table.

Several options are available to avoid this problem.

· Assign a globally unique filename (like a timestamp or guid for the filename) each time a temp table is created. This option does not use an INT file as the template for creating the table.

· Create the INT file for the temporary file. Then, use the extended B_ CREATE syntax to specify a new filename each time a temporary table is created (see examples under the B_CREATE API).

· Create the INT file for the temporary file. Assign each installation a dedicated prefix (e.g. Region1, Region2, Region3) or postfix. Make a copy of the temp file for each installation and add the assigned prefix (e.g., INT) or postfix to the file. Then, restrict each region to using the assigned filename (e.g., Region1_Temp) in B_CREATE calls (see examples under the B_CREATE API).

NOTE: In all cases, make sure to use the B_DROP_TABLE API to delete stale temp tables.

Access rights

SQL administrators can be very strict when assigning access rights to users. For example, they may insist that users do not have Create Table permission. Without Create Table permission, users cannot create temporary files on the server.

One solution to this problem is to assign a different login for temporary tables. The main application data can be accessed under a login that has Read/Write access. The temporary data can be accessed under a login that does not have access to the main data but does have Create Table permission.

The easiest way to assign a different login for temporary tables is to place the temporary files in a separate directory and setup the mds.ini file in that folder to use the assigned login.

Main Data Files Directory: C:\MyApp\DATA

mds.ini (UseTrustedConnection=no, User=mydomain\me, Password=b7fba9c0e1b0f217)

MARKETING.MKD

MARKETING_MKD.INT

PURCHASING.MKD

PURCHASING_MKD.INT

Temporary Files Directory: C:\MyApp\TEMP

mds.ini (UseTrustedConnection=no, User=mydomain\temp, Password=9dc42401d47a3fd0)

TEMP1.INT

TEMP2.INT

TEMP3.INT

NOTE: A user name and password stored in an INT file override settings in the mds.ini file.

BTR2SQL SDK


SQL Login and Direct Query Execution

SQL Login and Direct Query Execution

The Embedded SQL API can be used to run SQL Queries directly to the SQL backend. The advantage of using the Embedded SQL API together with the regular BTRCALL API instead of technologies such as ADO is that no new connection to the server is opened. This also allows for the nesting of embedded SQL commands together with standard BTRCALL functions in the same transaction.

All string data should be passed and will be returned as null terminated strings.

& See also SDK documentation: Modules tab > SQL Login and Direct Query Execution

B_SQL_MAX_OPEN_CURSOR (10000)

Retrieves or sets the maximum number of open cursors in use by the driver. A higher number of cursors allow the driver to cache a lot of queries into open tables, but leads to higher resource usage on the server.

B_SQL_OPEN_CURSOR (10001)

Opens a new cursor that is used to execute commands. The position block parameter contains a handle to the cursor.

B_SQL_CLOSE_CURSOR (10002)

Closes a previously opened cursor.

B_SQL_PREPARE (10003)

Prepare SQL Statement for execution.

B_SQL_EXECUTE (10004)

Execute a (previously prepared) SQL statement.

B_SQL_FETCH (10005)

Fetch the next row from a previously executed query. If there are no more rows, then the return value is zero.

B_SQL_BIND (10006)

Binding parameters are used in conjunction with other queries with parameters (queries such as ‘SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = ?’). The query is prepared once and can then be executed multiple times by repeatedly rebinding (by calling B_SQL_BIND) and re-executing it (by calling B_SQL_EXECUTE). If there are multiple parameters in the query, then call B_SQL_BIND once for each parameter in order before calling B_SQL_EXECUTE. This process can then be repeated again, with calls to B_SQL_BIND, followed by a new call to B_SQL_EXECUTE).

B_SQL_GET_RET_COLUMNS (10007)

Returns (as the return code) the number of columns in the most recently executed query.

B_SQL_GET_RET_ROWS (10008)

Get number of rows returned from the previously executed SQL statement. In certain cases, the number of rows cannot be determined in advance, whereby the return value is zero.

B_SQL_GET_COL_NAME_SIZE (10009)

Retrieves the name and size of a given column in a result set from a previously executed query. The name is returned in the dataBuffer and the size is the return value of the function.

B_SQL_GET_COLUMN (10010)

Returns the data in a column in the current row. All fields are returned as strings.

B_SQL_START_TRAN (10011)

Starts a transaction in the database.

B_SQL_COMMIT (10012)

Commits a previously started transaction.

B_SQL_ROLLBACK (10013)

Aborts a previously started transaction, rolling back any changes done since the start of the transaction.

B_SQL_GET_ERROR (10014)

Returns the native SQL error for the last failed operation.

B_SQL_LOGIN (10015)

Programmatically login to an SQL Server to suppress the popup login dialog box. The format of the login structure is:

struct MDS_SQL_LOGIN
{
char server[30];
char username[30];
char password[30];
char database[30];
char schema[30]; /* only used by B_SQL_GET_LOGIN */
}

If the UserName and Password members are zero-length strings, trusted connections will be used in MS SQL Server.

B_SQL_LOGOUT (10016)

Programmatically logs out from an SQL Server. If no server name is provided, then a logoff will be sent to all of the servers to which the driver is currently connected.

B_SQL_GET_LOGIN (10017)

Retrieve the login information for the file identified.

For the given file, return the Server, Database name, User name and password used to login with. This information could have originally come from the .INT, .INI, save information in the registry, or from the Login dialog box.

B_SQL_ATTACH (10018)

Attach a database to a current SQL Server instance.

B_SQL_DETACH (10019)

Detach a database from a current SQL Server instance.

B_SQL_LDB_CREATE_INSTANCE (10020)

Create a new LocalDB instance.

B_SQL_LDB_DELETE_INSTANCE (10021)

Remove a LocalDB instance.

B_SQL_LDB_FORMAT_MESSAGE (10022)

Return the localized description for a LocalDB error. Use this function to retrieve the error details when a B_SQL_LDB_ call fails

B_SQL_LDB_GET_INSTANCE_INFO (10023)

Get information for a LocalDB instance, such as whether it exists, version information, whether it is running, and so on.

B_SQL_LDB_GET_INSTANCES (10024)

Return all the LocalDB instances with a specified version.

B_SQL_LDB_GET_VERSION_INFO (10025)

Return information for a specified LocalDB version.

B_SQL_LDB_GET_VERSIONS (10026)

Return all LocalDB versions available on a computer.

B_SQL_LDB_SHARE_INSTANCE (10027)

Share a specified LocalDB instance.

B_SQL_LDB_START_INSTANCE (10028)

Start a specified LocalDB instance.

B_SQL_LDB_START_TRACING (10029)

Enable API tracing for a user.

B_SQL_LDB_STOP_INSTANCE (10030)

Stop a specified LocalDB instance from running.

B_SQL_LDB_STOP_TRACING (10031)

Disable API tracing for a user.

B_SQL_LDB_UNSHARE_INSTANCE (10032)

Stop sharing a specified LocalDB instance.

BTR2SQL SDK


Fast Inserts

Fast Inserts

The Fast Insert APIs allow an application to use native SQL backend functionality to get data into a table as fast as possible. They also allow a full 32-bit data buffer to support variable length records (normally containing Note or BLOB data) which usually require Chunk operations after the B_INSERT.

& See also SDK documentation: Modules tab > Fast Inserts

Inserting variable length data

If the table has variable length records then there are three options for delivering the variable portion:

1) The entire record is in memory in one structure (no matter the size).

Pass the entire structure to the B_FAST_INSERT with appropriate dataLength. Set the keyBuffer to NULL. This is the fastest method.

2) Variable data still needs to be read and the length is known.

Pass a pointer to a callback routine in callBack and length of the “BLOB” in chunkDataLen.

3) Variable data is not in memory; the length is not yet known.

Pass a 0 in the chunkDataLen value and allow the chunk callback to dynamically read the data during the Fast Insert.

If the application does not want to submit all variable length data together with the fixed portion in B_FAST_INSERT (option 1), pass an MDS_FAST_INSERT structure in the key buffer, and set the key buffer length to the size of the structure. The values of the structure are shown in options 2 and 3.

The callback function is repeatedly called to allow the application to fill up the variable data portion of the record. The function should return the number of bytes of variable data it has copied into the “data” parameter each time, or zero to indicate completion.

B_BEGIN_FAST_INSERT (5001)

Begin Fast Insert Mode.

Structures:

Specify the bulk copy batch parameters in the MDS_FAST_INSERT_MSSQL structure.

NOTE: BATCHSIZE takes precedence over ROWS_PER_BATCH or KILOBYTES_PER_BATCH

struct MDS_FAST_INSERT_MSSQL
{
long batchSize; // Number of rows per transaction
long kbytesPerBatch;// Kilobytes Sent per Batch
long rowsPerBatch; // Rows Sent per Batch
long indexOrder; // Btrieve index number data is ordered by
bool lockTable; // Table will be locked with TABLOCK if True
};
const long NoIndexOrder = -1;

NOTE: Only FastInserts operations should be performed until the EndFastInsert is complete. Regular B_Insert operations may produce unpredictable results.

B_FAST_INSERT (5002)

Insert a record in Bulk / Fast mode.

Structures:

typedef void (__stdcall *MDS_CHUNKCALLBACK)
(CHAR** data, LONG* size);

struct MDS_FAST_INSERT
{
MDS_CHUNKCALLBACK callBack; // Callback function for retrieving variable length portion of record (chunks)
long chunkDataLen; // Known Length of variable portion (chunk)
};

B_END_FAST_INSERT (5003)

Stop Fast Insert mode and commit new data to the database.

BTR2SQL SDK


Driver Trace Control

Driver Trace Control

Several functions are available to control the trace output and allow the application to add information to the trace.

& See also SDK documentation: Modules tab > Driver Trace Control

B_TRACE_INIT (5017)

Enable tracing and define the filename and level of information.

B_TRACE_GET_SETTINGS (5018)

Retrieve current settings of trace file.

B_TRACE_START (5019)

Start tracing (after perhaps pausing it for a while).

B_TRACE_STOP (5020)

Pause the trace output.

B_TRACE_MSG (5042)

Send a message or comment to the trace file.

BTR2SQL SDK


Schema Functions (DTI Function Support)

Schema Functions (DTI Function Support)

Sometimes an application needs to add new tables to the database or modify existing tables or other changes to the schema. Pervasive.SQL provided the Distributed Tuning Interface (DTI) for this purpose. BTR2SQL introduces similar APIs. All are found in mds_btrieve.h.

& See also SDK documentation: Modules tab > Schema Functions

MdsSetDatabase

MDSRESULT MdsSetDatabase(

const char *

)

lpszName

Establish the current database. This function sets the current database in the SQL Server.

MdsAddTable

MDSRESULT MdsAddTable (

MDS_TABLEINFO *

MDS_COLUMNMAP *

WORD

)

tableProps,

columnList,

columnCount

Create a new table in the server as well as set up the definition file (.int).

NOTE: Index creation is handled through the regular Btrieve API B_BUILD_INDEX.

MdsAddFields

MDSRESULT MdsAddFields(

const char *

MDS_COLUMNMAP *

WORD

)

fileLocation,

columnList,

columnCount

Add new fields to the end of an existing table and update the definition files on the client.

MdsGetFileInfo

MDSRESULT MdsGetFileInfo(

const char *

const bool

MDS_TABLEINFO *

MDS_COLUMNMAP *

WORD

)

fileLocation,

IncludeSqlData,

tableProps,

columnList,

columnCount

Add new fields to the end of an existing table and update the definition files on the client.

Renaming tables and files

The following APIs are available to rename files and tables.

NOTE: All of the parameters (except table name in MdsRenameTable) reference the originally named Btrieve data filename, NOT the INT filename. So, when you issue a request to rename "Billing.mkd" (which does not actually exist), the driver actually renames Billing_mkd.int.

MdsRenameTable

MDSRESULT MdsRenameTable(

const CHAR *

const CHAR *

)

fileLocation,

newTableName

Rename an existing table on the server as well as the definition file (.INT) on the client. For example, the following code renames table "receipts_2014" to "2014 Receipts" on the server and updates the TABLE_NAME in the receipts_2014_mkd.INT file.

char oldFileName[] = "receipts_2014.mkd";

char newTableName[] = "2014 Receipts";

MdsRenameTable (oldFileName, newTableName);

MdsRenameFile

MDSRESULT MdsRenameFile(

const CHAR *

const CHAR *

)

fileLocation,

newTableName

Rename the local data file (i.e., the INT file). Use MdsRenameTable and MdsRenameFile together to accomplish both. For example, the following code renames file "receipts_2014_mkd.INT" to "2014 Receipts_mkd.INT".

char oldFileName[] = "receipts_2014.mkd";

char newFileName[] = "2014 Receipts.mkd";

MdsRenameFile (oldFileName, newFileName);

B_RENAME_FILE

B_RENAME_FILE is also available; it is a subfunction of B_CREATE. B_RENAME_FILE renames the local data file (i.e., the INT file). For example, the following code renames file "receipts_mkd.INT" to "receipts_2014_mkd.INT".

#define B_RENAME_FILE (B_CREATE) // = 14

#define B_KEY_RENAME_FILE = -127

char oldFileName[] = "receipts.mkd";

char newFileName[] = "receipts_2014.mkd";

strcpy(databuf, oldFileName);

strcpy(keybuf, newFileName);

BTRCALL(B_RENAME_FILE, posBlock, databuf, &datalen, keybuf, B_KEY_RENAME_FILE);

In summary | Renames Server Table | Renames Local Data File (the INT file)
MdsRenameTable | yes | no
MdsRenameFile | no | Yes
B_RENAME_FILE | no | yes

Deleting tables and files

MdsDropTable

MDSRESULT MdsDropTable(

const CHAR *
)

fileLocation

Delete an existing table on the server and remove the definition file (.INT) on the client. The .INT is not removed if it has PERMANENT_INT set to YES. For example, the following code removes the "Billing" table.

MdsDropTable ("Demodata\\Billing.mkd")

MdsDeleteFile

MDSRESULT MdsDeleteFile(

const CHAR *
)

fileLocation

Delete the local INT file (call after using MdsDropTable). For example, the following code deletes the "Billing_mkd.INT" file ".

MdsDeleteFile ("Demodata\\Billing.mkd")

B_DROP_FILE

Drop the SQL table and remove the INT file. If present, the actual MicroKernel file and the definition in the DDFs (or XML source files) remain.

NOTE: To prevent an INT file from being deleted when a table is dropped, you can set the PERMANENT_INT token in the INT file to YES (the default setting is NO). This is useful if for example, you want to drop and then later recreate a file using the same structure.

#define B_DROP_FILE (B_OPEN + 3000)

char FileName[] = "receipts.mkd";

strcpy(keybuf, FileName);

BTRCALL(B_DROP_FILE, NULL, NULL, NULL, keybuf, 0);

B_DELETE_FILE

B_DELETE_FILE is also available; it is a subfunction of B_CREATE. It drops the SQL table and deletes the INT file.

#define B_DELETE_FILE (B_CREATE) // = 14

#define B_KEY_DELETE_FILE = -128

char FileName[] = "receipts.mkd";

strcpy(databuf, FileName);

BTRCALL(B_DELETE_FILE, posBlock, databuf, &datalen, NULL, B_KEY_DELETE_FILE);

In summary | Renames Server Table | Renames Local Data File (the INT file)
MdsDropTable | yes | no
MdsDeleteFile | no | yes
B_DROP_FILE | yes | yes
B_DELETE_FILE | yes | yes

Truncating files

B_TRUNCATE_FILE

Truncate file (delete all records).

#define B_TRUNCATE_FILE (B_OPEN + 2000)

char FileName[] = "receipts.mkd";

strcpy(keybuf, FileName);

BTRCALL(B_TRUNCATE_FILE, NULL, NULL, NULL, keybuf, 0);

BTR2SQL SDK


Migration Functions

Migration Functions

If you would like to write your own interface or have more control over migrating files from Btrieve to SQL, you can use these migration functions. They make it easy to migrate the entire database in one call, migrate certain tables with different settings, or migrate one table at a time.

& See also SDK documentation: Modules tab > Migration Functions

MdsPrepareDB

MDSRESULT MdsPrepareDB (

const char*
const char*
MdsMigrationControl
const char*
MDS_MIGCALLBACK
)

ddfLocation,
tableName,
whatToDo,
xmlConfig,
callback

Setup the server and/or database with needed objects for running your Btrieve application. Depending on the server, create stored procedures, views, etc. as needed to properly support the Mertech driver. Generally, the Mertech driver will not be able to function correctly until the server and specific database have been initialized with a few objects. This is normally done by the migration tool. This API needs to be called before running the application.

MdsMigrateOneTable

MDSRESULT MdsMigrateOneTable(

const char*
const char*
MdsMigrationControl
const char*
MDS_MIGCALLBACK
)

ddfLocation,
tableName,
whatToDo,
xmlConfig,
callback

Migrate a table from Btrieve to SQL.

MdsMigrateAllTablesInDdfs

MDSRESULT MdsMigrateAllTablesInDdfs(

const char*
MdsMigrationControl
const char*
MDS_MIGCALLBACK
)

ddfLocation,
whatToDo,
xmlConfig,
callback

Migrate a database from Btrieve to SQL.

MdsMigrateAllTablesInConfig

MDSRESULT MdsMigrateAllTablesInConfig(

const char*
MdsMigrationControl
const char*
MDS_MIGCALLBACK
)

ddfLocation,
whatToDo,
xmlConfig,
callback

Migrate multiple tables from Btrieve to SQL.

BTR2SQL SDK


Additional Btrieve APIs

Additional Btrieve APIs

These additional APIs augment the standard Btrieve API and are implemented in the same manner – utilizing the familiar BTRV() or BTRCALL() functions. A direct call interface is also available for some of the APIs.

& See also the SDK documentation: Modules tab > Extended Btrieve API, Modules tab > Version Information¸ Modules tab > Choosing Fields to Fetch

B_ENCRYPT_PASSWORD (3005) )or MdsEncryptPassword

const MDSRESULT MdsEncryptPassword (

char *

char *
unsigned short *
)

password,

encryptpwd,
encryptpwdlen

Encrypt a password for usage in an mds.INI file

B_SET_MDS_OPTION (3017) )or MdsSetOption

const MDSRESULT MdsSetOption(

unsigned short * char *
)

option,
value

Set the value of an option specific to the BTR2SQL driver (deprecated, use MdsSetSetting)

MdsSetSetting

const MDSRESULT MdsSetSetting(

char *

char *
char *
)

foldername,

settingname,
value

Set a global or folder-specific value for an option for the current BTR2SQL driver.

MdsGetSetting

const MDSRESULT MdsGetSetting(

char *

char *
char *

unsigned short *
)

foldername,

settingname,
value

valueSize

Retrieve the value of an option for the current BTR2SQL driver.

MdsGetError

const MDSRESULT MdsGetError(

char *
WORD *
)

errorDesc,
size

Retrieve the last error message. This function can be called to retrieve a detailed error message (usually the message generated by the database server) after an error code has been received from an operation.

B_IS_MIGRATED (5000)

Used to determine whether or not a file has been migrated to an SQL Backend.

NOTE: This function does not verify the existence on the server or validity of the table. It simply indicates that the Btrieve file was migrated (the .INT file exists). It is possible that the SQL table may have been removed.

B_FAST_STAT (5015)

This is the same as a B_STAT(15) except the record count is not returned.

B_STAT is a common operation to perform after the B_OPEN — especially for Btrieve class wrappers. The operation usually includes the current record count for the file. Obtaining the number of rows in a table in SQL is a relatively slow process. So, if the application does not need this extra piece of information, utilize the B_FAST_STAT instead.

B_EXTENDED_VERSION (5026)

Retrieve information about the Mertech driver or attached SQL backend.

The Extended Version function can do two things: retrieve information about the Mertech driver (version, dll name, etc.) or retrieve a version string from the active SQL server. Making this call against the Pervasive access dll causes a return code of 1. So this is a useful way to know if your application is running against a Btrieve or a SQL backend.

B_SET_FIELD_FETCH (2042)

Control which fields are retrieved from the server.

This is very useful for performance especially for large records. This API allows the application, without dealing with GetExtended buffers, to narrow the list of fields that are retrieved from the server.

BTR2SQL SDK


Accessing Both Btrieve and SQL Backends

Accessing Both Btrieve and SQL Backends

Consider the following scenario. During the migration process, some files are left in Btrieve, while others are migrated to an SQL backend. This could be an interim step during the migration process to test the migration. Or, a decision may have been made to leave private tables in Btrieve on the local machine, while common tables are stored on a central SQL Server. The Mertech drivers support this scenario.

Rather than replacing the Pervasive dll with the Mertech dll of the same name, an application needs to load Pervasive’s wbtrv32.dll and one of the Mertech migration dlls (sql_btr.dll, mys_btr.dll, ora_btr.dll, or pgs_btr.dll ) to simultaneously access the Btrieve and SQL backend.

Load the required Mertech dll (MS SQL in the example). Then, declare a function to be called in place of BTRCALL when accessing the MS SQL backend. For example:

#include <BtrieveFile.h>

// Load Pervasive's Btrieve access dll

BtrieveFile* btrieve = new BtrieveFile("w3btrv7.dll");

// Load Mertech's MicrosoftSQL library

BtrieveFile* mssql = new BtrieveFile("sql_btr.dll");

// Open an old Btrieve file

bStat = btrieve->BTRCALL(B_OPEN, NULL, &(datalen=0), FILE_NAME, MAX_KEY_BUF_SIZE, 0);

// Open a Btrieve file migrated to MSSQL

bStat = mssql->BTRCALL(B_OPEN, NULL, &(datalen=0), FILE_NAME2, MAX_KEY_BUF_SIZE, 0);

By loading the different dlls explicitly, the application is able to access the BTRCALL function from each; providing access to both Pervasive Btrieve files and SQL-migrated files.

You can also use this same method to access multiple SQL backends. For example, load the sql_btr.dll and the ora_btr.dll to simultaneously access MS SQL and Oracle backends.

Note: The INT files that are produced during migration are specific to each SQL backend so you will need to create a folder for each backend and provide an MDS.INI (sample in the SDK) that specifies the INT-Folder location and login information for each server.

Visual Basic Linking:

For MS SQL Server:

Declare Function BTRCALL_MSSQL Lib "sql_btr.dll" Alias "BTRCALL" (ByVal OP, ByVal Pb$, Db As Any, DL As Integer, ByRef Kb As Any, ByVal Kl, ByVal Kn)As Integer

For MySQL:

Declare Function BTRCALL_MYSQL Lib "mys_btr.dll" Alias "BTRCALL" (ByVal OP, ByVal Pb$, Db As Any, DL As Integer, ByRef Kb As Any, ByVal Kl, ByVal Kn)As Integer

For Oracle:

Declare Function BTRCALL_Oracle Lib "ora_btr.dll" Alias "BTRCALL" (ByVal OP, ByVal Pb$, Db As Any, DL As Integer, ByRef Kb As Any, ByVal Kl, ByVal Kn)As Integer

For PostgreSQL:

Declare Function BTRCALL_PGSQL Lib "pgs_btr.dll" Alias "BTRCALL" (ByVal OP, ByVal Pb$, Db As Any, DL As Integer, ByRef Kb As Any, ByVal Kl, ByVal Kn)As Integer

With these additional definitions, the application can call the original BTRCALL() function to access Btrieve as well as for example BTRCALL_ORACLE () to access an Oracle backend.

BTR2SQL SDK


Troubleshooting

Troubleshooting

BTR2SQL allows you to generate two types of reports to trace errors that can occur during the migration process. The migration report is a comprehensive report summary that you can use to determine whether or not the migration was successful. If any inconsistency is encountered, the report displays where the error occurred.

Another method of reporting that BTR2SQL uses is a low-level driver trace that you can enable by setting either environment variables or registry values.

External tools can also be downloaded to assist in troubleshooting.

Related Topics

Migration Report
Migration Driver Trace
Runtime Driver Trace
Startup Trace
External Tools


Migration Driver Trace

Migration Driver Trace

If you are receiving an error during migration, you may need more details than the summary report provides. Enabling the driver trace during migration can identify which row failed and the values of the fields. This report is enabled in the Preferences dialog box (see Setting Migration Preferences ).

Select Report | Driver Trace.

11:49:59.900> SQLBTR 5.0.5914.0 SQL_BTR.dll [10000000] (C:\Program Files\Mertech Data Systems\DB Drivers\Btrieve\bin\sql_btr.dll)

11:49:59.900> Computer : GATEWAY

11:49:59.900> ModuleName : C:\Program Files\Mertech Data Systems\DB Drivers\Btrieve\bin\Btr2SQL.exe

11:49:59.900> Process # 280 WTS # 0

11:49:59.916> Trace Level: 1 flush: no buffers: 'sql pb kb db '

11:49:59.916> Date: Tue Dec 18 11:49:59 2012

11:49:59.947> Btrieve: 10.30 NT Server Engine

11:49:59.947> Active Code Page: 1252 (1252 (ANSI - Latin I))

11:49:59.947> Active OEM Code Page: 437 (437 (OEM - United States))

|-----------------------------------------------------------------------------------------|

11:50:13.431> Performance: 13.48748 gap in trace

11:50:13.431> btr2sql.exe: Converting Class

|-----------------------------------------------------------------------------------------|

11:50:13.478> btr2sql.exe: Enabling SnapShot Isolation on Database . . .

11:50:13.478> PrepareBuffer: /*EOVDV#F@PB@@WF"uanref#QDP#CMHLU^WMCQWKMU[JQNHBVHKM"NJ
___________________________ mc*/

11:50:13.525> Execute query time: 0.04576

11:50:13.525> PrepareBuffer: /*EOVDV#F@PB@@WF"uanref#QDP#PDEG]BKNOHPWGE[PL@TPJNP#MO
____________________________mc*/

11:50:13.759> Execute query time: 0.23281

11:50:13.759> OLE-DB ERROR: Execute failed:

11:50:13.759> HRESULT: 0X80040E14, SQLSTATE: 42000, Native Code: 5058 : Option 'READ_COMMITTED_SNAPSHOT' cannot be set in database 'tempdb'.

11:50:13.759> MSSQL ERROR: Execute failed: Errors in SQL command: 5058 : Option 'READ_COMMITTED_SNAPSHOT' cannot be set in database 'tempdb'.

11:50:13.759> MDS ERROR: #25000: 'Execute failed: Errors in SQL command: 5058 : Option 'READ_COMMITTED_SNAPSHOT' cannot be set in database 'tempdb'.'

11:50:15.369> Performance: 1.60708 gap in trace

11:50:15.369> PrepareBuffer: /*<"<$@CMH#qq[wcchfq!,<.>(/.1-#*/

11:50:15.431> Performance: Prepare ran long: 0.06619

11:50:15.603> Execute query time: 0.16406

11:50:15.603> Read 1 rows

11:50:17.681> Performance: 2.07310 gap in trace

11:50:17.681> Status 12 opening Btrieve file 'C:\ProgramData\Pervasive Software\PSQL\Demodata\fieldext.ddf'

11:50:17.681> Field 1 ID type=15 ofs=0 size=4 flags=0

11:50:17.681> Field 2 Name type=0 ofs=4 size=7 flags=1

11:50:17.681> Field 3 Section type=0 ofs=11 size=3 flags=1

11:50:17.681> Field 4 Max_Size_nis

11:50:17.681> Field 5 Max_Size type=14 ofs=15 size=2 flags=4

11:50:17.681> Field 6 Start_Date type=3 ofs=17 size=4 flags=0

11:50:17.681> Field 7 Start_Time type=4 ofs=21 size=4 flags=0

11:50:17.681> Field 8 Finish_Time type=4 ofs=25 size=4 flags=0

11:50:17.681> Field 9 Building_Name type=0 ofs=29 size=25 flags=1

11:50:17.681> Field 10 Room_Number type=14 ofs=54 size=4 flags=0

11:50:17.681> Field 11 Faculty_ID type=14 ofs=58 size=8 flags=0

11:50:17.744> RecordSize: 66 flags: 512

11:50:17.744> Index 1.1 ID flags=258

11:50:17.744> Index 2.1 NAME flags=1298

11:50:17.744> Index 2.2 SECTION flags=1282

11:50:17.744> Index 3.1 FACULTY_ID flags=278

11:50:17.744> Index 3.2 START_DATE flags=274

11:50:17.744> Index 3.3 START_TIME flags=258

11:50:20.384> Performance: 2.63401 gap in trace

11:50:20.384> Creating new table (CLASS) at 'C:\ProgramData\Pervasive Software\PSQL\Demodata\CLASS.MKD'

11:50:20.384> RecSize=66 (fixed length) 11 fields, 3 indexes.

11:50:20.431> Transaction Started iso level=READ COMMITTED

Figure 28 Sample Migration Driver Trace

Troubleshooting


Runtime Driver Trace

Runtime Driver Trace

The Runtime Trace file is generated while your application is running. Each Btrieve function call made is logged. The Runtime Trace file is enabled in the Preferences dialog box (see Setting Migration Preferences).

NOTE: It is important to note that you should turn this option on only when trying to debug the calls and data being sent and received from the server. For normal operation, remember to turn this off otherwise you will experience performance degradation.

Two variables control the trace output. TRACE_ON defines the location of the trace file — full (or relative) path and filename. TRACE_LEVEL sets the amount of output — depending on the amount of detail needed.

‑1: ERROR_LEVEL Only messages generated because of an error situation. Messages in trace are preceded by "ERROR:" or "WARNING:"

0: BASIC_LEVEL Basic output - flow of application. Showing begin/end transactions, locks gained/released, record reads

1: DETAIL_LEVEL1 Field values in record read/written - Btrieve input/output buffers

2: DETAIL_LEVEL2 The "debug" level - it fills in more details above level 1 in order to track down a problem

3: DETAIL_LEVEL3 Needed only in rare cases

Each TRACE_LEVEL includes details from all previous levels.

NOTE: In the event that you are receiving an error from the server and the nature of the problem is unclear, Mertech recommends you run this trace first and then contact Mertech Technical Support. For any error on the server, you should first look in the server documentation for clarification before contacting Mertech.

22:16:25.140> SQLBtr 3.1.1.2

22:16:25.140> ModuleName : C:\bat\WBEXEC32.exe

22:16:25.140> Trace Level: 0

22:16:25.140> Date: Thu Nov 08 22:16:25 2007

|-----------------------------------------------------------------------------------------|

#1

22:16:25.140> BtrieveFunctionCall : B_OPEN KeyNum: 0 DataLen: 0 KeyLen: 29

22:16:25.140> Filename = "c:\pvsw\demodata\billing.mkd"

22:16:25.140> Reading table information from: c:\pvsw\demodata\billing_mkd.int

22:16:26.843> Login (ServerName=sqlsrv,UserName=sa)

22:16:26.843> License File : c:\bat\sql_btr.cfg

22:16:27.000> SQL Server 9.00.3054.00 - Developer Edition SP2

22:16:27.031> Database set to: DemoData

22:16:27.031> OpenTable: "DemoData"."dbo"."BILLING"

22:16:27.031> hFileHandle: 7

22:16:27.031> ReturnCode : 0 DataLen: 0

|-----------------------------------------------------------------------------------------|

#2

22:16:27.109> BtrieveFunctionCall : B_STAT KeyNum: 0 DataLen: 2185 KeyLen: 255 PosBlock: 7

22:16:27.109> GetRecordsUsedOF :"DemoData"."dbo"."BILLING" = 1315

22:16:27.109> ReturnCode : 0 DataLen: 80

|-----------------------------------------------------------------------------------------|

#3

22:16:29.796> BtrieveFunctionCall : B_GET_FIRST KeyNum: 0 DataLen: 61057 KeyLen: 255 PosBlock: 7

22:16:29.796> FIND GT "DemoData"."dbo"."BILLING" BY INDEX 1 MAX_ROWS=10 IndexName: BILLING_I01

22:16:29.828> FetchNextRecord: "DemoData"."dbo"."BILLING" [FOUND] MDS_RECNUM = 1

22:16:29.828> Move KeyColumns -> BTR Key : Table BILLING Index 1

22:16:29.828> STUDENT_ID="100062607"

22:16:29.828> TRANSACTION_NUMBER="1"

22:16:29.828> LOG="03/28/1996 15:38:52"

22:16:29.828> REGISTRAR_ID="130312616"

22:16:29.828> ReturnCode : 0 DataLen: 137

|-----------------------------------------------------------------------------------------|

#4

22:16:30.796> BtrieveFunctionCall : B_GET_NEXT KeyNum: 0 DataLen: 61057 KeyLen: 255 PosBlock: 7

22:16:30.796> FIND NEXT "DemoData"."dbo"."BILLING" BY INDEX 1 MAX_ROWS=10 IndexName: BILLING_I01

22:16:30.796> FetchNextRecord: "DemoData"."dbo"."BILLING" [FOUND] MDS_RECNUM = 2

22:16:30.796> Move KeyColumns -> BTR Key : Table BILLING Index 1

22:16:30.796> STUDENT_ID="100285859"

22:16:30.796> TRANSACTION_NUMBER="1"

22:16:30.796> LOG="03/28/1996 15:38:52"

22:16:30.796> REGISTRAR_ID="313053054"

22:16:30.796> ReturnCode : 0 DataLen: 137

|-----------------------------------------------------------------------------------------|

#5

22:16:32.296> BtrieveFunctionCall : B_UPDATE KeyNum: 0 DataLen: 137 KeyLen: 255 PosBlock: 7

22:16:32.296> BeginTransaction [Implicit]: sqlsrv

22:16:32.296> BindParameter: STUDENT_ID = '99237283' lPosBind: 1 lLength: 8

22:16:32.296> BindParameter: MDS_RECNUM = '2' lPosBind: 2 lLength: 1

22:16:32.921> ImplicitTransactionCommit: thomaslap

22:16:32.921> Move KeyColumns -> BTR Key : Table BILLING Index 1

22:16:32.921> ReturnCode : 0 DataLen: 137

|-----------------------------------------------------------------------------------------|

#6

22:16:34.000> BtrieveFunctionCall : B_CLOSE KeyNum: 0 PosBlock: 7

22:16:34.000> CloseTable: "DemoData"."dbo"."BILLING"

22:16:34.000> ReturnCode : 0 DataLen: 0

|-----------------------------------------------------------------------------------------|

#7

22:16:35.296> BtrieveFunctionCall : B_STOP KeyNum: 0

22:16:35.296> LogoutFrom: (ServerName=thomaslap)

22:16:35.296> ReturnCode : 0 DataLen: 0

Figure 29 Sample Runtime Driver Trace

Related Topics

Troubleshooting
Turning on application logging


Turning on application logging

Turning on application logging

There are several ways to turn on application logging:

Tracing driver calls from BTR2SQL using the Preferences dialog box

The easiest method of enabling the trace file is to set it from BTR2SQL in the Preferences dialog box by checking the Runtime Trace File option. However, this tool is not usually deployed with your application, so the end-user may not have access to this.

Tracing driver calls with environment variables

SET TRACE_ON=<TracefileName>

SET TRACE_LEVEL=<level>

NOTE: There are no spaces after the equals sign.

The environment variables must be active in the system before starting the application. You can add these two variables using the My Computer properties (Advanced tab, Environment Variables button). Or you can do this at a Command Prompt before starting the application:

Fig 23 Command Prompt

Figure 30 Setting up a trace file at the command prompt

Tracing driver calls with registry settings

HKEY_CURRENT_USER\SOFTWARE\Mertech Data Systems\DB Drivers\BTR\driver\v5

where driver is one of MySQL Driver, Oracle Driver, SQL Server Driver, or PgSQL Driver

Add the string value TRACE_ON and set its value to the name of the trace file. Add another string value named TRACE_LEVEL with a number between -1 and 3. If the registry keys do not exist, you will need to create each key in the hierarchy before entering the values.

Fig 24 Registry

Figure 31 Setting up a trace file using the registry

NOTE: Settings can also be stored under HKEY_LOCAL_MACHINE

Tracing driver calls with mds_global.ini settings

You can also use settings in the mds_global.ini (Trace_on, Trace_Level, and Trace_Flush) to control application logging.

& See also mds_global.ini

Tracing driver calls using MdsSetSetting

You can also call MdsSetSetting to programmatically set trace variables.

& See also Setting global and folder specific values programmatically

Driver Trace Control Tool

Logging can also be configured using Mertech’s separate Driver Trace Control Tool. Click the Windows Start button, open the Program menu and select Mertech ISDBC Drivers for Btrieve | Driver Trace Control Tool.

Fig 25 Trace Utility

Figure 32 Setting up a trace file using the trace utility

Specify the trace file and trace level, then click Save.

Runtime Driver Trace


Startup Trace

Startup Trace

There is a startup trace file to record errors detected during the early stages of driver initialization (i.e., before the regular trace begins). The startup trace is automatically turned on and then turned off after successful driver connection.

The startup trace file is named "mds_<drivername>.log" (for example, mds_ora_btr.log, mds_sql_btr.log, mds_mys_btr.log, mds_pgs_btr.log) and is always written to %temp%.

NOTE: Always use Windows Start | Run "%temp%" to navigate to the temp folder rather than manually going to what you *think* is the temp folder.

The startup trace logs only errors and only during the driver’s initial phase. The startup trace file is good place to look if, for example, your user-defined trace file is not created. An entry may be recorded in the startup trace file indicating that the user-defined trace file cannot be opened.

Troubleshooting


External Tools

External Tools

Errors are often caused because a driver dependency is not installed or not available on the Windows PATH. Dependency Walker and Process Monitor are two good tools that you can use to identify a missing dependency for the BTR2SQL Migration Utility or your application and to troubleshoot runtime errors.

Related Topics

Troubleshooting
Dependency Walker
Process Monitor
Blog Topics


Dependency Walker

Dependency Walker

1. Download Dependency Walker from www.dependencywalker.com. Mertech driver and the Migration Utility are 32-bit, make sure to download the 32-bit version of Dependency Walker.

2. Open the downloaded ZIP file.

3. Create an extract folder and extract all the files.

4. (Optional) drag a shortcut to your desktop or quick launch toolbar.

Identifying statically loaded dll dependencies

1. Start Dependency Walker.

2. Choose File | Open from the menu bar and browse to the location of the Mertech driver dll. Mertech driver dlls are usually located at <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\ bin.

3. Choose sql_btr.dll for MS SQL, mys_btr.dll for MySQL, ora_btr.dll for Oracle or pgs_btr.dll for PostgreSQL.

Missing or mismatched dependencies appear in yellow or red. The most common dlls to be missing are libmysql.dll and oci.dll.

Examining your application at runtime

1. Start Dependency Walker and set the View | Full Paths option.

2. Select the application using the File | Open command.
The application is scanned for dependencies and a hierarchical diagram of all dependent modules is displayed in the Module Dependency Tree View.

3. Select Profile | Start Profiling.

4. Make sure Use full paths when logging file names is checked.

5. Click OK.

Your application should begin to run. As your application runs, Dependency Walker logs information to the Log View and updates the tree and list views.

External Tools


Process Monitor

Process Monitor

Process Monitor can also be used to do identify missing dependencies and to troubleshoot other runtime errors.

1. Download Process Monitor from https://technet.microsoft.com/en-us/sysinternals/processmonitor.

2. Open the downloaded ZIP file.

3. Create an extract folder and extract all the files.

4. (Optional) drag a shortcut to your desktop or quick launch toolbar.

5. Start Process Monitor.

6. Select File from the menu bar and make sure Capture Events IS NOT checked.

7. Select Edit | Clear Display.

8. Select Filter and add Process Name btr2sql.exe to the list of events to be captured.

9. Enable event capture: select File from the menu bar and select Capture Events so IT IS checked.

10. Start the Btr2SQL Migration Utility or your application and reproduce the error.

11. Disable event capture in the Process Monitor: select File from the menu bar and select Capture Events so it IS NOT checked.

12. Examine the output. NAME NOT FOUND in the Result column can point to a missing dependencies.

External Tools


Blog Topics

Blog Topics

The Mertech Data Systems web site contains white papers and blogs to assist with additional troubleshooting topics. The site search option [image] can help you to find the required topics.

External Tools


Copyrights and Contact

Copyrights and Contact

BTR2SQL Version 5.2
April 25, 2016

Copyright © 2007 - 2016 Mertech Data Systems, Inc. All Rights Reserved. This document is for informational purposes only. Mertech makes no warranties, expressed or implied, in this document.

BTR2SQL and ISDBC are trademarks of Mertech Data Systems, Inc.
Btrieve and Pervasive.SQL are registered trademarks of Pervasive Software Inc.
Magic is a registered trademark of Magic Software Enterprises Ltd.
MySQL is a registered trademark of MySQL AB.
PostgreSQL is a registered trademark of PostgreSQL Inc.
Oracle is a registered trademark of Oracle Corporation.
SQL Server and Windows are registered trademarks of Microsoft Corporation.
Other trademarks and trade names mentioned herein are the property of their respective owners.

If you would like to know more about Mertech’s products, please visit our web site www.mertechdata.com or contact us at:

Corporate Head Office

Mertech Data Systems, Inc.
18503 Pines Blvd. Suite 312
Pembroke Pines, FL 33029
USA
Tel: +1 (954) 585-9016
Fax: +1 (866) 228-1213

California Office

Mertech Data Systems, Inc.
114 East Shaw Avenue, Suite 100
Fresno, CA 93710
USA