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

This article will try and explain a little bit about the new Database Events that are part of the new features of VFP 7, and how to use these for audit trails, database security etc. Database Events are one of the many new additions to VFP 7; these are like triggers which automatically execute specific routines when something is done with a member of the DBC (such as a Table or a View) or the DBC itself. By default the DBC Events are turned off. There are two ways you can turn on the DBC Events for a given DBC. One is programmatically using something like the following command:

DBSetProp("MyDatabase","Database","DBCEvents",.T.)

The other is using the Set Events On check box in the new Database Properties dialog, to access this right click in the Database Designer and choose Properties.

Note: Once you turn on the DBC Events the DBC will no longer be backward-compatible with previous versions of VFP, but once you turn them off the DBC will become backward-compatible again. The Version property of a DBC returned by DBGetProp(), normally 10, is 11 if DBC Events are turned on.


Just turning on the DBC Events doesn’t mean anything will happen; you have to specify what you want to happen, and this article will explain how to do that, also I will give an example of restricting access to the Database and Tables within the DBC, also the example will show how to keep a check of modifications made.

How much overhead do DBC Events add? Testing the difference in time to open a table with and without DBC_BeforeOpenTable and DBC_BeforeCloseTable events (but nothing in those events) showed a difference of only 2-3 percent, which isn’t a significant amount; obviously, putting extensive code into events will have an impact on performance.

DBC Event Descriptions


DBC Events for Databases
Parameters  DBC_OpenData
cDatabaseName, lExclusive, lNoUpdate, lValidate
  Details This event is called after the DBC is opened. After it fires, if another DBC is current, DBC_Deactivate fires for that database (since it won’t be the current database anymore). DBC_Activate for the newly open database then fires (since this database becomes the current one). Only after DBC_Activate does DBC() return the name of the Database. If DBC_OpenData returns False, the Error “file access is denied” is triggered and the database can’t be opened. (Note: If you have VFP you can stop this code from firing by opening the DBC as a DBF with the USE database.dbc, this then opens the DBC as a table, you can then change the stored procedure code and recompile the database with COMPILE DATABASE)  DBC_CloseData
cDatabaseName, lAll
  Details Called before a DBC is closed; return False to prevent the DBC from being closed. DBC Deactivate also fires (after DBC_CloseData), since the database is no longer the current database.
DBC_ActivatecDatabaseName 
Details
Called when the DBC is made current via such means as Set Database, after Open Database completes, clicking on an open Database Designer window, and so on. If another DBC is current, DBC_Deactive fires for that database first (if that database has events enabled).  DBC_DeactivecDatabaseName  Details
Called when the DBC is made non-current; return False to prevent this from happening (this also prevents the DBC from being Closed).  DBC_BeforeAppendProccFileName, nCodePage, lOverwrite
  Details Called before the Append Procedures command starts; return False to prevent the procedures from being updated.  DBC_AfterAppendProccFileName, nCodePage, lOverwrite  Details
Called after the Append Procedures command has completed.  DBC_BeforeCopyProccFileName, nCodePage, lAdditive
  Details
Called before the Copy Procedures operation starts; return False to prevent procedures from being copied. 
DBC_AfterCopyProccFileName, nCodePage, lAdditive  Details
Called after the Copy Procedures operation has completed.  DBC_BeforeModifyProc
  Details
Called before stored procedures are modified; return False to prevent the editor window from appearing. 
DBC_AfterModifyProclChanged
  Details 
Called when the window for stored procedures is closed.
DBC_BeforeDBGetPropcName, cType, cProperty Details

Called before DBGetProp() executes. Returning False prevents the property’s value from being read, in which case DBGetProp() returns NULL.
DBC_AfterDBGetPropcName, cType, cProperty
Details
Called after DBGetProp() completes but before the value is actually returned.

DBC_BeforeDBSetPropcName, cType, cProperty, ePropertyValue
Details Called after DBSetProp() executes; return False to prevent the property value from being changed.DBC_AfterDBSetProp
cName, cType, cProperty, ePropertyValue
Details

Called after DBSetProp() completes.DBC_BeforeValidateData
lRecover, lNoConsole, lPrint, lFile, cFileName
Details
Called before Validate Database executes; return False to prevent the DBC from being validated. Note that the cFileName parameter isn’t passed if the lFile is False.
DBC_AfterValidateDatalRecover, lNoConsole, lPrint, lFile, cFileNameDetailsCalled after Validate Database has completed.
DBC_ModifyDatacDatabaseName, lNoWait, lNoEdit
 
DetailsCalled after Modify Database is issued; return False to prevent database modifications.

DBC_PackData
 Details
Called before Pack Database executes; returning False from this event prevents the database from being packed, but also tiggers a “file access denied” error, so be prepared to trap for this.

DBC Events for Tables
There’s a DBC event associated with everything you can do structurally with a table. One issue is The Remove Table and Drop Table commands, which do the same thing, have a different set of events. If you want to trap the removal of a table, then be sure to handle both set of events. Another issue is that if you open a table with a different alias, that alias is passed for the cTableName parameter in all table events rather than the real name of the table. Workaround for this is to use:

CursorGetProp("SourceName") 

to determine the real name of the table.


DBC Events for TablesParameters DBC_BeforeAddTablecTableName, cLongTableNameDetailsCalled before a free table is added to the DBC; return False to prevent the table from being added. DBC_AfterAddTablecTableName, cLongTableNameDetailsCalled after a free table is added to the DBC. DBC_BeforeCreateTablecTableName, cLongTableNameDetailsCalled before a table is created; return False to prevent table creation. DBC_AfterCreateTablecTableName, cLongTableNameDetailsCalled after a table is created. DBC_BeforeDropTablecTableName, lRecycleDetailsCalled before a table is removed from the DBC using Drop Table; return False to prevent a table from being removed. DBC_AfterDropTablecTableName, lRecycleDetailsCalled after a table has been removed.  DBC_BeforeRemoveTablecTableName, lDelete, lRecycleDetailsCalled before a table is removed from the DBC using the Remove Table command or visually in the Database Designer; return False to prevent the table from being removed. DBC_AfterRemoveTablecTableName, lDelete, lRecycleDetailsCalled after a table has been removed. DBC_BeforeModifyTablecTableNameDetailsCalled before a table structure is modified; return False to prevent modification. DBC_AfterModifyTablecTableName, lChangedDetailsCalled after a table structure has been modified. You can’t tell what changes are made unless you save the structural information somewhere in the DBC_BeforeModifyTable (or use metadata) and then compare the current structure with the saved information in the DBC_AfterModifyTable. DBC_BeforeRenameTablecPreviousName, cNewNameDetailsCalled before a table is renamed. DBC_AfterRenameTablecPreviousName, cNewNameDetailsCalled after a table has been renamed. DBC_BeforeOpenTablecTableNameDetailsCalled before a table is opened; returning False prevents the table from being opened and triggers a “file access denied” error, which you should be prepared to trap. DBC_AfterOpenTablecTableNameDetailsCalled after a table is opened. DBC_BeforeCloseTablecTableNameDetailsCalled before a table is closed; return False to prevent the table from being closed (no error is triggered). DBC_AfterCloseTablecTableNameDetailsCalled after a table is closed.

DBC Events for Views and Relations will be covered in Part 2, when I have had time to play with these. So back to the main topic and using some of the events described above. Note that these events return .T. for successful and .F. to fail, so if you returned .F. in every event you would not be able to do anything with the database or any table contained within the database.

Example


This Example contains a simple database with a few tables, these tables are:

Audit – Keeps a log with what is going on
dbcUsers – Users allowed to access the Database, and which access level
TestTable – table you can use to modify and do whatever with.

The example will only allow access level 9 to Modify Tables and Stored Procedures.
Users Available:

Login = Admin, Password = Admin – Access Level 9
Login = Guest, Password = Guest – Access Level 5
Login = User, Password = User - Access Level 2

Note there is no error handler with this example.

Events used:
DBC_OpenData
DBC_AfterOpenTable
DBC_BeforeOpenTable
DBC_AfterCloseTable
DBC_BeforeCloseTable
DBC_BeforeModifyProc
DBC_BeforeModifyTable
DBC_AfterModifyTable
DBC_ModifyData
DBC_BeforeAddTable
DBC_AfterAddTable
DBC_BeforeCreateTable
DBC_AfterCreateTable


Note
If you open a main file in the Database like Audit without first opening the Database, you might get a C5 error, why this happens I don’t really know, but if you open the files in the following order you won’t have any problems:

OPEN DATABASE dbcEvents
USE Audit

This is only a small part of what the Events can do for you, with some work you can write tools to keep track of table structure changes, and then e-mail these changes to the rest of your team, so that they know what has been added or deleted. This In the next article I will show how to use Event handlers and triggers to give you a proper audit trail, so when users add/delete or change records these get logged to an audit table.

If you want to know more about these great new features, please feel free to ask.

Download code
You can download Simons sample here. The download is a zipfile. Its size is 29.567 bytes.

ABOUT THE AUTHOR: SIMON ARNOLD

Simon Arnold Simon has worked with FoxPro for over 16 years. He currently works for a company based in Harrogate, North Yorkshire (UK), which specialises in FoxPro, Web and Unix development.
He has a weblog at http://weblogs.foxite.com/simonarnold.

FEEDBACK

Atul R Potdar, Nashik, Maharashtra @ 5/23/2016 12:00:33 PM
Sir,
This is excellent Idea but I will be more thankful if you explain the details procedure how to protract the data by this technique.
Awaring a formal replay please

email id is
rrp_sw@rediffmail.com



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: