François Ghoche, ATON Consulting (France) & The European Xbase Institute
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.
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.
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
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.
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.
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
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",; ...
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.
The new dBASE command CREATE SESSION defines a new data file environment each time invoked :
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).
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.
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].
There are several possible places for including CREATE SESSION commands.
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.
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()
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.
Creating a session before starting a transaction is a good way to isolate the transaction process, and avoid unintentional starting of a nested transaction.
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 do not affect memory variables' scope and visibility. In other words, behavior for variables will be the same whether sessions are on or off.
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
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 :
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.
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.
- Desktop operation
- Interactive operation (Command window)
- Programming mode
- Reports and labels
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.
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).
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.
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( )
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)
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.
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 :
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.
Within an application, transactions can be handled implicitly (automatically), or explicitly, upon user's request.
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.
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 :
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( ).
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.
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.
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.
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.
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.
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()
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.
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.
Consider the scope of these four methods with great care :
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.
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.
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.
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