FoxPro

FoxPro Developer's Conference '94

Session 142
Session 243
Programming
Multi-User Applications

Calvin Hsia
Calvin Hsia Consulting

Overview

All versions of Foxpro since 2.5 are multiuser out of the box. We'll talk about the theory of multiuser programming, and how it's applied in Foxpro. Several multiuser design approaches are presented. With this background in mind, designing multiuser applications is as easy as single user apps.

Note: this session will only loosly follow these notes

The Old Way

Before the widespread automation of information in this computer age, information was stored on paper. It’s hard to imagine a world without computers these days. When a user required access to shared information, she would walk over to the file cabinet and find the desired file. If she wanted to work on it at her desk, she could remove the file and keep it at her workplace until she finished.

This meant that others could not have access to the information while she had it. If she had made a copy of the data, and took a copy of it to her desk, others could access the data, and multiple access would be achieved. However, if a user wanted to make a change to the data, immediately, all other copies in existence would become obsolete.

For example, suppose the information in the file is the number of seats available on a flight from Palm Springs to Toronto. Hundreds of travel agents from around the country have access to this information and could potentially reserve seats for customers.

How did we deal with this situation? In the old days, the travel agent would call a single central office and make an inquiry about the flight and availability. The central office would indicate that a seat was available and lock that seat so that no other travel agent could simultaneously call in and take the same seat. This lock will last until the travel agent and the customer have decided whether or not to reserve the seat.

The New Way

Things haven’t really changed with multiuser data systems in the computer age. There is still a central office arbitrating multiple access to data. Individuals can still request that files or records be locked. Typically, this is done at the computer network file server. When data is locked, other users cannot make any changes to it. Locks can last milliseconds, and data access is seemingly instantaneous.

This vast improvement in speed makes it important that you keep in mind that another user can sneak in and change data while you’re not careful!

Foxpro provides several commands and functions that facilitate multiuser access. These are all described very well in the Foxpro manuals.

Experimenting with Multiuser features

A very easy quick way to learn about how Foxpro behaves with multiple users is to experiment. You can either to this with 2 adjacent stations, or you can use the tip about testing multiuser on a standalone machine described later.

BROWSE a datafile in 2 sessions. Experiment with changing a record in one session, then watch the results in the other. Experiment with SET REPROCESS, SET REFRESH, and ON ERROR

SET REFRESH will change the rate of screen updates of a BROWSE, CHANGE or EDIT.

SET REPROCESS will change the behavior of Foxpro when it tries to lock a record, and fails. It’s behavior changes whether there is an ON ERROR procedur in effect as well.

Try using this error handler:

ON ERROR WAIT WINDOW STR(ERROR(),5)+"-"+MESSAGE()

Try changing the Company field in the BROWSE on 1 station, and ?COMPANY on the other station. What do you see?

Try running this program with and without an error handler:

	DO WHILE .T.
	  =RLOCK()
	  WAIT WINDOW TIMEOUT 1 "Locked"
	  UNLOCK
	  WAIT WINDOW TIMEOUT 1 "UnLocked"
	ENDDO

Try the following program. Start FPWin and FPDos under Windows, or have them running on 2 networked machines sharing the same default directory. Using another Windows session, start the program by copying a file (I used CONFIG.SYS here) to the default directory. Note that the results of RECNO() of an empty table return 1, so you will see 2 records with a 1 in them.

SET EXCL OFF
ERASE config.sys
CLEAR
CLOSE DATA
IF !FILE("TEMP.DBF")
	CREATE TABLE temp (name c(5),USER C(2))
ELSE
	  IF _Windows
		  USE TEMP excl
		ZAP
	ENDIF
ENDIF
IF TYPE("m.flag")='U'
	m.flag=IIF(_DOS,"D","W")
ENDIF
USE temp
DO WHILE !FILE("config.sys")	&& wait for the starting gun
ENDDO
BROW NOWAIT
SET REFR TO 1
SET REPR TO 2 seconds
DO WHIL INKEY()=0 AND FILE("config.sys")	&& stop by deleting the file
	INSERT INTO temp (name,USER) VALUES (STR(RECNO(),5),m.flag)
	SHOW WINDOW temp REFR
ENDDO
BROW NOCLEAR NOWAIT
WAIT WINDOW NOWAIT "done"
USE

Using an ON ERROR procedure is essential in creating multiuser applications. You might be writing code to perform an update of a file, but necessary files or records might be locked by another user. Your ON ERROR routine can trap for these situations, and depending on the approach you’ve decided to take with multiuser apps, can RETRY or present the user with a screen dialog.

As you can see from the above example, understanding what SET REPROCESS TO and SET REFRESH to do is important.

What the System can do

FoxPro relies on only a few functions from the underlying operating system to provide multiuser access to file services. The network operating system can OPEN a file in exclusive mode, which means no other user can open the file at all, or SHARED mode, in which case other users can also open the same file.

The application software (FoxPro) can request the operating system to LOCK or UNLOCK a range of bytes for a particular file. When data is locked, other users can read it, but cannot write to it.

When your application does a FLOCK() to lock the file, Foxpro merely requests the operating system to lock the range of bytes including the entire file. Likewise, RLOCK() locks only the range of bytes for that one record.

Even though you might have a lock on data, Foxpro allows other users to read that data. This is sometimes called a "dirty read".

Bear in mind what each Foxpro command does when you’re designing multiuser applications. Think about how long your program locks data, and whether it’s possible that some other user can sneak in and change data unexpectedly.

Multiuser Degrees

An application can be classified as single-user or multi-user. However, there are many degrees of multi-user functionality. It could be designed to lock every data file while making a single change to a single record. This is the Pessimistic approach If it locks as little data as possible, then it is the Optimistic approach. The Pessimistic approach takes less work, and is quite robust, but it increases the chances of contention, where user requests for data collide. The optimistic approach is more work, but results in fewer users waiting for shared data to be available.

An application can be designed to lock data for as little time as possible. Every time your application needs to make a change to the data, it can lock what it needs, make the change, and unlock. If you do this series of steps without user intervention, then the lock only lasts a few milliseconds.

Suppose the user wants to make a change to a record. Your program needs to read the current data, present it to the user for modifications, and then write the changes to the file.

In the time it takes to do these steps, another user could have made a request to change the same data.

You can lock the record for the entire duration.

You can lock the record for a few milliseconds only after the user has made changes and confirmed them.

In the first approach, no other user can make changes while the file is locked. In the second approach, another user could have posted changes to the data after you’ve read the data, but before you’ve posted changes.

You have to decide what you want your application to do in this situation. When you’re ready to post changes, you can reread the data record and compare it with a copy of the original. If no changes have been made, you can go ahead and post.

You can do nothing at all to your application except SET EXCLUSIVE OF, and rely on Foxpro’s implicit file locking. Many simple single user applications can work this way. This means when your program makes a change to data, FoxPro automatically will lock the record, make the change, and unlock the record. However, this approach is the least flexible.

GETS vs MEMVARS

One of the great debates in using Foxpro is whether to GET a field directly or to GET to a memvar. Information is presented to the user for editing using the @GET command. The GET can get information directly into a field of a DBF or into a variable in memory.

If you GET fields directly, when your user chooses to ADD a new record, your program needs to ADD a new record, even if the user eventually decides to abort the addition. In that case, you have to DELETE the newly added record.

If you GET to variables, you need only present the screen to the user and not change the DBF at all until the user has confirmed the addition.

In the old days of unreliable hardware, GETting to memvars was de riguer. GETting to fields requires the record to be locked for the duration.

Foxpro’s SCATTER/GATHER commands are very handy in either case. They read/write a local copy of the data record in variables or an array.

Using the GET to FIELDS technique makes it difficult for any user to obtain a file lock, especially if the user starts EDITing a record, and goes to lunch.

Another variation is to perform GETs to a cursor. Since the cursor is a temporary data file that’s seen only by your workstation, there is no need for file or record locking.

Serial Keys

Your program needs to create a unique ID for each record in the customer file. Using RECNO() is not a good idea, because somebody someday might decide to PACK the file, which will renumber each record.

One approach is to lock the customer file, find out the last ID used, and increment it, then add the new record . It works if you use the minimal locking time approach. This approach doesn’t work well if you are using the GET to FIELDS technique, because another user might have a record locked for a long time.

Another method is to keep the LAST_ID in a separate DBF, which is used only for maintaining keys. Then you only need to lock the record in the ID dbf, increment the value, and then create a new record in the CUSTOMER dbf.

Potential Problems

Contention

Contention is the fancy term for when 2 users want the same data at the same time. You have to decide what you want your application to do in this circumstance. You can rely on SET REPROCESS and an appropriately designed ON ERROR procedure to handle contention.

Concurrency

Producing accurate reports, such as an inventory report or an accounting report while a user has a record locked may be difficult. You have to decide whether you want the reports to be up to date, in which case you can lock the entire file, or USE the file EXCLUSIVEly. Or you can let the user produce the report, knowing that some of the data might not be up to date.

The SET LOCK command controls whether Foxpro automatically locks a file for certain commands like AVERAGE, COUNT, etc. If you want to ensure current data while using these commands, you can SET LOCK ON.

Deadlock

Otherwise known as deadly embrace, this is a potentially difficult problem that’s fairly easy to avoid. It occurs when one user has a resource locked, and is waiting for a second, while another station has the second resource locked, while waiting for the first:

Station A has the CUSTOMER file locked, and is waiting to lock the INVOICE file

Station B has the INVOICE file locked, and is waiting to lock the CUSTOMER file

Now your system is hung in an infinite loop. This problem applies equally to record locks.

The simplest way to avoid this problem is to abide by a rule that all locks be placed in the same sequence. That way, the CUSTOMER file will always be locked before the INVOICE file.

Data Corruption

Most data base systems are disk based rather than memory based. That way, they can easily handle files that are much larger than memory can hold. However, this means that they are more vulnerable to disk and system failure. When the disk is a shared network drive, the vulnerability is increased. A UPS is highly recommended.

Performance

A network server can be viewed as a remote shared hard disk. If you perform an operation on your local hard disk, there is a certain amount of information that gets sent to and from the disk over the wires between your motherboard and your disk drive. When you access a network hard disk, the same information is sent, but it just goes over a network wire instead.

Usually, network access speed is slower than local hard disk speed. If you can, put copies of data on local disks to increase performance.

Rushmore is Foxpro’s way of using indices to speed up data access. Index tags provide direct access to desired data. Often, Foxpro doesn’t have to access the DBF at all to satisfy a query. It just looks at the index.

Minimizing the network traffic can boost network performance.

If you put the following line in your config.fp, then Foxpro will copy it’s overlay files to the local drive, reducing network traffic. If the local drive access is faster than the net access, which is usually the case, performance increases.

OVERLAY=<local drive> OVERWRITE

You can also direct Foxpro to place it’s temporary files on a local drive:

Tmpfiles=<local drive>

Tips & Tricks

Testing Multiuser features on a standalone machine

You don’t have to be on a network to test the multiuser features of your program or Foxpro. You can use a multitasking environment, such as Windows or Desqview. Keep in mind that if you do multitask, you need to use a multiuser task arbitrator, such as SHARE. SHARE intercepts all file requests and arbitrates between multiple requests for the same file or record. Share only works on the machine it’s running on, and only for DOS disk volumes on that station. Some networks require that you flag the Foxpro EXE and OVL files READ-ONLY to allow multiple access.

Be sure to set the parameters of SHARE high enough:

SHARE /F:space /L:locks

/F:space
allocates file space (in bytes) for the MS-DOS storage area used to
record file-sharng information. The default value is 2048.

/L:locks
Sets the number of files that can be locked at one time. The default
value is 20.

Windows for Workgroups 3.11 comes with it’s own version of SHARE that’s automatically loaded.

On a Novell network, Netware arbitrates multiple access to files on the server, so you do not need SHARE unless you’re running a Multitasking system on a workstation and want to open the same file more than once on that station.

INSERT INTO vs APPEND BLANK/REPLACE

When you want to add a new record to a DBF, use the INSERT INTO command, rather than the APPEND BLANK. The latter approach means that all the indices are updated with blank values, then with the REPLACEd values. INSERT INTO only updates the open indices once. If you rely on implicit locking, then the INSERT INTO will lock the header once, and then the record. APPEND BLANK/REPLACE will lock the header once, and the record twice: once for adding the blank record, and again for the REPLACE.

Recycling Deleted Records

Rather than PACKing a DBF periodically to remove deleted records, you can reuse them. PACKing requires EXCLUSIVE use of a file. When your user wants to add a new record, rather than adding a new record, your program can search for a previously deleted record, then RECALL it and use it. When the user wants to delete a record, if you REPLACE the deleted record with BLANKs, then it will appear at the top of the index, to make it easier to find.

DBF files have an extra byte for the DELETE flag for every record. That’s why if you DISPLAY STRUCTURE, the number of bytes returned is one greater than the sum of the field lengths. If you have DELETEd records and SET DELETED ON, keep an index tag on DELETED(). That way, Foxpro can check the index tag, rather than go to the DBF to access the data.

Refreshing the local copy

If another user changes the field "Company", then if you ?COMPANY, you see the old value and not the new. A quick way to refresh your local copy of COMPANY is to GO RECNO().

Testing for a record lock.

There is no way that your program can test to see if a record or file is locked by another user without actually doing a lock. This is not a deficiency in design of Foxpro or networks. Suppose that there were a function ISLOCKED() that told your application that the record was not locked. As soon as your program starts executing the next instruction, another user could have locked the data.

Testing to see if you’re running on a network

Developer’s often need to determine whether their application is running in a multiuser environment. For example, my applications will ask for a login nameand password if they’re running on a network.

Foxpro has a function called Network() that used to return whether you were running on a network or not. However, since every copy of Foxpro 2.5 is multiuser, this function always returns .T.

Your program can reliably test to see if it’s running on a network by attempting to USE a file AGAIN SHARED. SYS(2011) will return the Lock status. If it returns "EXCLUSIVE", then that DBF is not on a network or on a volume protected by SHARE.

USE <somedbf> AGAIN SHARED
*if you have a DBF style HELPfile, you can USE SET("HELP",1) AGAIN SHARED
*You can’t use the resource file (SET("RESO",1) or SYS(2005))
*because each instance of Foxpro requires it’s own Read/write version
*of it, and thus each instance of Foxpro will open it’s own resource
*file EXCLUSIVE. However, if Foxpro finds that the resource file is already
*marked readonly, it will be opened NOUPDATE. This way, multiple users
*can share the same resource file.
?SYS(2011)="Excl"

Using Cursors

Often while writing a program, it’s useful to create a temporary DBF. Foxpro allows you to create a CURSOR, which is unique to the current workstation. If many stations executed

SELECT cno,company,address,SUM(invoice.amount) ;
FROM customer ;
GROUP BY 1;
ORDER BY 2;
INTO CURSOR temp

simultaneously, there would be no collisions, because the DBF("temp") is actually a temporary file name.

Controlling Printer Redirection

You can control the Windows printer destination quite easily within FoxProWin.

The following simple code uses the FOXTOOLS library to show and change the current windows printer. You can modify the code and add error handling to suit your needs.

SET TALK OFF
IF !"FOXTOOLS"$SET("LIBR")
	SET LIBRARY TO sys(2004)+"foxtools" ADDITIVE
ENDIF
lcConn=RegFn("WNetGetConnection","C@C@I","I")
lcAdd=RegFn("WNetAddConnection","CCC","I")
lcCan=RegFn("WNetCancelConnection","CI","I")
?"Lpt1 is redirected to "+ShowPrn("LPT1")
DO RedirPrn WITH "LPT1","",""
?"Lpt1 is now redirected to "+ShowPrn("LPT1")
DO RedirPrn WITH "LPT1","\\zenith\hp",""
?"Lpt1 is now redirected to "+ShowPrn("LPT1")
RETURN
PROCEDURE ShowPrn
	PARAMETERS lcPort
	lnLen=70
	lcStr=SPACE(lnLen)
	lnResult=CallFn(lcConn,lcPort,@lcStr,@lnLen)
RETURN lcStr
*EOP ShowPrn
PROCEDURE RedirPrn
	PARAMETERS lcPort,lcDest,lcPassword
	IF !EMPTY(ShowPrn(lcPort))
		lnResult=CallFn(lcCan,lcPort,1)		&& The 1 means to close open files
	ENDIF && !EMPTY(ShowPrn(lcPort))
	lnResult=CallFn(lcAdd,lcDest,lcPassword,lcPort)
RETURN
*EOP RedirPrn

Wide Area Access

Remote control packages like PC-Anywhere and Carbon Copy allow a remote station to log into a local network staion via communications lines. The information sent over the phone line is limited to keystrokes and screen changes (and perhaps mouse movements). Because the bandwidth of a phone line at 9600 baud is much lower than that of a typical LAN connection (typically on the order of 100 times lower), this approach works well.

Other solutions include making the actual LAN connection over a phone line, and these tend to be very slow, because so much information needs to be sent over the line. For example, just loading Foxpro over the phone wire requires transmitting the FOXPRO.EXE, which is over a megabyte.

Novell has a product called Novell Access Server which is actually a version of DesqView running locally on a single network station, allowing multiple remote logins from stations running a licensed version of PC-Anywhere. Other similar solutions come from Citrix. I have a FoxPro DOS application that lives on a Novell server, using NAS with multiple remote stations on each island of Hawaii logging in via 9600 baud links. This has been working quite reliably for over a year.

Where to go from here

The award winning FoxPro manuals are quite good at explaining all the different Multiuser features.

For more help, describe your problem in a message and post it on CompuServe’s Foxforum. Within hours, hundreds of people can see your message, and possibly post a solution. Read the Foxforum topic near thebeginning of the Foxpro help file.

FoxPro Advisor Magazine is published by Data Based Solutions

Your local bookstore has many Foxpro books. Browse around and find one that suits your needs.

Programming Multi-User Applications
(c)1994 Calvin Hsia