ftp.barnyard.co.uk/2015.02.ftp.barnyard.co.uk.tar/ftp.barnyard.co.uk/cpm/walnut-creek-CDROM/MBUG/MBUG075.ARC/DBASE.DOC

Text File | 1979-12-31 | 18KB | 433 lines

.pl70
                         USING dBASE II

                    by GRAHAM COLLINS - MUGSA


INTRODUCTION

     There are a number of database programs available which
allow you to store, alter, and retrieve files of information.
The difference between these programs and word processors is that
database programs allow you to manipulate the stored information
in many different ways, e.g. sorting and indexing.

WHAT_IS_A_DATABASE?

     The term DATABASE refers to a collection of information
which is organised in a structured way, e.g. address records:

Record         Field 1        Field 2               Field 3
No.             (Name)       (Address)            (City, State)

0001           John Smith     163 Main St         Adelaide, S.A.
0002           Bill Jones     21 West St          Melbourne, Vic
0003           Robin Hood     44 Sherbrooke Av    Perth, W.A.

     Any database can contain 1 or more records.  Each record can
contain up to 32 fields of information.

     Different types of fields are allowed:

     1.  Character fields - these can contain text (i.e.
"strings" of characters)

     2.  Numeric fields - these can contain numbers, including
decimals, but no alphabetical characters or spaces.

     3.  Logical fields - these can contain T (true), F (false),
Y (yes), or N (no).

     You are going to set up a database file on some of the
wineries in the Barossa Valley (see Table 1, next page).

STARTING_UP

     Turn on the computer, and insert the dBase-II system disk
into drive A.  After the CP/M prompt (A>) comes up, type: DBASE-
II.  You may either enter the date, or just press RTN.

     At this stage you should see the dBase-II prompt which is: .

LOGGING_ON_TO_DRIVE_B

     Insert your CP/M data disk into drive B and type: SET
DEFAULT TO B.  This informs the dBase-II program that files
should be stored and retrieved from the disk in drive B.
SETTING_UP_A_RECORD_STRUCTURE

     When the prompt: . reappears, type: CREATE, and in response
to the question on the screen, type WINRYLST as your file name
(note that the maximum length of a file name is 8 characters).

     Now dBase-II will ask you to set up your record structure.
Call field 001 Winery.  It is a character field, and if you
examine Table 1 you will see that the longest name is 32 charac
ters, so that is the length you will use.  There are no decimal
places.  So you should enter: Winery,C,32.  Use the same proc
edure to enter fields 2 (Address), 3 (District), and 4 (Phone).

     When your record structure is set up it should look like:

Field     Name, Type, Width, Decimal Places
001       WINERY,C,32
002       ADDRESS,C,18
003       DISTRICT,C,13
004       PHONE,N,6

     Instead of entering field 005, press RTN to exit, and press
Y to take the option of entering data straight away (N would take
you back to the dBase-II prompt).  The screen will now be set up
ready to accept information.

ENTERING_THE_RECORDS

     Type in the information in Table 1 (don't worry too much
about any typing mistakes, there are some deliberate ones
anyway).  Notice that when the number of characters entered
equals the length of the field, the cursor goes to the next field
without you having to press RTN.

Table 1:  List of wineries to enter into dBase-II file called
WINRYLST

BAROSSA SETTLERS, TRIAL HILL RD, LYNDOCH, 244017
EDEN SPRINGS WINE ESTATE PTY LTD, KING ST, WILLIAMSTOWN, 246404
OLD STOCKWELL CELLARS WINERY, DUCKPOND RD, NURIOOTPA, 622136
BONNEYVIEW WINES PTY LTD, STURT HWY, BARMERA, 882279
LEO BURING PTY LTD, PARA RD, TANUNDA, 632184
SEPPELT B & SONS, MILL ST, SEPPELTSFIELD, 632626
MOCULTA WINE CO, TRURO RD, MOCULTA, 639065
LE VIGNE, SEPPELTSFIELD RD, MARANANGA, 622880
WARDS GATEWAY CELLAR PTY LTD, BAROSSA VALLEY HWY, LYNDOCH, 244138
MOUTADAM VINEYARD, LOW EDEN RD, EDEN VALLEY, 641101
SEMMLER BROS, LYNDOCH VALLEY RD, LYNDOCH, 244327

SAVING_THE_RECORDS

     When you get to record 0012, press RTN to end the entry of
data.  The red light should show on drive B to indicate that the
file is being saved.  If this doesn't happen, type: USE WINRYLST.

SEEING_WHAT'S_ON_THE_DISK

     To see what files are on your disk, type: DISPLAY FILES.
You will see on the screen a list of all data base files (DBF)
currently stored on the disk.  WINRYLST should be one of them.



EXAMINING_AND_EDITING_THE_RECORDS

     Now that you have set up a database with 11 records, you can
manipulate the data.  If you haven't already typed: USE WINRYLST
then do it now.  Then clear the screen by typing: ERASE.  (If you
had not typed SET DEFAULT TO B at the start of this session, you
could still bring up WINRYLST by typing: USE B: WINRYLST).

     To list all of the records in your file, type: LIST.  If you
don't want the record numbers to show up during a listing, type
LIST OFF.

     The command EDIT allows you to edit selected records in the
database currently in USE.  The address of Mountadam Vineyard is
really HIGH EDEN RD not LOW EDEN RD.  This change needs to be
made to that record.  Mountadam is record number 10 so type:
EDIT 10

     The full entry for record 10 will appear.  Use the following
cursor commands to bring the cursor to the right field:

     CTRL X moves the cursor down to the next field.
     CTRL E moves the cursor up to the next field.
     CTRL D moves the cursor ahead one character.
     CTRL S moves the cursor back one character.

(Note the similarity of these commands to those in Word Star).

     Correct the entry by overtyping, and press CTRL W to finish
the edit.  Use the same procedure to change the address of Eden
Springs Wine Estate Pty Ltd from KING ST to QUEEN ST.

     If you have any other typing errors that need to be
corrected, the following commands might be useful:

     CTRL G to delete a character
     CTRL Y clears an entire field
     CTRL V allows characters to be inserted
     CTRL R moves back one record
     CTRL C moves forward one record.

     You should experiment with these commands.

     To save all the changes to your disk, and bring up the
revised file, type: USE WINRYLST.  When the red light goes out on
drive B, and the dBase-II prompt reappears, type: LIST (or LIST
OFF) to check the records.

SORTING_THE_RECORDS

     One of the great advantages of dBase-II is its ability to
sort on various fields.  For example, suppose you want to sort on
telephone numbers.  Type: SORT ON PHONE TO WINPHONE.  This
command gets dBase-II to sort the records into order on telephone
numbers, and then puts them into a new file called WINPHONE.
     Type: DISPLAY FILES and notice that WINPHONE has been
created and stored on the disk.  To see what is on the new file,
type USE WINPHONE, and then type: LIST.

Repeat this procedure to get new files called WINNAME, WINADRES,
and WINDIST.  Use the command DISPLAY FILES to check on the new
files that you have created.

     Another important use of dBase-II is to retrieve information
selectively.  For example, suppose you want to list all of the
wineries in Lyndoch.

SELECTIVE_RETRIEVAL_OF_INFORMATION

     Make sure the file WINRYLST is in use.  Type: LIST FOR
DISTRICT='LYNDOCH'.  Records 1, 9, and 11 will appear on the
screen.

     DBASE-II has a command to allow you to search for all
occurrences of a string within a field.  For example, suppose you
wanted a list of all of the wineries in your file with limited
liability.  Type:

                      LIST FOR 'LTD'$ WINERY

     The $ sign indicates to dBase-II to search for the indicated
string in the WINERY field.

     This search command is very powerful, and allows you to
specify more than one string.  For example, suppose you want to
search for all wineries which were situated on a highway and also
had limited liability.  Type:

             LIST FOR 'LTD'$ WINERY.OR.'HWY'$ ADDRESS

(The stops either side of the word OR are very important!)

     The same technique can be used to search for telephone
numbers within a certain range.  For example, type the following:

                     LIST FOR PHONE>600000

     This brings up 7 records.

     Finally, a single telephone number can be displayed by
typing LIST FOR PHONE=xxxxxx (insert the extension number you
want in place of xxxxxx), or you can search for a particular
address by typing LIST FOR ADDRESS='TRIAL HILL RD' (in this case,
ADDRESS was declared by you to be a character field when you set
up the database, so it must be enclosed in single quotes).

ADDING_MORE_RECORDS

     The APPEND command is used to add records to a database.  As
an example, you are going to add another winery to your database
WINRYLST.  The data for this is:

CHATEAU DORRIEN WINERY & TOURIST CENTRE, STURT HWY, NURIOOTPA,
622850.

     A problem now arises.  The Winery field in your database is
32 characters in length, but the new name is 39 characters.  This
means that you will have to modify the structure of WINRYLST.
dBase-II allows you to do this, but modifying_the_structure
of_a_database_deletes_all_of_the_records_within_it.  Therefore,
the records should first of all be copied into a temporary file.

Use the following procedure:
1.  .COPY TO TEMP
2.  .MODIFY STRUCTURE, then type Y to proceed
3.  When the structure is displayed, make changes using the normal
    cursor control keys.
4.  Press CTRL W to save the new structure
5.  .APPEND FROM TEMP
6.  .DELETE FILE TEMP

     You can now type: DISPLAY STRUCTURE to check that the new
structure has a Name field of 39 characters.  Type APPEND, and
add the new record.

     List the records, and save by typing USE WINRYLST.  Now
update the sorted files by recreating WINNAME, WINADRES, WINDIST,
AND WINPHONE.  Make sure these files are correct by bringing up
each in turn, and listing them.

THE_BROWSE_COMMAND

     dBase-II has a command called BROWSE which lets you move
through the contents of a database, examining, editing, deleting,
etc.  Call up WINNAME and type: BROWSE.  All of the records will
be displayed.  Move the cursor with CTRL E and CTRL X, and watch
the record number at the top of the screen.  If you want to, you
can edit any record in the normal way.

     You may only want to examine names and telephone numbers.
In this case, type CTRL W to exit from BROWSE, and then type:

                  BROWSE FIELDS WINERY,PHONE

     You will get an alphabetical listing of names with their
telephone numbers.  Type CTRL W again.

INDEXED_SORTING

     The SORT command in dBase-II allows you to sort on a
particular field.  For sorting on more than one field, there is
the INDEX command.  For example, suppose you wanted to index
WINRYLST on name and district fields.  These then become the
key_fields.  Type the following:

                  INDEX ON DISTRICT+WINERY TO WNDEX

     This tells dBase-II to put the districts into alphabetical
order, and then to arrange the wineries into alphabetical order
within each district.  To check that the index file is on the
disk, type:

                    DISPLAY FILES LIKE *.NDX.

     To see what is on the indexed file, type:  USE WINRYLST
INDEX WNDEX.  This instructs dBase-II to use the index file WNDEX
created from the database file WINRYLST.  Now type: LIST (or:
LIST OFF).

     Notice that when wineries occur in the same district (the
primary field), their names (secondary field) are in alphabetical
order.  You can see that if you were constructing a database of
records on any subject , you would easily be able to arrange the
data in a variety of ways.

     The BROWSE command can be used on an index file to allow you
to move through the file and do some editing using the commands
mentioned previously.  Type: BROWSE.  You should see only one
record because the file pointer moved to the bottom when you did
the previous LIST.  Type CTRL W to get out of BROWSE, and then
type: GO TOP to instruct the file pointer to go to the top of the
file.  Now type: BROWSE again, and all of the records will be
displayed.

     Type CTRL W to get out of BROWSE, and type LIST FOR DISTRICT
='LYN'.  This command will enable you to get a listing of all of
the wineries in Lyndoch without having to type the entire
district name.

DELETING_RECORDS

     Sometimes you need to get rid of records from a database.
For example, suppose you wanted to remove LE VIGNE.  The DELETE
command is used to mark records for deletion, and the PACK
command then removes them completely.

     Type: USE WINRYLST to bring up your main file and display
the records with LIST OFF.  LE VIGNE is record number 8 so type:
DELETE RECORD 8

     Now type LIST OFF again and note that LE VIGNE is now marked
for deletion.  If you change your mind at this stage and decide
to keep LE VIGNE, you can type RECALL RECORD 8.

     Let's assume that you haven't changed your mind, so type:
PACK

     Now type LIST OFF and note that LE VIGNE has been removed
completely and permanently.

PROGRAMMING_WITH_dBASE_II

     dBase-II can be programmed to carry out a series of
procedures automatically.  This can be illustrated by two simple
programs.

     Delete WINPHONE by typing DELETE FILE WINPHONE.  Then type:
MODIFY COMMAND PHONE

     This instructs dBase-II that you are going to set up a
command program called PHONE.CMD.  When the screen clears, type
in the following (don't type the explanations on the right-hand
side):
SET TALK OFF                  - turns off dBase-II prompts
ERASE                         - clear screen
USE WINRYLST                  - put datadase WINRYLST into use
SORT ON PHONE TO WINPHONE     - create a file call WINPHONE
                                containing records sorted on
                                telephone numbers.
USE WINPHONE                  - put database WINPHONE into use
LIST OFF                      - list the records
CLEAR                         - close files and reset variables
SET TALK ON                   - restore Dbase-II prompts
RETURN                        - stop operation of command file
                                and return to dot prompt

     Press CTRL W to save the command file onto your disk.  Now
type DO PHONE.  When the procedure is finished, check that a new
file called WINPHONE has been added to the disk.

     A second example of programming with dBase-II will allow you
to add new records to your WINRYLST file automatically.  Type
MODIFY COMMAND WINRYADD and then type the following program
(don't include the explanations on the right-hand side).

*WINRYADD PROGRAM                  - lines with * are not executed
ERASE                              - clear screen
SET TALK OFF                       - turn off dBase-II prompts
USE WINRYLST                       - put database WINRYLST into use
STORE T TO RERUN                   - store "T" in memory and label the
                                     location where stored as "RERUN"
DO WHILE RERUN='T'                 - do the following as long as RERUN
                                     is storing "T"
APPEND BLANK                       - put a blank record on the end of
                                     WINRYLST

@ 7,10 SAY'ENTER THE NAME OF THE WINERY 'GET WINERY
                                   - print 'ENTER THE WINERY' on
                                     the screen at row 7, column 10
@ 9,10 SAY'ENTER THE ADDRESS            'GET ADDRESS
@ 11,10 SAY'ENTER THE DISTRICT           'GET DISTRICT
@ 13,10 SAY'ENTER THE TELEPHONE NUMBER   'GET PHONE
READ                               - read data into the blank record
@ 16,10 SAY'DO YOU WANT TO ADD MORE WINERIES (Y OR N)'
WAIT TO CHOICE                     - wait for user to enter "Y" or "N"
IF (CHOICE)='N'                    - )if user enters "N", then
STORE F TO RERUN                   - )change RERUN to "F"
ENDIF                              - end of the IF loop
ENDDO                              - end of the DO loop; if RERUN
                                     stores "T" then repeat the
                                     program, otherwise go to the
                                     next statement
CLEAR                              - close files and reset variables
SET TALK ON                        - restore dBase-II prompts
RETURN                             - stop operation of command file
                                     and return to dot prompt

     When the program has been entered, type CTRL W to save it, and
execute the commands by typing: DO WINRYADD and enter any new records
that you want to.

     When you have finished entering records, and the dBASE II prompt
has returned, bring up the WINRYLST file and examine it to see the new
records that you have just added.

PRINTING_THE_RECORDS

     To do any prdNting with Dbase-II, the printer is turned on by
typing SET PRINT ON, and likewise the printer is turned off by typing
SET PRINT OFF.

     As an example, bring up the index file by typing USE WINRYLST
INDEX WNDEX.  Then type SET PRINT ON and type LIST OFF.  The records
will be sent to the printer.  When you have finished, type SET PRINT
OFF.

     Type: QUIT to end the session.
 AME) = "Q" .OR. $(LNAME,1,1)=" "
        STORE F TO TRUE
    ENDIF
    SET INDEX TO NAME
    STORE T TO FIRST

    **** LOOP2 OF SUBROUTINE