Since FoxPro is being used as the front end and the back end is not the native FoxPro engine, but rather the Microsoft SQL Server for Windows NT, the developer must connect to this foreign engine. The method of connecting used in FoxPro is through the Open Database Connectivity (ODBC) standard. These ODBC drivers must be present for the connection to take place. A "gotcha" to take into account here is that FoxPro is a true 32 bit application and, therefore, requires 32 bit ODBC drivers. For those of you who were familiar with ODBC through installations of Microsoft Word, Microsoft Access, Microsoft Excel, Microsoft Visual Basic® 3.0 or FoxPro 2.6, Visual FoxPro requires an updated version of 32 bit drivers.
A connection takes place by specifying three or four parameters, depending on the type of connection. In a standard connection three parameters, the Data Source Name, User ID and Password are supplied. In a connection through a connection string, an additional parameter for the database to use is included. The ability to specify the database at the time of connection makes the connection string method the preferable connection method and in some back ends, other than the Microsoft SQL Server for Windows NT, it is the only connection method available.
Visual FoxPro has a unique capability in that the developer can store named connections in a database. These named connections are handy because you specify connection parameters that are saved in the database container and can then be referred to repeatedly by name. These connections can be created visually or programmatically. An added extra capability of these connections is that they can be shared, so that multiple processes can use just one connection, thereby decreasing the server system requirements.
The figures below show the two options available when you create or modify a database connection.
The Connection Designer can be invoked visually using the New File dialog, Project Manager or programmatically through the following code:
*** Open the Database first to hold the connection OPEN DATABASE cstest CREATE CONNECTION jfh
This existing connection can be modified in the future visually through the Project Manager or with the Modify Connection command. The following shows how to create the above named connections using code.
*** Standard connection CREATE CONNECTION jfh ; DATASOURCE 'Mars32' USERID 'sa' PASSWORD 'homnick' *** Connection string CREATE CONNECTION jfh ; CONNSTRING 'DSN=Mars32; UID=sa; PWD=homnick; DATABASE=pubs;'
The Connection Designer also allows you to define when and if a standard CS Login dialog is displayed. By not including one of the parameters, such as the password, a dialog (depicted below) will prompt the user for the missing information. Once again, it is preferable to use connect strings in most cases because of its capability to define which database to use.
The developer can also connect through SPT. A SPT connection is executed through the use of the SQLCONNECT() and SQLSTRINGCONNECT() functions. There are three ways to use SQLCONNECT(). The SQLSTRINGCONNECT() function is the preferred method of passing a connection string without an existing database connection. The code examples listed below show each possible usage.
*** 1. Invoke a SQL Server Login dialog
nCSHandle = SQLCONNECT()
*** 2. Standard connection
nCSHandle = SQLCONNECT('Mars32', 'sa', 'homnick')
*** 3. Connection using named database connection jfh
*** This database connection can be created as a standard connection
*** or with a connection string
nCSHandle = SQLCONNECT('jfh')
*** Connection string
nCSHandle = SQLSTRINGCONNECT('DSN=Mars32; UID=sa; PWD= homnick; DATABASE=pubs;')
The variable nCSHandle returned by the function can then be used in other SPT functions to point to this connection.