CP/M

home *** CD-ROM | disk | FTP | other *** search

/ CP/M / CPM_CDROM.iso / lambda / soundpot / a / dbtips.lbr / DBPROG.TZP / DBPROG.TIP

Wrap

Text File | 1993-10-26 | 18.1 KB | 464 lines

dBASE II PROGRAMMING TIPS 1 Database file structure 2 Command line limit 3 Changing the record count 4 Negative SKIP in RunTime 5 Num. fields & decimal places 6 Recreating a database header 7 Recovering Cross-linked files 8 Removing Duplicate Index Keys 9 Self-Cleaning Database Files 1 The internal structure of a dBASE II database file is composed of a header and data records. The header is a block of data which contains information about the structure of the data file, such as how many records it contains, and the date it was last updated. The table below shows how this information is stored. dBASE II DATABASE FILE HEADER: +---------+-------------------+---------------------------------+ | BYTE | CONTENTS | MEANING | +---------+-------------------+---------------------------------+ | 0 | 02H | denotes dBASE II database file | +---------+-------------------+---------------------------------+ | 1-2 | 16 bit number | number of records in data file | +---------+-------------------+---------------------------------+ | 3-5 | 3 bytes | date of last update (MM DD YY) | +---------+-------------------+---------------------------------+ | 6-7 | 16 bit number | size of the record | +---------+-------------------+---------------------------------+ | 8-519 | 32x16 byte array | Field descriptors terminated by | --+ | | | a carriage return (0DH) | | +---------+-------------------+---------------------------------+ | | 520 | 1 byte | 0DH if all 32 fields present, | | | | | otherwise a hex zero | | +---------+-------------------+---------------------------------+ | | | EACH FIELD DESCRIPTOR: <-------------------------------------+ +---------+-------------------+---------------------------------+ | BYTE | CONTENTS | MEANING | +---------+-------------------+---------------------------------+ | 0-10 | 11 bytes | field name in ASCII, zero-filled| +---------+-------------------+---------------------------------+ | 11 | 1 byte | field type in ASCII (C N or L) | +---------+-------------------+---------------------------------+ | 12 | 1 byte | field length in binary | +---------+-------------------+--------------------------------+ | 13-14 | 16 bit number | field data address | | | | (address is set in memory) | +---------+-------------------+---------------------------------+ | 15 | 1 byte | field decimal count in binary | +---------+-------------------+---------------------------------+ The data records are layed out as follows: 1. Data records are preceded by one byte that is a space (20H) if the record is not deleted and an asterisk (2AH) if it is deleted. 2. Data fields are packed into records without separators or record terminators. 3. Data types are stored in ASCII format as follows: DATA TYPE DATA RECORD STORAGE --------- ------------------------------------------ Character (ASCII characters) Numeric - . 0 1 2 3 4 5 6 7 8 9 Logical Y y N n T t F f (20H when not initialized) 2 Command line limit dBASE II uses a 254 byte buffer to interpret a command line. If a command line reachs or exceeds the buffer limit (such as with a REPLACE command), dBASE II will give unpredictable results. No error message is given. 3 Changing the record count An incorrect record count in a dBASE II datafile header can result from one of the following: 1. Turning off the computer before closing the datafiles or QUITting dBASE. 2. Interrupting a PACK, SORT, APPEND, or COPY operation. 3. Power supply interruptions while processing data. Some known methods for correcting a faulty record count in a dBASE II database are listed below. Method 1: Use the COPY command and make a new copy of the file under a different filename. Since the COPY command keeps an internal counter, it will restore the correct record count to the header of the new file after completing the copy. Enter dBASE and type the following: . USE <sickfile> . COPY TO <newfile> Method 2: With this method you will need to create and run a BASIC program. This program will work with MicroSoft BASIC or PC- DOS BASICA. You can write this program in dBASE II with MODIFY COMMAND. 100 'Program.: FIXHEAD.BAS 110 'Author..: Luis A. Castro 115 'Date....: 12/19/84 120 'Notes...: To run this program, type the following: 125 ' 130 ' A>MBASIC FIXHEAD (MicroSoft BASIC) 140 ' A>BASICA FIXHEAD (IBM-PC BASIC) 150 ' 200 PRINT 'Change record count on header of DBF file" 210 PRINT 220 INPUT "Enter FILENAME.EXT ",FILENAME$ 'Get the filename 230 OPEN "R", #1, FILENAME$, 3 'Open the file 240 FIELD #1, 1 AS DUMMY$, 2 AS OLDVAL$ 'Field of 3 bytes 250 GET #1, 1 'Get the first record 260 PRINT "Number of records ";CVI(OLDVAL$) 'Old record count 270 INPUT " Change to ",NEWVAL% 'Get new record count 280 LSET OLDVAL$=MKI$(NEWVAL%) 'Replace old with new 290 PUT #1, 1 'Save the record 300 CLOS% W1 'Close the file 400 OPEN "R", #1, FILENAME$, 1 'Open the file again 410 FIELD #1, 1 AS ONEBYTE$ 'Field record to 1 byte 420 LSET ONEBYTE$=" " 'Replace field value with a blank 430 PUT #1, 522 'Save the blank to byte 522 440 CLOSE #1 'Close the file 450 SYSTEM 'Exit to the operating system Method 3: Use DDT (CP/M-80) or DEBUG (PC/MS-DOS) to patch the two bytes in the datafile header containing the record count. This method assumes the entire datafile will fit into memory at one time and you should not use this method otherwise. The new record count value must be converted to hexadecimal before it can be patched in. IF DDT IS AVAILABLE (CP/M-80): A>DDT Filename.DBF (user input is underlined) <DDT version information> NEXT PC yyxx 0100 -S101 0101 ll aa (where aa is low-order byte of new value) 0102 hh bb (where bb is high-order byte of new value) 0103 mm . -<Ctrl-C> A>SAVE pp Filename.DBF (where pp is the number of pages) In order to calculate the number of 256 byte blocks to write to disk, called "pages" and represented by "pp" in our example, do the following: 1) Find the two four digit numbers just below NEXT PC in the DDT header. Forget about the right hand number, 0100, DDT will always load your file beginning at this address. 2) Select the yy portion of the first four digit number and convert this hexadecimal number to decimal. The decimal number minus one is the number of pages (pp) to SAVE. 3) There is only one special case where step (2) will not give the correct result. If the last two digits of this four digit number (xx in our example) is hexadecimal 80, then do not subtract one from the decimal number. IF DEBUG IS AVAILABLE (PC/MS-DOS): A>DEBUG Filename.DBF (user input is underlined) -E101 xxxx:0101 ll.aa <space> hh.bb <RETURN> (where aa is the low-order and bb is the high-order byte of the new record count value) -W -Q A> 4 Negative SKIP in RunTime SKIP-1 (with no spaces) works in dBASE II, but behaves differently when encrypted with RunTime's DBCODE version 2.4. On all RunTime formats it will execute a SKIP to the next record. SKIP -1 (with a space) will correctly skip to the previous record. 5 Num. fields & decimal places Although not documented, dBASE II expects the user to allow for a leading digit and a decimal point on numeric fields containing decimal places. For example, a numeric field of two decimal places should not be defined any smaller than four digits in length--one position for the leading digit, one position for the decimal point, and two positions for the two decimal places. If the structure for a numeric field does not allow for a leading digit (such as, a width of three and two decimal places), input to the numeric field will always be stored as zero. Also, if other numeric fields follow this field, they might automatically be zeroed out when numeric data is entered to the first field. 6 Recreating a database header To "recreate" a corrupted dBASE II datafile header, you will need to follow the two steps listed below. You will also need to run a BASIC program. First: Use the CREATE command in dBASE II to construct a new dBASE II header. Second: Run the BASIC program listed below. This program will work with MicroSoft BASIC or PC-DOS BASICA. You can input this program in dBASE II using MODIFY COMMAND. 100 'Program.: NEWHEAD.BAS 110 'Author..: Luis A. Castro 120 'Date....: 12/19/84 130 'Notes...: Run the program from CP/M or DOS as follows: 135 ' 140 ' A>MBASIC NEWHEAD/S:522 (MicroSoft BASIC) 150 ' A>BASICA NEWHEAD/S:522 (IBM-PC BASIC) 160 ' 200 PRINT "Inserting a new dBASE II header" 205 PRINT 210 INPUT "Enter old FILENAME.EXT ",OLDFILE$ 220 OPEN "R",#1,OLDFILE$,522 230 FIELD #1,1 AS OLDCODE$,2 AS OLDVAL$ 240 FIELD #1,3 AS D$,245 AS OLD1$,240 AS OLD2$,33 AS OLD3$ 250 FIELD #1,250 AS D1$,250 AS D2$,21 AS D3$,1 AS LASTBYTE$ 300 INPUT "Enter new structure FILENAME.EXT ",NEWFILE$ 310 OPEN "R",#2,NEWFILE$,522 320 FIELD #2,1 AS NEWCODE$,2 AS NEWVAL$ 330 FIELD #2,3 AS D$,245 AS NEW1$,240 AS NEW2$,33 AS NEW3$ 340 GET #2,1 400 PRINT "Number of records ";CVI(OLDVAL$) 410 INPUT " Change to ",NEWVAL% 420 LSET OLDCODE$=NEWCODE$ 430 LSET OLDVAL$=MKI$(NEWVAL%) 450 LSET OLD1$=NEW1$ 460 LSET OLD2$=NEW2$ 470 LSET OLD3$=NEW3$ 480 LSET LASTBYTE$=" " 490 PUT #1,1 500 SYSTEM 7 Recovering Cross-linked files Cross-linking happens when data from one file is written into another file on disk. This can occur with both the CP/M and MS- DOS operating systems. Many factors may cause this. Hardware and operating system malfunctions can cause data to be written to the disk incorrectly. The chances of this happening are lessened if the files on disk are closed when the malfunction occurs. Files may be cross-linked in dBASE II, but the situation is not unique to dBASE. We are aware of only one instance in which dBASE II may contribute to a cross-link. The problem occurs when an error condition arises during command file execution. If there are database or index files open at the time an error is found, dBASE II does not close these files. If the user returns to the dot prompt and issues MODIFY COMMAND to fix the program error, the operating system may assume that the disk space allocated to the database or index file is available space and may use it for the command file update. The disk memory map has not been protected along with the open database or index files. As a result, the command file instructions are written into the data fields of the database or index files while allocation is made for the command file. This creates a crossing or linking of files on the disk. To avoid this, issue a CLEAR command before using MODIFY COMMAND. This will ensure that the database or index files have been updated before any changes are made to the command files. Cross-linking is much less likely to occur on a disk that has been recently formatted or is relatively empty. It is more likely to occur on a disk that is nearly full, or which has been subject to much use, creation, and deletion of files. If cross-linking has already occurred, COPY (in MS-DOS) or PIP (in CP/M) the files to new files and delete the old files. The files can then be examined and any data corruption can be corrected at that time. The process of reclaiming information lost on the disk can be very laborious and require considerable manipulation from within dBASE II as well as at the operating system level. Cross-linking may have occurred if the application suddenly begins to take substantially longer to process data. A CHKDSK or STAT (CP/M) command may reveal lost clusters on the disk. An experience of cross-linking, whether in dBASE II, another application program, or interactively from the operating system, stresses the value of making frequent and comprehensive backups of your data and program disks. Reconstructing data from backups is almost always the easist way to recover from a cross-link. 8 Removing Duplicate Index Keys There are several methods available to remove duplicate key entries from an INDEXed database file. One way is to check for the duplicate when entering the record. The advantage here is that the integrity of the database file is always constant. The penalty is that the duplicate checking requires some programming gymnastics and the data-entry process is slowed to assess correctness and take action based on error. The second way is to run a duplicate checking routine against the INDEXed database file after the data-entry process is complete. The advantage here is that the data-entry process can be as fast as possible with no encumbrances. The data-entry logic can be simple and straightforward. The disadvantage is that the database file integrity is not maintained throughout the data-entry process. Duplicate key entry checking must be done against the entire database file after data-entry is complete. With those considerations in mind, the following routine can be used to remove duplicate key entries from an INDEXed database file subsequent to data-entry. It could be run whenever duplicate key entries need to be removed from the database file. Users of large mailing list systems might, for example, want to run this routine against their mailing list database files prior to mass mailings. This removes the burden of duplicate checking from the data-entry cycle entirely. * Program ...: Remove.PRG * Author ....: Your Name * Date ......: 6-1-85 * Note(s) ...: Batch duplicate key deletion routine. * * Replace YourFile, YourNdx, Your:key with the * appropriate names that correspond to your * application. * SET TALK OFF USE YourFile INDEX YourNdx * ---Initialize mkey to an impossible value. STORE "XXXXXXXXXXXX" TO mkey DO WHILE .NOT. EOF IF Your:key = mkey DELETE ELSE STORE Your:key to mkey ENDIF SKIP ENDDO CLEAR RETURN * EOF Remove.PRG 9 Self-Cleaning Database Files In many dBASE II applications, there is a simple option available which obviates the need for the time-consuming and otherwise problematic use of the PACK command. It is possible in fact to make your database file self-cleaning. A self-cleaning database file is one that simply overwrites records marked for deletion rather than deleting them. This would be easy to accomplish for small database files with the following commands: LOCATE FOR * IF .NOT. EOF RECALL ELSE APPEND BLANK ENDIF These few commands will look for a deleted record. If one is found it will be RECALLed and then the new data can be entered in its place. If a deleted record is not found then a new record is added. If the database file is of any size at all, the use of LOCATE can take much too long for this direct approach to be useful. Taking advantage of INDEXes and the FIND command can speed up the process considerably and make the self-cleaning process of an application virtually transparent to the user. To make use of INDEXing, add a status field to your database file. After INDEXing on the status field, it is a simple matter to FIND any record flagged for deletion and put the new data in its place. To exemplify, CREATE a database file with the following structure: STRUCTURE FOR FILE: Phone.DBF FLD NAME TYPE WIDTH DEC 001 Last C 018 002 First C 012 003 Phone C 014 004 Delstat C 001 ** TOTAL ** 00046 Then set up an index file INDEXed on Status: USE Phone INDEX ON Status TO Status To DELETE a record in this database file: DELETE REPLACE Delstat WITH "D" These commands tell dBASE II to DELETE the current record and also tells us that the record is marked for deletion by setting the Status field to "D." The following program demonstrates the basic routine you can use to APPEND records to a database file using the status field as a flag for FINDing deleted records. If a deleted record is found, the fields are blanked out and overwritten. * Program..: Add_rec.PRG * Author...: Brian Evans * Date.....: July 1, 1985 * Notes....: Demonstrates self-cleaning database files * by adding new records into deleted records. SET TALK OFF SET DELETED OFF ERASE USE Phone INDEX Status * ---Search for a record where the status field indicates that * it * ---is marked for deletion. STORE T TO is:true DO WHILE is:true FIND D IF # <> 0 * ---If a deleted record is found. RECALL * ---Clean out the fields. REPLACE Last WITH " " REPLACE First WITH " " REPLACE Phone WITH " " REPLACE Delstat WITH " " ELSE APPEND BLANK ENDIF * ---Add the new record in a custom data-entry screen ERASE @ 10,20 SAY " Last Name" GET Last @ 11,20 SAY "First Name" GET First @ 13,20 SAY " Phone Num" GET Phone PICTURE "(999)999-9999" READ CLEAR GETS STORE " " TO again @ 20,10 SAY "Add another record (Y/N)? " GET again PICTURE "!" READ STORE ( again <> "N" ) TO is:true ENDDO SET DELETED ON SET TALK ON CLEAR * EOF Add_rec.PRG