January 1992


by Michael Steffano

Have you ever needed to create an archival system to maintain historical information, or 
possibly needed to create and manage a large number of identical databases across multiple
directories?  The two most common methods I know of  implementing this have serious 
drawbacks.   That is what prompted me to delve into the FILELIST and MAKE_FILE commands,
as well as FMAC itself for an undocumented, but extremely important command, to come up 
with a method that is transparent, quick, and totally soft (i.e. you don't need to specify what 
database you want to manipulate ahead of time).  

I'll also demonstrate a nice use of the OPEN AS command that allows you to copy records
between duplicate files without having to specify the actual files at compile time.

Two Common Methods & Their Drawbacks
1) Copy the database using COPYFILE and then ZEROFILE it.  This method works great if you 
are dealing with small files; however, a large database with many indexes is going to take a long 
time to copy resulting in user frustration and the perception that your program is slow or locked

2) A direct result of the large file problem above, many people create a special directory where 
a set of empty database files reside.  These are then copied over to the new directory.  This 
method works great unless your database structures tend to change a lot, resulting in having
to manually update your template directory.  If you forget to do it then your programs may not
work against the new databases (or worse, they will work, but not in the way you intended!).

A Better Method
Method #2 above would be great if you did not have to manually update database changes when
they occured.  An additional nicety would be not having to hardcode which databases you wanted
to create in the new directory.  Also, not needing to change directories or manipulate DFPATH
would be sweet.

The following code fragments demonstrates the basic logic and commands necessary to make this

   string Df_Root_Name 8        // name of database to be copied
   string Df_Path 40            // path specifier
   string User_Display_Name 32  // this displays in DFQUERY
   string Program_Name 10       // name by which database is referenced in pgms
   string New_File_Name 50      // complete path incl. filename of new database
   string Text_String 80        // miscellaneous storage string
   string ThreeString 3         // holds string format of FileNumber 
   integer Save_FileNumber      // used to hold FileNumber
// Inputs:
//    Df_Root_Name  - name of database you want to create a copy of
//    Df_Path - full pathname specifier of directory where you want the
//              new database created (make sure to end the string with
//              your operating systems path delimiter (e.g. "/")
  // Ensure there is an up to date .DEF file on the default drive so that 
  // MAKE_FILE produces a correct copy of the database structure.
   gosub Produce_New_DEF
  // Load the FileNumber (Read comments below on why we need to do this)
   gosub Get_FileNumber
  // The next commands insure we get the current root, user, and program names
  // of the database from FILELIST.CFG.  You should do it this way to make sure
  // the case of the root name is the same as that which is already on filelist.
  // If you don't force this to be the same the command may not work and you
  // will get the dreaded Error 61 ILLEGAL REDEFINITION OF EXISTING FILE. 
   filelist FileNumber to User_Display_Name Program_Name
   filelist pathname to Df_Root_Name
  // Because of the method I chose to create my .DEF file, I force an UPPERCASE
  // on the root name so that Error 61 does not occur.
   uppercase Df_Root_Name        // You may not need this line...
  // Set up full pathname of new database 
   move Df_Path to New_File_Name
   append New_File_Name Df_Root_Name
   trim New_File_Name to New_File_Name
  // Change FILELIST.CFG root name to new entry.  Note, FILELIST$PUT is an
  // undocumented internal command... use at your own risk!  It is very
  // important that the new root name be in the same case as the old one! That
  // is why we first retrieve it from filelist.  FileNumber is used by the next
  // several commands to indicate which database to manipulate.
  // Note, filelist$put "" "" "" will remove an entry from the filelist 
   filelist$put "" "" ""
  // Put our new revised entry back into the same slot 
   filelist$put New_File_Name User_Display_Name Program_Name
  // Now, create the new empty database in the specified directory
   make_file Df_Root_Name Df_Path
  // Put the original database entry back in to FILELIST.CFG 
  // It is necessary to restore the FileNumber because Make_File sets it to 0 
   move Save_FileNumber to FileNumber  
  // This removes the current entry
   filelist$put "" "" ""
  // this restores the original entry 
   filelist$put Df_Root_Name User_Display_Name Program_Name

// ************** LOWER LEVEL SUBROUTINES ***********************************
// There are several ways to produce a .DEF file automatically. You may pipe
// input into DFFILE which, while not pretty, does work.  You can use a third
// party utility (like VsMakdef from Vinga) that runs from the command line
// (or via a RUNPROGRAM WAIT).  You can read through the database and format
// your own .DEF file.  This provides the highest level of program control but
// is quite a lot of work.  You also need to sense for 2.3 or 3.0 files.  Make
// sure your code or any third party utility creates the .DEF file exactly like
// DFFILE.  Check the root name carefully for case differences!  Since I use
// a Vinga utility I need to do an UPPERCASE in the above command so that an
   move "VsMakdef " to Text_String
   append Text_String df_root_name " -F"
   runprogram wait Text_String  // this does screw up the screen display a bit
// This routine assumes a .DEF file exists for the file specified.  We have
// to use this elaborate method to obtain the FileNumber in order to maintain
// the 'softness' of the code.  DataFlex does not support a direct method that
// I know of getting FileNumber directly (e.g. FILE_SIZE df_pathname ... requires
// that df_pathname be an OPEN database file specifier) from a variable file name
// specifier.
   move Df_Root_Name to Text_String
   append Text_String ".DEF"
   direct_input Text_String
  [seqeof] begin
     gotoxy 24 0
     show "Missing .DEF"
     abort   // or whatever way you want to gracefully exit
   readln Text_String  // this gets the first line of the .DEF file
   pos "#" in Text_String  // determine where the FileNumber is on the line
  // Grab the string containing the FileNumber 
   mid Text_String to ThreeString 3 (Strmark+1)  // largest # is 3 digits    
   move Threestring to FileNumber  // convert it
   move FileNumber to Save_Filenumber   // save this for later use
Basically, you supply the name of the database and the directory where you want the
empty database to be written, the logic creates a .DEF file, then uses it to do a 
MAKE_FILE where you specified.  The FILELIST.CFG manipulations are necessary
to avoid ERROR 61 as mentioned above.  

Okay, so now you have an empty database in another directory.  That may be all you
want, but you might also want to add some data to it (as in an archival system) based
on certain selection critieria.  In order to accomplish this in as soft a manner as possible
the OPEN AS command and FIELDINDEX come to our rescue.

// By using the OPEN AS format we can fake out DataFlex into using any files
// we want.  However, in order to compile this program you must define NEWFILE
// and OLDFILE somewhere in FILELIST.CFG.  These files do not require any 
// fields except RECNUM.  This code is designed to work with the code above.
  // Open the new empty database in the specified directory 
   open New_File_Name as NEWFILE
  // Open the production database which we wish to archive 
   open Df_Root_Name as OLDFILE

// Once the database is OPEN, you may want to copy records from one to the 
// other.  This method is totally soft.  Obviously, you would only want to
// use this method between identically defined databases.  The copy method is
// not the most efficient (a block move using an OVERLAP field would be the 
// best) but it works reasonably well.
  // This next command allows us to get the number of fields in the database,
  // which is the way we control the transfer. 
   file_size OLDFILE to curr_size max_size field_count
   while {condition is true}   
      clear NEWFILE
      find gt OLDFILE by recnum  // or whatever index you choose
     [ found] begin 
         for fieldindex from 1 to field_count
           // use 'movestr' to correctly transfer any data type 
            movestr OLDFILE.recnum& to NEWFILE.recnum&
         saverecord NEWFILE
      {test for condition}
Some final caveats:  this method was tested thoroughly on 2.3 files, but I see no reason
why it would not work under 3.0.  Also, before running any test code make a backup copy
of FILELIST.CFG.  While I was figuring out how to make this  work I must have creamed it
a dozen times.

Michael D. Steffano has been programming and designing large and small systems in 
DataFlex since 1982.