McAlister Merchant
Microsoft Corporation
September 1996
Microsoft® Visual FoxPro™ provides ways to connect to remote data of various types, including data created in nontabular format, on different platforms and from different products. You can get data of almost any type, as long as you know the format and have permission from the server administrator to access it. To do this, Visual FoxPro uses Open Database Connectivity (ODBC) technology, SQL drivers and language, and the speed and object-oriented programming features of Visual FoxPro. ODBC services are handled by the drivers available in your operating system and those supplied with Visual FoxPro. To use these services, you need to have information about your data source and, perhaps, a password.
Note You might also need to install additional SQL-Client software. Check with the remote data system administrator for details.
Once you set up Visual FoxPro to access the data, you can use the data remotely or locally, in views, or using SQL pass-through queries.
A view is an updatable cursor that consists of fields from one or more remote or local tables or other views. A multitable view functions on two or more related tables. View definitions are stored only in databases. Views might include join conditions for related remote or local data and specifications for handling updates and update conflicts. Views provide a data set that you can browse or use to update data you access with Select-SQL statements. Remote views provide the same functionality through remote ODBC data sources. A view is created the same way as a query, but when the view is based on a named connection, you can specify parameters that enable a user to redefine the scope of the view at run time.
Although queries and views are similar, there are significant differences:
A view might also contain specifications on filter and join conditions, indexes, and update criteria. Though queries can display a "snapshot" of a data source, views, because of their run-time characteristics, are true connections to their data sources.
A local view describes, in a database, the connection to tables that are in scope. In local views you store Select-SQL descriptions of native Visual FoxPro data without using ODBC protocols. The data accessed need not be contained in the database.
You create a local view by using the View Designer or the Visual FoxPro CREATE VIEW or CREATE SQL VIEW commands to describe a data set from tables or views in the current database.
A remote view describes the connection or data source in an SQL statement in the syntax of the remote server. You create a remote view by using the View Designer or the CREATE SQL VIEW REMOTE command and using remote SQL syntax to describe the data set. You must specify a remote ODBC data source or a named remote connection to data outside the Visual FoxPro database.
A connection is a definition that specifies the name of a data source. A named connection is a definition, stored in a database, that additionally lets you set properties that optimize the flow of data between Visual FoxPro and the data source. A named data source can include information about user ID, access password, database name, logon prompting and verification. By using a named connection in an application, you make it easy for a user to get to the right place, the right way; all the connection information is associated with the view used to display or manage the data—even if the data is remote.
You create a connection by using the Connection Designer or the Visual FoxPro CREATE CONNECTION command. You can also access data through temporary connections by using the SQLCONNECT or SQLSTRINGCONNECT commands.
To set up access to a SQL data source from Visual FoxPro for Windows® or Visual FoxPro for the Macintosh, you need the following:
The ODBC protocol enables you to move data between platforms, programs, and database management systems. ODBC drivers on different platforms enable you to transparently access data. You can also access local sites using ODBC.
When you want to access data from a remote source, you need to ask the system administrator about where and how the data is stored. You also need to get the name of the data source as well as the physical and logical location (server device and path name) of the data you want. ODBC services can access data in various forms, such as spreadsheets or word processor documents. The data you access on a SQL server is in a table in a database. It is helpful (though not necessary) to know the structure of the table(s) you want to access. To gain access to the data, you might need a password. You might also need additional server software.
Once you have this information, use it to set up the ODBC driver supplied with your version of Visual FoxPro.
A Microsoft Access user creates a database named theater, containing tables, reports, and forms to manage ticket sales for a theater group. This user is set up to be the administrator of the table called LaMancha. The database is located on a shared computer named EntertaiNet. No password is required for remote access.
The information required for identifying the ODBC data source in this Windows 95 example looks like this:
Data Source Name: LaMancha
Server: EntertaiNet
Database Name: theater
Password: <none>
Prepare for remote access by using the correct ODBC driver setup for your computer.
Data Source (LaMancha)
Server (EntertaiNet)
Database Name (theater)
You can also add additional information, including a descriptive note in the Description, the Network address, and the Network Library. The system administrator can provide all this information.
Data Source Name (LaMancha)
Server (AppleShareServer:EntertaiNet)
Network address (EntertaiNet)
Figure 2. ODBC setup for the Macintosh
The ODBC setup you complete persists until you modify or remove the description from your ODBC setup. This setup describes a connection to a device and database that may contain several accessible tables, so knowledge of the structure of the tables is helpful, if not necessary for effective access to data.
The ODBC setup merely registers the existence of an accessible ODBC resource. The business end of the connection is established in Visual FoxPro when you connect to the SQL resource you specified in the ODBC setup.
In Visual FoxPro you complete the communication between Visual FoxPro and the remote data source by establishing a Visual FoxPro connection. Within a database, in a view, you must first establish a connection and you may create a named connection. You can then use the local or remote view directly or use the view in one or more queries using that view.
To create a remote view, you must open a database and then use the Connection Designer or code to establish a connection to the data source and create the view. For ease of use or for interactive work, the Connection Designer is the best way to go.
When you highlight Remote Views in the Data tab of the Project Manager and click the New button, the Select Connection dialog box appears, enabling you to define or specify the data connection that your remote view will use.
To create a new connection, in the Select Connection dialog box
Figure 3. Selecting a data source in Visual FoxPro 5.0
In Visual FoxPro 3.0, click OK to save the connection. Visual FoxPro 3.0 verifies the connection when you use it in a remote view.
Figure 4. Selecting a data source in Visual FoxPro 3.0
Connections inherit the environment settings of the options dialog box and the active data session. You can control properties such as UpdateType, Prepared, and UseMemoSize by using the CURSORSETPROP in the current data session. Use DBSETPROP on an open database to make changes after you create and store a view or connection. See the code later in this paper.
At this point, having defined and established the connection to your data source, you can define a view of the remote data. You create this definition just as you would a query definition, but you can do more.
The remote view
After you create or re-establish a connection in the View Designer, you can create or modify a view. To use the additional control available through remote views, you can manage update operations and optimize data access through judicious remote and local indexing and by controlling data downloads. Using the View Designer to create the view, LaMancha_ducats, that uses the connection created in the Connection Designer produces the following example: a view that allows you to see the ticket sales for a performance date that has already passed. Users of this view can modify the ticket number and the performance selected.
Figure 5. Using the View Designer to create a view of ticket sales
This view also allows the user to update values in the Qty and Performance fields of the view, thereby updating the values in the remote table. You can see the settings that provide these functions in the following screens.
Figure 6. The View Designer Fields tab
Selecting view records
The Fields tab of View Designer enables you to select fields from the available tables and to create view fields with expressions. The Join tab enables you to specify one of five join conditions: none, inner, left, right, or full. The choices return information from rows where the following statements apply.
| Join Type | Returned rows |
| inner | left table value = right table value |
| left | all left table values + matching right table values |
| right | all right table values + matching left table values |
| full | all left table values + all right table values |
Note Some remote servers do not support outer joins. For details, check with the remote system administrator.
Figure 7. The View Designer Join tab
Filtering view records
The Filter tab enables you to describe how to limit the available records to a subset by using a simple expression in the tab, which becomes the WHERE clause of the defining SQL statement.
Figure 8. The View Designer Filter tab
Ordering view records
The Order By tab enables you to specify the order in which the records are presented in the view by using fields, aggregate functions, or expressions. Selections you make here become the ORDER BY clause of the defining SQL statement. Ordering is a function Visual FoxPro imposes on records in the view. The remote records retain their stored order.
Figure 9. The View Designer Order By tab
Grouping view records
The Group By tab enables you to specify how the returned data is grouped in the view by using fields, by specifying aggregate functions, or by building expressions in an SQL HAVING clause in the defining SQL statement.
Figure 10. The View Designer Group By tab
In Visual FoxPro 5.0, the Miscellaneous tab enables you to control the records included in the view by specifying a number or percentage of records to be returned. You can also specify that there be no duplicate records returned or that records be sent to Microsoft Graph, a report, or a table in cross-tabular format.
Figure 11. The View Designer Miscellaneous tab
Updating data source records
You can use the View Designer to manage update operations or you can use code, as in the example that follows. Set persistent update criteria and scope in the Update Criteria tab of the View Designer.
This option lets you specify the conditions under which an update in a multiuser environment fails.
The Update Criteria tab enables you to manage when and how updates occur and whether those updates are reflected in the original tables. The Update Criteria for the example is set to update the connected tables and to manage update conflicts that might occur in a multiuser environment in key and modified fields.
Figure 12. The View Designer Update Criteria tab
You can also use code to create the view and to manage updates. In the following example, DBSEPROP specifies the tables and fields to be updated, the key field that controls data selection, and the updatable view fields. The DBSETPROP SendUpdates property activates the update function. Using point and click and drag and drop, the View Designer generates the same view as the following code.
Example code
* Create a view named LaMancha_ducats.
* Specify the fields and the join and filter conditions
* in the view.
CREATE SQL View Lamancha_ducats AS;
SELECT Ticket.firstn, Ticket.secondn, Ticket.phone, ;
Ticket.emailname, Ticket.qty, Ticket.qty*Perform.ticketfee,;
Perform.datep, Ticket.performance;
FROM theater.ticket INNER JOIN theater.perform ;
ON Ticket.performance = Perform.number;
WHERE Perform.datep DATE() && creates view
* The following code uses the UpdateName, UpdatableField,
* and SendUpdates clauses of DBSETPROP( ) to create the
* selections in the View Designer example, persistent
* functionality in the LaMancha_Ducats view.
* Specify the datasource the view will update.
=DBSETPROP('LaMancha_ducats', 'View', 'Tables', 'ticket')
&& specifies table to update
* Specify the updatable fields - the Update Criteria tab.
=DBSETPROP('LaMancha_ducats.qty', 'Field', 'UpdateName';
'ticket.qty) && specifies data source field update name
=DBSETPROP('LaMancha_ducats.performance, 'Field', 'UpdateName';
'perform.number') && specifies data source field update name
* Specify fields as a unique key for update tables -
* the Update Criteria tab.
=DBSETPROP('LaMancha_ducats.first', 'Field', 'KeyField'' .T.)
=DBSETPROP('LaMancha_ducats.second,' 'Field', 'KeyField', .T.)
=DBSETPROP('LaMancha_ducats.performance,' 'Field', 'KeyField', .T.);
&& specifies a two-field unique key
&& in TICKET and perform tables
=DBSETPROP('LaMancha_ducats.qty', 'Field', 'Updatable', .T.)
=DBSETPROP('LaMancha_ducats.performance, 'Field', 'Updatable', .T.);
&& specifies data source updatable field
* Activate the update functionality of this view -
* equivalent to clicking the Send SQL Update checkbox.
=DBSETPROP('LaMancha_ducats', 'View', SendUpdates', .T.)
&& Activate View update functionality -
&& equivalent to click Send SQL Updates
Use the TABLEUPDATE( ) command to commit updates to the data source.
You can make temporary changes to the active view by using the CURSORSETPROP command. Such changes are not saved and don't affect the view description stored in the database. Make persistent changes to a saved view in an open database by using the DBSETPROP command.
Indexing
You can create a local index on a view by using the INDEX ON command, which can be a good way to optimize performance.
Example
INDEX ON Lamancha_ducats.first+Lamancha_ducats.second
&& matches the Order By tab and SQL statement
Since the view accesses only specific data from the data source, you can view a subset of what might be a large volume of data. Then, by creating a local index, you can use the advantage of scale. Remember, though, that a local index on a view is a temporary part of the view; the index is deleted when the remote view closes.
You can create relations between view indexes or between view and table indexes. For better performance when you SET RELATION between view and table indexes, make the view the parent.
You can enhance performance by using Rushmore Technology in your index.
You can use a view in multiple work areas without downloading the data for each area by using the USE command with the AGAIN and the NOREQUERY clauses.
Example
USE Lamancha_ducats && Open the view and download records
...
USE Lamancha_ducats AGAIN in 0 && Access the already opened view
– Or –
SELECT 0
USE Lamancha_ducats NOREQUERY && Use the view without a new download
You can use the NODATA clause of the USE command to open a view without downloading data so that you can review the structure or reduce the time and size requirements to activate the view. This is faster than setting the MaxRecords property to 0 because the data source needn't build the result set.
You can combine local and remote data into a single view by creating a local view, then adding any combination of local and remote data sources. When you combine local and remote data, for greater efficiency place all remote data into a single view and then join the local data into a local view.
You can enhance performance by controlling the number of records Visual FoxPro downloads when you open a view, the number of rows Visual FoxPro retrieves in multiple fetches (MaxRecords and FetchSize properties), and the downloading of memo fields (delayed memo fetching).
Example
USE Lamancha_ducats
?DBSETPROP('Lamancha_ducats', 'View', 'MaxRecords', 20)
* Download no more than 20 records.
?DBSETPROP('Lamancha_ducats', 'View', 'FetchSize', 10)
* Get 10 records at a time, until encountering
* a GOBOTTOM or SQLCANCEL command.
You can create local business rules that control view fields and rows in order to reduce response time, improve network overhead, or prevent updates that contain faulty data. Use the RuleExpression and RuleText properties of the DBSETPROP command, as in the following example.
Example
?DBSETPROP('Lamancha_ducats.qty', 'Field', 'RuleExpression',;
'qty <25 AND qty >=1') && Enter quantities between 1 and 25
When you use named connections in a data environment that's subject to change, you should include in your application a routine that checks for the data source and its location before you open the view. Then you can provide a way to incorporate the changes so the designed view still works.
Example
IF SQLCONNECT(NamedConnection) <1
WAIT WINDOW "Connection to server failed"
RETURN
ENDIF
For details on commands you use to access remote data, see Visual FoxPro Help topics on the following commands:
| Command | Comment |
| CREATE CONNECTION | Creates a named connection or opens the Connection Designer in a database |
| CREATE SQL VIEW | Opens the View Designer so you can create or modify views and connections |
| CURSORSETPROP | Enables or changes the Buffering table property or cursor properties |
| DBSETPROP | Sets or modifies properties in the current database |
| SQLCONNECT | Attempts a connection to a remote data source and reports the result, or opens the Select Connection Or Data Source dialog box |
| SQLSETPROP | Sets or modifies properties in an active connection |
| SQLSTRINGCONNECT | Attempts a connection to a remote data source using its connect string and reports the result, or opens the SQL Data Sources dialog box |
| TABLEREVERT | Discards changes made to buffered data |
| TABLEUPDATE | Commits updates in a buffered table or enabled in the current view |
Other Visual FoxPro SQL commands provide the functionality of the View and Connection Designers on remote data sources. If you use functions or expressions in a remote view, it must conform to the syntax of the data source server, such as Microsoft Excel or Word.
For information on subjects related to remote data access, see the following Visual FoxPro 5.0 documentation: Chapter 5, "Updating Data with Views," and Chapter 6, "Querying and Updating Multiple Tables," in the User's Guide. In the Developer's Guide, see Chapter 7, "Working with Tables," and Chapter 8, "Creating Views."
For information on subjects related to optimizing performance during data access, see Chapter 15, "Optimizing Applications" in the Developer's Guide.