Session E-CTEC

Intermediate Client-Server Techniques

Ted Roche

Contents

 

Beyond the basics of Visual FoxPro's use of connections and views to make remote and local computing very similar, there are a number of things you can do with client-server beyond the basic model of reading and writing data. In this session, we'll look at techniques for taking advantage of the capabilities of back-end servers and look more closely at the native VFP capabilities of working with server databases.

Client Server Technologies and Visual FoxPro

This short paper is not meant to be a comprehensive reference for all things client-server. Rather, it is intended to serve as a short introduction to the concepts and the techniques used to do client-server applications in Visual FoxPro. Attendees should have a familiarity with Visual FoxPro and the use of FoxPro's Database Container and Views in working with local data.

We'll start out with some simple definitions of the terms, examine why you would want to do client server, and then dig into the commands and functions that make it possible. We conclude with some discussions about some of the more advanced techniques and how you might consider integrating client server into your framework.

What Is Client-Server?

There are a number of definitions, more or less scholarly, more or less robust, about what exactly is client-server. One vendor, a few years ago, quipped "Client-server? Well, that's whatever it is I have to sell right now." Typically, for our purposes, the key element of client-server is that two separate processes are going to be running, one Visual FoxPro, the other the process that provides data to Visual FoxPro. These two programs can be running on either the same machine or separate ones, with some form of connection between them (network, serial, or Internet) to allow them to communicate.

Why Client-Server?

Many clients seem to be interested in client-server for reasons that go beyond the technical merits. This is certainly as valid, but be aware of the pitfalls of creating a client-server solution if it is not done primarily for a technical reason. There are three primary reasons for considering client-server:

Data Size: Visual FoxPro, like all of the xBase variants before it, is limited to 2 GB for any one table or file. This limitation has to do with the way that locking is performed on individual records and, while it is certainly feasible for this limit to be raised, it is unlikely we will see Microsoft do this. While there are many work-arounds to this limit, there are increasing challenges with FoxPro as tables become larger – longer times to reindex in case of corruption, for example. Overall, if size becomes a major factor, considering client-server is wise.

Security: Fundamentally, all access to FoxPro tables goes through the network operating system, so the user needs to have access to the directories containing the VFP tables. Anyone who can get access to the tables can, eventually, figure out how to read them, whether it is simply a matter of using an ODBC driver and Excel, or if they need to use a hex editor to crack your encryption scheme. Many client server systems can eliminate this threat altogether by only allowing access from the client to the server's interface, and not necessarily to the data at all. If you are dealing with highly confidential materials, client-server makes sense for security reasons.

Low bandwidth: Visual FoxPro is the fastest performing, most powerful desktop and LAN-based database management system available today, bar none. But VFP gets its phenomenal performance by taking advantage of the LAN environment, pre-fetching columns of information, caching table headers and index contents locally. While the process of acquiring this information is nearly imperceptible in a network environment, slowing the initial opening of tables by milliseconds, this can be a substantial delay if there is a "thin wire" (for example, a dial-up, WAN, or busy Internet connection) between the client and data. In these situations, placing the data and the responsibility for querying and processing the data at the server will minimize communication costs and improve speed.

Why NOT Client-Server?

As mentioned above, clients may be disappointed if they have selected a client-server architecture only to find the resulting product less than satisfactory. There are several reasons to consider other solutions other than client-server:

Resource Constraints: Client-server is fundamentally more complex than a single-tier, monolithic application. A new application needs to be learned, with its foreign terminology, syntax and language. Connectivity, ODBC and network issues will need to be addressed. New APIs may need to be mastered. Anomalies, infelicities, and plan old bugs in the vendor's interface and drivers need to be resolved. If your team is already maxxed out and behind schedule, don't think that client-server will be a silver bullet.

Ongoing maintenance: While vendors are trying to make their applications more "set it and forget it" installations, there are no "hands-free" installations. A Data Base Administrator (DBA) has to be available, at least part-time, to check logs, ensure backups are working, ensure disk space is adequate, etc. Realistically, this is a requirement for any computer installation, but servers typically need a little additional attention. Make sure to factor in the cost of a DBA as part of your client-server rollout.

Infrastructure instability: The perception among upper management is that client-server is inherently more robust, and that a client-server installation is less prone to crashing than a FoxPro installation. In fact, the stability of the network operating system and its servers have no relation to the software running on them, and an unstable NOS that crashes and requires a FoxPro application to reindex all of its files is going to require a server to go through recovery procedures, also. A far better use of effort in many of these cases is just to fix the crashing servers instead.

Performance: Provided that sufficient bandwidth is available, performance alone is never the reason to consider client-server, unless the client requires the application to perform more slowly. On equivalent hardware, Visual FoxPro runs circles around any data server software.

How Client Server Works

The primary difference between working with local (native VFP data) and client-server data is that you need to establish a connection to the server. After the connection is established, a view can be used to read and write data. In addition, the equivalent of FoxPro's native SQL statements can be used to provide more direct control than is usually available via views. Finally, like nearly everything else in Visual FoxPro, there is even another layer of fine control available to developers who require it.

Connections

A connection uses portions of Microsoft's Open DataBase Connectivity (ODBC) to establish a two-way communication channel between Visual FoxPro and the target server. This means that each client machine must have installed on it specific drivers for the target server. Each vendor has their own mechanism for providing, installing, configuring and supporting these drivers. Once established, the connection from FoxPro can be configured.

There is some confusion in the terminology VFP uses regarding connections. When you CREATE or MODIFY a CONNECTION using the syntax below, you are modifying the definition of a connection, and not actually affecting an existing connection. The connections stored within the DBCs are definitions of connections to be used when called upon.

FoxPro's connection can be defined visually within the DataBase Container (DBC) or directly from the Command Window with CREATE CONNECTION. Similar visual and command-based equivalents exist to DELETE, MODIFY, RENAME and DISPLAY CONNECTIONs.

Connections can refer to an ODBC Data Source Name (DSN), defined with the ODBC Control Panel Applet, or they can specify the equivalent parameters directly. In code, the connection settings might look like:

* The first connection depends on a pre-established

* DSN defined using the Control Panel

DEFINE Connection cnTest ;

DATASOURCE dsnAccessNWind ;   && a pre-existing ODBC DSN

DATABASE C:\Access\NWind.MDB  && point directly to the data

 

* The second connection, a DSN-less connection

DEFINE Connection cnVFPFile ;

  CONNSTRING "DRIVER={Microsoft Visual FoxPro};" + ;

             "SOURCETYPE=DBF;" + ;

             "SOURCEDB=C:\VS98\VFP98\"

 

To find out what parameters are required by your ODBC driver, check out the documentation by calling it up in the Control Panel's ODBC Applet, and selecting Help.

Finally, connections do not need to be maintained at all within a DBC. If the remainder of the system is using SQL Pass Through, connections can be created on the fly as well, using the SQLConnect() and SQLStringConnect() functions:

* Open a connection using a DSN, User ID and password

lnHandle = SQLConnect("mySQLServerDSN","SA","")

 

* Connect to a VFP table via the ODBC driver without a DSN:

lnHandle = SQLStringConnect("DRIVER={Microsoft Visual FoxPro};" +;

                            "SOURCETYPE=DBF;" + ;

                            "SOURCEDB=C:\VS98\VFP98\")

 

Views

Remote views are not remarkably different from local views, but there are a couple of areas of concerns.

The first concern is that the data types stored within the remote database may not map exactly to equivalent Visual FoxPro types. For example, there is no concept of a Logical or Boolean data type within Oracle's data scheme. This can be easily resolved by using the Field Properties Dialog Box and specifying the desired data type. Use this option also to map datatime fields to dates. Data fields are uncommon on backend databases, but Visual FoxPro math functions on dates is in units of days and on datetimes, in seconds. Take care to ensure that you are using the correct units.

A second concern is the proper use of connections. In the view designer, under the "Advanced Options" menu option on the menu labeled "Query," you will see a checkbox labeled "Share Connection." Simply because views are defined pointing to the same connection definition does not mean that each will try to share that actual connection, on the contrary, each view will create its own copy of the defined connection unless this checkbox is checked. There is an equivalent global setting in Tools/Options/Remote Data, but it did nothing in VFP 3 and 5. Finally, in VFP 6, it does cause the Share Connection to be selected by default when it is checked. See "Views and Connections" below for a discussion about the issues involved.

Like Connections, views may be defined and manipulated programmatically as well as visually. There are some real advantages to defining views programmatically. It is easy for everyone to see the definition for the view. You don't run into the problem with the View Designer where complex relationship are not displayed nor generated correctly. Finally, it can be easy to extended the meta-data nature of the system to actually generate Views on the fly. Take a look at GenDBC (included with Visual FoxPro in the HOME() + "Tools\GenDBC" directory) for some ideas on extracting database meta-data from the DBC and using that to generate the commands required to regenerate the DBC.

The CREATE VIEW command creates a remote view in the current database container. The format of the commands seems a bit strange, because the SQL SELECT command contained with CREATE VIEW command is not delimited in any way – it is not set off in quotes or parentheses or anything. This SQL SELECT commands describes how to retrieve the data to produce the view. Remember that Visual FoxPro is not retrieving this data – a server application is. So, the SQL used in the CREATE VIEW command is the SQL and its extensions native to the back-end server and not to Visual FoxPro. This means you want to avoid "FoxProisms" of built-in FoxPro functions (TRIM(), STR(), VAL()) unless you know they are supported by the back end.

There is one exception to speaking in the native tongue of the back-end server, and that is the situation of asking ODBC to translate some phrasing along the way. Remember that we are using ODBC to connect Visual FoxPro to the correct database driver. ODBC also provides an API and includes the ability to translate the request from a more generic SQL into one understood by the back end. Functions you are asking ODBC to process are set off within your SQL code by curly braces. These codes can include requests for built-in ODBC functions, translations of certain date and time formats, and even specific extensions supported by some drivers. Check out the documentation of your particular ODBC driver by calling it up in the Control Panels ODBC Applet, and selecting Help.

* Example of a view defined in code:

CREATE VIEW l_Customers_State ;

   REMOTE CONNECTION cnOracle SHARED AS ;

   SELECT * ;

     FROM Customer ;

    WHERE Customer.State = &pcState ;

    ORDER BY Customer.Name

 

Views and Connections

The reason you need to be concerned about how many connections you establish with the server is because each connection consumes resources, resources that tend to be precious to the server and tend to limit the scalability of the entire system. Also, some vendors license their servers by connection, rather than by user, so this could become a licensing issue.

So, why not share all communications with the server over just one connection? Well, there are several restrictions that make it more challenging to use only one connection. First, connections defined within a DBC can only be used for views within that DBC. If you have multiple DBCs in your application (and this is one or many good reasons not to do that), then you will be maintaining multiple connections. Second, while SQL Pass Through commands can share a connection with a view, by querying its connection and then using that same handle, a view will not share a connection first opened as SQL Pass Through. Finally, commands which are designed to take some time, or require more than a single "Request-Response" communication between client and server will "tie up the phone line" and need to be on their own connection. Such commands include progressive fetches, batches of commands, pre-compile commands, and several other situations. In this case, plan on one of more connections for the batch processes, and plan on sharing one connection, if possible, for the views and short-term SQL statements.

SQL Pass-Through (SPT)

As mentioned in the previous sections, all of the functionality that can be achieved using the Database Container and the visual tools is available programmatically within the language as well. Always one of the hallmarks of Visual FoxPro, it is particularly well implemented here.

 

Function

Purpose

SQLTables()

Returns a list of tables or view for a specified connection. Handy for black-box utilities.

SQLColumns()

Returns field specifications in one of several formats, useful for matching VFP's capabilities to that of a foreign database.

SQLConnect(), SQLStringConnect()

Described above, used to establish a connection with the server.

SQLExec()

Passes commands to the server, via ODBC, to be executed.

SQLCommit(), SQLRollback()

Provides the means to complete or abort a transaction. The transaction must be started in a manual mode, with a SQLSetProp(), described below.

SQLSetProp(), SQLGetProp()

Reads and sets properties for an existing connection.

DBSetProp(), DBGetProp()

Reads and sets properties within the DataBase Container on views, connections, tables, etc. These are the only settings persistent between sessions.

CursorSetProp(), CursorGetProp()

Set or read properties on open cursors, including buffering.

Advanced Client-Server

While the details of these topics are way beyond what is appropriate to cover in an introductory session, exposure to these concepts may give you some ideas on where you can consider taking these products in more advanced applications:

Batch SQL: Multiple SQL statements can be sent to the back end, with processing continuing on the front end, and occasional check son the status of the back end processing.

Progressive Fetching: A great convenience when pulling down a potentially large data set, progressive fetching returns control to FoxPro after a predetermined amount of records have been returned, and automatically retrieves additional records as needed. An excellent example would be a grid picklist of customers, where the operator could specify search criteria, and the customers would be listed in order of their likelihood to be picked. The operator could page down through the list, and additional records would be fetched as needed.

Precompilation: The SQLPREPARE() function allows the back-end to receive a SQL statement to execute, and be able to perform it's functions upon it – parsing, analysis, development of a showplan, etc. – and then execute the operation must faster when called upon.

In all three of these cases, these functions would require a separate connection, since they "tie up the phone line" as we mentioned above in the section on Views and Connections. However, if these functions provide a needed

Suggestions for Learning Client-Server

Client-server applications can seem like a lot to take on, but they are not too difficult if learned one piece at a time. I recommend learning to use the basic ODBC functionality within Visual FoxPro by experimenting directly with another table or database on your machine, using the VFP ODBC driver. As you gain experience, try DSN-less connections, direct connection using SPT, and attempt to rollback and commit various changes to the data. After you have gotten familiar with the basic commands and functions within Visual FoxPro, take a look at the server software. Learn how to install and configure it, if necessary, and what components are required to be installed on the client machines for connectivity and ODBC driver interfaces. Many products come with a simple command-window or interactive interface. Use this to verify that connectivity is working. It can be very frustrating to take on many unknowns and find yourself with a non-functioning system and no way to tell if you specified the wrong network protocol, the wrong addresses or ports, or if there is something left to configure.

Framework support

All FoxPro developers should be using a consistent framework for developing their applications. Rather than starting from scratch and re-inventing the wheel, a framework can provide a good starting point for development, and an ever-growing level of sophistication in the services provided. One of the key features a framework should provide is the routine services of retrieving and saving data.

Visual FoxPro is ideal for providing framework support for data services because there are several consistent and rich models of working with data. Selecting one of these models and using it consistently within your framework will provide a robust data mechanism.

Conclusion

Visual FoxPro provides excellent tools, both of the graphical variety, and through the command line, to fully control a client-server installation with Visual FoxPro acting as the client. Client-server technologies are appropriate for extremely large data sizes, for high-risk security situations, and for environments with low bandwidth communications from client to server. While the View Designer and the Connection Designer could allow a completely graphically-driven system design, Visual FoxPro also provides the tools and functions to mix programmatically-driven (or even metatable-driven) coding along with the graphical design. This combination makes for ease of learning combined with power in implementation.

About the Author

Ted Roche is a Microsoft Certified Solution Developer and the Director of Development at Blackstone Incorporated, a Microsoft Certified Solution Provider based in Waltham, Massachusetts, USA, where he develops complex applications using Microsoft's Visual Tools and BackOffice products. A contributor to six books on FoxPro, Ted's last effort was to co-author the "Hacker's Guide to Visual FoxPro 6.0" available from Hentzenwerke Publishing, as well as from finer bookstores everywhere. Ted has spoken at over a dozen professional conferences in Canada, Europe and the US, and co-authors the "Advisor Answers" column for FoxPro Advisor magazine. In his spare time, Ted is restoring an Arts and Crafts bungalow, collecting wild fruit on his New Hampshire estate, and herding a half-dozen cocker spaniels. Contact Ted at (781) 663-7400, or via e-mail at tedroche@compuserve.com