INT File Template for B_CREATE

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.

Background

The original B_CREATE syntax is:

B_CREATE KeyBuffer
Where KeyBuffer is <filename>

The only information available to the driver for creating a new file was the filename and indexes. The driver had to examine the indexes to determine there was a field of a particular data type and size at some specific offset in the record. However, the driver could not determine the names of all of the fields, so you might end up with a table such as:

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

Auto Template Option

B_CREATE was later expanded to make use of an existing int file (filename.int). When an application issues a B_CREATE, if an INT file already exists for the file, the driver compares its contents with the index definitions in the new create structure. If they match, the driver uses the definition in the INT file to create the table on the server. If the INT file does not look compatible, it is ignored and you are stuck with the MDS_FIELDx and MDS_FILLERx field names.

Several flags control the way the auto template option works. The 'flag' becomes the Key Number for the operation.

A flag telling the driver to ignore the existing INT file:

B_CM_OVERWRITE_IGNORE_INT_FILE= -6

B_CM_OVERWRITE_FORCE_INT_FILE =-99
B_CM_NOOVERWRITE_FORCE_INT_FILE=-100

INT File Template Option

The latest enhancement to B_CREATE expands the contents of KeyBuffer to include the new filename (as before), the template filename, and the table name to be created on the server. Now you can specify an INT file for an existing file that has a pattern you want to use to create the new file. For example you can create bar.btr with the same structure (but different table name) as foo.btr.

The new B_CREATE syntax is:

B_CREATE KeyBuffer
Where KeyBuffer is:
<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 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 causes an invalid filename error.

All forms of filenames are supported for both the filename and template parts of the string. This includes just a filename (relative to the “current directory”), current directory relative paths (.pathfilename or ..pathfilename), root relative paths (pathfilename), full paths (c:datafilename) and UNC paths (serversharepathfilename).

Notes

• An INI file in the folder where the template filename is specified is read. So it is possible for the template INT file to be in a different folder than what is directly specified for the template file.
  
  
• If the template filename does not exist, a status 12 is returned.

• A new INT file is created for the new filename with contents that match the template INT but using the new table name.

• If the new filename INT exists when the create call is made, it is ignored/erased and the template INT is used just as if the new file INT had not been there.

• If the new filename INT exists and is marked as Permanent, a FileAlreadyExists error is returned.

• The template INT should be marked as Permanent.

Example 1

Your Magic (or non-Magic) application has a DB table “custmain.mcf” with table name “Customers Main”. Custmain.mcf exists in C:MyAppData and C:MyAppDatamds.ini has an entry: INT-Folder=..intfiles

During migration to SQL, table “Customers Main” and C:MyAppIntFilescustmain_mcf.int are created.

The application later wants to access a new customer table with the same definition as “Customers Main”. C:MyAppData is the current folder at runtime.

1. The application opens ‘cust1.dat | custmain.mcf | Cust - John Smith’.
   
   
2. C:MyAppIntFilescust1_dat.int does not exist so the driver returns status 12.
   
   
3. Magic then issues a B_CREATE passing the same path string as before.
   
   
4. The driver reads C:MyAppIntFilescustmain_mcf.int into the usual internal file classes and updates the table name to “Cust – John Smith” and the physical name to “cust1.dat”.
   
   
5. The driver then issues a call to Create Table to on SQL backend, creates C:MyAppIntFilescust1_dat.int, and returns success.
   
   
6. Magic reissues the open and continues successfully.

Example 2

You can also use this new B_CREATE option in a multi-xxx (company, customer, site, etc.) environment where you utilize the same schema in multiple folders (one for each customer for instance). Scenario:

c:
 myapp
 data
 intfiles_mssql (all files marked internally as Permanent and OS Read/Only attribute set)
 people_btr.int
 jobs_btr.int
 salaries_btr.int
 intfiles_ora (all files marked internally 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_)

When either B_OPEN or B_CREATE references one of the customer files (for example, "c:myappdatacust1people.btr"), the driver looks at the INI file to determine where the INT files are located and then opens the INT file for the required server type (for example, "c:myappdataintfiles_mssqlpeople_btr.int").

So, rather than having thousands of INT files in each of the customer folders  (all having the same contents), the INI file directs the driver to a common location for the INTs.