Top 10 Tricks for Delphi and C++Builder VCL Database Developers by Cary Jensen

27/09/2014

Top 10 Tricks for Delphi and C++Builder VCL Database De…

EMBAR C ADER O HO ME

LO C ATIO N | KO R EAN | LO G O N

Watch, Follow, & Connect with Us Share This

COMMUNITIES

ARTICLES

BLOGS

RESOURCES

DOWNLOADS

HELP

EDN » Delphi » Database

Top 10 Tricks for Delphi and C++Builder VCL Database Developers by Cary Jensen

RATING

By: David Intersimone Abstract: This article provides an overview of a number of important techniques in general Delphi and C++Builder VCL database development.

Top 10 Tricks for Delphi and C++Builder VCL Database Developers by Cary Jensen Jensen Data Systems, Inc. Note: The following paper was presented at the 1999 Inprise/Borland Conference in Philadelphia Pennsylvania. Click on the source code link to download the examples used in this paper. The Visual Component Library (VCL) provides a number of components that greatly simplify database development. These include the components on the Data Access page of the component palette, as well as those on the Data Controls page. Additional database development components appear on the Midas page of the component palette, but these are generally associated with multi-tier development, which is a special case, and are largely ignored in this paper.

Download Trial Buy Now Download Delphi XE7 now! Get Free Trial Special Offer

This paper provides you with an overview of a number of important techniques in general VCL database development. If you are currently developing database applications that use the VCL you will not doubt be familiar with some of these techniques. Therefore, the explicit goal of this paper is to provide you with a list of techniques that should be familiar to all active VCL database developers, assuring your awareness of these operations. It should be noted in advance that some of the technique described here are appropriate for both client/server applications as well as those that are not (including both stand alone applications as well as those that run on a network).

The Importance of Preparing Parameterized Queries and StoredProcs Client/server applications, those where the data is stored and for the most part manipulated on a remote database server, make extensive use of queries and stored procedures. When working with data

http://edn.embarcadero.com/kr/article/20563

1/25

27/09/2014 Top 10 Tricks for Delphi and C++Builder VCL Database De… manipulation language (DML) SQL statements, it is generally necessary to prepare the query or stored procedure prior to its execution. Likewise, since the act of preparing a query or stored procedure involves the allocation of resources on the server, it is necessary to unprepare the query or stored procedure when done. This preparation and unpreparation can be performed through explict calls to TQuery and TStoredProc components Prepare and UnPrepare methods, or they can be performed automatically by these components. This is how it works: When you make a TQuery or TStoredProc active (by setting its Active property to True or calling its Open method) an implicit call to Prepare will be generated if the query or stored procedure has not already been explicitly prepared. Likewise, when the query or stored procedure is made inactive (by setting Active to False or calling the Close method) the UnPrepare method is implicitly called. This calling of UnPrepare, however, only occurs when the Prepare statement was implicitly called. Whenever a query or stored procedure is explicitly prepared, by calling the Prepare method prior to activating the object, an implicit call to UnPrepare does not take place. In these cases it is necessary for you to also explicitly call UnPrepare when you are through with the object. Since the preparation and unpreparation of a query or stored procedure requires time (including a network round trip as well as resource allocation on the server), it is best to minimize the number of times these operations are performed. If you are working with a query or stored procedure that is executed repeatedly, therefore, it is critical that you explicitly prepare the object prior to its first activation, and only unprepare it after it is de-activated for the last time. The significance of explicit versus implicit invocation of Prepare and UnPrepare is important when the query or stored procedure that you are working with is intended to be called repeatedly. For example, parameterized queries, those that include one or more parameters, are often called repeatedly. The difference in performance between explicitly and implicitly prepared queries is demonstrated in the Delphi project named PREPARE.


2/25

27/09/2014


Figure 1. The PREPARE project main form

Judicious Use of Data Modules, and When to Avoid Them A data module is a form-like container. Unlike a form, however, a data module is never visible to the user. Instead, its sole purpose is to hold one or more components that can be shared by other parts of your program. One of the most common uses for a data module is to hold data sets (including the TClientDataSet, which is available in the client/server editions of Delphi 3 and 4), permitting two or more forms within the application to share the properties and methods defined for those data sets. The alternative to using a data module is to place a different set of data set components on each form in your application. While there is certainly nothing fundamentally wrong with this approach, it means that every form contains data set components that must be individually configured. If two or more forms need to display the same data or event handlers (for providing client-side data validation, for example), placing those data sets on a single data module that is shared by the two or more forms provides for easier development and maintenance. But data sets are not the only components that can be used with data modules. In fact, a data module can hold any component that does not descend from TControl. This includes MainMenus, PopupMenus, OLEContainers, IBEventAlerters, Timers, as well as any components on the Data Access and Dialogs pages of the component palette, to name a few. The most common components to place on a data module are datasets (including TTable, TQuery, TStoredProc, and TClientDataSet components). Many developers also like to place TDataSource components


Webinars on demand!

Delphi Like

16,694 people like Delphi.

3/25

27/09/2014 Top 10 Tricks for Delphi and C++Builder VCL Database De… on the data module as well. While this is often considered to be a matter of style, I prefer to place DataSource components onto the individual forms. Doing so permits you to convert a form from using one data module to another by simply changing the DataSet property of the DataSource. If the DataSource component appears on the data module, switching a form from using one data module to another requires that the DataSource property for every data-aware control on the form be changed. Depending on the number of data-aware controls, this may be a major task.

Facebook social plugin

The DATAMOD project on the code disk demonstrates how a data module permits two forms to share a common cursor to a data set.

More social media choices: Delphi on Google+ @RADTools on Tw itter

ARTICLE TAGS restore Figure 2. The DATAMOD project with two forms sharing a common cursor.

Should You Always Use Data Modules The answer is "No," you do not always put data sets on data modules. Yes, data modules are great. Yes, they provide you with a single repository for configurations and event handlers that can be shared. But they are not appropriate in every situation. Data modules are a perfect solution for those situations where two or more forms, or other similar containers, need to share a common set of components. The project example built earlier in this article is a good example of that. Since both Form1 and Form3 needed to share a common view of Table1, including any ranges, filters, sort orders, calculated fields, and so on, the data module provided an easy and effective means for this. This sharing is not limited to single Tables either. There is not reason why two or more forms cannot share a multitude of data sets, dialogs, timers, and the like, placed on one or more data modules. In fact, there are a number of situations where you must use a data module. For example, if you are using the MIDAS technology found in Delphi 3 and Delphi 4 client/server editions, you must place your BDEDataSet components, as well as any provider components that you need, onto a remote data module. Remote data modules are special data modules that implement certain interfaces necessary for the cross-application communication that is required by MIDAS. Another example where you are required to use a data module can be found with the Web Broker components. These components, also available in Delphi 3 and Delphi 4 client/server, as well as available separately from Inprise, make use of a WebModule (a TDataModule descendant). Using the WebModule you define the actions to which your web server extension can respond. (If you use a WebDispatch component, a web module is not necessary.) But there are situations where a data module can be used, but doing so unnecessarily complicates your applications. The general rule of thumb is that you do not use a data module when there should be no


4/25

27/09/2014 Top 10 Tricks for Delphi and C++Builder VCL Database De… sharing of a data set. The classic example of this is when you are creating reports that use Delphi data sets for their data. These data sets should never be shared. The reason for this is that VCL-based reporting tools must navigate a data set in order to print data. Imagine what can happen if one of these reports uses a data set on a data module, and that same data set is used by data-aware controls on a form. The user is viewing the form and then prints the data set. The next thing the user sees is their form scrolling frantically, as the report navigates the data set. Not only can this be confusing to the user, but is causes a catastrophic loss of performance for the report, since the data-aware controls in the user interface must be repainted as each record is navigated to. What is interesting about the preceding example is that it represents a "best-case senario" for data module sharing with reports. Imagine what happens to the report if the user is currently editing a record, and that record contains errors that prevent the cursor from leaving it. Imagine what would happen if the user prints two reports simultaneously, and both of those reports share a data module. They would be fighting for the control of the cursor, but the user may never know this. Clearly, reports should not share data set.While reports provide a clear example where data set sharing is not acceptable, there are two other situations where data modules are generally not acceptable. The first is when you have one form that uses a completely unique view of data. That view either may involve a table that is never viewed from any other form, or a table that makes use of a range, filter, or sort order that is not used anywhere else in the application. A second instance where data modules should typically be avoided is when you are writing multiple instance forms. A multiple instance form is one where more than one copy can be displayed simultaneously. Of course, part of such a design is that each instance displays a different record, or set or records, or different sort order, or some similar difference. Obviously, such forms cannot share a single data set. The easiest way to design a multiple instance form is to add the data set or data sets directly to the form. This ensures that each instance of the form has its own data set or sets, meaning that each form has its own cursor(s), and view(s), of the data. For both of these last two examples it could be argued that a data module could still be used. For unique view forms, a data module can be used, just not shared. Likewise, with multiple instance forms, each instance of the form can be responsible for creating its own instance of a data module. However, using a data module in these cases unnecessarily complicates your application. Why use two containers (a form and a data module) when one will suffice. Since the primary benefit of data modules is simplicity, it seems absurd to use a data module when it increases complexity. A final note about data modules is certainly in order here. By default, they are auto-created. If you always use data modules, and they are always auto-created, it is likely that all of your data sets will be opened when you start your application. This can result in long application start up times, and an unnecessary number of table locking resources being used. I once saw an application that had a data module that was auto-created, and it contained about 100 data sets. As you can imagine, one reason that the client asked me to look at this application in the first place was that they were unhappy with the load time. The solution to problems caused by auto-created data modules is to remove them from the Auto-created forms list on the Project Options dialog box, just as we did to Form3 in the example presented earlier in this article. Once you do this, however, you must take responsibility for creating your data modules on-the-fly, prior to displaying a form that makes use of the components on the data module. This can be complicated, however. If one data module can be used by two or more form, each form must test for the pre-existence of the data module upon the form's creation. If the data module does not yet exist, it must be created. Releasing the data module, if this is desired, also requires more coding. Specifically, since one data module may be used by more than one form, it is not enough to simply free the data module when a form is closing. Instead, you must implement some form of reference counting for the data module, so that you release it only when the last form requiring it is being closed.


5/25

27/09/2014


When and How to Disable DataSets Data sets, including tables, queries, stored procedures, and client datasets encapsulate methods that perform data access. You use a data source when you want data controls (such as DBGrids, DBNavigators, DBEdits, and so forth) to be able to view and/or manipulate this data. The data source and data set components communicate with one and other, permitting the data source to instruct a data control to repaints itself following changes to data, as well as permitting the data source to attempt to place a data source in an edit mode in response to a users interaction with a data control. The communication between a data set and a data source is not always desirable, however. For example, if you code needs to temporarily leave the current record to examine data in some other location of a table before returning to the original record you probably do not want a DBGrid displaying the data to show the user this operation. In instances like these you should disable the data sets communication with the data source. You do this by invoking the data sets DisableControls method, invoking EnableControls to restore the communication. There is one general rule of thumb for using DisableControls. Specifically, you must provide some assurance that the EnableControls method is executed following a call to DisableControls. One way to do this is to immediately following a call to DisableControls with a try-finally, including the EnableControls in the finally block. The following Object Pascal pseudo code demonstrates this technique: Table1.DisableControls; try // Perform some operation on Table1 finally Table1.EnableControls; end; This technique is demonstrated in the DISCNTRL project on the code disk. Figure 3. The DISCNTRL projects main form


6/25

27/09/2014


The DISCTNRL project includes a button that scans through every record in a local Paradox table, converting the data in the Name field to either uppercase or lowercase characters.

The Power of Ranges A range limits the records in a data set to a subset of all records. A critical feature of a range is that it makes use of an index, providing high performance in both local and client/server applications. By comparison, filters created using the Filter property of a data set, do not use indexes, making them acceptable only when working with relatively small data sets. Client/server developers tend to avoid ranges since they are only available with TTable components. This is unfortunate. While it is generally a better to use either SELECT queries or stored procedures to extract subsets of records from a remote database server, the speed and simplicity afforded by ranges makes them a useful tool in two-tier applications. In fact, I have seen instances where ranges substantially out-perform queries for operations such performing summary calculations on a small subset of records. You have two options when it comes to applying a range. The first, and easiest to use, is the SetRange method. This method has the following syntax: void __fastcall SetRange(const System::TVarRec * StartValues, const int StartValues_Size, const System::TVarRec * EndValues, const int EndValues_Size); Both arrays that are passed as parameters must have the same number of elements. The value in the first http://edn.embarcadero.com/kr/article/20563

7/25

27/09/2014 Top 10 Tricks for Delphi and C++Builder VCL Database De… element of the first array corresponds to the beginning, or lowest values for the range on the first field of the index. The value in the second element, if provided, identifies the lowest value for the range on the second field of the index, and so on. The elements of the second array identify the ending, or highest values of the range for each indexed field, with the first element corresponding to the first field in the index, the second, if provided, for the second field in the index, and so on. The arrays that you pass to the SetRange statement do not have to have the same number of elements as there are fields in the current index. For example, if the current index is based on the City, State, and Country fields, it is acceptable to set a range only on the city field, or both the city and state fields, if desired. The following demonstrates how SetRange can be used to limit the display of records in a table to those where the city field contains "New York". Assume that Table1 is a component defined for a table named CLIENTS.DB. Furthermore, assume that this table has an index named CityIndex, which is a single field index on the City field of CLIENTS.DB. The following statement sets the IndexName property to CityIndex, and then sets a range to display only those clients whose records contain New York in the City field: Table1->IndexName = "CityOrder "; Table1->SetRange(ARRAYOFCONST(("New York ")),ARRAYOFCONST(("New York "))); To set a range based on a multi-field index, include more than one set of starting and ending values in the array parameters. For example, if you have a table named Invoices, and this table is using an index based on the fields CustNo and InvoiceDate, the following statement will display all records for customer C1573 for the dates 12/1/98 through 5/1/99: Table1->SetRange(ARRAYOFCONST(("C1573 ", "12/1/98 ")),ARRAYOFCONST(("C1573 ", "5/1/99 "));

Using ApplyRange An alternative to using SetRange is to use the methods SetRangeStart, SetRangeEnd, and ApplyRange. While these statements also require an index (either primary or secondary), it permits fields to be explicitly assigned their starting and ending values for the range without using an array. The following example defines the same range as that demonstrated in the preceding listing: Table1->SetRangeStart(); Table1->FieldByName("CustNo ").AsString = "C1573 "; Table1->FieldByName("InvoiceDate")->AsString = "12/1/98"; Table1->SetRangeEnd(); Table1->FieldByName("CustNo")->AsString = "C1573 "; Table1->FieldByName("InvoiceDate")->AsString = "5/1/99"; Table1->ApplyRange(); Removing a range is much easier than applying one. To remove a range use the method CancelRange. This method has the following syntax: void __fastcall CancelRange(void); In general you should issue a Refresh to a Table after calling CancelRange. The use of SetRange is demonstrated in the RANGE project, shown in Figure 4. The following includes all of


8/25

27/09/2014 Top 10 Tricks for Delphi and C++Builder VCL Database De… the relevent code, found on the OnClick event handler for the Button named RangeButton.: void __fastcall TForm1::Button1Click(TObject *Sender) { if (Button1->Caption == "Apply Range") { Table1->SetRange(ARRAYOFCONST((Edit1->Text)), ARRAYOFCONST((Edit1->Text))); Button1->Caption = "Cancel Range"; } else { Table1->CancelRange(); Table1->Refresh(); Button1->Caption = "Apply Range"; } }

Figure 4. The RANGE Project

Creating Local Lookup Tables with ClientDataSet The ClientDataSet component, while normally associated with thin client applications in a multi-tier environment, have the additional capability of being able to read and write directly to the local hard drive.


9/25

27/09/2014 Top 10 Tricks for Delphi and C++Builder VCL Database De… This feature is often used to create briefcase applications > those that permit a user to connect to the application server, retrieve data, save a local copy of it, and then disconnect from the server. The user is then free to use the stored copy of the data without being attached to the server. At some point the user re-connects to the server and uploads any changes made to the data since it was downloaded. There is another, albeit similar use for ClientDataSet components. This is to use the ClientDataSet to load small lookup tables from the local hard disk. The ClientDataSet component stores data in an in-memory table, making access to that data, once it is loaded, very fast. Furthermore, by loading the data from the local hard drive you can greatly reduce network traffic. Obviously, this technique is not appropriate for every application. For example, when the lookup tables change frequently the use of local copies increases the likelihood that a user will not see a valid value. Furthermore, if you have great number of lookup tables, with some of them being very large, the memory required to hold this data may reduce your application's performance due to swapping. However, in those situations where the lookup tables do not change frequently, are of a reasonable size and quantity, and especially in situations where network communication is slow, the ClientDataSet provides an attractive solution. The use of a ClientDataSet as a local lookup table is demonstrated in the CDSET project shown in Figure 5. This simple project displays data from the SALES table in database pointed to by the IBLOCAL alias. Upon creation of the application's data module, shown in Figure 6 the following OnCreate event handler executes: procedure TDataModule2.DataModule2Create(Sender: TObject); var RecordsLoaded: Integer; begin //initialize CDSFile variable CDSFile := ExtractFilePath(Application.ExeName)+'emplkup.cds'; if not FileExists(CDSFile) then begin Query2.Open; //Get the Query's provider interface ClientDataSet1.Provider := Query2.Provider; //Load all records ClientDataSet1.Data := Query2.Provider.GetRecords(-1,RecordsLoaded); Query2.Close; //Save file to disk for future use ClientDataSet1.SaveToFile(CDSFile); end else ClientDataSet1.LoadFromFile(CDSFile); //Open the main table Query1.Open; end; If the local copy of the Employee lookup table is not found in the same directory as the application, Query2 is opened, which selects the EmpNo and Full_Name fields from the Employee table. The ClientDataSet is then set to the same Provider as the Query, and then the Query's Provider is used to call GetRecords, which returns an OLEVariant. This value is assigned to the ClientDataSet's Data property, thereby making it


10/25

27/09/2014 available.


An application must provide a mechanism for updating a local lookup table is there is any chance that the official copy will change. In this application this feature is provided by the following event handler, which is attached to the Edit | Update Lookup menu item: procedure TForm1.UpdateLookup1Click(Sender: TObject); var RecordsLoaded: Integer; begin with DataModule2 do begin Query2.Open; ClientDataSet1.Provider := Query2.Provider; ClientDataSet1.Data := Query2.Provider.GetRecords(-1,RecordsLoaded); Query2.Close; ClientDataSet1.SaveToFile(CDSFile); StatusBar1.SimpleText := IntToStr(RecordsLoaded) + ' lookup records loaded'; end; end;


11/25

27/09/2014


Figure 5. The CDSET project uses a ClientDataSet to store a local copy of the Employee lookup table

Figure 6. The data module of the CDSET project includes two Queries, a Provider, and a ClientDataSet.

Making Direct BDE Calls Data-aware controls encapsulate calls to the Borland Database Engine (BDE). There are times, however,


12/25

27/09/2014 Top 10 Tricks for Delphi and C++Builder VCL Database De… when you need to get information from, and control features of, the BDE not surfaced by these controls. This can be done by making BDE calls directly. This section describes where you can find information about BDE calls, and provides several demonstrations of the techniques you can use. Delphi and C++ Builder come with several sources of help for using the BDE. The first is the BDE help file. BDE32.HLP is located in folder C:Program FilesCommon FilesBorland SharedBDE. Another source of information about the BDE can be found in the BDE interface unit. This unit is BDE.PAS in Delphi and BDE.HPP in C++ Builder. There are two general approaches for working with the BDE. The first is to provide for all BDE calls directly, without the intervention of data-aware controls. Accessing BDE functions and procedures this way is a lot of work. In order to do this, you must take responsibility for initializing the BDE, as well as establishing database handles, cursor handles, and record handles. How to do this is demonstrated in the following section.

Packing Tables The use of BDE calls to pack Paradox or dBASE tables in demonstrated in the project PACKTAB.DRP. (Packing releases space from Paradox tables occupied by deleted records, and removes records marked for deletion from dBASE tables.) The critical code for the main form's unit is shown in following listing. procedure TForm1.Button1Click(Sender: TObject); var Tab: PChar; begin if FileListbox1.FileName = '' then begin MessageDlg('No table select',mtError,[mbOK],0); Exit; end; GetMem(Tab,144); try StrPCopy(Tab,FileListBox1.FileName); PackTable(Sender,Tab); finally Dispose(tab); end; end; procedure TForm1.PackTable(Sender: TObject; TabName: PChar); var hDb :hDBIDb; hCursor :hDBICur; dbResult :DBIResult; PdxStruct :CRTblDesc; begin {Initialize the BDE.} dbResult := DbiInit(nil);


13/25

27/09/2014 Check(dbResult)

Top 10 Tricks for Delphi and C++Builder VCL Database De… ;

{Open a Database.} dbResult := DbiOpenDatabase('','STANDARD',dbiREADONLY,dbiOPENSHARED,'', 0,nil,nil,hDB); try {Check raises an exception if the BDE call returns an error code other than DBIERR_NONE. The DBTables unit must be in the uses clause to use Check. In Delphi 2 this procedure is located in the DB unit.} Check(dbResult); except DbiExit; raise end; {Open a table. This returns a handle to the table's cursor, which is required by many of the BDE calls.} dbResult := DbiOpenTable(hDB, TabName, '','','',0,dbiREADWRITE, dbiOPENEXCL,xltNONE,False,nil,hCursor); try Check(dbResult); except DbiCloseDatabase(hDB); DbiExit; raise end; {The BDE is initialized, a database is open, and a cursor is open for a table. We can now work with the table. The following segment shows how to pack a dBASE or Paradox table. Note that before we can pack the Paradox table, the table's cursor handle must be closed, otherwise we would get a 'Table in use' error.} try Panel1.Caption := 'Packing '+ FileListBox1.FileName; Application.ProcessMessages; if AnsiUpperCase(ExtractFileExt(FileListBox1.FileName)) = '.DB' then begin {Close the Paradox table cursor handle.} DbiCloseCursor(hCursor); {The method DoRestructure requires a pointer to a record object of the type CRTblDesc. Initialize this record.} FillChar(PdxStruct, SizeOf(CRTblDesc),0); StrPCopy( PdxStruct.szTblName,FileListBox1.Filename); PdxStruct.bPack := True;


14/25

27/09/2014

Top 10 Tricks for Delphi and C++Builder VCL Database De… dbResult := DbiDoRestructure(hDB,1,@PdxStruct,nil,nil,nil,False); if dbResult = DBIERR_NONE then Panel1.Caption := 'Table successfully packed' else Panel1.Caption := 'Failure: error '+IntTostr(dbResult); end else begin {Packing a dBASE table is much easier.} dbResult := DbiPackTable(hDB, hCursor,'','',True); if dbResult = DBIERR_NONE then Panel1.Caption := 'Table successfully packed' else Panel1.Caption := 'Failure: error '+IntTostr(dbResult); end; finally DbiCloseCursor(hCursor); DbiCloseDatabase(hDB); DbiExit; end; end; Before any calls to the BDE can be made, you must give your unit access to the BDE import unit. You must add this unit to your Delphi uses clause or include it in C++ Builder. In this Delphi example this unit is listed in the Interface uses clause. Also, the DBTables unit must be added to the Interface uses clause. This unit defined the Check procedure, which, when called, automatically raises an exception if passed a return code from a BDE call that not successful. Since this form contains no data-aware components, all access to the BDE must be performed through code. This is achieved through the use of three basic BDE calls, DbiInit, DbiOpenDatabase, and DbiOpenTable. Each of these three calls are demonstrated in the procedure PackTable. In this case, the table that is selected from the main form is opened for exclusive use, w hich means that no other user can access this table while this procedure has the table opened. It is also possible, and usually desirable, to open a table for shared use. The PackTable procedure demonstrates how to pack a Paradox table use the function DbiDoRestructure, while a dBASE table requires the use of DbiPackTable. An example of both of these procedures is demonstrated. The use of DbiDoRestructure in this example is as simple as this function can get. To actually change the structure of a Paradox table with this function would require a much more complex argument list. Once your work with the BDE is done, it is necessary to cleanup after the application. In this case, both the table cursor handle and the database handle need to be released (using DbiCloseCursor and DbiCloseDatabase), and then the BDE must be deactivated (using DbiExit).

Preventing Data Corruption As you can see from the preceding example, managing all of the access to the BDE is a lot of work. It is much easier if you permit data-aware components to do some of this work for you. For example, if you place a Table component onto a form, and then open that table, the BDE will already be initialized, a database handle will be established, and a cursor handle will also exist. (The database handle can be obtained from


15/25

27/09/2014 Top 10 Tricks for Delphi and C++Builder VCL Database De… the DBHandle property of the table, and the cursor handle can be obtained from the Handle property.) Furthermore, when the application no longer needs the BDE, the data-aware components take responsibility for releasing the handles and deactivating the BDE. While a number of BDE calls require a database handle, a table handler, or both, you do not need to acquire these using BDE calls directly. Instead, you can use the DBHandle and/or the Handle properties of your Database and/or DataSet components. This is done in the following project, which demonstrates a technique that is very valuable in high transaction environments. Specifically, it can all but eliminate index out of date errors when Paradox tables are used in multi-user applications where there are many simultaneous postings to the tables. This project is named DBISAVE. In order to improve performance, the BDE often caches edits to local tables. Consequently, a write operation may be delayed a short while after a record has been explicitly posted. The one drawback to this is that if there is a system failure following the record post, but prior to the BDE writing the cached edits, the local may become corrupt. Using BDE API calls, you can instruct the BDE to immediately write posted changes to tables, reducing the likelihood that a system failure will damage tables. There used to be two techniques that you could use for this purpose, but only one of these is considered acceptable with the current versions of the BDE. The acceptable version is to create an AfterPost event handler for each of your DataSet components. From within this event handler you call DbiSaveChanges. This function has the following syntax: function DbiSaveChanges(hCursor): DBIResult; The hCursor argument corresponds to the Handle property of a DataSet. The following AfterPost event handler demonstrates this technique: procedure TForm1.Table1AfterPost(DataSet: TDataset); begin DbiSaveChanges(Table1.Handle); end;

Using TBatchMove and BatchMove BatchMove is a feature that permits you to quickly move records into a Table component. The source of these records can be any DataSet. For example, after executing a query that returns a cursor to a set of records, you can use BatchMove to create a permanent table with those records. Likewise, BatchMove can be used to quickly make a copy of some or all of the records from one table, placing the copies records into another. The capabilities of the BatchMove component appear simple enough. It permits you to move, or copy, records from a DataSet to a table. But this simplicity belies its usefulness. Like a SQL INSERT query, BatchMove can be used to insert records from any DataSet (Table, Query, or StoredProc) into an existing table. Unlike an INSERT query, however, the table that you are copying the records to does not already need to exist. But this is just the beginning. There are, in fact, four major capabilities of the BatchMove component: creating a table and placing the current records of a DataSet in it, deleting records from a Table that correspond to those in a DataSet, inserting records from a DataSet to a Table, and updating existing records in a table based on those in a DataSet. Before continuing, let's consider the essential properties and the sole method of this component.


16/25

27/09/2014


Using the BatchMove Component There are three essential properties for the use of the BatchMove component. These are Source, Destination, and Mode. The Source property can be assigned any DataSet component. In other words, you can assign either a Table, a Query, or a StoredProc component name to this property. While any Table component is acceptable, you should only assign one of the other DataSet descendants to this property if they return a cursor to a dataset. For example, it would reasonable to assign a Query component that contains a SQL SELECT statement to the Source property, but it would not make sense to assign to this property a Query containing an ALTER TABLE statement. The Destination property is always assigned a Table component. This table is where the records of the source DataSet records are copied, deleted, inserted, or updated. The third essential property, Mode, defines the type of operation performed by the BatchMove component. There are five different modes. These are shown in the following table, along with whether the table assigned to the Destination property must exist prior to calling the Execution method of BatchMove, as well as whether the destination table must be indexed. Mode batAppend (default) batAppendUpdate batCopy batDelete batUpdate

Destination Must Exist? No Yes No Yes Yes

Must be Indexed? No Yes No Yes Yes

One of the most common uses for a BatchMove component is to create a new table containing the records returned by a Query or StoredProc. Fortunately, this is also the simplest use of BatchMove. This technique is demonstrated in the project BATDEMO, shown in Figure 7. The main form for this project provides the user with a memo field in which to type a SQL SELECT query. The results of this query, which is executed against the database selected in the Alias combobox when the Execute Query button is clicked, is displayed in the Query Result DBGrid on the form. After executing the query, the results can be written to a new table by clicking on the Copy Result to Table button. The following is the code associated with this button: procedure TForm1.Button1Click(Sender: TObject); begin if Query1.Active = False then Exit; if SaveDialog1.Execute then begin Table1.TableName := SaveDialog1.Filename; with BatchMove1 do begin Source := Query1; Destination := Table1; Mode := batCopy; Execute; ShowMessage(IntToStr(MovedCount)+' records copied'); end; end; end;


17/25

27/09/2014


This code starts by ensuring that the Query component is active. If it is, the code displays the Save File common dialog box using a SaveDialog component. If the user selects or enters the name of the file to copy the query result records to, indicated when the SaveDialog's Execute method returns True, the selected filename is assigned to the Table component Table1. Next, the BatchMove's Source property is set to Query1, its Destination property is set to Table1, and its Mode property is set to batCopy. Its Execute method is then called, which initiates the copying. Finally, a message is displayed, indicating how many records were actually copied based on the BatchMove's MovedCount property. When the Mode property is set to batCopy, BatchMove will create the destination table if it does not already exist (this is also true when Mode is set to batAppend, despite what it says in the online help description). If the destination table is an existing table, it is replaced by the new table when Mode is set to batCopy, and added to if Mode is set to batAppend. In each case where Mode is set to batCopy, the destination table is not keyed. If you want to apply an index to this table you must use the AddIndex method of the TTable class.

Figure 7. The BATDEMO Project main form. Under normal conditions, all records in the source DataSet are copied to the destination (that is, unless a range has been applied to the source DataSet). There may be times that you want to place an upper limit on the number of records that BatchMove can process. For example, if the source DataSet has the potential of containing a very large number of records, in the millions, for example, and the user has the ability to request that all of these records be processed by BatchMove, you may want to specify that no more than a certain number of records can be copied. You can do this with the RecordCount property. When RecordCount is set to 0 (zero), its default value, all records referred to by the source DataSet are processed by the BatchMove's Execute method. When you set this property to any positive integer, that number identifies the maximum number of records that BatchMove will process during any one call to its Execute method.


18/25

27/09/2014 Top 10 Tricks for Delphi and C++Builder VCL Database De… For information on how to use the BatchMove component to update the data in a table, as well as delete data from a table, consult the online help.

Using TTable.BatchMove While the preceding example made use of a BatchMove component, there is another alternative. Specifically, the BatchMove method of the TTable class. There are several important differences between TBatchMove.Execute and TTable.BatchMove. 1. There are very limited options when you use TTable.BatchMove. For example, you cannot define a maximum number of records to be moved. All records from the source are copied. Likewise, you cannot specify a problem or key violation table name 2. The destination of the operation is always the Table that BatchMove is being called on. The following is the syntax of TTable.BatchMove: function BatchMove(ASource: TBDEDataSet; AMode: TBatchMode): Longint; When you call TTable.BatchMove, you specify the DataSet from which the records will be moved and the type of move to produce (batCopy, batDelete, etc.). TTable.BatchMove returns the number of records affected by the operation.

Synchonizing Tables Within a Delphi application it is possible to point two or more Table components to the same physical table. There are times when it is desirable to synchronize these two table components, that is, to make them both point to the same record. One way to synchronize these tables is to use a search method, such as FindKey, FindNearest, or Locate. A second way is to directly synchronize the two tables. This technique, which makes use of the GotoCurrrent method of the Table class, is somewhat limited, but very powerful. You use GotoCurrent to make two Table components point to the same record. GotoCurrent has two restrictions. First, and obviously, the two Table components must be associated with the same physical table. Second, if a range has been defined for one or both of the tables, the record being synchronized to must exist in the range of both tables. However, GotoCurrent does not require that the two tables use the same index. GotoCurrent has the following syntax: procedure GotoCurrent(SourceTable: TTable); The GOTOREC projects demonstrates the use of GotoCurrent.

Creating Audit Trails Using Cached Updates An audit trail is a record of changes that users make to a database. While not all applications require an audit trail (and I personally would discourage implementing one unless there is a clear need for such a feature) the cached updates feature of data sets provides a simple yet powerful mechanism for implementing one. To implement an audit trail using cached updates you take the following steps: 1. Permit the tables for which an audit trail is desire to be edited by users only when these tables are in the cached updates mode.


19/25

27/09/2014 Top 10 Tricks for Delphi and C++Builder VCL Database De… 2. Use a OnUpdateRecord event handler to apply the updates. 3. Apply the updates within a transaction. This transaction should apply to the tables being updated as well as the audit trail tables. 4. Within the OnUpdateRecord event handler write the audit trail record before updating the table for which the OnUpdateRecord event handler is executing. 5. Rollback the transaction if any of the records being updated cannot be applied. This serves to rollback changes to the audit trail table as well. There are numerous issues that you will have to address when creating an audit trail. Specifically, how much detail will you track, and how convenient will it be to search the audit trail records. On one hand you may merely want to note who made a change, when, and to which record. On the other extreme is tracking every change, not only the fact that a change occurred but noting which values were changed and what they were changed to. Regardless of how much detail you want to collect, you also have to decide whether each piece of information that you save will be stored in a separate fields of the audit trail table or in just a few fields. If your audit trail table contains a field for the user's name, date/time of change, type of change (insert, delete, modification) as well as two fields for every field in the table whose changes are being tracked (one for the old value and one for the new) you will have a very large audit trail table, but it will be extremely easy to analyze and search. On the other hand, keeping only several fields, including the users name, date/time, type of change, and a memo with a string containing old and new values results in a much smaller table. On the down side, storing all change data in a single memo, or even a small group of memo fields, makes the information much more difficult to use. There is no one approach that is suited for all situations. You will need to take into account how the audit trail will be used and the frequency with which records are added to it in making your decision. The AUDTRAIL project on the code disk demonstrates the recording of an audit trail within the context of cached updates. This simple example writes three fields to an audit trail table. The first two contain the user's name and date/time of posting while the third is a memo field that contains a description of the update action and all data affected by the action. The following code is attached to the OnUpdateRecord event handler for the Query shown in Figure 8. procedure TForm1.Query1UpdateRecord(DataSet: TDataSet; UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction); var i: Integer; s: String; begin Table2.Edit; Table2.Insert; case UpdateKind of ukInsert: begin Table2.Fields[0].Value := UserName; Table2.Fields[1].Value := Now; s := 'INSERT'; for i := 0 to Query1.FieldDefs.Count - 1 do


20/25

27/09/2014

Top 10 Tricks for Delphi and C++Builder VCL Database De… if Query1.Fields[i].Value <> Null then s := s + ',[' + Query1.Fields[i].FieldName + ']=' + Query1.Fields[i].DisplayText; Table2.Fields[2].Value := s; end; //ukInsert ukDelete: begin Table2.Fields[0].Value := UserName; Table2.Fields[1].Value := Now; s := 'DELETE'; for i := 0 to Query1.FieldDefs.Count - 1 do if Query1.Fields[i].Value <> Null then s := s + ',[' + Query1.Fields[i].FieldName + ']=' + Query1.Fields[i].DisplayText; Table2.Fields[2].Value := s; end; //ukDelete ukModify: begin Table2.Fields[0].Value := UserName; Table2.Fields[1].Value := Now; s := 'MODIFY'; for i := 0 to Query1.FieldDefs.Count - 1 do if Query1.Fields[i].OldValue <> Query1.Fields[i].NewValue then s := s + ',[' + Query1.Fields[i].FieldName + ']OLD=' + Query1.Fields[i].OldValue + ':NEW=' + Query1.Fields[i].NewValue; Table2.Fields[2].Value := s; end; //ukModify end; //case UpdateKind //Post the data to the audit table Table2.Post; //Since the Now function returns time to the //millisecond, pause for a millisecond to ensure //that duplicate keys are never generated. Sleep(1); //Apply the update UpdateSQL1.Apply(UpdateKind); //Complete. Signal completion. UpdateAction := uaApplied; end;


21/25

27/09/2014


Figure 8. The AUDTRAIL Project main form.

The Advantage of Design-Time FieldDefs Assignment In Delphi 4 and later and C++ Builder 4 and later it is possible to define FieldDefs for table components at design-time. The advantages of doing this include: 1. You can reduce network traffic by using FieldDefs rather than having to retrieve metadata from the database. 2. You can visually define the structures of tables at design time as opposed to writing manual code. 3. You can easily "borrow" the structures of existing tables. To create FieldDef definitions at design time, click the ellipsis button on the Table's FieldDefs property to display the collection editor. Click the Add New button on the collection editor once for each field you want to add to the table. Then, select each field, one at a time, and use the Object Inspector to define the fields properties, as shown in Figure 9.


22/25

27/09/2014


Figure 8. Configure your FieldDefs using the Object Inspector. To borrow the structure of an existing table you must first prepare by doing the following: 1. Set the DatabaseName and TableName properties of your Table component to refer to the table that has the structure that you want to borrow. 2. Right-click the table and select Update Table Definition from the displayed Speedmenu. This has the effect of defining FieldDefs based on the table to which you are referring. 3. Change the TableName and or DatabaseName to refer to the new table you want to create. 4. You can now create the new table at design time by right-clicking and selecting Create Table from the displayed Speedmenu. To create a new table at runtime, call the table's CreateTable method. The AUDTRAIL project used design time-specified FieldDefs and IndexDefs definitions that are used to create the audit trail table when the application starts if the table does not already exist.

Summary The VCL provides you with a wide range of solutions for database problems. This paper has outlines some of those techniques that should be known to every VCL database developer. While not all of these techniques are appropriate for every application, there are some that you will likely find yourself using again and again.

About the Author Cary Jensen is President of Jensen Data Systems, Inc., a Houston-based company that provides database developers for application development and support. He is an award-winning, best-selling co-author of eighteen books, including Oracle JDeveloper 2 Starter Kit (Fall 1999, Oracle Press), Oracle JDeveloper 2 (1998, Oracle Press), JBuilder Essentials (1998, Osborne/McGraw-Hill), Delphi In Depth (1996, Osborne/McGraw-Hill), and Programming Paradox 5 for Windows (1995, Sybex). Cary is also Contributing Editor of Delphi Informant Magazine, where his column DBNavigator appears monthly. He is a popular speaker at conferences, workshops, and training seminars throughout North America and Europe, and has served on four of the Inprise/Borland conference advisory boards. Cary has a Ph.D. in Human Factors Psychology, specializing in human-computer interaction. You can reach him at [email protected]. The Jensen Data Systems Inc. Web site is http://www.jensendatasystems.com/. Cary is available for onsite training when you have 6 or more people who need training in JBuilder, Delphi, or Oracle JDeveloper. For more information contact Jensen Data Systems at (281) 359-3311. For training in Europe contact Susan Bennett at +44 (0) 181 789-2250.


23/25

27/09/2014


LATEST COMMENTS Move mouse over comment to see the full text

Reply Posted by Gert Kello on Apr 13 2000

Top 10 Tricks for Delphi ... some bugs Have to agree with others that it's a greate article. But there are some bugs in the AUDTRAIL project. The audit of deleted and inserted records does not work correctly. When record is deleted or... Reply Posted by damian marquez on Mar 08 2000

Good idea Rafael I've been thinking about doing that Rafael... Anyway I've always desired to have a kind of "Data Environment" for the form (I think VFP had that, but I would like to see an ellegant solution from... Reply Posted by Rafael Aguiló on Jan 27 2000

Top 10 Tricks for Delphi and C++Builder VCL Database Developers by Cary Jensen I do not completly agree when Cary Jensen says: "A second instance where data modules should typically be avoided is when you are writing multiple instance forms." The key is not to use the same... Reply Posted by Doug Samuel on Jan 26 2000

Top 10 Tricks for Delphi and C++Builder VCL Database Developers by Cary Jensen Excellent article. Only request is that there be a way to display it in a "printer friendly" format. Right now it is difficult to print without losng the right edge of the article. Reply Posted by Filip Cruz on Jan 19 2000

Top 10 Tricks for Delphi and C++Builder VCL Database Developers by Cary Jensen Great article. It would be good to see more like it. Maybe even more tutorials like Charlie Calvert's or even like the excelent Java tutorials on the SUN web site. Tutorials for Delphi that is... Serve r Re sponse fro m : ETNASC 04

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.


Site Map

24/25

27/09/2014



25/25

Top 10 Tricks for Delphi and C++Builder VCL Database Developers by Cary Jensen

Recommend Documents