Data Binding with Visual FoxPro 3.0

Author

Company

Introduction

Visual FoxPro 3.0 introduces a variety of new additions in order to make data binding seamless and totally integrated with your FoxPro applications.

This session will address a few of the major enhancements to VFP 3.0 and the issues surrounding data binding and the 3.0 object model. The session will begin with an in-depth explanation of the Data Environment object, and the advantages you will gain by using it.

I will then introduce you to Data Sessions to show you how we’ve enhanced FoxPro to allow for multiple instancing of your forms and formsets. Next we’ll move into a discussion on Row and Table Buffering before closing things out with an explanation of the use of Views, followed by a quick demo to show all of these pieces working together.

These notes are based on my experiences in testing VFP 3.0, as well as my work with several of the noted gurus of FoxPro to understand where people would most likely be confused. I have also monitored the Beta Forum closely to help me formulate the topics of this discussion.

Introduction to the Data Environment

The Data Environment (DE) is an object that has been introduced to enable users to visually design and include their data requirements while they are creating new forms, formsets and reports. It has its own set of properties events and methods, and can be used as a container for other objects such as cursors as well as local and remote views. The DE has taken the hassle out of setting up your data for use with your forms and formsets. While in Form Design mode, the DE supports drag and drop functionality in a number of ways that we will discuss later. When you instantiate your forms, the DE takes care of opening up all of the necessary databases, tables, and views needed for your applications, and then closes them, when you are done.

Synchronous Record Management

Another powerful feature of the Data Environment is its ability to maintain and manage synchronous record movement in FormSets. Now, when you create a FormSet with a Dataenvironment, all of your record handling is done in conjunction with the currently active form. For example, say you have a Formset with two Forms, and a dataenvironment with a one to many relationship set up between two tables. On page one you may have a combobox bound to a field from the parent side of the relation. And on page two, you could have a grid bound to the table from the child side of the relation. With a Dataenvironment, any data manipulation that is performed on the first form, automatically gets handled on the second form. So if in the above scenario, you instantiate your form, and then use the combobox to change the value, the Interactive change event of the combobox will trigger form two to automatically pick up the changes, and reflect them in the updated grid on page two. Even the code that you would normally need to use in order to set up and maintain your relations has been eliminated by the use of the DE.

Another huge benefit from using the dataenvironment is the ability to prototype locally and then scale up to client-server using identically structured data environments. Utilizing views in the dataenvironment, gives you the ability to create updatable subsets of data either locally or remotely to allow you to handle your data manipulation with the local FoxPro Engine. We will discuss the use of views in more detail later in the session.

Drag/Drop functionality

The Data Environment has been designed to support drag and drop functionality to make it even easier to populate the Data Environment window. You can populate it by dragging tables or fields from the project container into either the form designer, or the actual data environment window.

You can also drag tables or fields from the Schema Window into the Form Designer, and the DE window will populate automatically with the proper table. Dragging two related fields or tables into either the Form window or the DE window will result in both tables being added and the relation will be inherited automatically. One additional feature that has been included is the ability to drag fields or tables from the DE window into your actual form. In all of the above drag/drop scenarios, dragging an entire table into the form will result in a Grid being created which is automatically bound to the table. Dragging a field from the DE window into your form will result in a textbox being created which is automatically bound to the field.

Adding Tables and Views to the DE

There are basically two ways with which you can add a table or a view to the DE. When you are in the DE window, you can use the right-click feature in order to add attached tables, free tables or views. The Add Tables dialog will present you with a selection of tables from any currently open databases. If you want to add a view to the DE, you need to select the views checkbox, this will result in the label changing to ‘Views in Database’. If you want to add a free table, you need to use the Other commandbutton to bring up the GetFile dialog to open a database or free table. The Database dropdown shows any database in any open project, plus any database specifically opened by OPEN DATABASE. If there are no tables or views in the database ‘(None)’ is shown. Alternatively, you could also use the Add Tables menu option of the Data Environment menu pad. Choosing this option will result in the same Add Tables Dialog that you were given by using the rt-click option.

DE Properties, Events and Methods

The Data Environment is actually a container object that can contain Cursors, Views and Relations, and contains it own set of associated properties, events, and methods. VFP gives you the choice of having the DE load all of your tables automatically when you instantiate your form, or you can load them manually just like in 2.6. We won’t discuss all of these properties, events and methods, in detail but we will want to take a closer look at a few of the more important ones.

DE Firing Order

When you execute a DO FORM statement on a Form that contains a Data Environment, the following is the order that the events will be fired in.

Form Load

DE Init

Form Init

Form Activate

Form Destroy

Form UnLoad

DE Destroy

DE through Code

The DE can also be created entirely through code and then saved out as a class with a particular Form. In order to accomplish this, you will need to store your DE class in a PRG and have your DE-Less form class create an instance of it in the objects INIT statement.

Make sure your formclass has multisessions set to .T.

Instantiate the DE object from the Forms Load event because controls contained within the form have their Inits triggered before the form itself.

oMyForm1=CREATEOBJECT('Form')
oMyDE1=CREATEOBJECT('Dataenvironment')
oMyDE1.ADDOBJECT('curs1','cursor')
oMyDE1.curs1.database='testdata.DBC'
oMyDE1.curs1.cursorsource='Customer'
oMyForm1.ADDOBJECT('txt1','textbox')
oMyForm1.txt1.controlsource='Customer_id'
oMyForm1.txt1.visible=.t.
** Use the Forms SaveAs method to save everything out to an .SCX 
** which is actually how we get the DE associated to the form.
oMyForm1.saveas('oMyForm2',oMyDE1) && Note that the second parameter specifies the associated Dataenvironment
RELEASE oMyForm1
DO FORM oMYForm2
** Also note that you can save the DE out to a .VCX, but none of the ** form information is saved with it.
oMyDE1.SAVEASCLASS('mydevcx','myde1')
** Saves the DE to a vcx called mydevcx, with a class name of myde1

Summary

In summary, the Dataenvironment is a container object that allows you to visually design your data, while you create your forms and formsets. You can add free tables, attached tables and views through the DE window or the dataenvironment menu. The Drag/Drop functionality of the DE makes it incredibly easy to set up complex relationships, as well as creating your data bound controls by dragging from the project container, database schema window, or the actual DE window itself. The synchronous record management allows you to create complex formsets without having to worry about managing the data on the other forms. If you are a die hard programmer, the DE even allows you to create it entirely through code. The DE offers tremendous advantages to the FoxPro programmer, and when used effectively, it can save you hours of work.

Data Sessions

Introduction to Data Sessions

Data Sessions are designed to allow you to run multiple instances of your forms and formsets. Having individual sessions for each instance, allows your forms to access separate environments even though the identical tables, views, etc., are being used.

When the DATASESSION property of a Form or Formset is set to true, VFP creates a new private session each time that Form or Formset is loaded. By creating a private session, you essentially create a separate set of 32,767 workareas for use by each instance of your form. This behavior can be overridden by setting DATASESSION to 1, which is the default global datasession used by the command window and the Project manager. The number of sessions available are only limited by the amount of memory and available disk space.

Once the form is running, you can determine what the current session is set to by using the SET(“DATASESSION”) statement. You can also query the Read-Only DATASESSIONID property in order to determine what data session your form or formset is running in.

View window enhancements

With an increase in the number of workareas to 32,767, it became pretty evident that the current view window would have to undergo some changes in order to be able to quickly track all those workareas. The first major change in the view window is the ability to use the dropdown in order to track sessions. When a form is running in a private session, you will see the form name followed by the session number in parenthesis. For example if form1 is running in it’s own instance you may see Form1(3) to represent the session number that Form1 is executing in. The next major change is the removal or workarea numbers and letters from the listbox. It would be nearly impossible to maintain all of those workareas in a list, so the decision was made to remove the workarea numbers. The status bar of the view window will show you what table is open, which database it belongs to, and which workarea it’s open in.

Scoping of SET commands

The following SET commands are scoped to Data Sessions. This set is the set that controls the behavior of data manipulation commands.

Due to performance implications, other SET commands that control display or environment are not scoped to Data Sessions.

Data Sessions in Toolbars and FormSets

DataSession and DataSessionId are also exposed on toolbars. When ‘DataSessionId’ is written or when ‘SET DATASESSION TO’ is issued within a toolbar, form, or formset, it will permanently effect the working session for that form and all objects it contains. It will not effect non-contained objects (i.e. those created within an object via CreateObject()). Changing the value of DataSessionId will increment the ref count of the session being changed to and decrement the ref count of the session being changed away from. However, if a toolbar, form or formset was created with DataSession= 2 (Private) and the DataSessionId is changed, then the initial session will NOT be released as a side effect of changing the DataSessionId. The toolbar, form or formset will have to be released to free its initial session.

Changing the DataSessionId on an object with data bound controls will cause those controls to lose (or at least switch) their data sources. In general, it’s recommend that this feature be used on objects that have no data bound controls.

There are two functions that can also be used to help determine what instance is being run, and what workareas are in use.

The AINSTANCE() function returns an array of values indicating what instances are currently in use. Tracks form instances as long as you scope the variable to the form object.

The AUSED() function will return an array of values indicating what workareas are currently being used by VFP.

Data Buffering

Introduction to Data Buffering

Visual FoxPro 3.0 now has the ability to implement data buffering for your applications. By choosing either row or table buffering, you will now have complete control over when FoxPro takes locks on your data, and when that date gets written to disk.

In the past, developers have used the scatter/gather technique in order to manage their data during updates. With Buffering, there is really no longer a need to Scatter your data each time you enter the field or Gather it as you exit the field as a way of managing updates. Buffering allows you to edit your data just as though you were editing the table directly, with the advantage of allowing you to discard your changes before they are actually committed to disk.

You can initiate buffering with the CURSORSETPROP() function. Once Buffering is enabled, the current record is copied to a memory or disk location which is managed by VFP. When a user moves off of the record, or attempts to update the record, VFP will try to lock the record, verify that no other users have changed the record, then write the edits to the table. Once buffering has been enabled, it remains in effect until you disable it, or close the table.

Detecting and Resolving Conflicts

VFP provides functions to enable you to detect, evaluate and resolve conflicts between data update operations in a multiuser environment. These function are as follows

• GETFLDSTATE()–Detects a change in a field
  
  
• SETFLDSTATE()
  
  
• GETNEXTMODIFIED()–Detect and locate a changed record in buffered data

  When used with parameter of 0 finds the first modified record.

• CURVAL()–Returns the current value of the record in the buffer.
  
  
• OLDVAL()–Returns the value of the record when it was originally stored in the buffer.
  
  
• TABLEUPDATE()–Commits the changes to disk.
  
  
• TABLEREVERT()–Reverts changes back to original values.

Which buffering method to use

There are several factors that should be considered when deciding which buffering method to use. Table and record size, and number of users on the system, are probably the most important factors that should help you decide.

VFP has two types of buffering, Row Buffering, which involves writing individual records out to a memory location and then storing the updates done by users at that location until the actual writes to disk are executed. And Table Buffering, which involves storing the entire table in a buffer in memory. While record buffering can work on more than one record at a time, if this is your objective, it is recommended that you use Table buffering.

Table Buffering is useful when you dealing with a one to many form situation. You can set the table buffering on the Grid, and then update your modified records in one shot.

Enabling Buffering

You need to use the CURSORSETPROP() function in order to enable either record or table buffering programmatically from with VFP 3.0. When using this function to enable buffering, you need to pass it two parameters. The first parameter indicates the buffering property, and the second parameter indicates the type of buffering to implement. The following tables shows the values and descriptions for the type of buffering you can choose.

For example, if you wanted to enable pessimistic record locking you would use the following syntax.

=CURSORSETPROP("Buffering",2)

This statement causes VFP to attempt to lock the record at the current record pointer position. If the lock is successful, VFP places the record in a buffer and permits editing. When you move the record pointer or issue a TABLEUPDATE() command, VFP writes the buffered record to the original table.

Appended records are added to the end of the buffer. The RECNO() function returns sequential negative numbers on records you append to a table buffer. The TABLEREVERT() function can be used to remove appended records from a Buffer.

Both TABLEREVERT() and TABLEUPDATE() remove the negative value from the appended record, while maintaining then sequence. If you use Buffering with REMOTE Data the buffering property is either 3, optimistic row buffering, or 5, optimistic table buffering. MULTILOCKS must be set ON for all buffering modes above 1.

Using Transactions

A transaction is another method used to buffer data updates. Transactions only have an effect on tables that are contained within a Database container. VFP allows nesting of transactions up to five levels deep. The following three commands are used to enable transaction processing.

• BEGIN TRANSACTION–Starts the transaction
  
  
• ROLLBACK–Reverse, with nothing written to disk, since the most recent BEGIN TRANSACTION.
  
  
• END TRANSACTION–Lock records, write to disk, all changes made since the most recent BEGIN TRANSACTION then unlock the records.

The above three commands have no effect on remote tables. In order to enables transactions for remote tables use the following.

• SQLSETPROP()–Use to enable transactions
  
  
• SQLCOMMIT()–Write changes to disk
  
  
• SQLROLLBACK()–Revert changes and don’t write to disk.

Views

Views are designed to bring client-server operations down to a user level. Views are a set of records that have been extracted from one or more tables, that can be based on either remote or local datasources. They allow you to extract a subset of records, without having to download all of the records on the data source. Once a view has been created, it can only be accessed when its parent DBC is open. Views also allow you to dynamically update you data from within the view itself. When a view is executed, VFP brings down a snapshot of data, and allows you to deal with the data using FoxPro’s local engine. Since views can be created off of local or remote data, you can essentially prototype your applications using all local data, and then simply exchange your local cursor for an identical remote cursor, and all processing is done automatically behind the scenes.

Using Views with REMOTE Data

The first step in creating a remote view is to create a connection to your remote datasource. VFP has a connection designer which makes it easy for you to create or customize a connection for use with other servers. The connections also get saved as part of the database. Once the connection has been created, you can use the view designer to set up the options for your query. VFP has four timeout interval options as follows.

• Connection–Number of seconds VFP will wait for a connection to be established with remote server
  
  
• Idle–Number of seconds of idle time allowed before connection is terminated
  
  
• Query–Number of seconds VFP will wait for the server to respond to a request
  
  
• Wait Time–Number of milliseconds to wait between calls for data

Use the Update Criteria tab in the view designer to control how changes you make to remote data are sent back to the remote data source or local table.

Choose the Send SQL Updates option if you want to be able to send changes back to the source table. You can also choose to have individual fields be updatable by selecting the updatable column next to the name of the field. Note that you must have a key field defined for a table in order to make any fields in that table updatable. By default, once you set the key field in the view designer, it is not updatable. If you want all fields to be updatable, choose the Update All button.

The Fetch memo on demand option prevents VFP from downloading all memo files along with the other fields. VFP will fetch the memo, when you actually enter into the memo field. This provides a huge gain in performance, due to the smaller data set being downloaded from the server.

When you create your views locally, they look identical to a remote view. You don’t need to add any special code to handle updates, but you will probably want to add your ODBC error checking. When the view is executed, VFP downloads a matching subset of data, and allows you to offload processing of the request to the remote server. Additionally, you can USE Viewname and perform any of the normal database manipulation commands and functions, or create reports based on the data in your view. One last feature that I want to mention is the concept of progressive fetching. Progressive fetching means that VFP bring back a smaller subset of records for you to begin working with, and then during idle times, it will go back and fetch more. This allows you to begin working with your data instantly, without having to wait for all of the records to be downloaded. This dramatically increases your productivity and decreases your down time.