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