BTR2SQL SDK¶

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

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

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.

status = BTRCALL(B_CREATE, NULL dataBuffer, &dataLength, keyBuffer, 0, 0);

Where keyBuffer is <filename>

CREATE TABLE cust1_dat (mds_field1 int, mds_filler1 binary(10), mds_field2
varchar(50), mds_filler2 binary(500))

status = BTRCALL(B_CREATE, NULL dataBuffer, &dataLength, keyBuffer, keyNumber, 0);

Where keyNumber is one of the above flags.

status = BTRCALL(B_CREATE, NULL dataBuffer, &dataLength, keyBuffer, keyNumber, 0);

Where keyNumber is one of the above flags and keyBuffer is:

<filename> | <template filename> | <table name>

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

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.

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

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

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

{
  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 Fast Insert operations should be performed until the B_END_FAST_INSERT 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.

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.

Schema Functions (DTIFunction 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 *tableProps,
                      MDS_COLUMNMAP *columnList,
                      WORD 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 *fileLocation,
                        MDS_COLUMNMAP *columnList,
                        WORD columnCount)

Add new fields to the end of an existing table and update the definition files on the client.

MdsGetFileInfo

MDSRESULT MdsGetFileInfo(const char *fileLocation,
                          const bool IncludeSqlData,
                          MDS_TABLEINFO *tableProps,
                          MDS_COLUMNMAP *columnList,
                          WORD columnCount)

Add new fields to the end of an existing table and update the definition files on the client.

MDSRESULT MdsRenameTable(const CHAR *fileLocation,
                          const CHAR *newTableName)

char oldFileName[] = "receipts_2014.mkd";
char newTableName[] = "2014 Receipts";
MdsRenameTable (oldFileName, newTableName);

MDSRESULT MdsRenameFile(const CHAR *fileLocation,
                         const CHAR *newTableName)

char oldFileName[] = "receipts_2014.mkd";
char newFileName[] = "2014 Receipts.mkd";
MdsRenameFile (oldFileName, newFileName);

MDSRESULT MdsDeleteFile(const CHAR *fileLocation)

MdsDeleteFile ("Demodata\\Billing.mkd")

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 *ddfLocation,
                       const char *tableName,
                       MdsMigrationControl whatToDo,
                       const char *xmlConfig,
                       MDS_MIGCALLBACK 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 *ddfLocation,
                              const char *tableName,
                              MdsMigrationControl whatToDo,
                              const char *xmlConfig,
                              MDS_MIGCALLBACK callback)

Migrate a table from Btrieve to SQL.

MdsMigrateAllTablesInDdfs

MDSRESULT MdsMigrateAllTablesInDdfs(const char *ddfLocation,
                                     MdsMigrationControl whatToDo,
                                     const char *xmlConfig,
                                     MDS_MIGCALLBACK callback)

Migrate a database from Btrieve to SQL.

MdsMigrateAllTablesInConfig

MDSRESULT MdsMigrateAllTablesInConfig(const char *ddfLocation,
                                        MdsMigrationControl whatToDo,
                                        const char *xmlConfig,
                                        MDS_MIGCALLBACK callback)

Migrate multiple tables from Btrieve to SQL.

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 *password,
                                   char *encryptpwd,
                                   unsigned short encryptpwdlen)

Encrypt a password for usage in an mds.ini file.

B_SET_MDS_OPTION (3017) )**or **MdsSetOption

const MDSRESULT MdsSetOption(unsigned short option,
                              char *value)

Set the value of an option specific to the BTR2SQL driver (deprecated, use MdsSetSetting).

MdsSetSetting

const MDSRESULT MdsSetSetting(char *foldername,
                               char *settingname,
                               char *values)

Set a global or folder-specific value for an option for the current BTR2SQL driver.

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

The format for the call is:

MdsSetSetting(foldername, settingname, value)

• foldername is the full or relative path to the folder where the mds.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")

// 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"

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)

const MDSRESULT MdsGetSetting(char *foldername,
                              char *settingname,
                              char *values,
                              unsigned short valueSize)

MdsGetSetting(foldername, settingname, return value)

const MDSRESULT MdsGetError(char *errorDesc,
                            WORD *size)

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

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