Shareware Grab Bag

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

/ Shareware Grab Bag / Shareware Grab Bag.iso / 003 / db3tips4.arc / DB3MISC < prev next >

Wrap

Text File | 1987-03-03 | 15.8 KB | 372 lines

Recycling dBASE Records (PC Magazine Vol 6 No 2 Jan 27, 1987 Power User) A system required that records be constantly appended into the "current activity" database, then later archived and deleted. PACKing out the obsolete records was time-consuming, and records were APPENDed every day. The solution is to reuse the unwanted records instead of throwing them away and replacing them with new ones. When a record has been archived and is no longer wanted, REPLace all the character fields with a null character and all the numeric fields with zero. Since the file is INDEXed, the empty key field(s) cause the recycled records to appear at the top of the file. When entering new information, first GO TOP and check if the key field of the record is blank; if not, no reusable record is available, and a new one must then be APPENDed. GO TOP IF <key field> = " " * Enter new data into this recycled record ELSE APPEND BLANK * Enter new data into a new record ENDIF Editor's Note: If you have a high turnover in a database (and normally USE it with an INDEX), recycling records in this way will save loads of time, not only because PACKing is less frequent, but also because it's faster to REPL data than to APPEND a new record. An additional advantage is that unwanted records are dispatched immediately instead of skulking around on death row, waiting for the file to be PACKed. Further, every time a file is PACKed, there's the danger of a power outage or some other glitch. If you will be recycling records interactively from the dot prompt, it might be convenient to create a memory variable to do the work, SAVE it in a .MEM file, and RESTore it for use the next day. As a simple example, suppose your file has 3 fields: Name(C,20), Course (C,6), and Year (N,4), and the file is INDEXed on Name. Entering this statement will create the memory variable: recycle=[REPL Name with "",Course with "",Year with 0] When you want to blank an obsolete record, simply position on it and enter: &recycle Because the Name field is now empty (that is, because dBASE will evaluate Name=" " as true), the index will position this record at the top of the file where you needn't even perform a FIND to locate it: just GO TOP. ----------------------------------------------------------------- Debug with DOTPROM (PC Magazine Vol 6 No 2, Jan 27, 1987 Power User) When debugging a menu-driven program, it's often useful to be able to step outside for a moment, simulate the "." prompt so you can issue commands interactively, and then resume program operation. The DOTPROM.PRG works perfectly for any command that does not use a macro ("&"). For the menu choices that call DOTPROM.PRG, display a quiet little "." at the right screen margin. When a "." is entered, the program is routed to issue the DO DOTPROM command, which clears the screen below line 16, issues a realistic-looking "." prompt, and awaits a dBASE command. Any command you enter is picked up as a memory variable: cuscom. If you enter nothing (i..e, simply hit Enter), you return to the calling program; otherwise, dBASE issues your command and returns the next "." prompt. ** DOTPROM.PRG @ 16,0 CLEA @ 17,79 cuscom="incoming" DO WHIL cuscom>" " ? @ ROW(),0 SAY [Enter any dBASE command] ?? [ (except one using "&") <CR>=Xit] ACCE ". " TO cuscom IF cuscom>" " SET TALK ON &cuscom SET TALK OFF ENDI ENDDO RETU Editor's Note: dBASE III Plus does provide a SUSPEND command that gives you the full power of a real "." prompt, but you must enter RESUME to restart your program. For earlier dBASE releases, there is no alternative to DOTPROM.PRG. With this method, you lose whatever is on the screen below line 16, but during debugging that's a small price to pay. ----------------------------------------------------------------- Clearing with Clipper (PC Magazine Vol 6 No 4 Feb 24, 1987 Power User) The dBASE CLEAR TO command, which clears a rectangle section of the screen, is especially useful for blanking out a screen area for menus, boxes, etc. Unfortunately, this feature is not supported by the Clipper Compiler. However, Clipper's BOX command offers a solution to the problem. To draw a box with Clipper, you specify the coordinates of the four corners and the eight ASCII characters to be used for the four corners and four sides. If you add an optional ninth character, Clipper will fill the inside of the box with it. If this ninth character is CHR(32), the ASCII space, the interior of the box is cleared. If all nine characters are specified as CHR(32), the corners and sides will be duly drawn with spaces and the ninth CHR(32) will fill the area. Thus, for example, what in dBASE III would be: @ 2,20 CLEAR TO 6,59 simply becomes @ 2,20,6,59 BOX REPLICATE(CHR(32),9) in Clipper's instructions. ----------------------------------------------------------------- Memo Field Diet Plan (PC Magazine Vol 6 No 4 Feb 24, 1987 Power User) If you're working with a database that contains a memo field, the normal dBASE III delete/pack commands will remove a record from the DBF file, but the space used by the associated DBT memo file is not removed. The problem is magnified when there is a lot of append/delete activity in your file. Your DBT file gets like the attic -- stuff goes in but nothing ever comes out. There is one way to remove the deleted memo space, however, and that is by using dBASE's COPY TO command. This is a typical command sequence: USE <filename> COPY TO temp FOR .NOT. DELE() ERASE <filename.dbf> ERASE <filename.dbt> RENAME temp.dbf TO <filename.dbf> RENAME temp.dbt TO <filename.dbt> Remember that any indexes associated with your fill will have to be rebuilt. ----------------------------------------------------------------- Zero Zapper (PC Magazine Vol 6 No 4 Feb 24, 1987 Power User) When downloading data from other systems (especially from main- frames), it is not unusual to find numeric fields packed with leading zeros. If the imported dBASE field is numeric, this is not a major problem, though it's awkward to read numbers preceded by zeros; and, when any dBASE operation is performed on them, dBASE strips them without even being asked. But when you want the import field to be a character type, real problems arise. Indexes get skewed because the leading zeros don't index the same as leading spaces. To strip leading zeros from numeric fields, use: REPL ALL <fieldname> WITH <fieldname> Character fields are more complicated. Assuming that the field being stripped is called Fld: SET CONS OFF DO WHIL .NOT. EOF() x=1 DO WHIL "0"=SUBS(Fld,x,1).AND.x<LEN(Fld) x=x+1 ENDDO REPL Fld WITH REPL(" ",x-1)+SUBS(Fld,x) SKIP ENDDO SET CONS ON Editor's Note: Because it employs the REPLicate functions, the program will work only with dBASE Plus. If you're using an earlier dBASE version, STOR a string of spaces with the same length as your target field before entering the first DO WHIL loop. Then, instead of the line containing the REPLicate function, substitute: REPL Fld WITH SUBS(slug,1,x-1)+SUBS(Fld,x) Actually, if the character field to be stripped contains only numeric characters, the leading zeros can be stripped with one command line. Assuming again that the field in question if called Fld and that its length is 8, REPL ALL Fld WITH STR(VAL(Fld),8) One word of caution. You can encounter mainframe data that not only fills in leading zeros, but also substitutes an alpha character among the numerics to indicate a negative value. Before using any of the above routines, make sure the imported numerics are all numeric -- otherwise your data will be corrupted. If you do encounter alpha characters mixed into the numeric data, the fix is a hassle and should be attempted only if you know what you are doing. It's weird to look at a freshly imported number-type field and see an alpha here and there. You know it can't possible be there, but it is. You know dBASE can't possibly put up with this nonsense, and you're right again. The steps to rescue this kind of data are: 1. Restructure your dBASE import file so that all the fields are character-type. Add an extra 1-byte flag field for each numeric where there are alpha characters. Then (with appropriate grumbling) reimport the raw data. 2. Mark the flag field where an alpha exists in the numeric data. If there aren't too many records, a visual scan may suffice. Otherwise, you'll need to write a short procedure to examine each byte, marking the flag field each time it hits something other than a number, space, or numeric sign. 3. Determine from the mainframe operator what the blasted code is -- for example, that an A in the number stream means -1 and B means -2. 4. Correct the data. You can use BROWse if there aren't too many corrections, setting a filter to see only the flagged records. Other- wise, another routine will be required to STUFF in the replacement number. 5. Then: REPL ALL Fld WITH -1*Fld for flagged fields. 6. As a last step, you might ask the mainframe operator if he can format his output differently next time. And you can judge what kind of person he is by whether he answers, "Sure!" or "Gee, I don't think we can do that." ----------------------------------------------------------------- Pause Plus (PC Magazine Vol 6 No 6 Mar 31, 1987 Power User) The pause routine in PC Mag Vol 5 No 20 for dBASE applications is very useful; however, different messages require different amounts of attention. Moreover, because different processors run at different speeds, a delay routine based on the execution time of a DO Loop is not very portable: a satisfactory delay for a PC running at 4.77 MHz will be almost halved by a 6-MHz AT; compile and run it on a Compaq 386 and you may not even see the message. One solution is to use the system clock for the timing of delays. The enhanced routine below passes two parameters: a message and the desired duration in seconds. For example, DO pause WITH message,3 The message will be displayed for 3 seconds in this case. By using teh system clock, we have time on our side. *** PAUSE.PRG PARA mess,dur ?? CHR(7) SET CONS OFF mess=CHR(26)+" "+mess+" "+CHR(27) @ 24,0 @ 24,(80-LEN(mess))/2 GET mess CLEA GETS cntl=0 DO WHIL cntl<dur time=TIME() DO WHIL time=TIME() ENDDO cntl=cntl+1 ENDDO @ 24,0 SET CONS ON RETURN Editor's Note: Unfortunately, dBASE reads the system clock only in whole seconds. Depending on where you land in the cycle, a "1-second" pause could be as short as .01 seconds or as long as 1.99. Hence, this routine will be a little unpredictable if 1-second displays are passed. By contrast, Nantucket's Clipper compiler offers a SECONDS() function accurate to 1/100 of a second -- just like the DOS system clock. Perhaps future releases of dBASE will, too. ----------------------------------------------------------------- Enhancing dBASE Reports (PC Magazine Vol 6 No 6 Mar 31, 1987 Power User) In along dBASE report, Group and Sub-Group headings don't stand out at all; they seem to get lost in the body of the report. However, you can embed printer control characters right in the dBASE .FRM file to make your headings as bold and brassy as you please. For example, suppose you are producing a report sorted by State and then by City, and you are using these as Gruop and Sub-Group fields on page 2 of the dBASE III Report Form Generator. Normally, you would enter the field names, State and City, as the Group and Sub-Group expressions, and the character strings, STATE and CITY, as the Group and Sub-Group headings. Assume further that CHR(14) turns on doublewide printing for one line, that CHR(27)+CHR(69) turns on emphasized printing, and that CHR(27)+CHR(70) cancels emphasized printing. To make your Group information print doublewide, just enter the following as the Group expression: CHR(14)+"STATE: "+STATE Note that the Group heading ("STATE: ") is incorproated as a character string within the Group expression, and both will print doublewide. To emphasize the Sub-Group similarly, enter this as the Sub-Group expression: CHR(27)+CHR(69)+"CITY: "+CITY+CHR(27)+CHR(70) In this case, the escape sequence, CHR(27)+CHR(70), is required to cancel emphasized printing -- otherwise, it will continue throughout the report. dBASE's .FRM file can be used in this way to send any other printer control characters and escape sequences you like; just remember to cancel them each time. The main Title and Column headings are more difficult because dBASE treats these strictly as character strings. The Alt key plus numeric keypad combination seems to work. ASCII characters 1-32 don't work, but when 128 is added to the desired ASCII number, the 128-160 character range produced the desired results. Editor's Note: Here are a few more suggestions. Group and Sub- Group headings will stand out even more if you insert a carriage return -- CHR(13)+CHR(10) -- at the end of the Group and/or Sub-Group expression. You can underline the headings either by using escape sequences or by inserting a carriage return followed by a string such as: "=======". With some experimentation, you can create an algorithm to replicate the underlining string so that it's exactly as long as the contents of your heading. To make your Group and Sub-Group expressions more readable to yourself, you may prefer to initialize some memory variables and use these instead of the actual escape sequences. For example, STOR CHR(27)+CHR(45)+CHR(1) TO uline Then, for your Group expression, enter: uline+"STATE: "+STATE instead of: CHR(27)+CHR(45)+CHR(1)+"STATE: "+STATE In some cases, you might want to enhance the contents of a whole column though this will slow your printer's output considerably. Again, you'll have to experiment to get the desired result. Since dBASE's HEADing option transmits escape sequences, you might substitute a HEADing in place of the regular Title. Another work- around to enhance the main Title takes advantage of the fact that in printing the report, the optional HEADing always precedes the main Title and the Group heading always follows it. Thus, for example, you could use the HEADing to transmit the main Title's sequence, then start the Group expression with the cancelling sequence. ----------------------------------------------------------------- Double Macro Function (PC Magazine Vol 6 No 6 Mar 31, 1987 Power User) While the dBASE macro substitution function (the "&" symbol) is very useful in itself for shortening code, you can get even more mileage from it by concatenating two macros. For example, STOR "mag" TO a STOR "name" TO b STOR "PC Magazine" TO magname ? a+b magname ? &a&b PC Magazine This powerful capability allows greater flexibility in array simulation and field selection routines. Keep it in mind when naming fields for any new database systems. Editor's Note: In simulated arrays or other complex routines, this routine lets you replace whole blocks of commands with a little DO WHILe loop. (But the code for double macros will make tough reading 6 months later.) dBASE II, dBASE III version 1, and dBASE III Plus all accept both the double macro and even the &a&b syntax.