Visual FoxPro controls all communication with client-server databases by using a software component called a connection.
All connections require the appropriate ODBC drivers to be installed on the local workstation. A single application may communicate with several different client-server databases by defining a connection for each datasource.
Connections can be defined in a Database Container for use together with FoxPro remote views as a very convenient way to access data. They may also be defined programmatically when used together with SQL statements that are passed directly through to the server.
Many parameters influence the behaviour and performance of connection. Fox example, synchronous connections are relatively easy to handle programmatically and will cause the application to wait until all the data requested from the server has arrived. Asynchronous connections allow access to data and continues receiving data from the server in the background.
Client-server performance can be influenced by the number of connections open at any one time. An application with many users should minimise the number of open connections for each client. Visual FoxPro aids this by allowing remote views to be controlled by a single shared connection.
ODBC (Open DataBase Connectivity) is the Microsoft Windows middleware for connecting to external data sources from Windows applications. It has proved very successful, and many applications have direct support for ODBC including the Microsoft Office product range.
ODBC terminology has changed several times over the last decade with OleDB and ADO drivers providing better functionality and performance particularly with SQL Server. Non-Microsoft database vendors usually support ODBC for their drivers and the latest drivers for SQL Server operate as ODBC drivers even if their underlying functionality is the more recent ADO.
The ODBC drivers need to be installed on each workstation that requires access to the data source and will work with all supporting applications once they are installed. Specific drivers are not required for Visual FoxPro.
You may wish to access FoxPro data from other Windows applications. The FoxPro ODBC driver can be download from the Microsoft website. Just search for ' FoxPro ODBC driver'.
A full ODBC connection string can be used to create a connection to the server. In this case there is no need to define a datasource on the workstation as only the relevant drivers need to be installed.
This string may contain references to the driver, server, username, password, calling application, workstation name, and database name to create a connection to SQL Server for example:
DRIVER=SQL Server;SERVER=(local);UID=sa;PWD=;APP=Microsoft(R) Windows NT(TM) Operating System;WSID=REDNTS001;DATABASE=pubs
The connection string may also be used in connection with a datasource that has been defined on the workstation:
The SQLSTRINGCONNECT function can be used to test the connection from FoxPro and should return a positive number greater than zero if sucessfully connected to the database server. Use AERROR to resolve any problems.
lcSQL = [DRIVER=SQL Server;SERVER=(local);UID=sa;PWD=;APP=Microsoft(R) Windows NT(TM) Operating System;WSID=REDNTS001;DATABASE=pubs]
LnHandle = SQLSTRINGCONNECT( lcSQL )
An ODBC datasource may be defined on a workstation using the control panel application to provide consistent details to any application running on the workstation. The datasource can include username and password information as well as the default database to use.
The datasource can be one of three types:
· User DSN is available only to the current workstation user.
· System DSN is the most commonly used option and is available to all applications and users on the workstation.
· File DSN is rarely used but has the advantage that it can be installed easily by copying a file into the relevant folder.
The author has had trouble configuring File DSNs to work with FoxPro.
Some points to note when installing an ODBC datasource for SQL Server:
· Storing the username and password in the datasource allows any user on the workstation to access the database. Consider omitting the username and password and providing the information within the FoxPro connection.
· Specify the (local)server when referring to the locally installed copy of SQL Server
· Authentication is more straightforward from the application programmers point of view when using SQL Server security instead of Microsot Authentication. The username and password can reflect the permissions of the application rather than that of the individual user.
· Take care when using the system administrator (sa) password. Preferably ask the database administrator to give you a development username and password.
· Try to set the default database in the datasource to avoid accidentally creating items in the wrong database.
· Test the connection every time.
Specifying an ODBC Driver for a SQL Database
Test the ODBC connection from FoxPro using the SQLCONNECT command. The function should return a positive number greater than zero. Use AERROR to determine any problems:
lnHandle = SQLCONNECT('dsnPubs','sa','')
ODBC is the middleware that connects Visual FoxPro to the server. The performance of the ODBC driver will significantly affect client-server performance. Always use the latest drivers as more recent drivers may offer enhanced performance.
The latest SQL Server drivers from Microsoft use ADO technology and offer a high level of performance which more than matches the DB-LIB library that has traditionally been used with SQL Server and Sybase implementations.
It may be worthwhile looking at third party suppliers of ODBC middleware for non-Microsoft databases. Note that performance may be significantly faster when connected to SQL Server in comparison with non-Microsoft databases such as Oracle and Sybase.
In rare instances the use of a specific library or DLLs or ADO ActiveX controls may provide faster access to data. Connecting to a database with ODBC is usually the preferred method for FoxPro, but you may need to look at these options for specific requirements.
Connection Pooling will improve performance in applications, such as a COM server where connections are being rapidly created and destroyed.
An easy way to modify connection properties is to use the Project Manager to open the required database container. Once the database container is opened the following command may also be used to modify the connection:
OPEN DATABASE dbcNwind
MODIFY CONNECTION conNwind
The Connection designer is also available from the DATABASE menu of an open database container or by right clicking on the background.
Modifying Connection Properties with the Database Designer
The Connection designer allows an existing ODBC datasource to be used or for the full connection string to be entered. The full connection string has the advantage that only the required driver needs to be installed on the workstation.
SQL Server can be configured for integrated security and will automatically sense the login permissions on the database from the currently active NT login.
The defaults shown in the connection properties window above are fine for most requirements:
· Synchronous execution is the best option when beginning to work with client-server systems so leave the ASYNCHRONOUS checkbox empty. FoxPro will wait for all the required data to be retrieved before continuing with processing.
· Displaying the warnings will display any ODBC authentication issues to the user. The alternative is to trap and display the errors under program control (using the AERROR function).
· Leave batch processing and the automatic transactions set to True to allow FoxPro to automatically handle the commit and rollback activity on the database.
Various timeout intervals can be defined for a Connection to help prevent problems with runaway queries. These can normally be left on the default values.
In a development environment or with user defined selection criteria you may find that the server takes a long time to return a query (even if the connection is asynchronous). In this context it is advisable to set the Query timeout so that control returns to the workstation for a runaway query (which is eventually cancelled on the server).
Connections can be defined programmatically in a Database Container with the CREATE CONNECTION command. In this case a Database Container must be open for modification.
CREATE CONNECTION [ConnectionName | ?]
[USERID cUserID] [PASSWORD cPassWord]
| CONNSTRING cConnectionString]
The following example creates a connection in the SALES database container using a datasource that has already been defined on the workstation:
SET DATABASE TO sales
CREATE CONNECTION conNwind DATASOURCE odbNwind
Alternatively the full connection string can be used:
CREATE CONNECTION conpubs ;
CONNSTRING 'DRIVER=SQL Server;SERVER=(local);UID=sa;PWD=;DATABASE=pubs'
All the properties for a Connection object can also be set programmatically into the Database Container with the DBSETPROP command. For example, the username could be changed with the following command:
Connections can be created programmatically with the SQLCONNECT or the SQLSTRINGCONNECT command depending on whether an ODBC connection is created on the workstation or a full connection string is used.
Both function returns a connection handle as a positive integer greater than zero if a connection is made sucessfully. Problems can be resolvved with the AERROR comand.
lnHandle = SQLCONNECT( 'dsnpubs', 'sa', '')
Some of the advanced properties can be set programmatically or with the Connection Designer and are described below:
· The BATCHMODE is used for multiple SQL pass through queries with a single command. Each query can be returned simultaneously in batch mode or one by one.
· DISPLOGIN allows the system to prompt for the user password each time the connection is used.
· The CONNECTBUSY property is read-only but is useful for determining whether an existing query is active.
· The PACKETSIZE defaults at 4096 (4K) and may be changed if the multiples of the record size are significantly smaller or larger than the default.
· TRANSACTIONS may be set to manual and SQLCOMMIT and SQLROLLBACK used to update the server. A simpler approach might be to set the buffering on the cursor to table level and control server updates programmatically.
For very sophisticated access, the ODBC handle may be determined with a connection property and the ODBC driver interrogated directly with low level operating system calls.
The connection handle used for the current cursor can be determined with the CURSORGETPROP() function by looking at the CONNECTHANDLE property. The SQLSETPROP() function can then be used to change connection properties before a subsequent requery.
Default values for some of the options discussed above may be set for the FoxPro environment in the Remote Data tab of the Options window which is available from the FoxPro Tools menu.
Remote Data Options Window
Default options for Connections may be specified programmatically using the SQLSETPROP function with a zero Connection handle. To default all Connections to asynchronous processing the following code is required:
SQLSETPROP( 0, 'asynchronous', .T.)
Similarly the default cursor properties may be set by using the CURSORSETPROP command with a zero workarea. To set the default buffering to table level buffering for all cursors use the following syntax:
CURSORSETPROP( 'buffering', 5, 0 )
Asynchronous processing is an important property of a Connection in that control is returned to the application while a query is still running. The number of records to fetch from the server at any one time is specified for each remote view and the application will continue running once the first set of records has been returned. The remaining records are retrieved in the background.
The default number of records to retrieve is 100 and is determined by the FETCHSIZE property of the view. Using a view that has 10,000 records to be retrieved from the server may take some minutes with a synchronous connection. An asynchronous connection will retrieve the first 100 records and then return control to the application.
The following command will set up a Connection for ASYNCHRONOUS queries so that program control will return whilst the cursor is being populated with data returned from the server. You may need to execute the second SQLEXEC several times before the data begins to be retrieved. A zero indicates that data is being retrieved or that FoxPro is waiting for the first results. A negative value is an error and a 1 indicates that all the data has been retrieved:
lnHandle = SQLCONNECT('dsnDataSource','sa','')
? SQLEXEC(lnHandle, [SELECT * FROM largeTable])
? SQLEXEC(lnHandle, '')
Retrieving more records is performed automatically in the background but is forced to operate immediately by issuing a GO command, for example GO 1000, to force FoxPro to retrieve the first 1,000 records or a GO BOTTOM to force FoxPro to retrieve all the records synchronously. Be careful when issuing this type of command when a large amount of data is being retrieved.
ASYNCHRONOUS queries can cause problems when a Connection is being used for accessing several tables. Use the CONNECTBUSY property and the SQLCANCEL functions to control the connection. In general SYNCHRONOUS connections are easier to handle and you should filter the query to an acceptable number of records.