Using Visual FoxPro to Access Remote Data
|
|
|
|
Using Visual FoxPro to Access Remote Data |
|
McAlister Merchant, Microsoft Corporation
Introduction
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.
Microsoft® Visual FoxPro™ provides ways to connect to remote data of
various types, including data created in non-tabular 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.
Creating Views and Connections
Although queries and views are similar, there are significant differences:
Queries
Views
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.
Local View
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.
Remote View
Connections and Named Connections
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.
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.
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.
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.
Preparing to Access Remote Data Sources
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. 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.
Example
The information required for identifying the ODBC data source in this Windows
95 example looks like this:
Data Source Name: LaMancha
To set up access to a SQL data source from Visual FoxPro for Windows® or
Visual FoxPro for Macintosh®, you need the following:
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.
Server: EntertaiNet
Database Name: theater
Password: <none>
Set Up SQL Server
To set up the ODBC driver in Windows or Windows NT
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.
To set up the ODBC driver on a 68K or Power Macintosh System
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.
Prepare for remote access by using the correct ODBC driver setup for your
computer.
Data Source (LaMancha)
Server (EntertaiNet)
Database Name (theater)
Data Source Name (LaMancha)
Server
(AppleShareServer:EntertaiNet)
Network address (EntertaiNet)
Connect Visual FoxPro to SQL Data Source
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.
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.
Building a Connection
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
To create a remote view you must open a database 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
way to go.
Connections inherit the environment settings of the options dialog 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.
This list reflects the data sources you specified in the ODBC driver setup.
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.
Building a View
The Remote View
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.
Selecting View Records
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.
Filtering View Records
Ordering View Records
Grouping View Records
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.
Updating Data Source Records
Use the Table list box to choose the tables to update.
Use the Field name list box to mark the key and updatable
fields.
The KeyField property of views identifies fields in an index key expression.
The KeyField property enables you to uniquely identify rows in a remote data
source. You can modify and restore the default values. The Updatable property
of views identifies fields the user can update in the remote data source.
Use the SQL WHERE clause to set restrictions on any updates.
This option lets the you specify the conditions under which an update in a
multi-user 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 multi-user environment in key and
modified fields.
You can also use code to create the view and to manage updates as in the
following example in which 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. The View Designer generates the same view using point and click and
drag and drop as the following code.
Example code
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.
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.
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.
The Filter tab enables you to describe how to limit the available records to a
subset by using a simple expression in the tab, that becomes the WHERE clause
of the defining SQL statement.
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.
The Group By tab enables you to specify how the returned data is grouped in
the view by using fields, or by specifying aggregate functions, or building
expressions in an SQL HAVING clause in the defining SQL statement.
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.
* 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.);
&& spe cifies 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.
Optimizing Views and Connections
Indexing
Example
You can create a local index on a view by using the INDEX ON command, which
can be a good way to optimize performance.
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.
Controlling Access and Downloads
Example
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.
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
More Information
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 and Chapter 7, "Working with Tables,"
and Chapter 8, "Creating Views" in the Developer's Guide.
For information on subjects related to optimizing performance during data
access, see Chapter 15, "Optimizing Applications" in the
Developer's Guide. 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:
|
© 1997 Microsoft Corporation. All rights reserved.
|