After you’ve designed your database, you can build the database through the interface or with the language. You might want to add existing tables to your database, and then modify them to take advantage of the data dictionary features in Visual FoxPro. If you are working within a project in the Project Manager, you can add the tables as you create them.
For more information about creating a database for a multiple-user environment, see Chapter 17, Programming for Shared Access.
This chapter discusses:
When you create a database, you gather tables together into one collection and gain the benefit of data dictionary features.
A data dictionary gives you greater flexibility in designing and modifying the database, and frees you from having to write code to create field-level and row-level validation or to ensure the uniqueness of values within primary key fields. The Visual FoxPro data dictionary enables you to create or specify:
Some data dictionary features, such as long field names, primary and candidate keys, default values, field-level and record-level rules, and triggers, are stored in the .dbc file but are created as part of the process of building a table or view. For information about these features, see Chapter 7, Working with Tables, and Chapter 8, Creating Views.
To collect tables into a database, you need to create a database container to hold all of the objects such as views, connections, and stored procedures associated with the tables that make up your database.
To create a new database
-or-
For example, the following code creates and exclusively opens a new database called Sample:
CREATE DATABASE Sample
When you create a new database, it is empty, containing no associated tables or other objects. Adding a table creates links between the table file and the database container. The link information stored in the database about a table is a forward link. The link information stored in the table about the database container is the backlink.
Links specify the associations between a database container and tables.
You can use the following commands and functions to work with a database and its objects programmatically.
Commands and functions that manipulate databases and database objects
Each Visual FoxPro table can exist in one of two states: either as a free table, which is a .dbf file that is not associated with any database, or as a database table, which is a .dbf file that is associated with a database. Tables associated with a database can have properties that tables outside a database do not have, such as field-level and record-level rules, triggers, and persistent relationships.
You associate tables with a database by creating them within an open database, or by adding existing tables to a database. For information about creating new tables, see Chapter 7, Working with Tables.
To add a free table to a database
-or-
For example, the following code opens the testdata database and adds the orditems table:
OPEN DATABASE testdata
ADD TABLE orditems
You must explicitly add an existing free table to a database to make it a part of a database. Modifying the structure of a free table does not cause Visual FoxPro to add the free table to a database, even if a database is open when you issue the MODIFY STRUCTURE command.
You can associate a given table with only one database. However, you can use the data in an existing .dbf file without incorporating it into your database.
To access a table in another database
-or-
Use the “!” symbol to refer to a table in a database other than the current database. For example, if you want to browse the orditems table in the testdata database, you can type:
USE testdata!orditems
BROWSE
In the previous example, the testdata database is opened automatically for you when you issue the USE command, but Visual FoxPro does not set testdata as the current database. A database opened automatically, as in the previous example, is automatically closed when the table is closed, unless you open the database explicitly before closing the table.
For information about using a view to access information outside your database, see Chapter 8, Creating Views.
When you add a table to a database, Visual FoxPro modifies the table file’s header record to document the path and file name for the database that now owns the table. This path and file name information is called a backlink, because it links the table back to the owning database. The process of removing a table from a database not only removes the table and associated data dictionary information from the database file, but also updates the backlink information to reflect the table’s new status as a free table.
You can remove a table from a database through the interface or with the REMOVE TABLE command. As you remove the table from the database, you can also choose to physically delete the table file from the disk.
To remove a table from a database
-or-
-or-
For example, the following code opens the testdata database and removes the orditems table:
OPEN DATABASE testdata
REMOVE TABLE orditems
Removing a table from a database does not automatically delete the table file. If you want to both remove the table from the database and delete the table’s .dbf file from the disk, use the DELETE clause of the REMOVE TABLE command or the DROP TABLE command. For example, the following code opens the testdata database and deletes the orditems table from disk:
OPEN DATABASE testdata
REMOVE TABLE orditems DELETE
The following code also opens the testdata database, then deletes the orditems table without moving a copy to the Windows Recycle bin:
OPEN DATABASE testdata
DROP TABLE orditems NORECYCLE
If you move database files (.dbc, .dct, and .dcx), or a table associated with a database, the relative paths change and might break the backlinks and forward links that Visual FoxPro uses to associate database and table files:
You can reestablish links and update the relative path information to reflect the new file location.
To update links after moving a table or a database
For example, the following code opens the testdata database and displays dialog boxes that allow you to locate tables that are not in the locations contained in the database:
OPEN DATABASE testdata
VALIDATE DATABASE RECOVER
Tip If you want to use a table without taking time to reestablish the links for all tables in the database, you can open the table with the USE command. Visual FoxPro displays the Open dialog box to allow you to locate the owning database or delete the links.
For information on removing the backlink from a table whose owning database has been deleted accidentally from disk, see FREE TABLE.
You can create persistent relationships between tables in a database. Persistent relationships are relationships between database tables that are stored in the database file and are:
Unlike temporary relationships created with the SET RELATION command, persistent relationships do not need to be re-created each time you use tables. However, because persistent relationships do not control the relationship between record pointers in tables, you will use both temporary SET RELATION relationships and persistent relationships when developing Visual FoxPro applications.
In Visual FoxPro, you use indexes to establish persistent relationships between tables in a database. You create a persistent relationship between indexes rather than between fields, which enables you to relate tables based on a simple or a complex index expression.
To create a persistent relationship between tables
-or-
For example, the following command adds a one-to-many persistent relationship between the customer and orders table, based on the primary cust_id key in the customer table, and a new foreign key, cust_id, in the orders table:
ALTER TABLE orders;
ADD FOREIGN KEY cust_id TAG ;
cust_id REFERENCES customer
If you were to then examine the database schema in the Database Designer, you would see a line joining orders and customer, representing the new persistent relationship.
Indexes provide the basis for persistent relationships
The type of index tag or key determines the type of persistent relationship you can create. You must use a primary or candidate index tag or key for the “one” side of a one-to-many relationship; for the “many” side, you must use a regular index tag or key. For more information on index types and creating indexes, see Chapter 7, Working with Tables.
To delete a persistent relationship between tables
The width of the relationship line increases to indicate that you have selected the relationship.
-or-
Use the DROP FOREIGN KEY clause with the ALTER TABLE command.
For example, the following command deletes a persistent relationship between the customer and orders table, based on the primary cust_id key in the customer table, and a foreign key, cust_id, in the orders table:
ALTER TABLE orders DROP FOREIGN KEY TAG cust_id SAVE
Establishing referential integrity involves building a set of rules to preserve the defined relationships between tables when you enter or delete records.
If you enforce referential integrity, Visual FoxPro prevents you from:
You can choose to write your own triggers and stored procedure code to enforce referential integrity. However, the Visual FoxPro Referential Integrity (RI) Builder enables you to determine the types of rules you want to enforce, the tables on which you want the rules enforced, and the system events that will cause Visual FoxPro to check referential integrity rules.
The RI Builder handles multiple levels of cascading for cascade deletes and updates and is recommended as a tool for building referential integrity.
To open the RI Builder
When you use the RI Builder to build rules for your database, Visual FoxPro generates code to enforce relational integrity rules, then saves it as triggers that reference stored procedures. You can view this code by opening the stored procedure text editor for your database. For information about creating triggers programmatically, see “Using Triggers” in Chapter 7, Working with Tables.
Caution When you make changes to the design of a database, such as modifying database tables or altering indexes used in a persistent relationship, you should rerun the RI Builder before you use the database. Rerunning the RI Builder revises the stored procedure code and table triggers used to enforce referential integrity so that they reflect the new design. If you don’t rerun the RI Builder, you might have unexpected results, because the stored procedures and triggers are not rewritten to reflect your changes.
You can create stored procedures for the tables in your database. A stored procedure is Visual FoxPro code that is stored in the .dbc file. Stored procedures are code procedures that operate specifically on the data in the database. Stored procedures can improve performance because they are loaded into memory when a database is opened.
To create, modify, or remove a stored procedure
-or-
-or-
Each of these options opens the Visual FoxPro text editor, allowing you to create, modify, or remove stored procedures in the current database.
You can use stored procedures for creating user-defined functions that you reference in field- and record-level validation rules. When you save a user-defined function as a stored procedure in your database, the code for the function is saved in the .dbc file and automatically moves with the database if you relocate the database. Using stored procedures also makes your application more portable because you don’t have to manage user-defined function files separately from your database file.
Each Visual FoxPro database has Version and Comment properties. You can view and set these properties with the DBGETPROP( ) and DBSETPROP( ) functions.
For example, the following code displays the version number of the testdata database:
? DBGETPROP('testdata', 'database', 'version')
The value returned represents the Visual FoxPro .dbc version number, and is read-only. Using the same function, you can view the comment, if one exists for the database:
? DBGETPROP('testdata', 'database', 'comment')
Unlike the Version property, the Comment property can be set. Use the DBSETPROP( ) function to enter a description or other text that you want to store with the database.
To set the comment property on the current database
-or-
For example, the following code changes the comment for the testdata database:
? DBSETPROP('testdata', 'database', 'comment', ;
'TestData is included with Visual FoxPro')
You can also use the DBGETPROP( ) and DBSETPROP( ) functions to view and set properties on other database objects such as connections and views.
When you create a database, Visual FoxPro creates and exclusively opens a .dbc (DataBase Container) file. The .dbc file stores all the information about the database, including the names of files and objects associated with it. The .dbc file does not physically contain any top-level objects such as tables or fields. Rather, Visual FoxPro stores file path pointers to the tables in the .dbc file.
To examine the architecture of your database, you can browse the database file, view the schema, browse the database objects, validate the database, and even extend the .dbc file.
The database schema is a visual representation of the table structures and persistent relationships established in your database. The Database Designer window displays the schema of the open database.
To view the database schema
For example, the following code opens the testdata database and displays the schema in the Database Designer:
MODIFY DATABASE testdata
A database schema is a representation of the objects in a database.
From the Database Designer, you can use the Database toolbar to create a new table, add an existing table to the database, remove a table from the database, or modify the structure of a table. You can also create connections and edit stored procedures.
The database file contains a record for each table, view, index, index tag, persistent relationship, and connection associated with the database, as well as for each table field or view field having extended properties. It also includes a single record that contains all the stored procedures for the database.
For information about the structure of the .dbc file, see Table File Structure.
While the Database Designer provides a conceptual representation of the database schema, sometimes you might need to browse the contents of the database file itself. You can browse a closed database by issuing the USE command on the .dbc file. The following example opens a Browse window displaying the contents of the sales database in table form.
CLOSE DATABASE sales
USE sales.dbc EXCLUSIVE
BROWSE
Caution Don’t use the BROWSE command to alter the database file unless you are knowledgeable about the structure of the .dbc file. If you make an error while attempting to change the .dbc file you can invalidate the database and potentially lose data.
Each .dbc file contains a Memo field named User that you can use to store your own information about each record in the database. You can also extend a .dbc file to add fields to accommodate your own needs as a developer. Fields must be added to the end of the structure. You must have exclusive access to a .dbc file to modify its structure.
To add a field to a .dbc file
For example, the following code opens the Table Designer so you can add a field to the structure of Testdata.dbc:
USE TESTDATA.DBC EXCLUSIVE
MODIFY STRUCTURE
When you add a new field to a database file, begin the field name with “U” to designate it as a user-defined field. This designation prevents your field from conflicting with any future extensions to the .dbc file.
Caution Don’t change any existing Visual FoxPro- defined fields in a .dbc file. Any changes you make to a .dbc file could affect the integrity of your database.
Validating a database ensures that the rows of the database are storing accurate representations of the meta-data in the database. You can check the integrity of the current database with the VALIDATE DATABASE command.
To validate a database
For example, the following code uses and validates the .dbc file for the testdata database:
OPEN DATABASE testdata EXCLUSIVE
VALIDATE DATABASE
After creating a database, you might want to add it to a project if it isn’t already part of one. If your database is already part of a project, you can remove it from a project. Also, if you no longer need the database, you can delete it from the disk.
A database in the Project Manager
When you create a database with the CREATE DATABASE command, the database is not automatically a part of a project, even if the Project Manager is open. You can add the database to a project to make it easier to organize, view and manipulate database objects through the interface, as well as to simplify the process of building an application. You can add a database to a project only through the Project Manager.
To add a database to a project
You can remove a database from a project only through the Project Manager.
To remove a database from a project
You can delete a database from disk using the Project Manager or the DELETE DATABASE command.
To delete a database
-or-
For example, the following code deletes the sample database:
DELETE DATABASE sample
Always use one of the methods above to delete a database from disk. Using the Project Manager or the DELETE DATABASE command enables Visual FoxPro to remove backlinks to the database from the tables in a database. If you use another file manipulation utility to delete a database file, such as the Windows Explorer, these backlinks are not removed.
Note The DELETE DATABASE command does not delete the tables associated with a database from the disk; rather, the tables that were associated with the database become free tables. If you want to delete both a database and all its associated tables from disk, use the DELETETABLES clause with the DELETE DATABASE command.
You can have many Visual FoxPro databases on your system to meet organizational needs in a multi-user environment. Multiple databases offer the following advantages:
For example, you might have a sales database that maintains sales information used primarily by the sales force working with customers and another database that maintains inventory information used primarily by the buyers working with suppliers. At times the information needs of these groups will overlap. These databases can be opened at the same time and accessed at will but they contain completely different types of information.
Multiple databases can add flexibility to your system.
You can use multiple databases either by opening more than one database simultaneously or by referencing tables in a closed database. Once multiple databases are open, you can set the current database and select tables in it.
When a database is open, the tables and relationships between tables are controlled by the information stored in the open database. You can have more than one database open at a time. For example, you might use multiple open databases when you run multiple applications, each based on a different database. You might also want to open multiple databases to use information, such as custom controls, stored in a database that is separate from your application’s database.
To open more than one database
-or-
Opening a new database does not close any databases you’ve opened previously. Other open databases remain open, and the newly opened database becomes the current database.
When you open multiple databases, Visual FoxPro sets the most recently opened database as the current database. Any tables or other objects you create or add to the database become a part of the current database by default. Commands and functions that manipulate open databases, such as ADD TABLE and DBC( ), operate on the current database.
You can choose a different database as the current database through the interface or with the SET DATABASE command.
To set the current database
-or-
For example, the following code opens three databases, sets the first database as the current database, then uses the DBC( ) function to display the name of the current database:
OPEN DATABASE testdata
OPEN DATABASE tastrade
OPEN DATABASE sample
SET DATABASE TO testdata
? DBC( )
Tip Visual FoxPro might open one or more databases automatically when you execute a query or a form that requires the databases to be open. To be sure you are operating on the correct database, set the current database explicitly before issuing any commands that operate on the current database.
You can choose from a list of tables in the current database with the USE command.
To choose a table from the current database
The Use dialog box displays so that you can select a table to open.
For example, the following code opens the sales database and prompts you to select a table from the list of tables in the database.
OPEN DATABASE SALES
USE ?
If you want to select a table that is not associated with the open database, you can choose Other in the Use dialog box.
You can close an open database from the Project Manager or with the CLOSE DATABASE command.
To close a database
-or-
For example, the following code closes the testdata database:
SET DATABASE TO testdata
CLOSE DATABASE
Both options close the database automatically. You can also close databases and all other open objects with the ALL clause of the CLOSE command.
Issuing the CLOSE DATABASE command from the Command window does not close a database if the database was opened by:
In these circumstances, the database remains open until the Project Manager closes the database, or until the form using the database is closed.
Visual FoxPro uses the current database as the primary scope for named objects, such as tables. When a database is open, Visual FoxPro first searches within the open database for any objects you request, such as tables, views, connections, and so on. If the object is not in the database, Visual FoxPro looks in the default search path.
For example, if the customer table is associated with the sales database, Visual FoxPro would always find the customer table in the database when you issue the following commands:
OPEN DATABASE SALES
ADD TABLE F:\SOURCE\CUSTOMER.DBF
USE CUSTOMER
If you issue the following command, Visual FoxPro will first look in the current database for the products table.
USE PRODUCTS
If products is not in the current database, Visual FoxPro will look outside the database, using the default search path.
Note You can specify the full path for a table if you want to be able to access it inside or outside a database — for example, if you anticipate a change in the location of a table. However, you increase performance when you reference only the table name, because Visual FoxPro accesses database table names more quickly than names specified with a full path.
Database errors, also called “engine errors,” occur when run-time errors occur in record-level event code. For example, a database error occurs when a user attempts to store a null value to a field that doesn’t allow null values.
When a database error occurs, the underlying database engine that detects the error typically posts an error message. However, the exact nature of the error message depends on what database is being accessed — for example, the error messages produced by a remote database server (such as Microsoft SQL Server) will probably be different from those produced if a database error occurs in a local Visual FoxPro table.
In addition, engine-level errors are sometimes very generic, because the database engine has no information about the context in which a record is being updated. As a consequence, error messages produced by a database engine are often less useful to the end user of a Visual FoxPro application.
To handle database errors in a more application-specific way, you can create triggers using the CREATE TRIGGER command. The trigger is called when a record update is attempted (delete, insert, or update). Your custom trigger code can then look for application-specific error conditions and report these.
If you handle database errors using triggers, you should turn buffering on. That way, when a record is updated your trigger is called, but the record is not immediately sent to the underlying database. You therefore avoid the possibility of producing two error messages: one from your trigger, and another from the underlying database engine.
To create custom error messages using triggers