Advanced Client-Server Functionality in Visual FoxPro


Erik Svenson

Microsoft

Advanced Client-Server and Visual FoxPro

This presentation employs Microsoft® Windows NT SQL Server as a back end. All examples and sample code reference the demonstration PUBS database that ships with NT SQL Server.

The advanced client-server capabilities of Microsoft Visual FoxPro™, for the most part, revolve around the setting of properties for named and unnamed connections, remote views, and cursors. These properties are readable through DBGETPROP, CURSORGETPROP and SQLGETPROP. Conversely, these properties can be written to using DBSETPROP, CURSORSETPROP and SQLSETPROP. In this paper we will be looking at different scenarios where these properties are manipulated to perform advanced client-server procedures. Specifically, we explore techniques for updating remote tables, fetching remote data, creating heterogeneous joins, dynamic view connections and shared view connections.

Updating Remote Tables

Updating the back end tables using local cursors is performed through the Microsoft FoxPro® cursor buffering scheme. This discussion will not center on how FoxPro table buffering works but rather on the unique considerations of buffered remote tables. Through the use of buffering, those cursors that are the result sets of SQL Select statements can be updated.

With local FoxPro tables you can enable buffering with a single CURSORSETPROP() function, by passing the function parameters of ‘buffering’ and a numeric equivalent. With remote tables, the buffering scheme requires more information. FoxPro needs to know the primary key fields so as to execute an update. The actual table names that are being updated on the server must be supplied, since the table name might not have anything to do with the alias name and there might be more than one table involved in the view. Finally, a mapping of the fields might be required if the field names in the FoxPro table do not correspond to the column names in the server table. Let’s look at some examples using a view. We will start with the simplest arrangement and then progress to a more complex one.

The simplest way to implement remote updating is visually through the View Designer. As can be seen in the following figure, the View Designer has a page frame devoted to updating. This page differentiates the View Designer from the Query Designer. All of the settings that we hard code in the examples below can be implemented visually with the View Designer.

This next example is the simplest hard coded scheme requiring the least amount of settings. This view was created using the statement “select* from authors” .

*** Simple arrangement using a view
USE jfh
*** Set tables to update
=CURSORSETPROP('Tables', 'authors')
*** Set primary key field
=CURSORSETPROP('KeyFields', 'au_id')
*** Set capability to update
=CURSORSETPROP('SendUpdates', .t.)
*** Set fields to update
=CURSORSETPROP('UpdateFieldList', 'au_id,au_fname,au_lname,;
			phone,address,city,state,zip,contract')
Remote views default to a optimistic record locking scheme so the buffering property does not need to be set. This next example assumes joined tables that require field name mapping, allowing update of three of the fields and we are changing the buffering scheme to an optimistic table buffering scheme.

*** More complex remote update scenario using a join view
*** where some local cursor row names different than back 
*** end table names and all fields are not updatable

CREATE SQL VIEW jfh REMOTE CONNECTION jfh SHARED AS ;
	select authors.au_id, authors.au_lname, authors.contract,;
	titleauthor.au_ord as titleau_ord,titleauthor.royaltyper ;
   as titleroyaltyper, titleauthor.title_id, titlauthor.au_id ;
	as titleau_id from authors,titleauthor ;
	where authors.au_id = titleauthor.au_id

USE jfh
*** Set tables to update
=CURSORSETPROP('Tables', 'authors,titleauthor')
*** Set primary key field
=CURSORSETPROP('KeyFieldList', 'au_id, titleau_id, title_id')
*** Set capability to update
=CURSORSETPROP('SendUpdates', .t.)
*** Set field mapping
=CURSORSETPROP('UpdateNamelist',;
    'titleau_ord titleauthor.au_ord, ;
	 titleroyaltyper titlauthor.royaltyper;
	 titleau_id titleauthor.au_id. title_id titleauthor.title_id;
	 au_id authors.au_id')
*** Set fields to update
=CURSORSETPROP('UpdateFieldList', 'contract,titleau_ord,;
			titleroyaltyper')

*** Set buffering to optimistic table locking
SET MULTILOCKS ON
=CURSORSETPROP('Buffering', 5)
The SQL Pass Through (SPT) implementation looks similar to the above coding example for a view. With SPT, instead of using an existing view to create the local cursor, the developer executes a SQLEXEC() function. To implement the above complex update scenario using SPT, the code would appear as follows:

*** SPT remote update scenario using a join
nCSHhandle = SQLCONNECT('jfh')
=SQLEXEC(nCSHandle, ;
	'select authors.au_id, authors.au_lname, authors.contract,;
	titleauthor.au_ord as titleau_ord,titleauthor.royaltyper ;
   as titleroyaltyper, titleauthor.title_id, titlauthor.au_id ;
	as titleau_id from authors,titleauthor ;
	where authors.au_id = titleauthor.au_id')
USE jfh
*** Set tables to update
=CURSORSETPROP('Tables', 'authors,titleauthor')
*** Set primary key field
=CURSORSETPROP('KeyFieldList', 'au_id, titleau_id, title_id')
*** Set capability to update
=CURSORSETPROP('SendUpdates', .t.)
*** Set field mapping
=CURSORSETPROP('UpdateNamelist',;
    'titleau_ord titleauthor.au_ord, ;
	 titleroyaltyper titlauthor.royaltyper;
	 titleau_id titleauthor.au_id. title_id titleauthor.title_id;
	 au_id authors.au_id')
*** Set fields to update
=CURSORSETPROP('UpdateFieldList', 'contract,titleau_ord,;
			titleroyaltyper')

*** Set buffering to optimistic table locking
SET MULTILOCKS ON
=CURSORSETPROP('Buffering', 5)
Most of these properties can also be preset set for a view within the database container using the DBSETPROP() function. This gives the developer the capability of defining standard properties for a view and then simply setting buffer properties after using the view. For the complex remote update example above these settings can be performed as follows:

*** Set capability to update
=DBSETPROP('jfh','view','SendUpdates', .T.)
*** Set tables to update
=DBSETPROP('jfh','view','Tables', 'authors,titleauthor')
*** Set primary key fields
=DBSETPROP('jfh.au_id','field','KeyField',.T.)
=DBSETPROP('jfh.titleau_id','field','KeyField',.T.)
=DBSETPROP('jfh.title_id','field','KeyField',.T.)
*** Set fields to update
=DBSETPROP('jfh.contract','field','Updatable',.T.)
=DBSETPROP('jfh.titleau_ord','field','Updatable',.T.)
=DBSETPROP('jfh.titleroyaltyper','field','Updatable',.T.)
*** Set field mapping
=DBSETPROP('jfh.au_id','field','UpdateName','authors.au_id')
=DBSETPROP('jfh.titleau_id','field','UpdateName','titleauthor.au_id')
=DBSETPROP('jfh.titleau_ord','field','UpdateName','titleauthor.au_ord')
=DBSETPROP('jfh.title_id','field','UpdateName',;
		'titleauthor.title_id')
=DBSETPROP('jfh.titleroyaltyper','field','UpdateName',;
		'titlauthor.royaltyper')
Once these properties are set, a view can be used and these properties will be preset. This gives the view approach to querying and updating a distinct advantage over using SPT. With SPT, properties must be reset every time a new local cursor is created.

Outer and Heterogeneous Joins

Since Transact-SQL is being used in creating views with Windows NT SQL Server as a back end, outer join capability is available. A left or right outer join can be specified by simply placing a “*” on either side of the joins “=“ sign. This outer join can also be implemented visually through the use of the view designer. Here is an example of an outer join view.

*** Create left outer join view 
CREATE SQL VIEW jfh CONNECTION jfh SHARED AS select * from authors,titleauthor;
	 where authors.au_id *= titleauthor.au_id
A heterogeneous join exists when you join tables from two different environments. In your client-server implementation you are not necessarily going to maintain all tables on the server. Some tables, for example fairly static reference tables, might be maintained locally. To join these tables from the different environments you must first create a Remote SQL View for the remote table and then a local SQL View that references the remote view and local table. To demonstrate this, I have copied the sample authors table and named it lauthors. The example will join this local lauthors table with the remote titleauthor table.

*** Create heterogeneous join view 
CREATE SQL VIEW jfh REMOTE CONNECTION jfh SHARE;
    AS select * from titleauthor

CREATE SQL VIEW hview AS select * from lauthors, jfh;
	 where lauthors.au_id = jfh.au_id
Fetching

An exciting new feature in Visual FoxPro is the concept of progressive fetching of query results. By default, FoxPro performs background fetches of 100 rows at a time. This value can be changed by using the DBSETPROP() function for views and the SQLSETPROP() function for SPT queries. This progressive fetching allows the developer to execute browses or reports while the fetching goes on. For example, a query can be executed that returns 10,000 rows and a report is executed on these results. By using the fetching capabilities, the report can start running while the background fetching is being carried out. Another example would be in a search routine that doesn’t allow the end user to download too large a portion of the table with too broad a search. The fetch value can be set in several ways, as demonstrated below.

*** At cursor level for SPT and Views
= CURSORSETPROP('FetchSize', 500)
*** At database level for Views
= DBSETPROP('jfh', 'View', 'FetchSize', 500)
The fetch can be canceled using the SQLCANCEL() function. This function requires a parameter of the connection handle. In the case of a view this connection handle can be ascertained through the use of the CURSORGETPROP() function.

*** Sample fetch cancel for a view
=SQLCANCEL(CURSORGETPROP('ConnectHandle'))
Another useful property that can be used with the fetch is to set the maximum number of records fetched. This can be used in conjunction with limiting the number of rows returned in a search or just an upper limit safety net, so that too many rows are never returned.

*** At cursor level for SPT and Views
= CURSORSETPROP('MaxRecords', 50000)
*** At database level for Views
= DBSETPROP('jfh', 'View', 'MaxRecords', 50000)
Dynamic View Connections

When a view is created, a named connection can be associated with that view. The connection is a read-only property of the view and cannot be programmatically changed. This presents a problem when different connection parameters need to be instituted at the time of connection. Varying the User ID and Password properties of a connection are commonplace occurrences in client-server applications. One option available to the developer is to have multiple occurrences of the same view with differently-named connections. This results in redundant views that add needless maintenance and complexity to the database container. Another option is to change the properties directly in the connection before opening the view. Unfortunately, this approach will not work efficiently in a DBC that is accessed by multiple users. Since making changes directly to the connection changes properties that are shared between multiple users, unacceptable results can occur from this approach.

To implement this dynamic connection capability I wrote a class I call SQLUnique. In the following paragraphs we will analyze this class so as to understand how view and connection properties can be manipulated by the developer.

This class initially creates a unique named database. After the unique database is created upon instantiation of the class, the developer can open a view with a chosen connection by calling a class function. To implement this class, the developer first defines a form property that holds the instantiation of the class. The reason for assigning a variable to a form-level variable is that when the form is released, the instance of the class is released. This triggers the destroy event of the class, which closes and erases the temporary database. The code that needs to be inserted into the load of the form is as follows:

oJFHView = CREATEOBJECT('SQLUnique')
oJFHView.UniqueView('authorsview','pubs','nodata')
Note that oJFHView is a form property that is automatically released when the form is. Also that the UniqueView procedure can be called for as many remote views as desired to populate the data environment.

We’ll now review the code related to the class itself. For brevity’s sake, error handling and the code for protecting class properties/variables have been dropped from this review. The class assumes the correct database is SET/OPEN and that the remote views and named connections exist in that database.

This first part of the code defines the class and defines two properties for the current database and unique database. The INIT procedure is the procedure that executes when the class is first instantiated with the CREATEOBJECT function. This procedure simply creates the unique database. All the code that follows from DEFINE CLASS to ENDDEFINE relates to the SQLUnique class.

DEFINE Class SQLUnique AS Custom
  cCurrentDB=SET('Database')
  cUniqueDB=SYS(3)

  PROCEDURE Init
    CREATE Database (this.cUniqueDB)
    SET Database TO (this.cCurrentDB)
This next part of the class is the destroy procedure, which closes the temporary database and then deletes the different components. The destroy procedure executes at the time the memory variable that the class was instantiated in is released. Since this is a form property, this procedure executes when the form is released.

PROCEDURE Destroy
      SET Database TO (this.cUniqueDB)
      CLOSE Data
      SET Database TO (this.cCurrentDB)
      fileerase=this.cUniqueDB+'.dbc'
      ERASE (fileerase)
      fileerase=this.cUniqueDB+'.dct'
      ERASE (fileerase)
      fileerase=this.cUniqueDB+'.dcx'
      ERASE (fileerase)
    ENDIF
The UniqueView procedure starts by accepting the parameters for the view, connection, and any USE command parameters. An array of all connection properties for the connection passed as a parameter is created. Our earlier example used the pubs connection as a parameter.

PROCEDURE UniqueView
    PARAMETERS cView, cConnection, cParms
    DIMENSION aGetProp(13)
    aGetProp(1)=dbgetprop(cConnection,'connection','Asynchronous')
    aGetProp(2)=dbgetprop(cConnection,'connection','BatchMode')
    aGetProp(3)=dbgetprop(cConnection,'connection','ConnectString')
    aGetProp(4)=dbgetprop(cConnection,'connection','ConnectTimeout')
    aGetProp(5)=dbgetprop(cConnection,'connection','Datasource')
    aGetProp(6)=dbgetprop(cConnection,'connection','DispLogin')
    aGetProp(7)=dbgetprop(cConnection,'connection','DispWarnings')
    aGetProp(8)=dbgetprop(cConnection,'connection','IdleTimeOut')
    aGetProp(9)=dbgetprop(cConnection,'connection','PassWord')
    aGetProp(10)=dbgetprop(cConnection,'connection','QueryTimeOut')
    aGetProp(11)=dbgetprop(cConnection,'connection','Transactions')
    aGetProp(12)=dbgetprop(cConnection,'connection','UserId')
    aGetProp(13)=dbgetprop(cConnection,'connection','WaitTime')
Once we have all the parameters from the current database (authorsview) we now switch to the uniquely named database and create a new connection. Using DBSETPROP all properties not set by the CREATE CONNECTION command are transferred.

SET Database TO (this.cUniqueDB)
    CREATE CONNECTION (cConnection) CONNSTRING " "

    =dbsetprop(cConnection,'connection','Asynchronous',aGetProp(1))
    =dbsetprop(cConnection,'connection','BatchMode',aGetProp(2))
    =dbsetprop(cConnection,'connection','ConnectString',aGetProp(3))
    =dbsetprop(cConnection,'connection','ConnectTimeout',aGetProp(4))
    =dbsetprop(cConnection,'connection','Datasource',aGetProp(5))
    =dbsetprop(cConnection,'connection','DispLogin',aGetProp(6))
    =dbsetprop(cConnection,'connection','DispWarnings',aGetProp(7))
    =dbsetprop(cConnection,'connection','IdleTimeOut',aGetProp(8))
    =dbsetprop(cConnection,'connection','PassWord',aGetProp(9))
    =dbsetprop(cConnection,'connection','QueryTimeOut',aGetProp(10))
    =dbsetprop(cConnection,'connection','Transactions',aGetProp(11))
    =dbsetprop(cConnection,'connection','UserId',aGetProp(12))
    =dbsetprop(cConnection,'connection','WaitTime',aGetProp(13))
With the connection being transferred to the unique database, we go back to current database and start getting the properties for the view that is going to be attached to the specific connection.

SET Database TO (this.cCurrentDB)

    aGetProp(1)=dbgetprop(cView,'view','FetchMemo')
    aGetProp(2)=dbgetprop(cView,'view','FetchSize')
    aGetProp(3)=dbgetprop(cView,'view','MaxRecords')
    aGetProp(4)=dbgetprop(cView,'view','SendUpdates')
    aGetProp(5)=dbgetprop(cView,'view','ShareConnection')
    aGetProp(6)=dbgetprop(cView,'view','SQL')
    aGetProp(7)=dbgetprop(cView,'view','Tables')
    aGetProp(8)=dbgetprop(cView,'view','UpdateType')
    aGetProp(9)=dbgetprop(cView,'view','UseMemoSize')
Since two of the properties necessary for updatability, KeyField and UpdateName, are only available at a field level, we need to create a field level array to capture them. I use the view in question with the ‘nodata’ keyword to get an available field list for that view. This is a good example of accessing field-level properties.

USE (cView) NODATA
    DIMENSION aGetFldProp(FCOUNT(),4)
    FOR jfhx=1 TO ALEN(aGetFldProp,1)
      cViewFld=cView+'.'+FIELD(jfhx)
      aGetFldProp(jfhx,1)=FIELD(jfhx)
      aGetFldProp(jfhx,2)=dbgetprop(cViewFld,'field','KeyField')
      aGetFldProp(jfhx,3)=dbgetprop(cViewFld,'field','Updatable')
      aGetFldProp(jfhx,4)=dbgetprop(cViewFld,'field','UpdateName')
    NEXT
    USE
We now have all the view and field level properties captured in two arrays. The next steps select the unique database, create the view with the specified connection and write all required properties to the newly created view.

SET Database TO (this.cUniqueDB)
    CREATE SQL View (cView) CONNECTION (cConnection) AS &aGetProp(6)

    =dbsetprop(cView,'view','FetchMemo',aGetProp(1))
    =dbsetprop(cView,'view','FetchSize',aGetProp(2))
    =dbsetprop(cView,'view','MaxRecords',aGetProp(3))
    =dbsetprop(cView,'view','SendUpdates',aGetProp(4))
    =dbsetprop(cView,'view','ShareConnection',aGetProp(5))
    =dbsetprop(cView,'view','Tables',aGetProp(7))
    =dbsetprop(cView,'view','UpdateType',aGetProp(8))
    =dbsetprop(cView,'view','UseMemoSize',aGetProp(9))

    FOR jfhx=1 TO ALEN(aGetFldProp,1)
      cViewFld=cView+'.'+aGetFldProp(jfhx,1)
      =dbsetprop(cViewFld,'field','KeyField',aGetFldProp(jfhx,2))
      =dbsetprop(cViewFld,'field','Updatable',aGetFldProp(jfhx,3))
      =dbsetprop(cViewFld,'field','UpdateName',aGetFldProp(jfhx,4))
    NEXT
  ENDIF
We are now pointing at the temporary database, and can execute the USE command with or without additional keywords, which was the third parameter passed when executing the procedure. In this example this would result in the command USE authorsview NODATA. Finally, we return to the original database.

IF TYPE('cParms')='L'
    USE (cView)
  ELSE
	 USE (cView) &cParms
  ENDIF

  SET Database TO (this.cCurrentDB)

ENDDEFINE
Note By executing the UniqueView procedure you are creating a temporary database and opening a view in that database with the named connection of your choice. You can easily extend this class to create connections with the User Ids and Passwords that you pass it as parameters.

Shared View Connections

A very useful capability available with connections in views is the capability to share the same connection among multiple views. This same connection can be shared also by SPT statements. By properly setting up the view connection, the developer can assure that only one connection is used per server user no matter how many views or SPT statements are executed. Additionally, a shared view connection makes connection maintenance seamless. When a view that gave rise to the connection is closed, that connection is also closed.

The following examples assume a database with two remote views named “authorsview” and “titleview” with a named connection called “pubs”. These views attach to the authors and titleauthors tables found in the server demonstration database pubs.

For a view to share its connection, the ShareConnect property must be set to .T. - True. This property cannot be set in the visual Connection Designer; therefore, it needs to be set with the DBSETPROP function. The syntax for setting this property for a remote view called authorsview is:

=DBSETPROP('authorsview','View','ShareConnect',.t.)

You also can define this as one of the key words when creating a remote view.

CREATE SQL VIEW authorsview CONNECTION pubs SHARE;
      AS select * from authors
The following procedures are an example of how these shared connections work. It is helpful to start SQL Performance Monitor and select the counter for user connections. This way you can see how the number of connections change as we perform the following steps. To start we will use “authorsview” remote view:

USE authorsview NODATA
If you now look at the SQL Performance Monitor you will notice the number of connections has increased by one. Now open up the “titleview” view.

USE titleview NODATA
Look at the SQL Performance Monitor and you will notice the number of connections has not changed. Let’s now create another couple cursors using SQL Pass Though. For the connection handle, we will refer to the cursor property of “authorsview”.

=sqlexec(cursorgetprop('ConnectHandle','authorsview'),;
               ' select * from discounts','discounts')

 =sqlexec(cursorgetprop('ConnectHandle','authorsview'),;
              ' select * from sales','sales')
A check of SQL Performance Monitor will reveal no increase in connections To complete the exercise execute the following.

CLOSE DATA
You can now see in SQL Performance Monitor that users’ connections have decreased by one. The implementation of shared view connections allows the developer to maintain control of connections by using and closing remote views.