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.

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

Prerequisites

Important

For runtime deployment, use the client libraries that match your application’s bitness. BTR2SQL provides both 32-bit drivers (wbtrv32.dll / w3btrv7.dll) and 64-bit drivers (wbtrv64.dll / w64btrv.dll). See Runtime File Setup for details.

Btrieve 6.15 or new versions of Actian/Pervasive.SQL 7.0 (or greater)

BTR2SQL contains database driver DLLs to replace your Btrieve 6.15 engine (wbtrv32.dll) or Pervasive.SQL 7.0 (or greater) engine (w3btrv7.dll). 64-bit equivalents (wbtrv64.dll and w64btrv.dll) are also provided for 64-bit applications. The replacement drivers allow your existing application to access the newly migrated SQL backend.

Btrieve data files with schema defined in Pervasive.SQL DDFs

The BTR2SQL GUI Migration Utility requires a Pervasive.SQL 7.0 (or greater) engine to read Data Definition Files (DDFs) during data migration. DDFs define the layout, or schema, of the tables in the Pervasive.SQL database. Without the DDFs, the data cannot be efficiently migrated to SQL.

If you are currently running Btrieve 6.15, you can obtain a later version of Pervasive.SQL and the DDFCONV tool from Pervasive (Actian).

If your application is written in UniPaaS (now called Magic xpa Application Platform), contact Mertech for additional information.

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.

Microsoft SQL, Oracle, PostgreSQL

MS SQL Server, Oracle, and PostgreSQL are the SQL backends currently supported by BTR2SQL. 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. to be sure the proper rights are set to create and migrate tables. The migration utilities add some basic procedures and views to the database to support certain operations that are needed by the drivers related to locking and other features.

  • An MSSQL client is included with the BTR2SQL installation. For deployment, download the MSOLEDBSQL driver from Microsoft.

PostgreSQL

  • PostgreSQL uses a single encoding for the entire database, so you must choose between ANSI and UTF-8 at database creation time. Most customers have ANSI datasets and should create an ANSI database.

  • ANSI database (recommended for most users) — Create the database with a locale-specific encoding such as WIN1252 or LATIN1, a collation compatible with your Actian/Btrieve tables, CTYPE of C, and template0. Example: CREATE DATABASE myapp ENCODING 'WIN1252' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;

  • UTF-8 database — Required only if your Btrieve tables use Unicode columns (WString or WZString data types). Create the database with UTF8 encoding. See Unicode Support for details.

  • A single database cannot mix ANSI and UTF-8 encodings. If you have both ANSI and Unicode columns, use a UTF-8 database.

  • You will need to use a user with appropriate privileges to create the tables and also to add procedures. The migration utilities add some basic procedures to the database to support certain operations that are needed by the driver.

  • Client DLLs are installed in <Program Files>\Mertech Data Systems\DB Drivers\Btrieve\clients\pgsql and should be copied to your application’s bin folder.

Oracle

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

  • Requires the full Oracle client or Oracle Instant Client with the main entry point being OCI.dll.

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.

Backend

Limits

Microsoft SQL Server

Max 1024 columns; max 8060 bytes per record

Oracle

Max 1000 columns

PostgreSQL

Max 250-1600 columns depending on column types

DDFs

Data Definition Files (DDFs) describe the layout of tables within Btrieve files and are essential for migrating data to SQL. Tools such as Pervasive’s DDF Builder can help create DDFs for databases that do not already have them.

If you need to build or modify DDFs manually, be aware of challenges such as variant records (unions), the IN DICTIONARY clause, and True-Null column handling. These topics are covered in detail in the Btrieve Data Concepts section of the Programmer’s Guide.

As an alternative to DDFs, BTR2SQL supports XML configuration files that fully define table structures for migration. See the Migration XML Configuration Reference for details.