Those of you that are familiar with xBase -- the database programming
language used with dBase, FoxPro and others -- are probably aware of the
RUN (or '!') command that will allow you to shell out of the program to
run an external program or DOS command. Were you aware that xBase can be
used to generate its own code (DOS batch file) before running it?
One of my duties as Vice President of Membership is to provide the Board of Directors and Sysops with a periodically updated roster of current and expired members. In order for the files to be readable by whatever program is being used by the user, they are exported in delimited ASCII format. To conserve space on the BBS, the files are compressed with PKZIP before uploading. Because the filename of the uploaded files changes each time it is created, and it is terribly inconvenient to type the long command line needed to run PKZIP each time, it is necessary for xBase to tell PKZIP what files to compress each time it is called with the RUN command. Herein lies the Mother of Invention!
In the interest of brevity, I will only explain the procedure I use for preparation of the file given to the Sysops. The other files are done in the same way but are less complicated since the Sysops roster is the only one that contains more than one file zipped together.
[Disclaimer: for security reasons, the filenames used in this report are fictitious]. The filename of the uploaded file is ROSTxxxx.ZIP consisting of two files, GOODxxxx.TXT (active memberships) and OLDxxxx.TXT (expired memberships) zipped together. xxxx indicates the date that file was prepared. For example, a file prepared on September 12 would be named ROST0912.ZIP.
The structure of the relevant database files are as follows. For the benefit of those unfamiliar with this format, the meanings of these data are:
column 1 field name 2 data type C = Character string N = Numeric D = Date 3 size of field 4 decimal placesonly the first half of the records shown here. The rest of the record contains fields that are use in my 'checks and balances' system and flags used as filters for various queries and reports that are inconsequential to the topic of discussion here.
The ProgramThe program that performs these functions looks like this. The line numbers have been added for reference and are not part of the actual program. Lines that are not part of the topic of this discussion have been deleted as indicated by the missing line numbers.
1 * BBSFILES.prg 2 * set up files for BBS roster reports 3 * updated: 3/22/94 4 set safety off 5 set talk off 6 close all 7 k = substr(dtoc(date()), 1, 2) + substr(dtoc(date()), 4, 2) 8 fna = "GOOD" + k + ".txt" 10 fnm = "OLD" + k + ".txt" 13 outpath = "d:\comm\tobesent\" 14 command = "pkzip "+outpath+"rost"+k+".zip -bh: -ex -m @h:\include.lst" 17 use BATMAKER 18 zap 19 append blank 20 replace BUFFERLINE with command 25 copy to h:\ZIPPIT.bat type sdf 26 use MASTER index MASTNAME 27 copy to TEMP 32 use APCOTEMP 33 zap 34 append from TEMP 35 replace all C_NUM with ltrim(rtrim(str(MEM_NUMB))) 36 replace all C_EXP with iif(lfm, "Lifetime", dtoc(EXPIRE)) 42 use APCO____ 43 zap 44 append from APCOTEMP 45 copy to &fna type delimited 49 use INACTIVE index OLDNAME 50 copy to TEMP 51 use OLDTEMP 52 zap 53 append from TEMP 54 replace all C_NUM with ltrim(rtrim(str(MEM_NUMB))), C_EXP with dtoc(EXPIRE) 55 use OLD____ 56 zap 57 append from OLDTEMP 58 copy to &fnm type delimited 59 use FILELIST 60 zap 61 append blank 62 replace FILENAME with fna 63 append blank 64 replace FILENAME with fnm 65 copy to h:\include.lst type sdf 73 run h:\ZIPPIT 74 set safety on 75 set talk on
ExplanationLines 4 through 6 set up the environment. Since the file 'TEMP' is also used for a variety of other purposes, it is usually already on the disk when this program is run. The command 'copy to TEMP' (lines 27 & 50) will overwrite this file. The program will be interrupted at those points to ask if this is really what you want to do. The same thing will occur when the 'zap' command is executed (lines 18, 33, 43, 52, 56 & 60) which deletes all records in the current file. This interruption feature is turned off by line 4. This feature is a good safety valve to have on, so it is turned back on by line 74 before the program exits. Line 5 prevents all the commands and their results from scrolling across the screen as the program runs, this can be quite unnerving when dealing with the 5000+ records of both files. This feature is also turned back on (line 75) before the program exits. Line 6 closes all open files.
Lines 7 through 25 are the actual meat of the subject of this summary. Line 7 converts the system date to a character string, pulls out the pieces needed, puts those pieces together in a single string and then assigns that string to a memory variable. That variable is then used to create the filenames of the output files ( lines 8 & 10) and the command (line 14) that will eventually be used to actually run PKZIP to compress the files. Lines 17 - 20 put the command line into the BATMAKER file.
Line 25 copys the file with the extension .BAT. The sdf data type (Standard Data Format) converts the data to ASCII with all fields (one, in this case) on one line, separated by spaces with no enclosing quotes and terminated with a Carriage Return. Wow, just the right format needed to create a DOS batch file!
Lines 26 - 58 create the actual files that are uploaded. The delimited data type is ASCII with the fields enclosed in quotes and separated by commas. Lines 35 and 54 convert the member number to a character string, this was originally added because when I inherited the files from my predecessor, they were imported from Paradox and the numbers were put out with a decimal point followed by a long string of zeros; these commands are no longer necessary but I am still in the process of streamlining the procedures. Lines 36 and 54 convert the expiration date to a character string. dBase normally puts out dates as an 8-digit number that can be confusing to read by a person not familiar with this format.
Lines 59 - 65 use the same procedures described above to create the file INCLUDE.LST. This is the file used in the PKZIP command to find the names of the files to include in the compressed file. Finally, Line 73 runs the batch file created earlier to call PKZIP and properly compress the files as required for uploading to the BBS and place them in the proper directory as required by most communications programs.
A side note on the use of PKZIP: for those who are not familiar with some of the finer features of PKZIP, the parameters included in the command line (line 14) mean:
The dBase RUN command is a powerful tool that can be used to call an external program or execute a DOS command from inside a dBase program. It can sometimes, however, have problems with command line parameters. The procedures described here can be a way to circumvent that shortcoming.