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 Oracle, MS SQL and PostgreSQL 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.

../../_images/image31.png

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.

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, 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>

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.

../../_images/image31.png

Figure 25 Data File Definitions

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

../../_images/image32.png

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

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

../../_images/image33.png

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.

../../_images/image34.png ../../_images/image35.png

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

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.

../../_images/image36.png

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=sqlsrv3sql2012

Database=SQLDB Schema=dbo

UseTrustedConnection=no

User=mydomainme

Password=4c8fe10aad3

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.

../../_images/image37.png

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=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

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.

../../_images/image38.png

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=4c8fe10aad

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.

../../_images/image39.png

mds.ini files in:

CORPORATE Directory

FLstore Directory

GAstore Directory

[SQL_BTR]

INT-Folder=..intfiles_corporate

Server=sqlsrv1sql2012

Database=SQLDB Schema=dbo

UseTrustedConnection=no

User=mydomainme

Password=4c8fe10aad3

Table-Prefix=COR

[SQL_BTR]

INT-Folder=..intfiles_regions

Server=sqlsrv1sql2012

Database=SQLDB

Schema=dbo

UseTrustedConnection=no

User=mydomainme

Password=4c8fe10aad3

Table-Prefix=FL

[SQL_BTR]

INT-Folder=..intfiles_regions

Server=sqlsrv1sql2012

Database=SQLDB Schema=dbo

UseTrustedConnection=no

User=mydomainme

Password=4c8fe10aad3

Table-Prefix=GA

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.

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

100 = Temp Table2

MagicIni = ..\app\magic.ini

[LOGICAL NAMES]

CurrentCust = FLstore

[Temp Tables]

5 = Insurance

8 = Vendors

100 = Temp Table1

100 = Temp Table2

MagicIni = ..\app\magic.ini

[LOGICAL NAMES]

CurrentCust = GAstore

[Temp Tables]

5 = Insurance

8 = Vendors

100 = Temp Table1

100 = 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.

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.

  1. Copy the deployment directory to the production site.

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

../../_images/image40.png
  • 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.

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.

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

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.

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 <file:///C:\Program%20Files%20(x86)\Mertech%20Data%20Systems\DB%20Drivers\Btrieve\sdk\docs\group___mds_header.html#gb7d407fd5c937f583f9c5814ac6e230d>`__  (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

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

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 SystemsDB Drivers\Btrieve\sdk\Samples\MdsApis\C

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

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