Contents
- Index
Visual FoxPro Database Information
Advanced Feature - Only to be used by a computer technician.
This database utility can help you correct database issues that can arise. Like all applications, if not properly maintained errors will popup.
We use this program for customers who have encountered corruption in their data (databases). The program can be used to VIEW and EDIT all of the database files utilized within the applications (systems).
The file necessary for setting up for Foxprun usage is contained in a self extracting program (DBC.EXE) which should be located in your \AutoData folder.
Go to a DOS Prompt, and to the appropriate folder as mentioned above, and execute the DBC program. This will extract the Foxprun program right into the folder where the data is located. This precludes having to type in long path names when using the required database files.
It is appropriate at this time, to comment on when this process should be used.
First, you must never start manipulating data, unless you have made a backup copy of the existing data.
Second, there are only certain times when the use of Foxprun is appropriate.
? When you have experienced a Numeric Overflow error condition.
? When a database becomes corrupt (garbage data).
? When databases are not in sync (detail transactions do not match the balance due).
? When you want to eliminate or modify the data in a certain database, i.e., resetting On Hand Quantity to zero in the Product.dbf.
NOTE: Within the Windows Versions of the program, there is the "Command Window" under Management | Customize | Transaction Settings | Security.
This Command Window allows execution of Visual Foxpro Commands. When using the Command Window, the first instruction to use is "SET STAT BAR ON". This provides a "status bar" to be displayed at the bottom of the screen showing what "record" is currently being accessed. This is beneficial when searching for records and/or manipulating the data.
FOXPRUN COMMANDS and HOW TO USE
After executing the command FOXPRUN DOT, you will be requested to "press any key to continue", this places you at the "command line" of FOXPRUN. After you USE and BROWSE a database you will notice that there is a MENU at the top of the screen. This gives you the necessary keystrokes for manipulating within the FOXPRUN program when you are viewing the data.
If the database that you want to view has already been identified, possibly via an error message in "Current Alias", then that is the database that you will want to use. Other problems may require several databases to be looked at.
It is not necessary to stipulate the "DBF" file extension, just the file name. Also remember, that if you use a file in FOXPRUN, you are using it "exclusively", meaning that no other program can use the file, therefore, all other machines must be out of the system.
In addition, it is only necessary to use the FIRST FOUR (4) POSITIONS of each command or Foxprun Key Words, i.e., BROWSE is BROW, DELETE is DELE, PRINT is PRIN and so on. Also, the command line is not available to the user if FOXPRUN is in BROWSE (BROW) Mode. A file can be selected, but no commands can be issued unless the record(s) are not visible on the screen. To issue commands against a file, if a file is in use, you must hit the "ESC" Key to get back to the command line. Typed and executed alone, the "USE" command removes a selected database, i.e., if the CUST file is in use and you type "USE" the file is released.
The commands that are most commonly used are the ones addressed in this technical note.
The following commands will be given in the sequence (as much as possible) in which you would normally use them. In addition, FOXPRUN commands should be considered case sensitive, meaning always use CAPITAL LETTERS.
COMMANDS.
Note: The following commands can be used from the Command Window
Item A. USE: Selects the database you want to work with.
Example 1: USE CUST <enter>
This selects the CUST.DBF database for use with the editor program.
Item B. BROWSE: Allows you to view the information contained in the database.
Example 1: BROW <enter>
This brings the entire database into a browse mode and available for viewing on the screen. To see fields that do not appear on the immediate screen, it is necessary to "SCROLL" across the screen by holding down the "Ctrl" Key and using the right arrow key to see the other fields. To return to the "home" position, you have to use the left arrow key with the "Ctrl" key held down.
Since the system is sensitive to actual "field names" within a file, the above command is sometimes necessary to get the actual field names that you want (need) to view. For instance, if you were going to SORT on a certain field, you must see exactly what that field name is in the file.
It is very inconvenient to have to scroll across the whole record each time you only want to view certain fields. Once you have identified the fields that you need to view, by "actual" field name, you can use the BROWSE Command in the following manner.
Example 2: BROW FIEL CUST_NUM, LAST_ACT <enter>
This command brings to the screen only the "fields" that you have identified. In this case you would only see the CUST_NUM and the LAST_ACT field on the screen, eliminating the need to scroll across the screen to find the required field. Many fields can be specified in this command and are required to be separated by commas.
Item C. SORT: Sorts a database by specified field(s).
Example 1: SORT ON CUST_NUM, INVOICE TO TEMP <enter>
When you SORT a database, the file will be sorted to a new "TEMP" database, i.e., the original file is still in its original sequence and the new file (sorted) is in the sequence of the fields that were used in the SORT command with new Record Numbers assigned.
This command is used for putting all records for a given field together at one point in the newly sorted file. For instance, if you needed to see all of one customer's transactions in the CASH_TRA file, you would sort on the CUST_NUM field. If you needed to have the INVOICE field in sequence within each customer, then "invoice" would be the second field specified in the command.
Note: If you have made required changes to the "sorted" TEMP File, and you want to use this file as the new, valid file; then after exiting the FOXPRUN Program you need to re-name the original file to a different name and re-name the TEMP File (the one you changed) to the original file name. Also, if records were added or deleted, to the new file, you must remember to re-index upon returning to the system.
If records were deleted, you might want to run the PACK Command (See Item E) to eliminate the deleted records from the file. It is not necessary to do this at this time as It will automatically occur the next time a Re-index, Clean and Pack is done.
Item D: INDEX: Sorts a file without changing the Record Number or Filename.
Example 1: INDE ON CUST_NUM <enter>
This method of sorting puts the file in the sequence of the stipulated field name of the selected file.
Item E. PACK: Removes records marked for deletion from the database.
Example 1: PACK <enter>
When executing this command all of the records that have been marked for deletion will be dropped from the file.
Item F. DISPLAY STRUCTURE: Display structure of a database to the screen.
Example 1: DISP STRU <enter>
(Displays to the screen)
It is not necessary to use "Display", except on rare occasions. Only when you have been manipulating data files on your own.
If a field is identified as a Character Field, the field may contain ALPHA or NUMERIC data. If defined as Numeric, it can only accommodate numbers (with or without Decimals). Decimals are counted in the field length. If defined as a DATE Field, then only DATES are used (mo/day/year).
Item G. MODIFY STRUCTURE: Allows modifying the structure of a database.
Example 1: MODI STRU <enter>
Database files have a structure, meaning that each field within the database is defined with a Field Name, further defined as a Character Field, Numeric Field or a DATE Field. These are further identified with the field length, and if Numeric, the number of Decimals.
If a field is identified as a Character Field, the field may contain ALPHA or NUMERIC data. If defined as Numeric, it can only accommodate numbers (with or without Decimals). Decimals are counted in the field length. If defined as a DATE Field, then only DATES are used (mo/day/year).
New fields may be added, or existing fields deleted, with this command. This should never be done unless data has been manipulated and this has messed up the structure.
When finished making required modifications, save the new structure by holding down the "Ctl" Key and hitting the "W" Key (Ctl, W).
Item H. LIST: Displays the fields (Field Names and Data) to printer or screen.
Example 1: LIST CUST_NUM, LAST_ACT TO PRIN OFF <enter>
(The OFF eliminates record number from printing)
Example 2: LIST CUST_NUM, TO PRIN FOR AMOUNT =< 10 OFF <enter>
Example 3: LIST CUST_NUM, AMOUNT
(Displays to the screen)
Use of this command to the screen, is limited, due to the fact that it would scroll too fast for viewing the data, unless there is limited data.
Item I. APPEND: Adds records from a database to the one currently being used.
Example 1: APPE FROM "database name" <enter>
If you have just converted a Product ASCII file into a Foxpro database format, and the name of the new file is NEWDBF.DBF, then you would want to append it to the existing Product.dbf in the system.
To do this, you would USE the Product.dbf File and then issue the command to append the Newdbf.dbf. This would add all the "records" from the Newdbf.dbf to the existing Product.dbf.
When appending records from one database to the one that you currently have open, the structures must be the same. The database that you are appending from does not have to carry "all" of the fields that are in the currently open database, but the fields must be in the same sequence in the file and the field names and size must be the same.
Item J. COPY: Copies records from a database to another for a specified condition.
Example 1: COPY TO TEMP FOR <fieldname> = <condition> <enter>
This is a seldom used command and the Operand condition can be "=, > or <". The Operand can be used singularly or in a combination form, i.e., possibly equal to (=) or equal to and greater than (=>) the condition given in the command. An example would be if you wanted to copy all the records that had a BAL_DUE equal to or greater than $5,000 to another file. One would use the following commands.
USE CUST
COPY TO TEMP FOR BAL_DUE => 5000
Item K. DELETE: Removes records that match the condition given.
Example 1: DELE ALL FOR BAL_DUE = 5000 <enter>
The above command line would mark all records that match the condition for deletion.
The Operand (the equal sign) can be used singularly or in combination form, i.e., possibly equal to (=) or equal to or greater than (=>) the condition given in the command.
Example 2: DELE NEXT xxxx <enter>
This form of the DELETE command marks for deletion, the number of records indicated in the command. It starts from the selected record (the record that was highlighted) and continues until the command is satisfied.
Item L. REPLACE: Replaces information in a field for a specified condition.
Example 1: REPL ALL BAL_DUE WITH 0 FOR BAL_DUE = 5000 <enter>
This command would search the entire file for any record with a Balance Due that equaled $5,000. When located, it would replace the $5,000 with $0.
The Operand (the equal sign) can be used singularly or in combination form, i.e., possibly equal to (=) or equal to or greater than (=>) the condition given in the command.
Example 2: REPL ALL <fieldname> WITH 0 <enter>
This is used more frequently, when the customer wants to reset the inventory quantity on-hand to zero. In this case the condition would not be used.
Example 3: REPL ALL <fieldname> WITH " " <enter>
This is used when the customer wants to reset a field to blanks.
Item M. QUIT: Allows you to exit the Foxprun Program.
Example 1: QUIT <enter>
Always release the file that you have been working with prior to issuing this command. Execute the "USE" Command at the command line prompt to accomplish this. This "QUIT" instruction should put the user back to a "C" prompt at one of the systems, i.e., Business, Auto, Video or Cleaner.
Item N. LOCATE: Locates a particular record (the first) of a given condition.
Example 1: LOCA FOR CUST_NUM = "xxxxxx" <enter>
This command is useful when you have sorted a file by the Customer Number (CUST_NUM) and want to go to that particular record in the sorted file. It will take you to the first record that matches the condition given in the LOCATE Instruction. After the LOCATE has found the first record, you would BROWSE the file (or the fields) to observe the information contained therein. This can be used whether you have used the SORT TO TEMP or the INDEX ON instruction.
Other useful simple commands
GO TOP ______ Takes the User to the Top of File (First Record).
GO BOTT _____ Takes the User to the Bottom of File (Last Record).
GOTO xxxx __ Takes the User to the Specified Record Number.
CHECKWID.FOX USE
Checkwid is used in conjunction with the Foxprun Program to correct databases with fields that have exceeded their capacity in field width.
If any field exceeds its width, a Numeric Overflow Error condition occurs. As an example, if the INVOICE Field (6 positions) goes to seven (7) digits, the field turns to Asterisks (******). This gives the error condition.
There are too many ways for this error to be created to cover in this document. The main thing is to consider what files may be damaged and what other files they may have affected.
The problem database may have been identified in the error message. Always try to get this information from the customer. After analysis of what, where and when the error occurred, let the Foxprun and Checkwid programs attempt to correct the files. Run the FOXPRUN program. At the command line prompt, type the following command.
DO CHECKWID
The system will ask you to "Enter the Name of the Database to Test". Supply the database name and Checkwid will go through the entire database, field by field, looking for the corrupted data. When it finds corrupted data it will correct the file by eliminating the asterisks.