Support for Temporary Data Files in Btrieve with BTR2SQL

Perhaps your application needs to create temporary data files at runtime. The Mertech drivers support creation of temporary files on the fly using the standard B_CREATE API. The table remains on the server until you call the B_DROP_TABLE API.

How does this work?

You call the B_CREATE API and provide the indexes and pathname for the file. The driver creates the requested table on the SQL backend. You can also provide a template (INT file) for the file and the table will be fully defined with field names and data types.

Creating the INT file

1. Make sure your file is defined in your DDFs.
   
   
2. Start the BTR2SQL Migration Utility and login.
   
   
3. Select File | Select File.DDF… and open your DDF file.
   
   
4. Select the temporary file (Temp.mkd in the example screenshot), then right-click to display the popup menu.

5. Select Create Table. The Create Table option creates an empty table and associated indexes in the target backend without migrating any data. It also creates an INT file (Temp_mkd.INT in this example).
   
   
6. Uncheck the Get Server Name from Login, Get Schema from Login and Get Database Name from Login check boxes if you want your current login information stored in the INT file. If login information is not provided in the INT file, it is obtained from the mds.ini file or through a login prompt.
   
   
7. Select OK in the Convert File dialog box.

The Migration Utility obtains the proper definition of the file from the DDF file. Field names and data types are translated directly into the SQL schema and also saved in an INT file. The INT file is stored in the same directory as the data file. The INT file contains all of the information necessary to allow the driver to connect to, open, or recreate the converted SQL table.

To prevent the INT file from being deleted when the table is dropped, edit the INT file and change the PERMANENT_INT token to YES (the default setting is NO).

PERMANENT_INT YES

Using the default INT file as a template

When an application issues a B_CREATE to create for example MyTempFile.mkd, the driver looks to see if a matching INT file (MyTempFile_mkd.INT) already exists. If it does, the driver compares its contents with the index definitions provided 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 temporary tables.

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

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

Using a different INT file as a template

The Mertech drivers recognize an extended B_CREATE syntax that allows the application to specify the new filename (as normal), the name of the INT file to be used as a template for the new file, and the name of table to be created on the server. This extended syntax is formatted in the KeyBuffer as:

<new 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 new 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 returns an invalid filename error.

All forms of filenames are supported for both the new and template parts of the string. This includes just a filename (relative to the “current directory”), current directory relative paths ( .\path\filename or ..\path\filename ), root relative paths ( \path\filename ), full paths ( c:\data\filename ) and UNC paths ( \\server\share\path\filename ).

Example

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.

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.

1. 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.
   
   
2. Create the INT file for the temporary file by following procedures under Creating the INT file. Then, use the extended B_ CREATE syntax to specify a new filename each time a temporary table is created.
   
   
3. Create the INT file for the temporary file by following procedures under Creating the INT 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.

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.