This session will look at several issues that arise when developing client server based applications using Visual FoxPro. The examples in this document use SQL Server as the back end but the concepts are applicable to most client server systems. We start by defining the problems faced when using optimistic vs. pessimistic buffering. This discussion is then extended to include the issues faced by notebook users or sites that only periodically exchange information with a host.
Finally, a technique for implementing lists used for lookup purposes is developed. This technique is applicable to workstations connected to the server through a local connection or by dial up access.
If only one user was allowed to access the database at a time, there would be no problems with concurrency and locking would not be required. Clearly this is impractical so a mechanism must exist to allow multiple users to update database information. Ideally, a concurrently executing set of transactions should affect the database the same way as a set of serial actions executed in some order. A serial transaction occurs when only one transaction executes at a time. A concurrency protocol ensures the serializability of transactions. Concurrency is enabled through the use of optimistic or pessimistic buffering techniques and transaction management.
Visual FoxPro supports optimistic and pessimistic buffering on FoxPro tables but optimistic only when used to access back end data.
Imagine pessimistic locking as checking a book out of a library. If I check it out, you can’t look at until I check the book back into the library. If you wish to access a record that I’ve locked you must wait until the lock is released from that record. Other users must also wait until this record is released reducing concurrency. The benefit of this scenario is that few transactions must be rolled back due to conflicts with other users.
Even if I have a record checked out, I might allow you to look at it without you being able to update it. There are many systems that do not even permit that. The act of invoking a SELECT statement could lock all of the records preventing others from looking at them. This is dependent on the back end and how the host DBMS enables locks. The overall system would be very slow in this situation since users would be waiting for very long time periods to get access to individual records.
An optimistic locking scheme means that you request data for update purposes by first taking a snapshot of it to a local copy. Updates are performed on that local copy and then it is sent back to the server. You hope that no one else has edited or altered the records you’ve been working on so the transaction can complete. Instead of locking records when we look at them, we match time stamps on update to confirm the records can indeed be updated. If someone else has made changes, a decision must be made whether or not to commit the update. The immediate benefit of this buffering scheme is that multiple users can check out the same record.
Visual FoxPro allows only this type of buffering mechanism in a client server environment. The local workstation does not update the time stamp however. This is a function of the server application. The transaction is committed if the time stamps match, otherwise it is rolled back.
Not every DBMS manages locks the same way. Locks can take place at the database, table or page level. Records or even columns may also be locked. The finer the granularity, the greater concurrency or number of users allowed to access the database. There is a penalty to pay for greater access since this also places a larger burden on the DBMS. Locking at the table level provides coarser granularity. It also means the higher likelihood of users having to wait for transactions to complete.
Visual FoxPro allows only optimistic buffering of data. This means that all procedures must take place on local copies of the data with the hope of proper synchronization of information when updates are finally sent to the server. The rest of this discussion also applies to satellite offices that do not have permanent links to the server. These locations are also working with local snapshots of the host data. The only difference is the time between updates and local refreshes.
The problems encountered by using local copies of information are obsolete foreign references and conflicts with the host system. Obsolete foreign references cause invalid acceptance or unfair rejection of information. What has to be resolved is the arbiter in the case of a discrepancy. Is the server information more accurate than a workstation or should the workstation’s information be taken as most accurate since this information is closer to the original source?
The examples that follow will use two tables: ACCOUNT and INVOICE. An invoice requires a valid ACCOUNTID before line items can be added. In addition, an account cannot be erased where open invoices for that account still exist. Many such relationships exist in a normal business. The problems presented here use the classic parent and child relationship. The reader should extend these arguments to more complex relationships to understand the scope of each problem.
In this example, host data has been copied to a local image but it has been erased by some other process in the system. The local validation routines will permit invoices to be entered for the account since the ACCOUNTID exists. The problem is known only when the update is submitted to the host. This is a case of invalid local acceptance followed by a delayed rejection by the host. Other business issues arise as part of this problem. The worst is when the local workstation proceeds based on the acceptance. One practical resolution is to route such problems to a central data entry supervisor to handle the situation.
Information has been added to the host after the local copy has been made. In this scenario, it will not be possible to post invoices against this account. The transaction is stopped very early and does not even use the host for validation. The only way to resolve this problem is to refresh the local data copy. A different problem can occur if the local system makes a new entry for the account. When this information is sent back to the file server, two versions of ACCOUNTID will exist for the same account.
The account may have invoices stored at the host but not in the local copy of the data. This account can be erased locally since there are no invoices present. On update to the host, the transaction will be rejected. To resolve this, the workstation triggers an unscheduled refresh from the server. This can be confusing to the operator since he has no way of knowing the problem even existed short of calling the head office.
In this final example the problem becomes apparent when a delete of the ACCOUNT is attempted. The local validation will not permit the transaction to take place. The server never even knows of the problem since the transaction never gets that far.
None of the issues described here apply to updates, only inserts or deletes. If the account header has been updated and the local workstation has made updates to the invoices do you allow the updates to take place?
There may be problems if you try to perform bulk updates using optimistic locks. If an update causes 50 records to be updated and 1 fails, the entire transaction may be rolled back. In this scenario, it may be best to break up transactions into smaller units of work. This might not be possible however. If you have created a delivery list for 1000 stores and the pricing information must be updated for all 1000, your application must be able to update all of them.
VFP cannot be considered in isolation to the server. Consider an update (A) that updates three rows. Row 1 and 2 update successfully. Update 3 however does not complete and the transaction is rolled back. A second transaction B issues an update for three rows, the second is identical to transaction A. It looks at the new time stamp in record 2 and does not commit it’s update due to the values entered by A. However the transaction is rolled back so B’s transaction could have taken place. Each record should be locked as it’s updated by a remote application to ensure cuncerrency.
In single user system there’s no concern for concurrency and no fear that another user’s actions will affect our own. We require transaction management since each user’s actions make permanent changes to the database and affect other’s perception of the database.
A transaction is a single unit of work. It should be considered as an all or nothing approach to database activity. All of the constituent components must be completed within a transaction or none them should happen. Activities are either committed or rolled back.
Transactions should match business activities where possible. This simplifies the mechanics involved in developing the logic for each unit of work. This simplification is important as transactions grow in size and complexity. The likelihood of rollbacks increases in complicated systems. In addition the probability that other transactions will be held up increases. This forces the user to submit the transaction again starting the process over again. As other users wait for the current transaction to commit, the overall throughput of the system is reduced.
A transaction could consist of a number of actions or a single action. In an invoicing application, multiple tables will be affected during an insert and will be handled under one transaction. A simple example of a business transaction is a banking system. If you transfer money from one account to another, the money must be removed from the checking account and then inserted into the savings account. The bank would not be happy if an account balance increased without a corresponding decrease in another account. On the other hand, you would not be pleased if money was taken from one account and not added to another.
The use of triggers can render transactions useless if they affect the original transaction. It is possible to use triggers to break apart transactions, thereby violating our basic premise. In these situations, statements can allow only portions an update to occur. An update statement for example could work on 10 rows but leave only 5 updated. Basic database manipulation functions such as INSERT, DELETE and UPDATE statements are in themselves transactions.
The following example is taken from the PUBS database that ships with SQL Server. Consider the following delete statement:
DELETE FROM titles WHERE title_id IN ("PC9999", "PS1372")
Alone this statement won’t cause any harm but it can be problematic when combined with a poorly written trigger. The trigger below checks to see if any of the rows being deleted have matching rows in the sales detail table. If they do the transaction is rolled back. This trigger is written correctly.
CREATE TRIGGER TITLEDEL ON TITLES FOR DELETE AS
IF @@ROWCOUNT <> (SELECT COUNT(DISTINCT DELETED.TITLE_ID)
FROM DELETED, SALES
WHERE DELETED.TITLE_ID=SALES.TITLE_ID)
BEGIN
ROLLBACK TRANSACTION
END
This next trigger executes the same test but elects to put back any row that has matching rows in the sales detail table. A programmer who executed the previous DELETE statement would be surprised when one row was deleted and the other was not. Do not allow triggers to break apart transactions.
CREATE TRIGGER TITLEDEL ON TITLES FOR DELETE AS
IF @@ROWCOUNT <> (SELECT COUNT(DISTINCT DELETED.TITLE_ID)
FROM DELETED, SALES
WHERE DELETED.TITLE_ID=SALES.TITLE_ID)
BEGIN
INSERT INTO TITLES
SELECT * FROM DELETED WHERE TITLE_ID IN
(SELECT DISTINCT TITLE_ID FROM SALES)
END
The problem of the previous example is that you have decomposed the atomic nature of the transaction. What was once a delete statement has become a DELETE and an INSERT operation.
There is a stored procedure in SQL server that allows you to determine which locks are active. Use sp_lock to identify users and processes that have active locks. Combine the output of this procedure with sp_who to determine the name of the user who has active locks.
local lnHandle
lnHandle = SQLSTRINGCONNECT( "DSN=SQL_Local; UID=sa; PWD=" )
lnSqlexec = sqlexec(lnHandle, "sp_lock", "cs_lock" )
lnSqlexec = sqlexec(lnHandle, "sp_who", "cs_who" )
=sqldisconnect(lnHandle )
It is possible to program transactions in such a manner that will make SQL Server crawl. Try to avoid controlling transactions from within VFP. Any activity that increases the length of time taken to create a transaction will significantly impact the entire system. The server will hold up other transactions while it waits for the controlling transaction to complete. This means that code should not be split between VFP and the back end.
User activity during a transaction is definitely not desirable. In fact this situation is the worst case scenario for transaction control. Imagine a transaction wrapped around an entry form and the user ends up going for lunch before completing the form. In this case, the entire system could be compromised. If you must wait for feedback from a user before committing the transaction put a timer on the form to force completion within a reasonable time frame. A better solution would be to have your end user approve of the transaction and then submit it. Your application will have logic that will handle any rejected transactions.
The following transaction should be submitted as a single transmission:
begin tran
update
delete
update
commit tran
Some tools will break this apart and submit it as five separate batches. This means additional network traffic is introduced as part of the transaction.
How do you manage transactions in a distributed environment particularly when you have to update multiple databases from different architectures? It’s entirely possible to do this in VFP since ODBC allows connectivity into multiple back ends. You may be accessing relational and network databases concurrently. All you require are the appropriate drivers to establish those connections. One DBMS cannot be expected to manage another DBMS’ transactions so the controlling logic must be placed at the application level.
Users of GUI based front ends are accustomed to all of the basic Windows features such as controlling forms by using the mouse, drag and drop, and scrollable lists. The last feature is often used to isolate only one element from a larger population. The list is helpful since the required element cannot be obtained easily without specific information. A primary key such as ACCOUNTID or INVOICENUM is often required to retrieve a given record if the scrollable list does not exist. If there are only two accounts in a customer table then it is conceivable for the operator to remember the ACCOUNTID for both accounts. In some systems the operators can remember up to 10,000 unique product numbers! These individuals have mastered these lists over years of constant familiarization with the application. For new or casual users, it is often not possible to ask them to enter a key number to retrieve a row. The scrollable list allows users to isolate a record quickly and accurately.
There are several methods to build such a list. One method uses a copy of the data in the form of an array or local cursor. This method is ideally suited to small lookup tables that are fairly stagnant such as a listing of provinces or states. The tables that populate such a list are often stored locally. This allows for quick look ups and lessens the burden on the network and server.
Another technique uses a dynamic method by creating a local cursor and displaying the information to a grid. This method is used when the host table is large and a local array or copy of the data is not feasible. When a local cursor is created in VFP, the entire table is not retrieved from the file server. Only enough information to satisfy the needs of the grid is retrieved. The following code will retrieve only a small set of information to the local workstation.
Select custname, ;
custid ;
from customer ;
into cursor qCust
A popular method to accomplish such a lookup table has been with the use of a third party product called JKEY. A cursor may be built using a select statement as in the previous example or the host table is queried directly. As the user enters keystrokes, JKEY does a seek to the closest match in the record set. The user can also browse through the records by scrolling up or down. The user presses ENTER to select the record he wants and continues from there. Unfortunately this solution does not work in a client server environment.
The traditional way of accommodating lists in client server scenarios has been not to use them. Data entry forms have been designed so that an account number or other designator be entered so that only one record is returned to the user. This method is not acceptable to many users who are accustomed to having lists available to assist them in data entry tasks. The rest of this section will discuss the logic used in creating the supporting cursor for such a list.
There are some problems in creating a selection list in a client server environment. A SELECT statement will force the server to process all rows that satisfy the original request. If the host table consists of 1,000,000 rows, the resulting network traffic will tax network resources and reduce overall throughput drastically.
The server can have the additional burden of creating similar SELECT statements for a large number of users. Imagine 100 users trying to perform the same lookup! Worse yet, imagine that you have a dial up connection to the server and that you have to retrieve 1,000,000 rows across a phone line.
In a client server situation we have to retrieve all of the set in order to work with it. Limiting the row count within VFP only limits the number of rows returned, the activity of retrieving the 1,000,000 rows still takes place at the server. In addition, we have no way of jumping around within the set, only the rows returned within VFP. If we wish to jump from client names beginning with the letter "A" or "G" we must reissue the query.
There are several basic requirements for the list box:
Most of the functionality for the list box is provided directly from VFP. The problem is how to retrieve a small set of records that meets our criteria.
This method of searching a table requires that it be pre-sorted. It uses a "divide and conquer" approach that starts by looking at the midpoint of the data set. If the data element at the midpoint is not the requested element, then a check is made to determine if the requested element is smaller than the midpoint. If so, the search continues at the midpoint of the start of the set and the original midpoint. If the requested element is larger than the midpoint, the search continues in the top half of the data set. Figure 1 shows how a list of items would be searched using this method. Note that at each fetch, the midpoint of the set is accessed. In fetch #2, either 2 or 8 would be accessed.
The binary search is fairly efficient. It requires no more than lgN+1 searches to find a match. N is the number of elements in the set.
The basic premise behind the implementation of a binary search is to retrieve only a small subset of rows, say 20, that contain enough information for the end user to select the exact row required. Once the exact row is selected, a final query is made of the back end to retrieve the specific information required to complete the form.
This technique depends on ordered data and access based on record numbers. This is a problem in SQL since there is no provision for record numbers in a client server environment as we have in Visual FoxPro. We must be able to access rows based on this record number for this technique to work.
There are two ways to implement this technique. The first method uses a cursor which is normally accessed through the host system’s API language. Cursors allow you to scroll through data and to access row elements based on their position within the data set. To build your cursor, invoke a SELECT statement and have all of your elements sorted on the field that will be used in the list. The cursor is then accessed using the API of the host system. If we chose to do this in SQL Server and VFP, we would use Foxtools.FLL to access the ODBC functions required. We cannot use SQL pass through since the commands are not SQL but part of a lower level library. The downside to implementing this technique is twofold. First of all, there is a large burden on the server in creating the cursor, particularly if there are a large number of users who require the same. The back end would also be tied up having to sort all those rows for every request.
The method I’ll discuss here involves using SQL only. We require an additional field in the table we wish to query. This field is numerical and contains ordering information of the column we’ll use for our lookup. We need this column since it provides positioning information for the table. the steps to do this in VFP are listed below.
The table would something like Table 1. If an index is placed on the ORDER column, the data would appear sorted as in the CUSTOMER column.
| CUSTOMER | ORDER |
|---|---|
| SMITH | 4 |
| BROWN | 2 |
| ADAMS | 1 |
| JOHNSON | 3 |
| WILLIAMS | 5 |
Table 1 Sorting information is kept in the ORDER column.
You can repeat this procedure to provide sort information for other columns by adding additional column to hold the sort information. To create row id numbers using SQL server use the following code as an example. It’s taken from the Transact SQL book that ships with SQL Server.
declare @key char(4), @rid int, @cnt int
begin tran
select @rid = 0, @key = ‘ ‘, @cnt = count(*)
from employee holdlock
while @rid < @cnt
begin
select @rid = @rid + 1, @key = min(storid)
from employee
where empnum > @key
update employee
set rowid = @rid
where empnum = @key
end
commit tran
The following code demonstrates the technique for populating a small local cursor in VFP. The end user will select the exact data element from the list, probably displayed in a combo box or even a grid.
** BINSORT
select customer
lcName = "APPLE"
select count(*) ;
from customer ;
into array laRowcnt
lnLow = 1
lnRowcnt = laRowcnt[1,1]
lnHigh = lnRowcnt
lnK = 1
do while lnHigh >= lnLow
lnMid = (lnLow + lnHigh)/2
select lastname ;
from customer ;
where nameorder = lnMid ;
into cursor lqTest
if lqTest.lastname = lcname
exit
endif
if lcname < lqTest.lastname
lnHigh = lnMid -1
else
lnLow = lnMid + 1
endif
lnK = lnK + 1
enddo
** At this point return 20 rows into a cursor that can be
** used to isolate the exact row required
lnRange = 10
lnLow = lnMid - lnRange
lnHigh = lnMid + lnRange
select * ;
from customer ;
where nameorder>= lcLow and nameorder <= lcHigh ;
order by lastname, ;
firstname ;
into table lqFin