BDC'96 - Francois Ghoche

Encapsulating Datasets and Transactions in Visual dBASE

François Ghoche, ATON Consulting (France) & The European Xbase Institute


Overview

Whether on a standalone computer, on a network file server or on client/server databases, event driven window oriented applications must handle concurrent access to data. This paper will cover sessions and transactions, the tools provided for this purpose, as well as table record buffering management. Sessions will first be explained and demonstrated for interactive as well as command window and programming use. Transactions will be covered on local tables, as well as network and client/server databases. Concurrent use of sessions and transactions then will be presented. Table record buffering management will finally be covered, both as an additional feature to complement transactions as well as a code saver methodology. All relevant code examples will be provided on the BDC '96 CD-ROM. Only specific code extracts are listed in this paper when necessary.


Sessions


Interactive use

In interactive use, sessions are turned On or Off through the Desktop properties menu. When Visual dBASE is first installed, sessions are activated by default. To turn them Off, select menu Properties/Desktop properties, and then the Files tab, and uncheck the Sessions check box.

dBASE DOS behavior

When sessions are Off, the behavior in Visual dBASE is very similar to what you may be expecting under dBASE for DOS. Let's take an example :

There is only one record pointer per open table. When the record pointer is moved in one window, it is also moved for all windows accessing data of the same table.

When you open the same table twice from the Navigator, you will notice from the commands generated in the Command window that it has been in fact opened only once. The second time, only the Browse (or Edit) command has been generated.

Further, if you double-click on other tables, they will then be opened in subsequent work areas.

USE H:\DBASEWIN\EXEMPLES\CUSTOMER.DBF
BROWSE
BROWSE
SELECT 2
USE H:\DBASEWIN\EXEMPLES\ORDERS.DBF
BROWSE
SELECT 3
USE H:\DBASEWIN\EXEMPLES\CONTACT.DBF
BROWSE

Sessions activated

When you check the Sessions check box again from the menu Properties/Desktop properties /Files tab, then :

As you can see, the commands generated in this case will be quite different.

To summarize :

CREATE SESSION
USE CUSTOMER.DBF
BROWSE
CREATE SESSION
USE CUSTOMER.DBF
BROWSE

And then :

CREATE SESSION
USE ORDERS.DBF
BROWSE 

Notice that when the data window is closed, the table is closed as well, while this was not the case when sessions were off. Visual dBASE 5.5 includes a choice to close the table when using the Speed menu on an open table in the Navigator.

Benefit of sessions

Opening a session isolates the data tables' work areas and pointers from possible interference by other applications and/or objects that may already be using the same tables. Behavior is then the same as if you were running two concurrent copies of Visual dBASE against the same tables.

Standard code generation (without sessions)

Query Designer

The code generated by the Query Designer :

On local tables :

* dBASE Windows .QBE file 9
CLOSE DATABASES
SET EXACT ON
SELECT 1
USE CUSTOMER.DBF ORDER TAG NAME
...
GO TOP

On server database :

* dBASE Windows .QBE file 9
CLOSE DATABASES
SET EXACT ON
SELECT 1
OPEN DATABASE SERVERSQL
USE :SERVERSQL:CUSTOMER ORDER TAG :RDB$PRIMARY22:
...
GO TOP

Form Designer

When a form is created using the Query Designer, the resulting "wfm" file contains the object oriented code generated. As far as the data is concerned :

Whether the code is generated by the Form designer on a single table, or by the Query designer (if a view is used), the first table is opened in the current work area.

CLASS CONTACTFORM OF FORM
   this.View = "CONTACT.QBE"
...
   DEFINE LISTBOX COMPANYLIST OF THIS;
       PROPERTY; 
         DataSource "FIELD COMPANY->COMPANY",;
...
   DEFINE RADIOBUTTON COMPANYTYPE1 OF THIS;
       PROPERTY; 
         DataLink "COMPANY->TYPE",;
...

Consequences

If the user opens several forms, tables from a previously opened form may be closed when a new form is opened, while other tables may be opened in the work areas.

Also, table pointers may be moved in one form, altering the view in another form, without user or application control.

Of course, all this can be solved by means of hand coding, the hard way. However, sessions will provide a more elegant (and automatic) solution.

dBASE sessions

The new dBASE command CREATE SESSION defines a new data file environment each time invoked :

Creation and selection of sessions

When you issue a command CREATE SESSION, a new session is then created, and work area #1 of a new set of work areas becomes the current one.

The session is then activated when a table - or a view - is opened by means of commands (USE, SET VIEW, OPEN DATABASE), or properties (this.view).

Data tables

Whenever a CREATE SESSION has been issued :

This means that automatic or programmer driven locking is applicable when two sessions operate on the same record.

When the shared record is modified in one session, modifications are visible in a form of the other session (in Visual dBASE 5.5). In version 5.0, screen refresh requires paging through records on the screen so that the display be updated.

You cannot specifically change sessions. Switching to another session can only be done by selecting an object belonging to the appropriate session.

Sessions and SET commands

SET REFRESH determines how often dBASE refreshes the screen with table information from the database. The Refresh property - from the menu Table / Desktop properties / Table tab - provides for the same purpose in interactive mode.

In Browse mode, you may need to use the menu choice Table / Refresh records to force updating the record buffer with changes that may have been applied by users on the table from other workstations on the network. The REFRESH command provides for the same result.

Some SET commands affect only the current session. They have to be executed again if they are needed in another session.


SET AUTOSAVE          SET BLOCKSIZE          SET CARRY            

SET CENTURY           SET CONFIRM            SET CUAENTER         

SET CURRENCY          SET DATABASE           SET DATE             

SET DBTYPE            SET DECIMALS           SET DEFAULT          

SET DELETED           SET DELIMITERS         SET DIRECTORY        

SET EXACT             SET EXCLUSIVE          SET FIELDS           

SET FILTER            SET IBLOCK             SET INDEX            

SET KEY TO            SET LOCK               SET MARK             

SET MBLOCK            SET MEMOWIDTH          SET NEAR             

SET ORDER             SET PATH               SET POINT            

SET PRECISION         SET REFRESH            SET RELATION         

SET REPROCESS         SET SAFETY             SET SEPARATOR        

SET SKIP              SET TALK               SET UNIQUE           



SET commands affecting only the current session

By including a specific set of environmental parameters (specifically the keywords of equivalent SET commands) you insure that they are applied to each newly created session. Appropriate parameters must be included in the sections [CommandSettings] and [OnOffCommandSettings].

Practical implementation

Where to start a session

There are several possible places for including CREATE SESSION commands.

Beginning of an application

This is the most obvious place that comes to mind. This is the minimum security the programmer can implement to insure the application data environment will be protected from interference by another application that could possibly run by the user concurrently.

Of course, this method offers no interest when the application is distributed in "exe" form. In that case, each different executable would launch its own Visual dBASE session anyway.

Beginning of each form

The advantage is then to isolate each form's data environment when the form is instanciated. If it is instanciated several times - or if another form using the same data tables is instanciated - it will not affect currently open tables in use within other instanciated forms.

Just include the command CREATE SESSION in the Header section of the "wfm" file. A new session is then created each time the form is instanciated, which protects the data environment from existing and future activities on the same data sources.

...
CREATE SESSION
...
** END HEADER -- do not remove this line*
* Generated on 07/06/94
*
local f
f = NEW CUSTOMERFORM()
f.Open()
CLASS CUSTOMERFORM OF FORM
   Set Procedure to BUTTONS.CC Additive
   this.View = "CUSTOMER.QBE"
   ...

Of course, if you are not instanciating the form from the "wfm" file (DO FormFile.wfm), the session has to be created in the relevant program file (prg), just before creating the new form object.

SET PROCEDURE TO OneForm.wfm ADDITIVE
CREATE SESSION
LOCAL MyForm
MyForm = NEW OneForm()

Beginning of queries

Queries may be used as a mean to open views on data at different locations within an application. It may then be a good idea to include a CREATE SESSION command as the first line of each query.

Beginning of a transaction

Creating a session before starting a transaction is a good way to isolate the transaction process, and avoid unintentional starting of a nested transaction.

Beginning of table-data related classes' instantiation

The justification for this method is similar to that of starting a new session before instanciating a form.

A session is automatically ended when a form run from the Navigator is closed. However, the case may be different in a program, and with proprietary classes. It then depends on the specific way data tables are closed by the application using the classes. Also, if there are several objects sharing the same session, it will be ended only when all are released.

Sessions and memory variables

Sessions do not affect memory variables' scope and visibility. In other words, behavior for variables will be the same whether sessions are on or off.

Limitations and work arounds

Session environment settings' initialization

Special care must be taken about environment settings when using sessions in applications.

As it has been previously mentioned, the environment parameters of corresponding SET commands will be applied automatically for each new session when created.

A safer way for the developer may be to prepare the necessary environment settings required at session initialization time in a program, a procedure or a method. The code can then be invoked each time a session is created, after the CREATE SESSION command.

CREATE SESSION
DO MyEnv.prg
SET VIEW TO MyView1
... other code ...
CREATE SESSION
DO MyEnv.prg
USE MyTable

Of course, whatever can be done through the Windows API can also be done from within Visual dBASE applications. You just have to inform dBASE about the function and its location. This prototype is described via the EXTERN command. Then, the API function can be used the same way as a dBASE function or a UDF. Examples of possible usage to control windows and related sessions is provided in the following files on the conference disk : dBWWin.zip and WinList.zip

Sessions switching

As there are no specific commands or functions to manipulate sessions, selecting and switching sessions will be done via the objects belonging to each session.

There are instances when a number of forms need to be opened from within one form. With multiple forms running, it becomes difficult to give focus to a particular form that is hidden under a number of already opened windows. The built-in method SetFocus( ) of the Form base class can be used to give focus to an instanciated form. You can test the following from the Command window :

SET PROCEDURE TO Animals.wfm ADDITIVE
SET PROCEDURE TO Cal.wfm ADDITIVE
F1 = NEW AnimalsForm()
F2 = NEW CalForm()
F1.Open()
F2.Open()

The calculator form has the focus. Now, to give back the focus to the first form, type :

F1.SetFocus()

By using events to trigger forms switching, you can give focus to one of the many opened forms by just one click of your mouse. You can keep the focus to the original form while opening further forms.

Extending from the example we just typed in the Command window, the sample program (Setfocus.wfm) demonstrates the use of the method SetFocus(). Four push-buttons are created, two for each child form. One button is used to open a child form and the other is used to give focus to this child form.

How to find a specific session and close it

The idea is to use a "phantom" window to select and then close a session.

FUNCTION CloseSession
    *-- Declaration of local variables
    LOCAL lo_TempForm
    *-- Instanciation of a window (form)
    lo_TempForm = NEW FORM()
    *-- Use a menu in order not to dammage the app menu
    *-- (dBASE reactivation)
    lo_TempForm.MenuFile = "AMENU.MNU"
    *-- Hide the form by positioning it outside the visible screen
    lo_TempForm.Top = -100
    lo_TempForm.Left = -100
    lo_TempForm.Height = 0
    lo_TempForm.Width = 0
    *-- Open the form
    lo_TempForm.Open()
    *-- Close the form
    lo_TempForm.Close()
    Release object lo_TempForm
RETURN .T.

The function can be declared in a general purpose procedure or library file. This is not a method of a specific class. When executed, the function will pick the current session (if any) and close it.

In the following example, the calling form is not detailed. It is a simple form, with a session started up front. Another form is called, which also includes a CREATE SESSION. When the second form is closed and released, control is returned to the first form. However, the first form's session will not be activated unless an event from this form gets triggered.

* Second.wfm
* This form allows the entry of an option number which validity is checked 
*  then the option number is returned.
        LOCAL f
        f = NEW OptionForm()
        f.readmodal()
        Return
** END HEADER, etc.
...
* First form
CLASS FirstForm OF Form
...
        PROCEDURE PUSHBUTTON_OnClick
           create session     && do that here
           Do second.wfm
           class::RestoreSession()
           Seek option
           * etc.
        RETURN
        PROCEDURE RestoreSession
           *-- empty procedure
ENDCLASS

The call to the dummy method in the same class restores the original session.

The two files, Client4.wfm and CherCli.wfm, illustrate a different design with a similar result.


Sessions summary

Sessions and environment settings

Sessions diagram

- Desktop operation

- Interactive operation (Command window)

- Programming mode

- Reports and labels


Transactions


Why transactions

Transactions provide a way to process a set of actions on the data of a database as a whole. The transaction can then be validated (committed) or canceled (rollback). In the latter case, the database is automatically restored to its original state, as before the transaction was started.

For instance, a transaction may cover adding, updating and/or deleting data in one or several records (rows), that need to all succeed or else all fail.

Another classic example of transaction may be a typical accounting operation : a debit and a credit record must be applied to transfer money from one account to another. Both debit and credit appends must succeed, or else the database must be rolled back to its previous state.

Transactions either complete or reject as a single unit of work, not necessarily on the database as a whole.

How transactions

Functions

Parameters

<database alias>

<isolation level>

0 Server default

1 Dirty read (uncommitted changes)

2 Read committed

3 Repeatable read

Usage on local tables results in a "Not supported " error message.

- Sybase - Only the server default, Read Committed, is supported.

- Oracle - Read Committed and Repeatable Read are supported. However, a Repeatable Read transaction is always Read Only.

- InterBase - Repeatable Read and Read Committed are supported.

Behavior vs. standards : The following table shows the changes.


Requested ISO               Actual isolation level used  

Sybase:                                                  

  DirtyRead                 ReadCommitted                

  ReadCommitted             ReadCommitted                

  RepeatableRead            "Not supported error"        

Oracle:                                                  

  DirtyRead                 ReadCommitted                

  ReadCommitted             ReadCommitted                

  RepeatableRead            RepeatableRead (READ ONLY)   

InterBase:                                               

  DirtyRead                 ReadCommitted                

  ReadCommitted             ReadCommitted                

  RepeatableRead            RepeatableRead               



Default is isolation level 1 (Dirty Read).

Rules and pitfalls

When a transaction has been started :

As an exception to this last rule, note that a transaction may be used to rollback when explicit locks fail.

Commands concerned by transactions

The action of the following commands is taken into account, and data concerned will be committed or rolled back at the end of the transaction :


  @ ... SAY ...        APPEND        APPEND BLANK     APPEND MEMO   
       GET                                                          

     BROWSE            DELETE            EDIT           FLOCK( )    

     INSERT            RECALL           REPLACE       REPLACE MEMO  

 REPLACE BINARY     REPLACE OLE        RLOCK( )                     



Commands not allowed in transactions

The following commands will return an error message :


   BEGINTRANS( )         CLEAR ALL           CLOSE ALL         CLOSE DATABASE    

    CLOSE INDEX           CONVERT           CREATE FROM          DELETE TAG      

       INDEX         MODIFY STRUCTURE          PACK                              

    USE (other              ZAP                                                  
     database)                                                                   



Local and database transactions

Local transactions on DBF and DB tables

In this context, "local" covers tables residing on standalone computers or on file servers. Use of local transaction functions is straightforward, without any parameters needed. Several tables can be involved in the same transaction. For instance :

PROCEDURE MyFormOnOpen
   SET VIEW TO MyView
   this.Modif = .T.
BEGINTRANS( )
...
PROCEDURE MyFormOnClose
   IF form.Modif
      IF MsgBox( "Do you wish to confirm ?", "Validation", 4+16 ) = 6
         COMMIT( )
      ELSE
         Rec_Pos = RECNO( )
         ROLLBACK( )
         GOTO Rec_Pos
      ENDIF
   ENDIF
...

If the transaction is not settled before closing the form (or the tables), the BDE will automatically take over and display a message box requesting a decision : Commit, Rollback or Cancel.

Transactions on database aliases

The database alias is defined at the database engine (DBE) level. This is done with the BDE configuration utility, which icon is installed in the same program group as Visual dBASE. The Aliases tab provides access to the page in which the alias names are defined.

Some remarks :

SQL servers specifics

The main difference is that when a BEGINTRANS( ) / COMMIT( ) / ROLLBACK( ) is executed on the client PC, we cannot be sure the function will really be executed by the server. This is because while the database engine is local to the computer hosting the application when using local tables, it is residing with the database for distant relational servers, or other files accessed through ODBC.

In the latter case, transaction functions must be interpreted as requests sent to the server, which will not necessarily be insured of successful achievement. For instance, the server may not support transactions, the connection can be lost, or the transaction can be refused for several reasons, which may be server dependent. Error handling in the application needs to take into account the possibility that the function returns an error from the server. If a transaction function returns .F., use SQLERROR( ) and/or SQLMESSAGE( ) to determine the nature of the server error that have occurred.

When modifying data in a relational server and no explicit transaction is started (no BeginTrans( ) ), the BDE still starts an automatic transaction at row level.

Where and when to start a transaction

Within an application, transactions can be handled implicitly (automatically), or explicitly, upon user's request.

Practical implementation

The developer can use transactions' automatic features or not. However, in both cases, events related to user changes and navigation must be handled anyway.

- Implicit transaction

Refer to files Client4.wfm, Product2.wfm and Menu2.wfm for a more detailed example.

- Explicit transaction

Refer to file Client5.wfm for a detailed example.

Rollback specifics

While returning data to its previous state as before the transaction was started, rollback still implies some work from the programmer part :

Refer to file Viewer.wfm for an example involving bitmap files.

In "dbf" tables, appended records are blanked and marked for deletion, but they remain in the table (in other words : the table is not packed).

Thus, if records have been deleted :

Error handling

The file TI2611.txt is provided on the conference disk with an example and explanation on using transaction functions return values together with SQLError( ) and SQLMessage( ).

Network transactions (file servers)

Concurrent transactions

dBASE does not allow concurrent or nested transactions. However, sessions can provide a work-around as well as some additional possibilities to handle several transactions on the same database.

How

Just issue a CREATE SESSION before starting a transaction. Database, tables as well as the transaction itself are then isolated within the session. You can then issue another CREATE SESSION command, which will provide a new clean and independent data environment. The new transaction may then be started, without interfering with the transaction started in the former session. This way, several transactions can be started and maintained, one per each session.

Practical implementation

Examples in files Client6.wfm and Product6.wfm demonstrate how a client and product form can start concurrent transactions. Just run any one of the two forms. Then, you can start the other form from the tool push-button of the form.


Table record buffer management


Now that we got the complicated stuff out of the way, we can stop for a minute and ask: Do we really need to use transactions for simple record level validation ?

Of course, asking the questions means the answer is: NO, we don't. Or, should we say : in Visual dBASE 5.5 we don't need to.

Buffer saves on variables and code

In DOS versions of dBASE most developpers used memory variables to buffer form data input (remember @ ... SAY/GET ?).

In Visual dBASE 5.5 the same level of security and versatility is achieved while establishing a datalink directly to the table fields. This is because the programmer now has access to the internal table record buffer. Whenever a record is read form the table on the disk, or a new record is appended to the table, this record is in fact present in dBASE memory, in a buffer zone.

Visual dBASE no longer reserves this buffer for its own usage only. A set of methods (functions) have been made available in the Form class to handle adding, saving or abandonning the current record buffer.

What this means is that all the pages of code we used to type to populate memory variables from table fields, and then handle the data entry in the memory variables, and then REPLACE the fields in the table back with the memory variables, all this code is now useless.

Please note that this feature is available in Visual dBASE only, and that table record buffering is not implemented in dBASE for Windows 5.0.

Modifying data

Suppose you have a form whose entryfields are datalinked to a table. When you are reading form a table, the current record is in the buffer. The datalinked fields' data is then available in the entryfields on the form. When the user gets to the end of the form, he may want to validate or else to cancel the changes.

PROCEDURE SaveButton_OnClick
        SELECT Table
        Form.SaveRecord()
PROCEDURE AbandonButton_OnClick
        SELECT Table
        Form.AbandonRecord()

Appending data

Instead of using the commands APPEND BLANK / REPLACE, we can now use the method BeginAppend() to create a new empty record in the buffer :

PROCEDURE AppendButton_OnClick
        SELECT Table
        Form.BeginAppend()

In a typical application, the form is then presented to the user for data entry. In a similar way as for modifying data, the methods SaveRecord() and AbandonRecord() are then used for validation / cancelling the new record.

There is one fundamental difference with APPEND BLANK / REPLACE: in case AbandonRecord() is used, it is just the buffer which is cleared. The record doesn't have to be deleted because it hadn't been added to the table yet.

Checking for modifications

As the validation / cancellation methods can be triggered in many different places in your program (in other words: the methods can be invoked via many different events), you may need to check if the buffered data has been modified by the user. This is done with the method IsRecordChanged() :

PROCEDURE NewRecordButton_OnClick
        SELECT Table
        IF Form.IsRecordChanged()
                * ... do some validation work
                Form.SaveRecord()
        ENDIF
        Form.BeginAppend()

Warning: To have IsRecordChanged() giving reliable results, you need to apply the patch for version 5.5a to Visual dBASE 5.5. You can probably find the patch on the Conference CD or in the lab. Otherwise you can also download it from the libraries in the forum VISUALDB on CompuServe, or from the Borland Web site (http://www.borland.com). You can also abtain the diskettes from Borland for a nominal fee.

Buffering methods' scope

Consider the scope of these four methods with great care :

  1. Being methods of the Form class, they are encapsulated in the form objects as created in your application.
  2. Acting on table records, they are also scoped to the current session.
  3. And finally, they operate on a record buffer, which is scoped to the work area.

In other words, there is a distinct record buffer per work area, in a given session, and you have to use the dot notation with the object name prefix (or the "form" generic one in the class description) to use the methods of a given form.

Some more examples are given on the Conference CD.

At this point, some warnings have to be made about specifics in the behavior and usage of record buffering.

The 10 buffering points to avoid side effects

  1. When you move the record pointer in a buffered table, an automatic SaveRecord() is issued by Visual dBASE.
  2. When the same table is open in two sessions, the two record buffers are completely independent, and the first one is not even aware of the second one.
  3. If you are calling subforms using the same table(s) from within your form, beware of buffer getting committed automatically. Remember you don't control the record pointer in that case, rather the View property of the form objects is in control.
  4. If you are using views including several tables, check carefully for correct behavior, as views can close and reopen tables, which will flush the buffer to disk in the meantime.
  5. Also, in a multi-table view, don't forget to SELECT the appropriate work area before issuing a form.BeginAppend() / SaveRecord() / AbandonRecord(), or else you may act on the wrong buffer.
  6. Don't use RECNO() with record buffering. There is no record number yet when you are updating the buffer. The record number will only be assigned when you SaveRecord(). However, you may use BOOKMARK() to "remember" a row in a table.
  7. Checking for duplicate keys and lookups : remember the data in the buffer in invisible to other sessions/users until it is saved.
  8. Beware of commands / events / methods which may result in moving the record pointer in your view. If you face such problems, check for some events which may refresh the form and trigger the update.
  9. If using a Browse object, you must beware of the fact that it is very easy for a user to move around in a Browse, and that navigating away from a modified record will trigger the automatic SaveRecord() if you don't pay attention.
  10. Stay in control : when the user has clicked the New Record button, disable it until proper action has been achieved and a SaveRecord() / AbandonRecord() has been issued.


Summary


In this talk, we have demonstrated how usage of both sessions and/or transactions can help use Visual dBASE efficiently in a multiprocess / multitasking event driven environment, a file server network application tool, and as client/server front end application and query tool. We have also shown how table record buffer management saves one level of transaction processing and lots of coding.We have included additional material in this paper than is covered in the talk, so that you may use it together with the samples provided to overhaul the subject in greater depth.


Acknowledgment

Some of the examples provided have been obtained from the dBASEWIN forum on CompuServe (where TeamB and other volunteers provide useful help to Visual dBASE users), and some others have been prepared together with David Acremann from MKO. Thanks to all.


Contact

François Ghoche can be reached at : Internet fgwebfghoche.com ;
CompuServe 100023,24 ; mail : A.T.O.N. - 33 allée des Genêts - 78280 Guyancourt (France) ;
voice : +33 (0) 139 440 301 ; fax : +33 (0) 139 440 331 ;

World Wide Web : http://www.fghoche.com.


Page Top / début de cette page
Page d'accueil de ce site / Homepage


Last update : 14 Oct 96