ARCHIVE SERIES PART 6 : FIND,SEARCH,CONTINUE COMMANDS
I continue my coverage of ARCHIVE with some of fun commands that allow us to find things in a database. Before getting into that I want to present an updated version of a chart giving some time comparisons between QLs and PCs. Some may remember this chart from the April 1990 issue of UPDATE. I recently had a chance to time a QL equipped with a Gold Card and thought others might find the comparisons interesting.
The chart was made by running my ARCHIVE program. DBEasy, on both the QL and PC and timing the various tasks. The PC and QL versions of DBEasy are written in the respective OL and PC versions of the ARCHIVE language and the code is very similar. This should not be considered the last word in comparisons because there are some differences in the software. Still it is nice to have numeric comparisons. The numbers are time in seconds.
DBEasy QL PC XT+ AT AT QL GOLD Task 7Mhz 4Mhz 8Mhz 10Mhz 20Mhz 16Mhz ------------------------------------------------------ Load Up 69 94 57 48 10 22 Find "xxx" 15 17 11 9 2 4 Step 10 58 50 19 12 8 20 records Display 17 20 7 5 2 4 New Page Sum on 13 9 4 2 1 2 population Order by 13 18 9 7 1 6 country Locate 4 4 2 2 1 1 'SWEDEN' Select 19 24 12 7 2 3 'EUROPE'
Note that the QL/Gold Card is about 4 times faster than a regular QL. Also a QL/Gold Card is pretty comparable and sometimes faster than a 10Mhz AT machine. The Gold Card makes the QL a very respectable computer by current standards of processor power and many think QDOS is still the best operating system around. Before the Gold Card, it was easier to abandon the QL for a PC. Now it is much harder to decide. There are strong arguments both ways as Al Feng brought up last issue. Some like myself will work in both worlds. I don’t have my own Gold Card yet but will get one as soon as finances allow.
FINDING THINGS IN A DATABASE
Locating needed information in a database is extremely important. The main reason we structure our information in database form is so we can find it again quickly on demand. ARCHIVE provides two means of ‘slow’ searching for information – FIND and SEARCH. To use either one we specify the command with a condition or value to match on and ARCHIVE will start at the beginning of the database testing record by record until a match is made or the end of the file is reached. The match becomes the current record. We are also provided with an associated command, CONTINUE, which will start up the last FIND or SEARCH (whichever was most recent) from the next record after the current record (not the beginning). It is a way to continue a SEARCH or FIND after a match so the next match can be found. There is also a numeric function provided, FOUND(), which is 1 or 0 depending on whether the most recent FIND, SEARCH, or CONTINUE had a successful match.
PREPARATION
In the description below I will underline what you should enter to participate in the examples. In preparation start up ARCHIVE and access the supplied GAZET_DBF with the LOOK command and display it.
look "{}gazet"<ENTER> {}=device, mdv1_, etc
display<ENTER>
FIND COMMAND
The find command is a very simple and very thorough way to find text in a database. Its drawbacks are its speed and its indiscriminate nature. Since it must compare all text in the database when trying to make a match, it takes time. If the database is small then find works very well. If the database is large and what you are looking for is near the end then it could take some time before the FIND makes a match. The FIND command is not case sensitive (ignores upper and lower case differences). It does not work on numeric fields. It has the form :
find (text in quotes or text variable)<ENTER>
Try these examples with the GAZET database :
find "london"<ENTER> find <ENTER> ARCHIVE puts quotes paris<ENTER> let a$="south"<ENTER> find a$<ENTER> use variable for text continue<ENTER> find next "south" find "xxx" ><ENTER> something not there print found()<ENTER> see found()=0 display<ENTER> restore database display
SEARCH COMMAND
The SEARCH command is a little more complex to use than the FIND command but can be more specific and maybe faster. It is still a slow way to find things in a large database. The SEARCH command cares about upper and lower case in text but case sensitivity can be suppressed if desired. The SEARCH command has the following form:
search (numeric expression)<ENTER>
When a search is initiated ARCHIVE goes to the beginning of the file and checks record by record until the numeric expression is true (nonzero) and stops or until it reaches the end of the database. The numeric expression is usually a logical expression (evaluating to zero or nonzero) involving one or more fields of the database. Because complex logical expressions can be used in the SEARCH command it is a very important database tool. Try the examples below.
search continent$="africa"<ENTER> fails print found()<ENTER> equals 0 africa not upper case display<ENTER> restore database display search continent$="AFRICA"<ENTER> now works print found()<ENTER> equals 1 verifies success display<ENTER> restore screen search continent$="AFRICA" and pop>20<ENTER> continue <ENTER> find next one <F5><ENTER> save on typing <F5><ENTER> look for more
As you can see we can fairly rapidly find all countries in africa which have a population of more than 20 million. SEARCH unlike FIND also works with numeric fields.
search pop>50 and pop<70<ENTER>
I would recommend becoming familiar with the ARCHIVE functions as they allow much more flexibility with the SEARCH command and many other commands. Look up each function used below in your ARCHIVE manual to learn more about it. If you see an unfamiliar expressing followed by something in parenthesis then you have found an ARCHIVE function. ARCHIVE does not allow user defined functions so the only ones that exist are listed at the end of the ARCHIVE section of your QL manual. ARCHIVE functions evaluate either to a text value (string) or a numeric value. Numeric values can be used as logical type where zero is false and nonzero is true. Below I illustrate some common uses for functions within the SEARCH command.
Suppress case sensitivity search lower(continent$)="africa"<ENTER> or search upper(continent$)="AFRICA"<ENTER> Search for text within field search instr(lower(languages$),"bantu")<ENTER> while found():continue:sprint:endwhile<ENTER> Use string splicing search continent$(1)="A"<ENTER>
Note that if a record had no entry for the continent$ field we would get an error since continent$(1) does not exist for that record. This would find any empty continent$ fields:
search continent$=""<ENTER> or search len(continent$)=0<ENTER>
CONTINUE COMMAND
As mentioned above the CONTINUE command allows for continuing on after a SEARCH or FIND match so the next match can be found. If for some reason you change your current record after something was found and then did a CONTINUE, ARCHIVE will begin the CONTINUE on the record following your current record (not the record found). You can repeat continue as many times as desired to work your way through the database. After you reach the last match your current record will not change because the CONTINUE could not make any more matches. You have no direct evidence of a SEARCH, FIND, or CONTINUE failure except that the current record doesn’t change. The value of the FOUND() function always indicates the outcome by its value if you check it. FOUND()=0 then failure or FOUND()=1 success.
Next time fast finds with ORDER and LOCATE. Until then Happy Archiving!
BREAK INTO DATABASE COMPUTING WITH DBEasy
DBEasy is my ARCHIVE front-end program for painless database work
Featured in a 4 page review by Bryan Davies in the May 91 QL World. Only $21.95 ppd in the US & Canada or $24.50 ppd elsewhere
A $99.00 SPECIAL FOR THOSE WHO WORK WITH BOTH QLs AND PCs
PSION’s PC-FOUR : QUILL, ABACUS, EASEL, ARCHIVE for the PC with manual and tutor disk. Plus both the QL and PC versions of my DBEasy program included free on 5.25″ or 3.5″
Order from : Wood and Wind Computing, RR3 Box 92, Cornish, NH 03745 603-675-2218