Code Generation -- from xBase???
Software Review by Mike Bianchi;
Vice President of Membership, Alamo PC


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.

File: MASTER.dbf
MEM_NUMB     N   6   0
LAST_NAME    C  20
COMPANY      C  35
ADDRESS_1    C  35
SUITE        C  10
CITY         C  18
STATE        C   2
ZIP          C  10
CRRT         C   4
DPB          C   2
SLUSH        C   2
SOURCE       N   3   0
FAX          C  12
BIRTHDAY     D   8
MEM_SINCE    D   8
EXPIRE       D   8
File: INACTIVE.dbf
MEM_NUMB     N   7   0
LAST_NAME    C  20
COMPANY      C 	35
ADDRESS_1    C 	35
SUITE        C  10
CITY         C  18
STATE        C   2
ZIP          C  10
SOURCE       N   3   0
BIRTHDAY     D   8
MEM_SINCE    D   8
EXPIRE       D   8
The various intermediate work files look like this:

File: APCOTEMP.dbf
C_NUM        C   6
MEM_NUMB     N   6   0
LAST_NAME    C  20
ADDRESS_1    C  35
SUITE        C  10
EXPIRE       D   8
C_EXP        C   8
File: APCO____
C_NUM        C   6
LAST_NAME    C  20
ADDRESS_1    C  35
SUITE        C  10
C_EXP        C   8
STAT         C   1
File: BATMAKER.dbf
FILENAME     C  12

The files OLDTEMP and OLD____ have the same structure as APCOTEMP and APCO____, respectively.

The Program

The 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"
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
33	zap
34	append from TEMP
35	replace all C_NUM with 
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
50	copy to TEMP
51	use OLDTEMP
52	zap
53	append from TEMP
54	replace all C_NUM with 
           C_EXP with dtoc(EXPIRE)
55	use OLD____
56	zap
57	append from OLDTEMP
58	copy to &fnm type delimited
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


Lines 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:

    tells PK to put its temporary work files on drive H:
    means to use the maximum compression, this causes it to run a little slower but if you use a relatively fast computer the difference is not significant
    this tells PK to move the files into the compressed file. Normally the files are copied. This parameter results in the original uncompressed files being deleted after they are added to the compressed file.
    @ (followed by a filename)
    identifies a file that contains the names of the files that are to be included in the compressed file. This feature is particularly useful when the use of the DOS wildcard characters is not appropriate.

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.