FOXPRO / DBASE
What is a Database ?
Database is a file containing particular data in a special format so that it can be used, changed, formatted or questioned as per the need. Database consists of number of records.
Record :-
Record contains the information stored in Foxbase file. Each record contains several fields for storing your information. Eg. City, state and phone of one person may be contents of a record in database file. Each record has a Record Number RECNO( ).
Field :-
When a database file is created we tell Foxbase the criterias on which we want to store the information in each record. This piece of information bearing same type of information in each record is called a field.
Exa- While inputting information about a person we can use his Name, Address, City, Pin, State as separate fields which come together to form a record for one person.
There are FIVE types of fields in Foxbase.
1) Character :
In character filed we can input any character data consisting of letters, digits, special sings etc (ASCII 0 to 255 ) we can use maximum 254 characters in a character field.
2) Numeric :
To stored number we use numeric field type. Maximum we can use 20 digits in numeric field (including decimal point ). Numeric part can use signs (+ or - ) and a decimal point.
3) Data :
Foxbase allowes combination of charactors ( / ) and numbers in a date field. By default its size is 8 ( mm/dd/yy ) format.
4) Logical :
The items which are to be told in turms of yes or no true or false 0 or 1 are called logical fields. Width is 1 char.
5) Memo :
Memo is actually a character field with no upper limit of width. So if a lengthy information in character field is to be entered , use memo field. Memo field are saved as separate files on the disk. Default shown size is 10 chars.
Command :
A command is a Foxbase instruction. Which consists of VERB, additional qualifiers parameters conditions scope expressions etc.
Expression :
An Expression is a group of fields, variables, constants, functions and operators. Each expression returns answer in the same type as it is asked or as the operands are. For example. Adding two number results in a number.
Field List :
Some commands include optional field list which specifies which fields are to be included using commas.
Variable :
A variable is an entity which changes the value as assigned. Suppose A is a variable and we use A=10. Now the value of A wherever used as variable will be 10. Afterwards if we assign a new value such as A=20, Now the value of A is 20 and not 10.
Constant :
Constant is an entity which depicts a fixed item. Suppose we use A=10 in this A is a variable but a numeric value assigned to it is certainly a constant i.e. 10 is a numeric constant and it I not going to change its value. Names, Addresses, numbers, strings do not get changed. So they are constants.
Data :
Any information feed to the computer is a raw data. Whether it has any meaning or not. Data abruptly can be said to have two types. Numeric data and string data.
Numeric means numbers and the variable which store such numbers are called numeric variable. Same is the case for strings. Any entity enclosed in a question mark is a string, then whether it is a number or just any ASCII character. When such strings are assigned to a variable the variable becomes a string variable.
Eg. Numeric variable = Number or numeric expression
String variable = String or string expn.
Memory variable :
Any data has to be assigned to a variable. This value is stored in the main memory and the variable is said to contain the information. So these variable are called memory variables.
Operator :
In any expression, there is a group of variables or functions or constants of same type. Between these variable some action is to be taken, it is denoted be operator. There are two types of the operators Mathematical Operators and Relational Operators.
Mathematical Operators :
+ - Addition
- - Substraction
* - Multiplication
/ - Division
** or ^ Exponential
Relational Operators :
= is equal to
> is greater than
< is less than
>= is greater than or equal to
<= is less than or equal to
<> is not equal to
SCOPE :
Scope means specification of those records on which we want to take action are use command.
It can be
ALL All the records of the database
NEXT n Next n no. of records from current one
REST All the following records to the current.
FoxBase commands and usage with syntax
1. CLEAR -
Used to clear the screen or part of it. The CLEAR command clears the display screen. Another form of CLEAR command is CLEAR ALL. This command form closed files and clears any active memory variable.
2. DIR –
Used to list all .DBF files in the directory, it is also used to see DIR using wildcards.
3. CREATE –
Used to create a new Database file.
Syntax :
CREATE filename
This command allows us to create a database and define its structure, when we enter the command CREATE, computer will ask,
Enter the name of file
Here type name of database file, suppose we type the file name as a SAMPLE, if the file name defined is already exists it will display the following massage –
SAMPLE . DBF already exists, overwrite it ( y/n ) ?
It we enter Y the old file will be erased and if N it will abort the CREATE command. To avoid the massage Enter the name of file type CREATE command followed by file name.
4. LIST –
Used to see / view records, part of records on given conditions.
Syntax :
.LIST [field1,field2..] [FOR condition1 .AND/OR. Condition2]
This command displays the contents of a database on the screen. We can use LIST command by several ways –
LIST STRUC TURE, LIST STATUS, LIST MEMORY, LIST HISTORY, LIST OFF, LIST TO PRINT, LIST STRUCTURE TO PRINT, LIST FOR condition
5. DISPLAY-
Used to see/view current record or records meeting given conditions, but one screen full at a time.
Syntax :
.DISPLAY scope field list FOR conditions
.DISPLAY MEMORY Used to display defined memory variables
.DISPLAY STATUS for viewing the status information of Foxbase.
.DISPLAY STURCTURE for seeing the structure of current database file
(file in use)
.DISPLAY ALL Give list screen by screen.
6. USE –
Either to close a database or to open it.
Syntax :
.USE to close current file
.USE filename to open given file.
7. QUIT –
To Quit foxbase and return to DOS Prompt
8. EDIT –
Edit is a full-screen editing command to change the contents of fields and records from the current database file. Edit does not allow adding more records. FOR conditions and FIELDS can be used to edit specific fields only.
9. APPEND –
APPEND is used to add new record to the database file or to change the contents of previously typed records. APPEND command adds new records at the end.
10. BROWSE –
BROWSE is a full screen editing command used either to add new records or change the contents or previous records and fields. All the field given in the FIELD LIST meeting given FOR condition are shown horizontally on the screen. LOCK option is use to lock a particular field. On reaching the end BROWSE asks whether you want to add new records to add press Y.
11. CHANGE –
A full screen editing command used to alter the contents of current database file. With change we can use conditions according to which we want change and fields which are to be changed.
12. MODIFY STRUCTURE –
MODIFY STRUCTURE is used to make changes in the structure of the file. This involves changing the sizes of the fields (width). Shifting the fieldnames etc. To add new fields in between already used two fields go to the second field and press ^N to shift the following fields down and then type the information required for new field. DO NOT change the name of the field as the data will be lost.
13. GO/GOTO nn –
To go to a specific record in an open Foxbase file. The record pointer is moved to that rec.
Syntax :
.GO nn or .GOTO nn or .nn
14. SKIP –
To go to previous or following records. nn moves the pointer by skipping nn records from the current one. Using –nn goes back.
Syntax :
.SKIP nn/-nn
15.DELETE –
Delete is used to delete the current record or the records meeting the given condition. Delete simply marks the records with a * sign. It does not actually deletes rec.
Syntax :
.DELETE scope [FOR condition]
16. RECALL –
Recalling means disable the * mark of the deleted records. By recalling we can undelete a record or records. Recall unmark the * sign of record.
Syntax :
.RECALL scope [FOR condition]
17. PACK –
Packing means copying all he records except deleted to same file by overwriting the previous data. So, by PACKing the deleted records are actually deleted for ever.
Syntax :
.PACK
18. ZAP –
ZAP means to delete all the records from the Foxbase file. If file is ZAPped, only the structure of the database remains but the data inside it is lost for ever.
Syntax :
.ZAP
19. CLOSE ALL –
CLOSE ALL is used to close all open files All the databases, indexes, Screens, Views etc. are closed all at a time.
20. SUM –
Sum is used to add given conditioned numeric records to get a total (sum) at the end. A sum of all numeric field is displayed separately
Syntax :
.SUM scope [numeric filed list] [FOR condition]
21. AVERAGE –
Average is a arithmetic average of sum divided by number of records summed.
Syntax :
.AVERAGE scope [numeric filed list] [FOR condition]
22. COUNT –
Count means counting number of records meeting a particular condition or conditions and display their total at the end.
Syntax :
.COUNT [FOR condition]
23. LOCATE –
Locating is a process to find the records meeting given condition and to go to that record ( move record pointer).
Syntax :
.LOCATE scope [FOR condition]
24. CONTINUE –
Continue is a additional command to LOCATE once a record is found by a locate command which is meeting given condition. The record pointer moves to it and process ends. To carry on the finding forward, continue is used. It can be used as many times as needed till its scope lasts (EOF).
25. ?
Print. ? is the way to ask Foxbase a question about the following the expression. The answer is display on the device after calculating the following part.
Syntax :
? a - just as PRINT A
?20+30 - to print sum of 20 & 30
?DATE ( ) - to print o/p of function.
26. ??
Print. ?? differs from ? as ?? displays the output the last cursor location. Whereas ? displays the answer the new line.
27. ERASE –
To delete a file ERASE command is used.
Syntax :
.ERASE filename.ext
28. DELETE FILE –
Used to delete a file
Syntax :
.DELETE FILE filename.ext
29. SORT –
Sorting means arranging in a sequencial form SORT in Foxbase is used to arrange the records according to any one of the files. Each time SORT creates named file as output without changing the current file. The created file contents the records in sorted format.
Syntax :
.SORT ON field TO filename
30. SORT ON SORT –
Sort on sort is needed when the contents of the fields on which the file is sorted contents the records having similar items. In this condition the first coming record gets precedence although the following should come before. In such conditions SORT ON SORT in used which tell that if such condition of same contents of the fields occurs, the record is sorted according to the next given field to avoid error.
Syntax :
.SORT ON field1,field2 TO filename
31. INDEX :
Indexing means arrange the records in a file in alphabetical sequence. Index command is more useful than the sort command as index command does not create a new file each time, instead it changes the sequence of records to form a index. When an INDEX is active the listings are shown according to it. Suppose and index on field1 is in use. The LIST command will display the records in alphabetical sequence of field1 only. One document can have as much as nine indexes, which can be opened at a time. But always one index is active ( called Master).
To create an index :
.INDEX ON field TO ndxname
To activate an Index file :
.SET INDEX TO ndxname
To open more ndxfiles while opening database :
.USE dbffile INDEX ndx1,ndx2,ndx3 …..
To activate an open Index file
.SET ORDER TO nn
Where nn is the number of index file used in
.USE dbf INDEX n1,n2,n3,…. Command.
32. FIND –
To find a text string in an indexed file.
.FIND string
33. CLOSE ALL -
To close all open files.
34. COPY FILE -
To copy a file from one location to other.
.COPY file.ext To Newfile.ext
COPY STRUCTURE -
To copy the structure of a database to a new file.
.COPY STRUCTURE TO newdbf
35. RENAME –
To change the name of a file from OLDNAME to NEWNAME.
.RENAME oldfilename.ext To newfilename.ext
36. REPLACE –
To replace the data from the records and fields that meet the given condition. The previously typed data gets lost and the new data is overwritten.
.REPLACE field WITH data_item FOR condition
37. SET commands –
SET command are used to toggle some switches in Foxbase. Each set is used to either switch on or off. General Format of Set On / Off command is.
.SET xxxx ON or .SET xxxx OFF
A) SET BELL ON / OFF -
To switch on/off the bell sound.
B) SET PRINT ON / OFF -
To set the printer on/off. When set on all the o/p is sent to printer, until we use SET PRINT OFF.
C) SET CENTURY ON / OFF –
Generally in data the year is written in two (97) digits (CENT OFF) to make it four digit (1997 ) use SET CENT ON.
D) SET HISTORY ON / OFF -
When we use commands on the command line, the commands remain stored sequentially in the memory so that they can be used afterwards. This is called the history. To use this feature of Foxbase Set History ON. To make it Disable use
.SET HIST OFF.
E) SET, ESCAPE ON / OFF -
When some information is scrolling on the screen the we want to stop it suddly, we can use escape to break the execution in between. When ESCAPE is ON this is possible.
F) SET HEADING ON / OFF -
Whenever we see a LIST the name of the fields are shown at the first line as the heading. To turn it off Use SET HEAD OFF.
G) SET SAFETY ON / OFF -
In Foxbase sometimes a file is already exiting and we want to make a file of the same name, when safety is on Foxbase will prompt that a file with the same name is existing, Do you want to overwrite it? This is a safety so that the file is not accidentally arased.
H) SET TALK ON / OFF -
Whenever we define a variable in Foxbase, by default, it displays the value again. This is just like a repetition or echo. To switch it off, use SET TALK OFF.
I ) SET STATUS ON / OFF –
The line which is showing the name of the opened file, drive etc. at the bottom of screen is called the STATUS BAR. To make it On/Off use SET STAT command.
J ) SET HELP ON / OFF –
Whenever there is a wrong command or the syntax error, by default Foxbase asks us a question whether we want help or not . Pressing Y shows us the corresponding help item. But some time an experienced used finds this troublesome to get every time the help massage. So, to switch this off use SET HELP OFF.
38. SET TO commands –
Set to commands are different than that of the set commands. Set to commands are needed to set configure a they have only these two operation but SET TO command have more than two functions.
A ) SET DEVICE TO PRINT / SCREEN –
To ask Foxbase to send output to which device. If it is screen, the o/p is shown on the monitor otherwise if it is PRINT the o/p is shown on monitor as well as it is printed on the printer.
B ) SET COLOR TO fgc,bgc –
Using this command one can set the foregrounds colors and the background color of the monitor / display The color names are denoted by –
Red - R Green - g
Blue - B white - w
Bright - + Light - -
Blinking - *
Syntax :
.SET COLD to R+,W
To set Bright Red for foreground and White for background.
39. CREATE/ MODIFY SCREEN –
To create a data entry screen in Foxbase we use CREATE SCREEN command. This command allows us to select required fields to be included in the data entry. It then opens a Black Board on which we can format the screen as we like, we can places massages, Boxes (single / double ) , fields anywhere we wants them to be. After a screen is completed we save it and when we use EDIT /CHANGE or APPEND command the corresponding active screen is displayed which looks much better than the usual data entry screen.
Syntax :
.CREATE/MODIFY SCREEN scrflname
After this command is used Foxpro opens a screen containing menus. Select first of all SETUP. It you have already created a database file. Choose SELECT DBF. Otherwise choose CREATE DBF. Using SELECT allows you to select one database file on which you want to create screen. Now the option LOAD FIELDS is available to you. Choose it . A list of all fields from that database is shown. Choose as many fields as you want to be taken in the screen display.
Press enter and the field name is selected. To unselect a selected field, press enter on that field again. Once all the required fields are selected use F10 function key to go the blackboard. The selected fields are shown on the BB one by one, one below the other. Now using arrow keys and Space Bar, Enter keys move the lines and words or field templates to the place you need them. Type text if you want to do so. Use now and then the boxes either single or double.
To draw boxes or lines press F10 then moving arrows highlight OPTIONS then select SINGLE or DOUBLE bars as you need. Now you are again in the Black Board. Move the cursor to required position from where you want to start drawing box or line. Now press Enter. Then move cursor to opposites coroner of the box or other end of line. Again press enter for ending. A box is displayed on the screen. To change the box parameters now. Move the cursor to any of the sides of the box. Press enter to hold the side and move arrow keys to stretch or compress it. At the end press enter again to place the box or line.
To modify the structure which consists changing field specifications, adding new fields, changing width, type or adding the picture format, giving upper and lower limit, adding template etc. Select appropriate option from it.
At the end when all the modifications in the Black Board are done and the screen is ready. Press F10. Then choose EXIT form the menu. Use SAVE to save the screen or ABANDONE to cancel the screen changes. We return to the DOT Prompt.
Use SET FORMAT TO scrname to select a screen you need.
Use EDIT, CHANGE, APPEND command to display the screen.
40. CREATE/MODIFY REPROT –
Reporting is a feature of Foxbase. Reporting means to create a report form to send or to keep as record. It is prepared by selecting appropriate fields and some field expressions, such as addition of two fields, or any mathematical equation between them.
The reporting involves setting of page to give output, summing or numerical fields, groups etc. and show as a report on page. It has headings to various columns other than usual field names. The output is shown in tabular format.
To do reporting first of all choose the file on which we want to make report using the USE filename command. Then use CREATE REPORT rptname command. Foxbase opens the screen for reporting, which contains various heads such as OPTIONS, GROUPS COLUMNS, LOCATE and EXIT.
First of all select OPTIONS. Then from it fill the appropriate information regarding the Title of the page, page width in columns, Left margin Right margin etc. We can tell the page length also. If we want to print the report in double spaces choose it and make it yes. Specify before or after page ejection and tell whether to leave Plain Page (No).
Then come to the next columns of COLUMNS without changing anything from the groups (if we want to change the options of group, for group reporting do that). From the COLUMNS menu press enter on CONTENTS. A right triangle shown, now either type the field name or press F10 to display the list of valid fields. We can enter an expression also in this place which calculates for you. Then press enter. Now Use down arrow to highlight HEADING. Press enter, a box appears. Type the heading for this column. When over press PGDN. Now a width for that column is shown by default. If we want to change it we can type the number. It this is a numerical field, specify whether we want to total this columns at the end or not. Now one column information is over. Use PGDN for next column. And go on in same manner.
To change the contents of any of the entered columns go to LOCATE and choose appropriate column to be changed. When all columns are prepared as we wanted. Use EXIT and SAVE the report. The report is ready.
To see the report on the screen use REPORT FORMAT rpt command. A report in the prescribed manner will be shown on the screen. To take the o/p on to the printer Use
.REPORT FORMAT rptname TO PRINT
41. CREATE / MODIFY LABEL –
If we have prepared a database containing the information about NAME, ADDRESS, CITY, etc. This information can be helpful to prepare beautiful LABELS in a very easy way. Generally we require Mailing Labels to be printed on printer. Following is the procedure to prepare LABELS.
To prepare LABELS use .CREATE LABEL lblname command. When this command is used, Foxbase comes out with a screen having menus to specify label criteria’s.
Firstly select options. There are various predefined label sizes available with Foxbase. If you want to use them you can try it. Otherwise we can specify our own format for the labels. Choose label width in columns. Choose Label height ( means how many lines in one label), then choose left margin. Specify how many lines to be left blank between two rows of labels. Then specify how many spaces (columns) to be left free between two vertical labels. Lastly specify how many labels we want to print across (in one line).
Now come to contents. The label lines are sown here (as per the height value we have given). Type the name of the fields or expressions here. To choose field name from the list we can use F10 key to load field. Specify expressions for all the lines. Now preparation of labels is over. Select EXIT and SAVE the label file.
To display the labels according to the these specification Use. LABEL FORMAT lblname command. The labels in predefined format will be shown on the screen. To have the output on the printer use .LABEL FORMAT lblname TO PRINT command.
42. FUNCTION –
1) Numeric Functions :
A) ABS (expN)
Absolute value of expression. Absolute value means take only positive values irrespective of its sign.
.?ABS(-10) -o/p 10
B) INT (expN)
Integer. Take integer part of value. Actually from a number the decimal part is cut.
.?INT (10.99) -o/p 10
C) LOG (expN)
Logarithm. Take Natural LOG of expn.
.?LOG (20) -o/p 1.30102999566
D) MAX (expN1,expN2)
Minimum. Find minimum from the given two numbers.
.?MIN (123.123.343.34) -o/p 343.34
E) MIN (expN1,expN2)
Minimum. Find minimum from the given two numbers.
.?MIN (123.1234 -o/p 123
F) MOD (expN1,expN2)
Modulus or remainder find the remainder of expN1 divided by expN2.
.?MOD (10/3) -o/p 1
G) ROUND (expN1,expN2)
Round of the figure and display with expN2 decimal places.
.?ROUND (10.579.2) -o/p 10.58
H) SQRT (expN)
Find Square root of given number.
.?SQRT (625) -o/p 25
I) VAL (expC)
Find Value of string. To extract a number from a string which is starting with a number VAL function is used.
.?VAL (‘100 Rupees only’) -o/p 100
2. String Functions –
A) ASC (expC)
Return ASCII value of the first character of the string.
.?ASC (‘Abcd’) -o/p 65 value of A
B) CHR (expN)
Return character from given ASCII code
.?CHR (65) -o/p A Code 65
C) ISALPHA (expC)
Return .T. if first character of the string is an alphabet (i.e. A-Z or a-z)
.? ISALPHA (‘this is a string’) -o/p .T.
D) ISLOWER (expC)
Return .T. if first character of the string is Lowercase letter.
.?ISLOWER (‘Shree Computers’) -o/p .F. not lowercase
E) ISUPEER (expC)
Return .T. if first char is an Uppercase character.
.?ISUPEER (‘Shree Computers’) -o/p .T. S is capital
F) LEFT (expC,expN)
Return string with expN character from the left side of string.
.?LEFT (‘Shree Computers’) -o/p ‘Shree’
G) LEN (expC)
Return length of given string.
.?LEN (‘India is my country’) -o/p 19 ‘the length’
H) LOWER (expC)
Return string converting uppercase character to lowercase.
.?LOWER(‘India’) -o/p ‘India’ ‘lowercase’
I) REPLICATE (expC,expN)
Return string by replicating given string expN number of times.
.?REPLICATE(‘This’.3) -o/p ‘ThisThisThis’
J) RIGHT (expC,expN)
Return string having expN number of characters from right of expC string.
.?RIGHT(‘Example’.5) -o/p ‘ample’ ‘from right’
K) SPACE (expN)
Return string with expN number of spaces.
.? ‘This’+SPACE(5)+ ‘is’ -o/p ‘This is’
L) UPPER (expC)
Return string converting expC to Uppercase Characters.
.?UPPER(‘India’) -o/p ‘INDIA’
3. Database Functions –
A) BOF( ) - Beginning of file Returns .T. if the record pointer is before the first record.
B) DBF( ) - Return name of the currently used database file.
C) EOF ( ) - Returns .T. if the record pointer is at the end of the last record in current database.
D) FIELD (expN) - Returns N’th field name from current database file.
E) FOUND ( ) - Returns .T. if the last Search operation found matching condition.
F) NDX (expN) - Returns name of the N’th open Index file currently.
G) RECCOUNT ( ) - Returns Total number of records in current database file.
H) RECHO ( ) - Returns the record number at which the record pointer is at that time.
I) RECSIZE ( ) – Returns value in bytes of the space required for storing one record in that database.
4. Date Functions
A) CDOW (expD) - Returns name of the Day of Week from given date expression.
B) CMONTH (expD) – Returns name of the month from given date expression.
C) CTOD (expC) – Converts given expc (string) to date format.
D) DATE ( ) – Returns the system date.
E) DAY (expD) – Returns the number of day of month from given date format.
F) DOW (expD) – Returns the numbers of day of week from given date expression.
G) DTOC (expD) – Converts given date expression to character format.
H) MONTH (expD) – Returns number of month from given date expression.
I) YEAR (expD) – Returns the year from given date expression.
5. Environment Functions
A) COL ( ) – Returns the Column number where currently the cursor is.
B) OS ( ) – Returns the name of operating system which is being used currently.
C) PCLO ( ) – Returns the Column number of printer head currently.
D) PROW ( ) – Returns the number of row where printer head is currently.
E) ROW ( ) – Returns the number of row where the cursor is currently.
F) TIME ( ) – Returns the system time.
G) VERSION ( ) – Returns the version number of Foxbase.