Creating Client-Server Solutions with Visual FoxPro

Creating Client-Server Solutions with Visual FoxPro

Joe Homnick

Homnick Systems

Client-Server and Visual FoxPro

This presentation employs Microsoft® SQL Server™for Windows NT™ 4.21a as a back end, all examples and sample code reference the demonstration “pubs” database that Microsoft SQL Server for Windows NT ships with.

Client-server development is gaining momentum as more companies select these systems to expand their existing systems and right size those old mini and mainframe databases. Microsoft SQL Server for Windows NT has turned what used to be a costly and complicated process into something that most firms can quickly and affordably assimilate.

Client-server is a system that centrally processes databases and sends the results of commands back to clients. This centralized processing scheme is excellent for databases that require large numbers of users and/or remote access. The Microsoft SQL Server for Windows NT database is also very popular due to its excellent security, ability to handle vast amounts of transactions, fail safe operation and scalability. The following figure illustrates the difference between a distributed database such as Microsoft Visual FoxPro™ and a centralized or client-server database such as Microsoft SQL Server for Windows NT.

Once you have settled on a client-server solution, such as the Microsoft SQL Server for Windows NT, you have only actually decided on the database engine, you must still have a programmable “front end,” so that end users can access this back end. The Microsoft SQL Server for Windows NT ships with a couple of front end tools named Object Manager and System Administrator. These tools are designed for high level manipulation of the database and are not meant to be end user tools for data entry, querying and reporting. The developer must still decide on a front end. Visual FoxPro is an excellent choice for this development environment. The developer must look at three main areas in Visual FoxPro when pulling together the application, connecting, querying and updating.

These operations can be performed using two methods. The developer can create views and manipulate the server tables much like native Microsoft FoxPro® tables or use a method known as SQL Pass Through (SPT). SPT is a method of passing through Transact-SQL native to the back end, with a result set being returned to the FoxPro environment. As we investigate these three main areas we will look at how to use both of these methods to accomplish the desired effects.

Making the Connection

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.

Querying/Accessing the Back End Tables

Now that we are connected to the back end, let us look at how to access those back end tables. Visual FoxPro allows the developer to move selected data from the server to a local FoxPro cursor. Looking back to our first figure we see that with the client-server solution the client, which is our FoxPro front end, sends a command to the server. The server then processes that command and sends the results back to the client. Usually the command that is sent is a Transact SQL Select statement and the result set is a FoxPro cursor.

As we look at these different query options, let’s remember that the trick to optimizing remote queries is to make sure the result set is relatively small. This is somewhat mitigated by the new fetch capabilities of FoxPro, which we will discuss later.

There are two methods of querying the back end tables. One way involves creating a Remote View that is part of the database container. The other way makes use of SPT by employing the SQLEXEC() function. Each of these methods require a SQL Select statement and return a local FoxPro cursor as a result. The methods of implementation, however, are quite a bit different.

Remote Views

Remote views allow the developer to attach remote tables to the database. These views can then be accessed with a standard FoxPro USE command and will accept all standard FoxPro commands such as SKIP, GOTO, REPLACE, LOCATE, etc. The developer should be aware that when a view is used, all of the records associated with the view are transferred from the server to the client in the form of a cursor. Therefore, with large tables it doesn’t make sense to select all of the records into a view. This would simply be copying all of the records from the server each time you used the view. Remember that optimization of remote queries, relates to the number of records returned from a query.

A Remote View can be created visually using the View Designer. This tool can be accessed visually through the New File dialog, Project Manager or Database Designer toolbar. Programmatically it can be accessed by issuing a CREATE SQL VIEW REMOTE command.

The same remote connection can be performed programmatically. There are multiple options available when creating the view programmatically, as can be seen with the following code.

*** Open the Database first to hold the connection
OPEN DATABASE cstest

*** Create remote view and use the table
CREATE SQL VIEW jfh REMOTE AS select * from authors
USE jfh

This code will ask you to select a connection or data source. If you select a connection, that connection will be saved with the view. Selecting a data source will cause the SQL Server Login dialog to be displayed every time you use the table.

*** Create view using a named connection with shared option
CREATE SQL VIEW jfh CONNECTION jfh SHARED AS select * from authors

This view demonstrates the use of a shared named connection. When this view is used, it will use the named connection properties and attempt to use an existing connection. If there is no existing connection, a new connection will be created that can then be shared with other views. This shared option is important since each connection takes up resources on the server and there are a finite number of connections available on the server.

The "?" Qualifier--Filtering Your View

As stated several times, you need to limit the number of rows returned by your view in order to optimize your application. This is accomplished by adding a filter criteria to your SQL Select statement when you create the view. Visual FoxPro has added a powerful new capability to aid in this effort with the “?” operand. When you start a reference for a criteria with a “?”, FoxPro will automatically enter the correct delimiters if required. This greatly simplifies using memory variables or fields in select statements. In this case a picture is really worth a thousand words, so follow the code sample stated below.

*** Create view with parameter criteria
cLname = 'Green%'
CREATE SQL VIEW jfh CONNECTION jfh SHARED AS select * from authors;
	 where authors.au_lname like ?cLname
USE jfh

The result of the above code is to have a local cursor with rows related to authors with a last name starting with Green. By changing the parameter and refreshing the view, different authors are returned to the local FoxPro cursor.

*** Change cLname and refresh view
cLname = 'R%'
=REQUERY(CURSORGETPROP('ConnectHandle'))

If cLname is not defined at the time that you use a view, a parameter dialog will appear requesting an end user input for the value.

SPT Queries

SPT queries are created using the SQLEXEC() function. Just like a Remote View, these queries return a local FoxPro cursor based upon a Transact-SQL select statement. To demonstrate this, I will use the above parameterized query that created a view.

*** Parameterized SPT query
cLname = 'Green%'
nCSHandle = SQLCONNECT('jfh')
nSPT = SQLEXEC(nCSHandle, 'select * from authors;
	 where authors.au_lname like ?cLname', 'jfh')

Note that the nCSHandle value is used in the SQLEXEC() function. The end result of this code is to have a local cursor aliased as ‘jfh’. This is exactly what happens when the above view example is used. There is no appreciable speed difference in using a view as opposed to executing SPT. The SPT simply gives a lower level of control to the developer. The most popular use for SPT is to execute stored procedures on the server as opposed to executing select statements.

Prototyping with Visual FoxPro - A Walk Through

The following discussion will center on creating a Visual FoxPro client-server prototyping environment. This approach allows the developer to go from prototyping development efforts with local tables to deploying the application with remote tables by simply referring to a local or remote database. The walk through example will take you from the database design considerations through the creation of a one to many form that will perform the same against local or remote tables.

The Database Design

In this prototyping example we are going to use the opening/setting of the database as the switch between local prototyping and remote server deployment. There are two databases in the example, JFHLOCAL and JHREMOTE. The goal is to be able to change from local prototyping to remote deployment by simply opening either JFHLOCAL, for local prototyping, or JHREMOTE, for remote deployment.

The client-server database design requires the use of views for both the local and remote tables. By implementing views on the local tables, transition between local FoxPro tables to remote Server tables becomes transparent. Both the JFHLOCAL and JHREMOTE Database Designer Windows appear as the following figure depicts:

The local database container holds two views. The first one is based upon the local authors table and the second on the local titleaut tables. The remote designer window looks exactly the same as the local one concerning the two views. There are however two important differences. First, of course, the JHREMOTE tables reside on the server. Secondly, the titleview view is based on the titleauthors table on the server as opposed to titleaut locally. Note how through the use of views the naming capabilities of different systems is no longer a factor.

These views are parameterized views. The parameter for the authorsview is on the state field and is using a memory variable cState for it’s criteria. The titleview is the many side of the relationship and therefore ties titleauthors.au_id to authorsview.au_id. Since authorsview.au_id is a ? parameter, the view will be looking to the local authorsview to satisfy the parameter value.

The Data Environment of the Form

Several procedures need to be performed in the Data Environment of the form to ensure seamless movement between local and remote tables. Please refer to the following figure as we proceed in the discussion.

We are adding the local views to the forms data environment and will be modifying some of the properties of this environment. First, the AutoOpenTables properties must be set to .F. - False. If this property is True the form will automatically open the tables in JFHLOCAL, which would destroy the cross platform effect desired. We will see later on how we manually open the tables in code. Secondly, the buffering properties of both cursors need to be changed since remote views require some sort of optimistic locking scheme. In this example, we change the Cursor1 (authorsview) buffering property to 3 - Optimistic Record Locking and the Cursor2 (titleview) buffering property to 5 - Optimistic Table Locking. Finally, since we are going to be opening up our own table we set the DataEnvironment InitialSelectedAlias property to authorsview.

The Form Controls

Using the data environment, we drag and drop the authorsview fields onto the form. For this exercise, lets use au_id, au_fname, au_lname and state. We can create a grid from the titleview table by dragging the whole table from the data environment and dropping it on the form. The data control panel that I use comes from the Visual FoxPro Wizard class library called WIZSTYLE.VCX. By adding this visual class library to my forms control toolbar, I can then place the TxtBtns class onto my form. This gives me navigation, find, print, add, update, delete and exit capabilities. Since we are working with a parameterized view as our master table (authorsview) we need a facility to refresh that view. We do this by adding a Requery button at the end of the TxtBtns class.

The form with the controls is depicted below:

The Form Code

Code is required in three places to complete this form. First, since we are opening the tables manually, we need to insert this code into the Load Event of the form.

USE authorsview 
USE titleview IN 0

The use of the authorsview could be extended to include the NODATA keyword. With NODATA the end user would need to execute a requery to populate the data in the form.

The next piece of code is placed in the Form Refresh Evenet. Since we are using a parameterized view to populate our grid, we need to requery that view each time we move within the parent authorsview table.

=REQUERY('titleview')
SELECT authorsview

Finally, we add the code to the Requery button click event. Since we left the name of this button the default Command1, the following code should be placed in the Command1.Click event.

=REQUERY('authorsview')
ThisForm.Refresh

Conclusion

You can now run the form from the local prototyping tables by opening/setting the JFHLOCAL database. Conversely, the remote deployment tables can be as easily accessed by opening/setting the JHREMOTE database. By following the basics of this exercise you easily set up powerful client-server applications that run transparently on local prototyping tables.

Visual FoxPro and the Microsoft SQL Server for Windows NT make an unbeatable combination for client-server development. Visual FoxPro has gained client-server capabilities that surpass any other front end currently available. The Microsoft SQL Server for Windows NT is the most robust server for the NT environment. Developers should seriously consider this powerful environment for their client-server projects.