A View is a SQL SELECT statement stored in the Database Container that presents a logical view of data to the end-user or programmer. The View can use tables from one or more Database Containers, or use Free Tables, or Remote tables from various external data sources. Sophisticated Views can combine tables from different data sources.
Views can be defined for many reasons including:
Views can be employed to present a logical View to a programmer or end-user where the underlying definition of the database is likely to change. This allows one programmer to create summary reports, for example using a view of the Customer and Order tables, whilst another works on data entry forms on the underlying tables. If table or field names are redefined on the underlying tables at a later stage in the implementation, the definition of the View can be changed without altering the table and field names presented to the report program.
A Visual FoxPro table can only be defined in a single Database Container. A corporate database may have several tables that are required in many databases to look up Employee Names or Company Departments for example. This data may be required in different applications and a design decision might be made to implement these tables as Free Tables and create a Local View in each Database Container to refer to the common tables. If this data is static data and is rarely modified, the Free Tables might be stored locally to ease?????S???a ?? up on network traffic. The same technique allows a Visual FoxPro table to be included in more than one Database Container.
Project Window showing Views
Views are created in a Database Container using the View Designer. This allows the required tables to be selected and any join conditions specified between them. The required fields, selection condition, order sequence, and update criteria are also specified. Views can access data from local FoxPro tables, free FoxPro tables, or from remote data sources using ODBC to connect to a database server.
Various field and view properties can be set with the view designer or by using the DBSETPROP() command. For example, the RuleExpression property of the View provides table level validation for the view regardless of the data source.
| Triggers cannot be defined for a View. |
Summary information is often required in several different functional areas of an application. The total outstanding Sales Orders for a Customer might be required in various forms and reports. The SQL statement to derive this information might be implemented as a View which includes a GROUP BY clause. This allows the statement to be coded only once in the application and also to allow the flexibility if the underlying physical data structure is changed in the future.
Remote Views allow for transparent access to external ODBC data and also maintain some degree of logical data independence from the physical definition of the underlying data. Various components are required to create remote views successfully:
ODBC, or Open DataBase Connectivity, drivers are the Microsoft standard drivers for connecting to external data sources from Windows applications. Many applications support these drivers including nearly all of the Microsoft product range.
The latest SQL Server drivers are shipped with various Microsoft products including FoxPro and they are also available from the SQL Server installation when installing client utilities for SQL Server. Make sure the latest 32 bit drivers are in use (at least version 2.5) which should match the speed of the native SQL Server data access library.
Drivers for other database servers are available from most of the database vendors and from Microsoft and third party publishers such as InterSolv, Visigenic, and OpenLink. In some cases the third arty drivers are better than the vendor supplied drivers. Look for ODBC 2.0 compatibility.
The ODBC drivers are loaded from the ODBC Control Panel and need to be loaded on each workstation that requires access to the data source. The ODBC driver will work with all supporting applications once they are installed and specific drivers are not required for Visual FoxPro.
ODBC drivers often specify the database to be accessed. If there are several SQL Server databases required from a workstation, a named driver is often specified for each database.
Specifying an ODBC Driver for a SQL Database
| Windows 95 does not allow ODBC Drivers to be loaded independently of the?????S???a ??ir application and the application setup is used to install them. FoxPro 3.0 ODBC Drivers are supplied with Microsoft Office for Windows 95. |
Visual FoxPro allows for Views to be created on remote data from external data sources using ODBC drivers that must be installed on the workstation for the appropriate data source. These remote Views are transparent to the programmer and end-user and behave as if they were Views defined on local FoxPro tables.
A Remote View can be defined to refer directly to the ODBC Datasource. The datasource may however also be used by other programs and a Connection can be defined in the FoxPro database container to create a logical link to the datasource. This allows the datasource name (and backend database) to be changed by changing only the Connection definition.
A Connection or ODBC Datasource must be defined for each Remote View that allows FoxPro to co-ordinate the links to the ODBC data source.
The use of Connections allows Visual FoxPro some flexibility in the independence of the data source from the definition of the View. It is possible to create an application on a Remote View that utilises different connections to link through to different data sources at various installation. Similarly, careful definition of Local Views allows for the application to be implemented as a Client-Server application by redefining the Local Views as Remote Views.
The Connection designer is available from the Project Manager or whilst creating a Remote View. The designer allows the ODBC datasource to be specified along with options to determine whether the user is prompted for a password each time the external datasource is accessed and default time-out intervals for the transactions to fail if no response happens from the external data source.
Defining a Connection
The CREATE CONNECTION syntax can be used to create a connection programmatically in the default database together with the User Name and Password if required.
SET DATABASE TO sales
CREATE CONNECTION conNwind DATASOURCE odbNwind
The View Designer allows for the Visual definition of the SQL statement that makes up the View. The tables that make up the View, the available fields, and the selection criteria are all defined visually along with the ability to group the table and calculate summary information.
Specifying Join Conditions with the View Designer
Tables are added to the View by Rightclicking in the View Designer and selecting the Add table option. It is possible to select Tables and Views from the default Database Container or from any other Database Container as well as specifying Free Tables with the Other... option.
The relationships of multi-table Views are automatically added into the Field Selection section of the View Designer if they have already been defined in the Database Container. They can be modified or added manually as the new table is added or by inserting a new entry in the Field Selection window with the appropriate Join condition.
Fields must be selected for the View and calculated or summary fields my also be defined. The Grouping for Summary information is defined in the GROUP BY window and the COUNT(), SUM(), AVG(), MAX(), and MIN() functions will operate if selected in the Field Selection window.
The Update Criteria must be defined before the view is updatable.
An improvement in Visual FoxPro 5.0 is the ability to set field properties for a view using ?????S???a ??the View Designer. The FIELDS pageframe has a PROPERTIES button which allows field properties to be set on the view.
These properties behave in an identical manner to the properties on fields in native FoxPro tables. Some of these properties are augmented by functionality that may have been defined on the server. For example, if no default is specified locally and a value is not entered for a field, then SQL Server may specify a default value for the field when the record is added if a default is defined on the server.
Field Properties Window
The Data Mapping will usually default to the field type most appropriate for FoxPro to receive values from the ODBC data source. Occasionally this may need to be modified and the View Designer allows the FoxPro data type to be modified to match particular processing requirements.
| Some View Properties must be specified by using the DBSETPROP() command. For example, a validation rule may be specified only with the DBSETPROP() command. |
Parameters may be defined for a View to limit the data selected in the SQL SELECT statement. The parameter can be employed programmatically and interactively.
The parameter is specified in the View Designer by placing a question mark before the parameter name in the EXAMPLE column of the selection criteria.
The name and datatype of the Parameter can be defined in a window called from the PARAMETER.. menu option for the View. The Parameter may then be referred to in the Join Condition by prefixing with parameter name with a question mark.
The e?????S???a ??nd-user is automatically prompted to enter the parameterised value unless a memory variable with the same name as the parameter is defined before opening the View.
m.lc_surname = 'B%'
USE authorsurnameview IN 0
BROWSE
| Parameter names can be defined for interactive use by enclosing the name in Quotation marks. For example: ?'Please enter the Customer Identifier' |
Views can be created programmatically by defining the SQL SELECT statement with the following syntax:
CREATE SQL VIEW ViewName AS SQLSelectStatement
A remote view has the REMOTE keyword added and the connection details specified:
SET DATABASE TO dbcpubs
CREATE SQL VIEW authorsurnameview ;
REMOTE CONNECTION odbpubs SHARED AS ;
SELECT * FROM authors WHERE au_lname LIKE ?lc_surname
More complex views are also possible. A local view that contains the total value of each Product ordered by a single Customer might be defined as follows:
SET DATABASE TO sales
CREATE VIEW ProductTotal AS ;
SELECT Products.product_id, Products.prod_name, SUM(Orditems.quantity),;
SUM(orditems.unit_price*Orditems.quantity);
FROM testdata!orditems, testdata!products;
WHERE Products.product_id = Orditems.product_id;
GROUP BY Products.product_id;
ORDER BY Products.prod_name, Products.product_id
| The View may not be updatable if the correct properties have not been set as the view defaults, for example, sendupdates. |
The Update criteria specify how the data in a view is updated back onto t?????S???a ??he local or the remote data source.
Specifying the Update Criteria with the View Designer
The Update Criteria can substantially affect the performance of the Remote View and consideration of the nature and performance of the back end database is required to fully optimise the Remote View together with the Connection definition.
A primary key must be specified for FoxPro to determine which record to update in the external data source. Visual FoxPro may be able to determine the key field but it can be specified manually by clicking beside the appropriate field in the column indicated with a Key icon.
| A remote view can be specified on a join of several tables. The Key fields for each table should be indicated with the view designer to make all the tables updatable. The equivalent SQL Server View only permits one table at one time to be modifiable. |
The fields that are modifiable by the program or end-user can also be specified for security and performance purposes by checking the column with a pencil icon.
The default update scenario is that FoxPro checks the values of the key fields and all modifiable fields to see if any changes have been made in the database whilst the user was editing the record on the workstation. If any changes have been made by another user, the update transaction fails.
Alternatively, the View can be configured to update the table regardless of other users changes to the record by specifying to match key fields only.
A timestamp field can be defined in the external data source which is maintained through the use of triggers or other means by all applications that update data. Visual FoxPro can then check the timestamp field only to see if o?????S???a ??ther users have updated any records.
The Key and Modified Fields option may be specified so that the remote view checks only the Primary Key and any fields that have been changed by the user. This allows several users to change the same field simultaneously so long as they do not update the same field.
| The TABLEUPDATE() command has a FORCE parameter which will override the error message returned if another user has changed the record and will perform the update regardless. |
Some database engines do not allow SQL UPDATE commands on records and the record must be deleted and reinserted. This is allowed by checking the appropriate option.
| Some additional properties of the Remote View are set in the Connection designer. |
Properties can be set for all of the objects in a Database Container including Tables, Views, Remote Views, and Connections. For many of the objects in the database container, the properties can be set both interactively using the appropriate designer or programmatically.
There are some view properties that are not accessible using the view designer. For example, there is a View record level validation property that can be used to validate data in the view. This is especially useful as there are no triggers for views.
DBSETPROP() is used to programmatically set database container properties. The function returns a True value if the property is successfully set in the Database Container. The following syntax can be used to set a Caption on a field in a Remote View specified in the Orders Database Container:
SET DATABASE TO orders
? DBSET?????S???a ??PROP( 'customertotal.cnt_id', 'field', ;
'caption', 'Orders' )
The following example sets the validation rule property for the view to call a validation function. This property cannot be seen or be set with the view designer:
SET DATABASE TO orders
? DBSETPROP( 'customertotal', 'view', ;
'reuleexpression', 'custval()' )
| See the help for DBGETPROP() for a list of the property names that can be set in the database container. Take care because Visual FoxPro does not check the validity of Database Container properties when validation rules are set programmatically. |