If you want a custom, updatable data set for your application, you can use views. Views combine the qualities of tables and queries: like a query, you can use a view to extract a set of data from one or more related tables; like a table, you can use a view to update the information in the view and permanently store the results to disk. You can also use views to take your data offline to collect or modify data away from your main system.
This chapter covers creating and updating views programmatically, as well as setting properties to optimize the performance of your views. For more information about databases, see Chapter 6, Creating Databases. If you want more information about tables or indexes, see Chapter 7, Working with Tables. For more information about the View Designer, see Chapter 5, Updating Data with Views, in the User's Guide.
This chapter includes:
Because views and queries have much in common, your steps in creating a view are like the steps you take in creating a query. You choose the tables and fields you want to include in the view, specify the join conditions used to relate the tables, and specify filters to select specific records. Unlike in queries, in views you can also select how the changes you make to the data in a view are sent to the original, or base tables, from which the view is built.
When you create a view, Visual FoxPro stores a view definition in the current database. The definition contains the names of the tables used in the view, and selected fields and the settings for their properties. When you use the view, the view definition is used to build a SQL statement that defines the view’s data set.
For information about view properties, see Setting View and Connection Properties later in this chapter, and see DBGETPROP( ) or CURSORGETPROP( ).
You can create two types of views: local and remote. Remote views use remote SQL syntax to select information from tables on a remote ODBC data source. Local views use Visual FoxPro SQL syntax to select information from tables or views. You can add one or more remote views into a local view, allowing you to access information from Visual FoxPro and remote ODBC data sources in the same view. For information on accessing local and remote data in a single view, see Combining Local and Remote Data in a View later in this chapter.
You can create a local view with the View Designer or the CREATE SQL VIEW command.
To create a local view
-or-
-or-
For example, the following code creates a view containing all the fields in the products table :
CREATE SQL VIEW product_view AS SELECT * ;
FROM testdata!products
The new view name appears in the Project Manager. If you open the Database Designer, the view is displayed in the same manner as a table in the schema, with the view name in place of a table name.
In the previous example, the table name is preceded, or qualified, by the name of the table’s database and the “!” symbol. If you qualify the table name when you create a view, Visual FoxPro searches for the table both in the open database list, including the current and any non-current databases, and in the default search path for the table.
If you don’t qualify a table with a database name in a view definition, the database must be open before you can use the view.
Tip When you create or use a view in the Project Manager, the Project Manager opens the database automatically for you. If you subsequently use a view outside the project, you must open the database or ensure the database is in scope before you can use the view.
You can use macro substitution to store the SQL SELECT statement into a variable which you can call with the AS clause of the CREATE SQL VIEW command. For example, the following code stores a SQL SELECT statement into the variable emp_cust_sql, which is then used to create a new view:
emp_cust_sql = "SELECT employee.emp_id, ;
customer.cust_id, customer.emp_id, ;
customer.contact, customer.company ;
FROM employee, customer ;
WHERE employee.emp_id = customer.emp_id"
CREATE SQL VIEW emp_cust_view AS &emp_cust_sql
You can modify existing views in the View Designer using the Project Manager or the language. If you want to modify the view’s SQL string programmatically, you must create a new view. You can then save the new view definition and overwrite the existing view name. To modify a view’s properties, see Setting View and Connection Properties later in this chapter.
Tip In the View Designer, you can open an existing view, and then copy the read-only SQL string and paste it in your code as a shortcut in creating a view programmatically.
To modify a view in the View Designer
-or-
In the View Designer, you can use the Query menu or the View Designer toolbar to add a new table to the view. The following code displays product_view in the View Designer:
OPEN DATABASE testdata
MODIFY VIEW product_view
You can rename a view from the Project Manager or with the RENAME VIEW command.
To change the name of a view
-or-
For example, the following code renames product_view to products_all_view :
RENAME VIEW product_view TO products_all_view
The database containing the view must be open before you can rename the view.
You can delete a view definition from a database using the Project Manager or the DELETE VIEW command. Before deleting the view, make sure the database containing the view is open and set as the current database.
To delete a view
-or-
For example, the following code deletes product_view and customer_view from the database:
DELETE VIEW product_view
DROP VIEW customer_view
Note These commands have the same effect; DROP VIEW is the ANSI SQL standard syntax for deleting a SQL view.
To access related information that is stored in separate tables, you can create a view and add two or more tables, or you can modify an existing view by adding tables. To add the tables, you can use the View Designer or the CREATE SQL VIEW command. After adding the tables, you can expand your control of the view results using the join condition you define between the tables.
To create a multitable view
-or-
Just adding the tables to the CREATE SQL VIEW command produces a cross-product. You need to specify a join condition in either the FROM clause or the WHERE clause of the statement to match related records between the tables. If persistent relationships between the tables exist, they are automatically used as join conditions.
Typically, to define a join condition, you use the relationships established on the primary and foreign key fields between the tables. For example, you might want to find information on the orders, including information on the customer who placed the order. You can create a view using the Customer and Orders tables. You specify a join condition to compare values in the fields they have in common and, usually, return those that are equal. In the example, Customer and Orders both have a Customer ID field.
To define join conditions in a view
-or-
Inner joins specified in the View Designer and displayed in the SELECT - SQL statement
The following code creates the new view as described in the example above, using the FROM clause to specify the join conditions for the view:
OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_view AS ;
SELECT * FROM testdata!customer ;
INNER JOIN testdata!orders ;
ON customer.cust_id = orders.cust_id
The join condition has several aspects: the type of join, the fields to join on, and the operator for comparing the fields. In this case, which is an inner join, only rows from the customer table that match one or more records in the orders table are included in the result.
To change the results of the view to meet your specific needs, you can specify:
Specifying joins on fields other than the primary and foreign keys can be useful in specific instances, but are not used in most views.
By changing the comparison operator, you can control which records are compared and returned in a manner similar to a filter. For example, if you are using a date field in the join, you can use the comparison operator to include only records before or after a certain date.
For more information about the sequence of joins, see Defining Multiple Join Conditions later in this chapter.
Choosing a different join type allows you to expand your query results to include both records that match the join condition and those that do not. If you have more than two tables in your view, you can change your results by changing the order of joins in the FROM clause.
You can modify the join types in your view using the View Designer or the language.
To modify a join type
-or-
-or-
If you want to include non-matching rows in your results, you can use an outer join. For example, you might want a list of all customers and whether or not they have placed an order. In addition, for customers that have placed orders, you might want the order numbers included in the view. When you use an outer join, the empty fields of the non-matching rows return null values.
You can also use the language to create this view by using the following code:
OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_view AS ;
SELECT * FROM testdata!customer ;
LEFT OUTER JOIN testdata!orders ;
ON customer.cust_id = orders.cust_id
To control which non-matching records are included in your view, you can choose from the following join types.
| To | Use |
| Return only records from both tables that match the comparison condition set between the two fields in the join condition. | Inner join |
| Return all records from the table to the left of the JOIN keyword and only matching records from the table to the right of the keyword. | Left outer join |
| Return all records from the table to the right of the JOIN keyword and only matching records from the table to the left of the keyword. | Right outer join |
| Return matching and non-matching records from both tables | Full outer join |
If you create views or queries with more than two tables, you can change the results by the order your join conditions are specified. For example, you might want to find information on the orders, including information on the employee who made the sale and the customer who placed the order. You can create a view using the customer, orders, and employee tables and specify inner join conditions on the fields they have in common: customer and orders both have a customer ID field; orders and employee both have an employee ID field.
This view has the following underlying SQL statement:
OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_emp_view AS ;
SELECT * FROM testdata!customer ;
INNER JOIN testdata!orders ;
ON customer.cust_id = orders.cust_id ;
INNER JOIN testdata!employee ;
ON orders.emp_id = employee.emp_id
You can specify your join conditions in the WHERE clause; however, you cannot specify a join type as you can in joins in the FROM clause. For remote views, the join clause always appears in the WHERE clause.
The following code creates the same view as the previous example, using the WHERE clause to specify the join conditions for the view:
OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_emp_view AS ;
SELECT * FROM testdata!customer, ;
testdata!orders, testdata!employee ;
WHERE customer.cust_id = orders.cust_id ;
AND orders.emp_id = employee.emp_id
When you want to use data located on a remote server, you create a remote view. To create a remote view, you must first be able to connect to a data source.
A remote data source is typically a remote server for which you’ve installed an ODBC driver and set up an ODBC data source name. To have a valid data source, you must ensure that ODBC is installed. From within Visual FoxPro, you can define a data source and connections.
For more information about setting up an ODBC data source, see Chapter 1, Installing Visual FoxPro, in the Installation Guide.
In Visual FoxPro, you can create and store a named connection definition in a database, which you can then refer to by name when you create a remote view. You can also set properties of the named connection to optimize the communication between Visual FoxPro and the remote data source. When you activate a remote view, the view’s connection becomes the pipeline to the remote data source.
To create a named connection
-or-
-or-
For example, to create a connection in the testdata database that stores the information needed to connect to the ODBC data source sqlremote, you can enter the following code:
OPEN DATABASE testdata
CREATE CONNECTION remote_01 DATASOURCE sqlremote userid password
Visual FoxPro displays remote_01 as the name of the connection in the Project Manager.
Creating a named connection in your database does not use any network or remote resources, because Visual FoxPro doesn’t activate the connection until you use the view. Until you activate the connection, the named connection merely exists as a connection definition stored as a row in the database’s .dbc file. When you use a remote view, Visual FoxPro uses the named connection referenced in the view to create a live connection to the remote data source, and then sends the request for data to the remote source using the active connection as the pipeline.
You can optionally create a view that specifies only the name of the data source, rather than a connection name. When you use the view, Visual FoxPro uses the ODBC information about the data source to create and activate a connection to the data source. When you close the view, the connection is closed.
When you use the CREATE SQL VIEW command with the CONNECTION clause, you specify a name that represents either a connection or a data source. Visual FoxPro first searches the current database for a connection with the name you specified. If no connection with the specified name exists in the database, then Visual FoxPro looks for an established ODBC data source with the specified name. If your current database contains a named connection with the same name as an ODBC data source on your system, Visual FoxPro will find and use the named connection.
When you use a view whose connection login information is not fully specified, Visual FoxPro might display a data source-specific box that prompts you for the missing information.
You can control whether Visual FoxPro prompts you for information that was left unspecified at connection time.
To control the display of ODBC login prompts
-or-
You can use an existing named connection to create a remote view. You can see a list of the connections available in a database by using the Project Manager or the DISPLAY CONNECTIONS command.
To determine existing connections
-or-
For example, the following code displays the connections in the testdata database:
OPEN DATABASE testdata
DISPLAY CONNECTIONS
Once you have a valid data source or named connection, you can create a remote view using the Project Manager or the language. A remote view is similar to a local view, but you add a connection or data source name when you define the view. The remote view’s SQL statement uses the native server dialect.
To create a remote view
-or-
If you use the CONNECTION clause with the CREATE SQL VIEW command, you don’t need to include the REMOTE keyword. Visual FoxPro identifies the view as a remote view by the presence of the CONNECTION keyword. For example, if you have the products table from the Testdata database on a remote server, the following code creates a remote view of the products table:
OPEN DATABASE testdata
CREATE SQL VIEW product_remote_view ;
CONNECTION remote_01 ;
AS SELECT * FROM products
You can use a data source name rather than a connection name when you create a remote view. You can also choose to omit a connection or data source name when you use the CREATE SQL VIEW command with the REMOTE clause. Visual FoxPro then displays the Selection Connection or Data Source dialog box, from which you can choose a valid connection or data source.
After you create a view, you can open the Database Designer and see that the view is in the schema displayed in the same manner as a table, with the view name and icon in place of a table name and icon.
If you join two or more tables in the Remote View Designer, the Designer uses inner joins (or equi-joins) and places the join condition in the WHERE clause. If you want to use an outer join, the Remote View Designer provides only left outer joins, the syntax supported by ODBC. If you need right or full outer joins or just want to use a native syntax for a left outer join, create the view programmatically.
After you’ve created a view, you can use the view to display and update data. You can also modify the properties of a view to increase view performance. You treat a view like a table:
You can use a view through the Project Manager or the language.
To use a view
-or-
The following code displays product_view in a Browse window:
OPEN DATABASE testdata
USE product_view
BROWSE
When you use a view, the view is opened as a cursor in its own work area. If the view is based on local tables, Visual FoxPro also opens the base tables in separate work areas. The base tables for a view are the tables accessed by the SELECT - SQL statement you include in the CREATE SQL VIEW command when you create a view. In the previous example, using product_view automatically opens the products table as well.
Data Session window displays the view and its base table
When a view is based on remote tables, the base tables are not opened in work areas. Only the name of the remote view appears in the Data Session window.
When you access a remote data source, you’re accessing potentially massive amounts of data. You can limit the scope of data selected into your view to be just the records you need at a given moment. This reduces network traffic and increases the performance of your view. For example, if you want to view information about customers in a particular country and their orders, you gain performance by downloading only the records for that country, rather than all customers, into your view.
One method you can use to limit the scope of your view is to add a WHERE clause to your view’s SQL statement. If you want to look at the records for the customers in Sweden, you would create this SQL WHERE clause for your view:
SELECT * FROM customer ;
WHERE customer.country = 'Sweden'
This code would effectively limit the scope of your view by downloading records for just the Swedish customers, but it would also require that you create a separate view for each country, because the actual value for one country’s customer.country is hard-coded into your view’s SELECT statement.
You can limit the scope of a view without creating a separate view for each subset of records by creating a parameterized view. A parameterized view creates a WHERE clause in the view’s SQL SELECT statement that limits the records downloaded to only those records that meet the conditions of the WHERE clause built using the value supplied for the parameter. This value can be supplied at run time, or passed programmatically to the view.
In the case of the previous example, you can create one view that allows you to download records for any country, simply by entering the country’s name as you use the view.
To create a parameterized view
-or-
The parameter you supply is evaluated as a Visual FoxPro expression and the value is sent as part of the view’s SQL statement. If the evaluation fails, Visual FoxPro prompts for the parameter value. For example, if you have the customer table from the Testdata database on a remote server, the following code creates a parameterized remote view that limits the view to those customers whose country matches the value supplied for the ?cCountry parameter:
OPEN DATABASE testdata
CREATE SQL VIEW customer_remote_view ;
CONNECTION remote_01 ;
AS SELECT * FROM customer ;
WHERE customer.country = ?cCountry
You can supply a value for ?cCountry programmatically when you use the view. For example, you could type the following code:
cCountry = 'Sweden'
USE Testdata!customer_remote_view IN 0
BROWSE
Visual FoxPro displays the customer records for Swedish companies in the Browse window for Customer_remote_view.
View displaying records whose country matches the supplied parameter
Tip If your parameter is an expression, enclose the parameter expression in parentheses. This allows the entire expression to be evaluated as part of the parameter.
If your parameter is not a variable or expression, you might want to prompt the user to supply the parameter value by using a quoted string as your view parameter. When you create a view parameter using a quoted string after the “?” symbol, Visual FoxPro does not interpret the string as an expression. Rather, you are prompted to enter the value for the parameter at run time. For example, the following code creates a parameterized remote view that prompts the user to supply a value for the ?'my customer id' parameter:
OPEN DATABASE testdata
CREATE SQL VIEW customer_remote_view ;
CONNECTION remote_01 ;
AS SELECT * FROM customer ;
WHERE customer.cust_id = ?'my customer id'
USE customer_remote_view
When you use the view in the previous example, the View Parameter dialog box is displayed.
View Parameter dialog box prompts for the value in quoted string
After you enter a valid customer ID, Visual FoxPro retrieves the record that matches that ID. If you enter the value “ALFKI” in the previous example and then browse Customer_remote_view, you see the customer record displayed in the Browse window.
Browse window displaying record for cust_id ALFKI
By using a quoted string as a view parameter, you ensure that Visual FoxPro will always prompt the user for the parameter value.
You can open multiple instances of a view in separate work areas, just as you can open a table in more than one work area. Unlike tables, views fetch a new data set by default each time you use the view.
To open a view in multiple work areas
-or-
-or-
When you access the view programmatically with the USE command, you can choose to open another instance of a view without requerying the data source. This is particularly useful when you want to open a remote view in multiple work areas without waiting for data to be downloaded from a remote data source.
To use a view again without downloading data
-or-
The following code uses the NOREQUERY clause to display the cursor fetched from the first instance of product_remote_view in two Browse windows without requerying the remote data source:
OPEN DATABASE testdata
CREATE SQL VIEW product_remote_view ;
CONNECTION remote_01 ;
AS SELECT * FROM products
USE product_remote_view
BROWSE
SELECT 0
USE product_remote_view NOREQUERY
BROWSE
You can specify a session number with the NOREQUERY clause. If you don’t specify a session number, Visual FoxPro searches in all sessions. If an opened result set is found for the view, a cursor is opened again on the same result set. If no open result set is found, a new result set is fetched for the view. As is true for tables, if the view is not found, a new view cursor is opened for you.
If you want Visual FoxPro to search only the current session for an opened result set for your view, you can use the AGAIN clause. The following code displays product_remote_view in two Browse windows:
OPEN DATABASE testdata
USE product_remote_view
BROWSE
USE product_remote_view AGAIN in 0
BROWSE
When you use the AGAIN clause, Visual FoxPro looks for an existing view cursor in the current session, and opens an additional alias pointing to this view cursor. Opening another instance of a view with the AGAIN clause is the equivalent of issuing a USE with the NOREQUERY clause with the current session number.
You can open and display just the structure of a view with the NODATA clause of the USE command. This option is particularly useful when you want to look at the structure of a remote view without waiting to download data.
To open a view without data
The following code displays customer_remote_view without data in a Browse window:
OPEN DATABASE testdata
USE customer_remote_view NODATA in 0
BROWSE
Using a view with the NODATA clause always opens a new view cursor. The NODATA clause is the fastest way to get the view’s structure because it creates the smallest cursor possible on the remote data source. When you use the NODATA clause, Visual FoxPro creates a WHERE clause for the view that always returns a false value. Because no records on the data source can meet the WHERE clause condition, no rows are selected into the remote data source’s cursor. Your view is retrieved quickly because you’re not waiting for the remote data source to build a potentially large cursor.
Tip Using the NODATA clause is more efficient than using a MaxRecords property setting of 0 on your view or cursor. When you use the MaxRecords property, you must wait while the remote data source builds a cursor for the view containing all data rows that meet the view’s normal WHERE clause conditions. Rows from the full remote view cursor are then downloaded according to the setting of the MaxRecords property.
You can create local indexes on a view, just as on a table, using the INDEX ON command. Unlike indexes you build on a table, local indexes you create on a view are not stored persistently: they vanish when you close the view.
Tip Consider the size of your view’s result set when deciding whether to create a local index on a view. Indexing a large result set can take a long time, and slow down performance of your view.
For more information on creating indexes, see Chapter 7, Working with Tables, or see INDEX.
You can create temporary relationships between view indexes or between view indexes and table indexes with the SET RELATION command.
For better performance, when you use the SET RELATION command to relate a view and a table, make the view the parent and the table the child in the relationship. Making the table the child is more efficient because the structural index of the table is constantly maintained, quickly accessed, and can be used by the data environment to order the records. The index on the view must be rebuilt each time the view is activated and takes more time than the index on the table. An index on a view is not part of the view definition; so, if you use a data environment, the view cannot be the child because the index on the child has to exist as part of the definition, which views don’t support.
When you create a view, the view inherits property settings, such as UpdateType and UseMemoSize, from the environment cursor, or cursor 0 of the current session. You can change these default property settings by using the CURSORSETPROP( ) function with 0 as the cursor number. After the view has been created and is stored in a database, you can change view properties with the DBSETPROP( ) function. The changes you make to view properties in a database are stored persistently in the database.
When you use a view, the property settings stored for the view in the database are inherited by the active view cursor. You can change these properties on the active cursor using the CURSORSETPROP( ) function for the view cursor. Changes you make with the CURSORSETPROP( ) function are temporary. The temporary settings for the active view disappear when you close the view; the temporary settings for cursor 0 go away when you close the Visual FoxPro session.
Connections inherit properties in a similar fashion. Default properties for connection 0 are inherited when you create and store a named connection in a database. You can change these default property settings for connection 0 with the SQLSETPROP( ) function. After the connection has been created and is stored in a database, you can change connection properties with the DBSETPROP( ) function. When you use a connection, the property settings stored for the connection in the database are inherited by the active connection. You can change these properties on the active connection using the SQLSETPROP( ) function for the connection handle.
Both views and connections can use a named ODBC data source. If you use an ODBC data source in a view, the connection inherits properties from the session defaults.
The following diagram illustrates property inheritance for views and connections. The gray lines represent the flow of property inheritance; the black lines represent Visual FoxPro commands.
View and Connection properties and their inheritance
When you create a view, the DataType property for all fields in the view is set to a default value. The value is the data type letter (D, G, I, L, M, P, T, Y) for fixed-length data types and the letter followed by precision and scale parameters in parentheses (B(d), C(n), N(n,d)) for variable length types. This property is read-only for local views. For a list of default data types, see “Downloading and Uploading Remote View Data” in Chapter 21, Implementing a Client/Server Application.
You can modify the setting of the DataType property for the remote view field with the DBSETPROP( ) function as shown in this table.
| ODBC data type of remote field |
Possible data types in Visual FoxPro cursor |
| SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR |
Character or Memo1 (default); also General or Picture |
| SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY |
Memo (default); also Character, General, or Picture |
| SQL_DECIMAL SQL_NUMERIC |
Numeric or Currency2 (default); also Character, Integer, or Double |
| SQL_BIT | Logical (default); also Character |
| SQL_TINYINT SQL_SMALLINT SQL_INTEGER |
Integer (default); also Character, Numeric, Double, or Currency |
| SQL_BIGINT | Character (default); also Integer, Numeric, Double, or Currency |
| SQL_REAL SQL_FLOAT SQL_DOUBLE |
Double (default); the number of decimal places is the value of SET DECIMALS in Visual FoxPro; also Character, Integer, Numeric, or Currency |
| SQL_DATE | Date (default); also Character or DateTime |
| SQL_TIME | DateTime3 (default); also Character |
| SQL_TIMESTAMP | DateTime4 (default); also Character or Date |
1. If the ODBC field width is less than the value of the cursor property UseMemoSize, it becomes a Character field in the Visual FoxPro cursor; otherwise, it becomes a Memo field.
2. If the server field is a money data type, it becomes a Currency data type in Visual FoxPro.
3. The day defaults to 1/1/1900.
4. If the value in the SQL_TIMESTAMP field contains fractions of seconds, the fractions are truncated when the value is converted to a Visual FoxPro DateTime data type.
You can use the DataType property to choose a different data type than the default. For example, you might want to download a server timestamp field to Visual FoxPro, but the default data type mapping into a Visual FoxPro DateTime field would truncate any fractions of seconds stored in the server timestamp. You might use the DataType property to map the remote timestamp field into a Visual FoxPro character field to preserve the fractions of seconds.
The local base tables opened automatically when you use a view are not automatically closed when you close a view; you must explicitly close them. This is consistent with the SELECT - SQL command.
You update data in a view just as you would update data in a table. With a view you can also update the view’s base tables. Views are, by default, buffered with optimistic row buffering. You can change this to table buffering; for more information on buffering, see Chapter 17, Programming for Shared Access.
You can update data in a view through the interface or the language. The first step in updating view data is to make the view updatable. In most cases, the default property settings automatically prepare the view to be updatable, but updates are not sent to the data source until you instruct Visual FoxPro to do so by setting the SendUpdates property to On.
A view uses five properties to control updates. These properties are listed here with their default settings:
View Update Properties and Default Settings
| View Property | Default Setting |
| Tables | Includes all tables that have updatable fields and have at least one primary key field. |
| KeyField | Database key fields and remote primary keys on the table. |
| UpdateName | Table_name.column_name for all fields. |
| Updatable | All fields except the primary key fields. |
| SendUpdates | Defaults to the session default, which is originally set to false (.F.); if you change it to true (.T.), that becomes the default for all views created in the session. |
| CompareMemo | Defaults to true (.T.), means that memo fields are included in the WHERE clause and used for detecting update conflicts. |
While all five properties are required to update data, the SendUpdates property serves as a “master switch” controlling whether or not updates are sent. As you develop your application, you might set the SendUpdates property off and then configure the other properties to enable updates to the fields you want updated. When you’re ready to test your application, you can set the SendUpdates property on to start updates flowing.
In some more complex situations, the default update settings may not provide updates for a view you create through the language. To enable updates, look at the default settings for each of the update properties and adjust them as needed. You can also specify additional properties, such as UpdateType, WhereType, and so on, according to your preferences. For a complete list of view properties, see DBGETPROP( ).
To make a view updatable from the View Designer
The default settings for views you create through the View Designer usually prepare the view to be updatable; you only need to select the Send SQL Updates check box to turn updates on. You can further modify the tables, fields, SQL WHERE clause, and Update options as you desire.
To make a view updatable by setting view update properties
The following example lists the steps you would follow to specify the five view update properties programmatically:
Note The default View properties may supply all the information needed to update your view.
For example, if you have a view based on the customer table called cust_view , you could set the table name with the following function:
DBSETPROP('cust_view','View','Tables','customer')
Tip If a table appears as a qualifier in the UpdateName property but is not included in the default list for the Tables property, the table might not have a primary key field specified. Make the table updatable by adding the field you consider to be a key field to the KeyField property list, and then add the table to the Tables property list.
Using the same example, you could make cust_id the key field using the following code:
DBSETPROP('cust_view.cust_id','Field','KeyField',.T.)
Caution Be sure the key field(s) you specify define a unique key both in the base table you want to update and in the view.
DBSETPROP('cust_view.cust_id','Field','UpdateName',;
'customer.cust_id')
Tip To avoid creating synonym fields in your view, you can qualify field names in the SQL statement you use to build your view. Then use the Visual FoxPro UpdateName property of the view to map each qualified field to the correct base table and field name.
DBSETPROP('cust_view.cust_id','Field','Updatable',;
.T.)
DBSETPROP('cust_view','View','SendUpdates',.T.)
When you use DBSETPROP( ) to set properties on a view before you use the view, the settings are stored in the database and are used automatically whenever you activate the view. Once the view is active, you can use CURSORSETPROP( ) to change property settings on the active view. Property settings you set on an active view with CURSORSETPROP( ) are not saved when you close the view.
You can update multiple base tables from a view. When your view combines two or more tables, you set properties to ensure that only the many side of the view query is updatable.
Views are updated on a table-by-table basis. You must ensure that for each table accessed in a view, the key field set is a unique key for both the view result set and the base table.
To make a multitable view updatable
-or-
In most cases, the default values provided by Visual FoxPro prepare a multitable view to be updatable, even when you create the view programmatically. The following code example creates and explicitly sets properties to update a two-table view. You can use this example as a guide for customizing update property settings on a view.
Updating Multiple Tables in a View
| Code | Comments |
|
Create a view that accesses fields from two tables. |
|
Set the tables to be updated. |
|
Set update names. |
|
Set a single-field unique key for the Employee table. |
|
Set a two-field unique key for the Customer table. |
|
Set the updatable fields. Typically, key fields are not updatable. |
|
Activate the update functionality. |
|
Modify data in the view. |
|
Commit the changes by updating both the Employee and Customer base tables. |
Because views are stored in a database, you can create:
The data dictionary features for views are similar in function to their counterparts for database tables. However, you use the language rather than the Table Designer to create captions, comments, default values and rules for views.
Like default values for table fields, view field default values are stored in the database and are available each time you use the view. Visual FoxPro doesn’t compare the default values you create locally with any default values established on the remote data source. You must create default values that are acceptable for the data source.
To assign a default value to a view field
-or-
For example, you may want your application to limit the amount of merchandise a new customer can order until you’ve had time to complete a credit check and determine the amount of credit you’re willing to extend to that customer. The following example creates a maxordamt field with a default value of 1000:
OPEN DATABASE testdata
USE VIEW customer_view
?DBSETPROP ('Customer_view.maxordamt', 'Field', 'DefaultValue', 1000)
You might also use default values to populate some rows automatically for the user. For example, you might add a Grid control to an order entry form that is based on a remote view of an order line items table. The order_id field is the key field that maps each row of the Grid to its counterpart in the remote order line items table. Because the order ID for each row in the grid will be the same for one order, you can use a default value to save keystrokes by populating the order_id field automatically.
Tip If one of your application’s business rules requires that a field contain an entry, providing a default value helps to ensure that a particular field-level or record-level rule will not be violated.
You can create local versions of remote data source rules to:
Visual FoxPro doesn’t compare the rules you create locally with remote rules. You must create rules that are acceptable for the data source. If remote rules change, you must change your local rules to match.
To create a rule on a view field or row
-or-
For example, the following code creates a field-level rule on orditems_view that prevents entering a quantity of less than 1:
OPEN DATABASE testdata
USE VIEW orditems_view
DBSETPROP('Orditems_view.quantity','Field', ;
'RuleExpression', 'quantity >= 1')
DBSETPROP('Orditems_view.quantity','Field', ;
'RuleText', ;
'"Quantities must be greater than or equal to 1"')
You can also use the DBSETPROP( ) function to create row-level rules.
You can build a view that is based on other views. You might want to do this if you need a subset of the information available in multiple other views, or if you want to combine local and remote data in a single view. A view based on other views, or on a combination of local tables and local or remote views, is called a multitiered view. The view that combines other views is the top-level view. You can have multiple levels of views in between the top-level view and the local or remote base tables. When you use a multitiered view, the views on which the top-level view is based and any Visual FoxPro base tables used in the top-level or intermediate-level views appear in the Data Session window. Remote tables do not appear in the Data Session window.
You can combine local and remote data in a view by creating a new local view based on a local view and a remote view.
To create a view that combines local and remote data
-or-
For example, to create a local view that combines information from the local Employee table and the remote Orders table, you can use the following code:
OPEN DATABASE testdata
CREATE SQL VIEW remote_orders_view ;
CONNECTION remote_01 ;
AS SELECT * FROM orders
CREATE SQL VIEW local_employee_remote_orders_view ;
AS SELECT * FROM testdata!local_employee_view, ;
testdata!remote_orders_view ;
WHERE local_employee_view.emp_id = ;
remote_orders_view.emp_id
When you update data in a multitiered view, the updates go down one level, to the view on which the top-level view is based. If you want to update the base tables on which a multitiered view is built, you must issue a TABLEUPDATE command for each view in the structure.
There are times when you might want to display, collect, or modify data independent of the host database. By using the offline view features in Visual FoxPro, you can use views to connect to a host database and create a subset of data for use offline. Then, working offline, you can use the view directly or through an application you create. When you are done, you can upload the changes stored in the view back to the host database.
Some scenarios where offline views are useful include:
Working with offline views
To create and use view data offline, you can use the following language features:
If you plan to use the offline view on a machine other than the one on which you created the offline view, you must prepare the offline destination by creating a copy of the host database file (.dbc); making sure the ODBC data source used by the view exists on the destination machine; and analyzing your data requirements to determine the contents of the view you need.
Note Use the ODBC Administrator program to install data sources on a machine. You can access the ODBC Administrator program from either the Visual FoxPro program group or from Control Panel.
As with online data, analyze your requirements before creating offline views to determine the design of the views you will need in the offline database. Once you know the subset of data you want to use offline, you can start with an existing view or create a new view. If a view already exists that returns the records you want to use offline, you can use it, or you can create one programmatically. The view you take offline is stored in a .dbf file in the offline database container.
Note If you plan to modify data in an offline view, be sure to make the view updatable before you take it offline. Once a view is offline, you can only set its update properties programmatically; you cannot modify an offline view in the View Designer.
To use an existing view offline
For example, if you want to go to client sites to update accounts, add customers, and record new sales, you need the customer information as well as current orders and the online product descriptions. You might have a view called customerinfo that combines information from the Customers table, Orders table, and OrderItems table. To create the view, use this code:
CREATEOFFLINE("customerinfo")
To programmatically create a view offline
For example, the following code creates a view that displays data from the Products table and the Inventory table from the online database. Since no update criteria are specified, this view is read-only:
CREATE SQL VIEW showproducts ;
CONNECTION dsource ;
AS SELECT * FROM Products INNER JOIN Inventory ;
ON Products.ProductID = Inventory.ProductID ;
CREATEOFFLINE("showproducts")After you create the view for your offline data, you can use it as you would any view in your application: you can add, change, and delete records. Multiple users can access the offline view concurrently using the same database in shared mode. If you decide you do not want to keep any of the changes, you can revert the information to reflect the original information.
Using the offline view, you can display and update data much as you do online with the same forms, reports, or applications. For example, the following code opens the view Showproducts:
USE Showproducts
Tip If you are not getting the subset of data that you expected, check the optimization settings for the remote view. If you set the MaxRecords property using the DBSETPROP( ) function, only that many records appear in your views offline. However, if you include a Memo field in the field list of your view, it is automatically included in the result set even if FetchMemo is set to false (.F.).
In some cases — especially in a multiple-user environment where data is modified by numerous people — you might want to examine changes made to the offline view before committing the changes to the source database. With the USE command and the ADMIN clause, you can see all changes that have been committed to a view since it was taken offline. You can then selectively revert changes that have been made without being connected to the data source. For example, the following code opens the view Showproducts in administrator mode:
USE Showproducts ADMIN
After you're finished offline, you can update the data on the sever using the same table update transactions you usually use with online data. When working with remote data, keep in mind the following tips:
Before you can process your updates, you need to use the USE command and the ONLINE keyword to reconnect to the host database . After you issue the command, Visual FoxPro attempts to locate the host database using the data source information stored in the view. After the connection is established, you can use TABLEUPATE( ) to process the updates stored in the offline data.
To make sure the connection information is correct regardless of the location of either the host or view tables, you need to use connection string syntax rather than a named connection.
To process a batch of changes against local tables, you can use manual transactions that allow you to process the entire batch of changes within a single transaction rather a series of separate transactions.
Updating local tables with offline views
| Code | Comments |
|
Reconnect to the host and open the view |
|
Check for update conflicts and update as appropriate. |
To process a batch of changes against remote tables, use manual transactions: begin with TABLEUPDATE( ) and finish processing with either SQLCOMMIT( ) or SQLROLLBACK( ).
To set the connection to manage your transactions manually, you need to use CURSORGETPROP( ) on the view cursor to get the connection handle, then set the Transactions property to manual mode.
In the following code, the current connection identification for the view, myview, is stored into hConn1. hConn1 is used to set the Transactions property to “2” for manual transactions.
hConn1 = CURSORGETPROP("CONNECTHANDLE","myview") ;
SQLSETPROP(hConn1,"TRANSACTIONS",2)
After you set the connection to handle the updates, you can use TABLEUPDATE( ) to handle your transactions.
If the host tables reside on a remote server, such as SQL Server, you might use the following code as a guideline.
Updating remote tables with offline views
| Code | Comment |
|
Reconnect to the host and open the view. |
|
Setting the connections on the views to handle transaction manually. |
|
Handling updates and update conflicts. |
If you are updating a single row, you can use automatic transactions. Because each statement to process an update, delete, or insert is handled as a separate transaction, rollbacks against prior transaction statements are not possible.
USE customerview ONLINE EXCLUSIVE
GO TO 3
IF TABLEUPDATE (0, .F. workarea)
* conflict handling code
ENDIF
Tip To update a single record in a local table, use the GETNEXTMODIFIED( ) function.
If you decide you want to delete the offline data and convert the view back to an online view, you can use the DROPOFFLINE( ) function.
To cancel offline updates
Be sure to check the return values. True (.T.) indicates success and false (.F.) indicates that the view was not closed before the command was issued.
The following code drops all of the changes made to the subset of data in myview. The view remains part of the database, but its current set of data is dropped.
DROPOFFLINE("myview")
You can delete offline records, but you can’t use the PACK, ZAP, or INSERT commands with an offline view.
You can optimize the performance of your views by setting view properties.
You can control the number of rows Visual FoxPro progressively fetches at one time from the host database with the FetchSize property of the view and active view cursor. You use DBSETPROP( ) and CURSORSETPROP( ) to set these properties.
You can use the delayed memo fetching feature to speed retrieval of view data. When you choose delayed memo fetching, Visual FoxPro does not retrieve the contents of a Memo field until you choose to open and display the field. Because Visual FoxPro needs the key field and table name to locate a row on the remote data source, you must set the UpdateName or UpdatableFieldList property, the KeyField or KeyFieldList property, and the Tables property for delayed Memo fetching to work. However, you don’t have to set the SendUpdates or Updatable properties to on in order to make delayed memo fetching work.
You can control the amount of data downloaded when you open a view by setting the MaxRecords property. When Visual FoxPro sends a SQL statement to the data source to create a view, the data source builds and stores a result set. The MaxRecords property specifies the maximum number of rows fetched from the remote result set into your view. The default setting is –1, which downloads all rows in the result set.
To control the number of rows downloaded into a view
-or-
For example, the following code alters the view definition to limit the number of rows downloaded into the view to 50, regardless of the size of the result set built on the remote data source:
OPEN DATABASE testdata
USE VIEW remote_customer_view
?DBSETPROP ('Remote_customer_view', ; 'View','MaxRecords', 50)
You can use the CURSORSETPROP( ) function to set the MaxRecords limit for an active view.
Tip You can’t use the MaxRecords property to stop a runaway query, because the MaxRecords property doesn’t control the building of the result set. Use the QueryTimeOut property to control the execution time on the remote data source.
To make optimization decisions for a view or query, you might need to know the execution plan: the order that joins and filter clauses will be evaluated. Using the SYS(3054) function, you can display one of three Rushmore™ optimization levels. The three levels indicate the degree to which the filter conditions or join conditions were able to use Rushmore optimization. The levels are completely (Full), partially (Partial) or not at all (None).
To display the execution plan for filters
For example, you might type:
SELECT * FROM customer, orders ;
AND Upper(country) = "MEXICO"
For this example, the screen might display:
Using Index Tag Country to optimize table customer
Rushmore Optimization Level for table customer: Full
Rushmore Optimization level for table orders: none
You can then pass 11 to the SYS function to evaluate joins in the FROM or WHERE clauses.
To display the execution plan for joins
For example, you might type:
SELECT * ;
FROM customer INNER JOIN orders ;
ON customer.cust_id = orders.cust_id ;
WHERE Upper(country) = "MEXICO"
For this example, the screen might display:
Using Index Tag Country to optimize table customer
Rushmore Optimization Level for table customer: Full
Rushmore Optimization level for table orders: none
Joining table customer and table orders using Cust_id
If the execution plan for your joins does not match your specific needs, you can force your join order to execute exactly as written without optimization from the processor. To force the evaluation order of the join, you need to add the FORCE keyword and place your join conditions in the FROM clause. Join conditions placed within the WHERE clause are not included in a forced join evaluation.
Note You can’t use the FORCE keyword in SQL pass-through statements or remote views because this keyword is a Visual FoxPro extension of the ANSI standard and is not supported in other SQL dictionaries.
The FORCE clause is global and therefore applies to all tables in the JOIN clause. Be sure that the order in which the join tables appear is exactly the order in which they should be joined. You can also use parentheses to control the evaluation order of joins.
In this example, the first join specified is also the first join evaluated. The Customer table is joined with the Orders table first. The result of that join is then joined with the OrdItems table:
SELECT * ;
FROM FORCE Customers ;
INNER JOIN Orders ;
ON Orders.Company_ID = Customers.Company_ID ;
INNER JOIN OrItems;
ON OrdItems.Order_NO = Orders.Order_NO
In this example, the join within the parentheses for the table Orders and OrdItems is evaluated first. The result of that join is then used in the evaluation of the join with Customers:
SELECT * ;
FROM FORCE Customers ;
INNER JOIN (orders INNER JOIN OrdItems ;
ON OrdItems.Order_No = Orders.Order_No) ;
ON Orders.Company_ID = Customers.Company_ID
You can use one active connection as the information pipeline for multiple remote views by sharing a connection. When you share an active connection, you:
You share connections by setting the view definition to use a shared connection upon activation. When the view is used, Visual FoxPro connects to the remote data source using the existing shared connection (if any). If a shared connection isn’t in use, Visual FoxPro creates a unique connection when the view is opened, which can then be shared with other views.
Only one active instance of a named connection definition is shared during a Visual FoxPro session. If multiple instances of the same connection definition are active, the first instance to be used as a shared connection becomes the designated shared connection. All views that use that connection definition and employ connection sharing will access the remote server through the designated shared connection.
Connections other than the designated shared connection are not shared. Connection sharing is not scoped to sessions.
To share a connection
-or-
-or-
The following code creates a view that, when activated with the USE command, shares a connection:
CREATE SQL VIEW product_view_remote ;
CONNECTION remote_01 SHARE AS ;
SELECT * FROM products
USE product_view_remote
When a connection is busy, such as when Visual FoxPro is progressively fetching data into a cursor, you don’t want to start another fetch or send updates on the same connection. You can determine whether a connection is busy with the ConnectBusy property, which returns a value of true (.T.) if the connection is busy. You can use this property in your application to test a connection before sending a request over a shared connection to a remote data source.
To determine whether a connection is busy
You need the connection handle to use the SQLGETPROP( ) function. You can identify the connection handle for an active view with the ConnectHandle property of the CURSORGETPROP( ) function. The following code identifies a connection handle and then uses the connection handle to test whether the connection is busy:
nConnectionHandle=CURSORGETPROP('ConnectHandle')
SQLGETPROP(nConnectionHandle, "ConnectBusy")