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 samplesmigration 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-btrcallb_create-null-databuffer-datalength-keybuffer-keynumber-0-1:

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>

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

mds.ini (UseTrustedConnection=no, User=mydomain\temp, Password=9dc42401d47a3fd0)

TEMP1.INT

TEMP2.INT

TEMP3.INT

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

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 (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                        const char *      lpszName
MdsSetDatabase(
                                 )

Establish the current database. This function sets the current database in the SQL Server.

MdsAddTable

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

MDSRESULT           const CHAR  *    fileLocation
MdsDeleteFile(      )

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

MdsGetSetting

const MDSRESULT MdsGetSetting(            char *             foldername,

                                          char *             settingname,
                                          char *             values

                                          unsigned short    valueSize
                                          *
                                          )

Retrieve the value of an option for the current BTR2SQL driver.

MdsGetError

const MDSRESULT MdsGetError(            char *            errorDesc,
                                        WORD*           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.

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.

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