Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.

Very often I read questions about the Data Environment of the forms and Data Sessions on both the Foxite Forum and other resources. Also, reviewing code from others made me realize that not everybody seem to understand the concepts behind the use of Data Environments, sessions and locking strategies.

The reason for writing this article is to leverage the knowledge of those who just started working with VFP or for those who work with VFP for a while now but who wish to get some more information on the subject as defined in the title of this writing.

In the samples that I give you here I use the TestData database that ships with VFP. I will not delve into the use of ODBC or oledb, that is beyond the scope of this article. Besides that, there are several articles in the article section of Foxite covering those subjects.

The Data Environment

Forms are used as an interface between the application code and the data on one side, and the user on the other side. Typically business rules that are applied to datasets are written in code in some way and the idea behind any application is to protect the user from applying these business rules by hand.
The data, stored in tables, must be made available before the individual fields can be shown in controls on the forms. This opening of tables can be done in several ways. Tables can be explicitly opened through code and next display the form like in:

Use <MyTable>
Set order to tag <SomeIndexName>
Do form <MyForm>

However, would it not be simpler to automatically open the tables once the form is activated? For one table this would not be a big advantage, but what about if your form has several tables and views open to serve the purpose of the form. It would be quite a burden to write the code for all those tables, indexes and views. This is where the Data Environment of the form shines.

First open the database, nothing more, no use to open any tables. Create a form and right click on the form, from the context menu select Data Environment.

figure 1, context menu of the form designer

The data environment opens and you get a selection of the possible tables in the database. Select Customer and the “table” appears in your Data Environment. Select Close and you are ready for the next part.

Since the introduction of the database in VFP 3 the term free table was introduced. A free table is a table that is not connected to a database. Of course you can add those to a form as well, select, from the dialog, the “Others” tab and you can browse your system to get a table you need in your data environment.

Make sure the customer table is NOT open before you begin. Type “SET” from the command window, the data session window should open and no tables should be showing in the left list-box. Next run the form, just for the purpose to see what is happening. In the data session window the customer table name should appear. When closing the form the name disappears. See the two pictures below.

Figure 2, designer and data session window

Figure 3, running form... table open

All this is controlled by two properties of the Data environment: the AutoOpen and AutoClose setting.

Figure 4, properties of the data environment

This simple exercise shows that using the Data Environment of the form enables you to open and close tables when you need them.

Data Sessions
A session is a period of time in which persons are occupied in work of any kind, and that knows a beginning and an end, however not defined in duration.
In terms of use of tables this could be translated as, a period of time in which tables are opened for data input, –lookup and –modification. These sessions are, in an application typically initiated by users. There are two options for data sessions. The default session and the private session. Below is a figure showing two forms, containing the same data.

Figure 5, two forms in the same session

Altering the data in one form immediately is visible in the other form when that form is activated. This could be rather confusing.
Looking at the session window we see that there is indeed only one table open and that both forms (read users) are not only in the same session but using the same memory space as well to modify the table.

figure 6, two forms in the same datasession

As you can see, both forms (and those forms can be on separate machines) use the same session. Making modifications to data in the table in one form is visible immediately in the other form. This approach also leads to another problem. Closing a form, and thus closing the table(s) in the DataEnvironment, will lead to errors in either form. In order for users not to interfere with each other you can use private data sessions. Setting the datasession to 2 (Private session) opens the the table in a separate session from any other session.
This is simply shown in the Data Session screen. Opening the dropdownlist on that form shows 3 sessions, one for the _screen session (BTW, _screen is “just” another form, try: ?_screen.baseclass) and the other two sessions are for form1 and form2.

Figure 7, two forms in the different sessions

Closing one of the forms closes only the tables in that session leaving the other sessions untouched.

A big advantage of sessions has to do with system limitations. The maximum number of open tables at one time is 65,535. Big deal you say, I hardly have that number of tables in one database. In the past I have worked on systems that were based on bigger databases that really contained hundreds of tables… and thousands of users. Now, it would not be hard to imagine that one user has 65 or 66 tables open in a complex process. What if I have 2000 users? I would need 2000*65=130,000 tables open… in ONE session. Splitting up sessions gives you the opportunity to have 65,535 tables open for one user.

If all 2000 users log in at the same time I would, theoretically have the possibility to open the 65000+ tables 2000 times AT THE SAME TIME! (131,070,000 tables). This would very likely lead to quite some network traffic (OK, I know THAT is an understatement) but that is not the topic here.

Another advantage of sessions has to do with child-forms. A child form is a form that is called by one main form BUT USES THE SAME SESSION. Let’s assume that the customers table used in this form has an order child form. You want to use the order table in a separate form and filter it according to the value of customer_ID in the customer table. You can set the session of that form to the default session and thus share the session with the main form where the customer table already resides. So, you create a form for the orders table, add the orders table to the Data Environment of that form and in the init of the cursor object there you place the code:

This.filter = "cust_ID = customer.cust_ID"

You start the customers form and from there you call the orders form. It should look a bit like this:

As you can see the orders table is opened in the session where the customer table is also opened. Closing the orders table leaves the customer table open. Closing the Customer form closes the customer table as well. In this way you can, partially, lower network traffic by opening the files when you need them. Also, forms will likely open slightly faster as less tables are opened at the same time.

What is buffering? In the past, and I mean in the time of FPW 2.x, we could create memory variables of our fields with the use of “scatter memvar memo (blank)”. This caused a record to be placed in memory where every field had a memory variable that was equal to the name of the field. Those variables are referenced with the m. showing that m. was a a memory variable, in contrast with where the field in the table was directly accessed. This provided a mechanism were we could roll back any changes made to the memory variables we made in the fields of a form. (a simple scatter would be sufficient.) Although this technique can still be used today and is even improved with the “TO NAME” clause we do not really need that any more.

The form itself has a property Buffermode, and that can be set to three different values:

0: No Buffering
1: Pessimistic
2: Optimistic

No buffering at all means working directly on the table. I showed that already. Although there are situations where this is a valid approach I would, under normal circumstances, not advice this.

So, what is all this pessimistic buffering about? Would that mean we don’t trust anybody anymore or what. Let’s see:
Running the two customer forms again I can start making changes to one form immediately. Trying to do so in the second, while somebody else is working in the first form, will lead to a messagebox, The dreaded:

Figure 8, Oh no, somebody else is working on this record!!

In the case of editing an existing record this can hardly be a problem. You want to modify something at the same time another user is doing the same thing on the same record could, after all, lead to update conflicts. This is prevented by the system itself.
However, the disadvantage, in my opinion, is that the problem comes when one user starts making changes to a record, thus locking it and leaving the record like that when the phone rings. Thus making it impossible for others to access the data in that record. Also, You have little or no control over the appearance of the messagebox. And the help provided might not be available when you have your own helpsystem connected to the application. In the case of locking a record this is mostly done for the purpose of modifying it.

In the case of my userforms the controls are mostly disabled unless the edit button is pressed. Rlock() first checks whether the record can be locked. If such is the case all controls are enabled that need to be enabled, in the case of a record update the table is updated, the record is then unlocked, the controls are disabled again and all buttons (either on a toolbar or on a form) are reset to dis/enabled. For cancelation of actions the changes are rolled back with tableRevert() and controls are dis/enabled as they should to create the original situation. In case NO lock can be made the user receives a messagebox WITHOUT the help button. (No use for that in my apps)

Just check for the form in the downloads.

Basically you can provide data-access to multiple users indeed when having optimistic buffering. What happens here is that the record is, shortly, locked when the data is flushed to disk. With function like OldVal(), CurVal() and GetFldState() you can check whether any changes are made to the record (GetFldState() ) and whether the oldvalue is the same as the current value (OldVal() and CurVal() ).
This however, is quite cumbersome to work out and, as stated in one of the threads, a good developer is rather lazy then tired. I am one lazy developer indeed.

Download Code
You can download the code and other files discussed in this article here. The download is a zipfile. Its size is 4,224 bytes.


Boudewijn Lutgerink Programming is one of the many hobbies of Boudewijn. He has worked with computers since 1985 and is the author of two books from Sybex. He has a weblog at http://weblogs.foxite.com/boudewijnlutgerink.


Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: