In this section we shall look at some more technical aspects of ADO technology.
An alternative way of storing a connection to an ADO dataset, rather than using the connection string, is through a data link file. This removes an undesirable feature of the connection string approach whereby the location of the database is hard coded into the .exe file. To create a link file run a text editor such as Notepad and save an empty file with a .UDL suffix. Find the file in Windows Explorer, double click it and the connection string editor will appear; choose the Provider and Database file as before and click OK to save the file. Here is a sample file generated in this way:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\Delphi examples\invoice_database3\invoices.mdb;Persist Security Info=False
To use the data link file click on the dieresis of the ConnectionString property, choose the Use Data Link File option in the dialogue box and use the Browse button to locate the file.
A common location for data link files is C:\Program Files\Common Files\System\OLE DB\Data Links. If the location of the database is changed the .UDL file can be edited to reflect this.
The cursor of a dataset is a marker that keeps the position of the current record and which defines the mode of movement through it. These two characteristics are covered by the CursorLocation and CursorType properties:
The use of ADO with databases takes place within the context of clients and servers. It is, of course, possible to create a database with a program such as MS Access for use on a single computer, but most serious work with databases now takes place within the context of a network. Within a network environment there are client computers and servers and data tends to be held on servers so it can be controlled and shared centrally and used by the clients (this is, after all, how the term database came about, a solution to the problem of separate departments keeping their own information).
The CursorLocation property has two possible settings, clUseClient and clUseServer. When the cursor location is in the client, all processing of the dataset takes place on the user's, or client's, computer; the data is retrieved from the server and held in the memory of the client computer while processing takes place. Processing might include editing data and adding or deleting records. The cursor is managed by the ADO Cursor Engine, which provides a service to other OLE DB providers, such as the MS Jet database engine. Some benefits of client cursors are that they are faster than server cursors and more versatile because the data is held in memory; some disadvantages are that data has to be transferred across a network before it can be processed and there are issues to be resolved concerning updates by different clients.
In a client/server database such as one based on SQL Server, InterBase or Oracle, the cursor is located on the server, along with the Database Management System (DBMS). Server-side cursors are more suited to situations where the datasets are very large and may not fit into a client's memory (remember that many real-world databases can contain hundreds of tables and millions of records). On the other hand, as the number of users of a database system grows the load on a server-side cursor increases and response times fall as the server becomes a bottleneck. Demands for more users using more data across a world-wide network drive the development of bigger and faster servers and improved communications speeds.
Choice of cursor type is affected by the choice of cursor location. There are four main types of cursor:
There is a fifth cursor type, ctUnspecified, which is part of ADO but is not needed in Delphi applications (it is provided for programming in languages such as Visual Basic where no help is provided and properties can be unspecified; in Delphi ADO components have constructors, which initialise the their own properties so that none are unspecified).
A client-side cursor location (for a local database such as a list of contacts on a personal computer) is limited to the static cursor only, while the other three are limited to server-side locations. Cursors are measured in terms of how expensive they are in resources.
The least expensive cursor in terms of resources, and the one with the fastest performance, is the OpenForwardOnly type. This cursor type reads the number of records specified in the CacheSize property (default of 1) and reads another one when it runs out. It is unable to navigate backwards through the dataset beyond the number of records in the cache. The forward only cursor type is suitable for batch processing but not for any operation that requires a jump to a particular record, forwards or back.
The static cursor type reads a complete dataset (or result set) and provides an area in memory of CacheSize records. The cursor can move backwards and forwards through the result set but updates made by other users (edit, add, delete) cannot be seen because the data is static.
The keyset cursor is based on a set of keys, usually the primary key, which are loaded into the client's memory and used to identify the relevant records on the server for purposes of updating - only the keys are transferred, not the complete records, so data transfer is reduced. When the application needs to perform an update the records corresponding to the the keys are transferred so the data, coming from the server, is up to date.
The most expensive cursor in terms of resources and processing is the dynamic type, which is almost identical to the keyset cursor except that the whole data set is reread when data is not in the cache.
It is possible to build client-server databases with MS Access but large applications, such as those covering a large enterprise, require more powerful DBMS systems such as SQL Server and Oracle. A SQL Server database will typically be located on its own server and may be spread across two or more servers.
It is possible to sort a dataset in memory on different fields by setting up an index on a chosen field. This can be done by using the OnTitleClick event of the DBGrid component:
procedure TForm1.DBGrid2TitleClick(Column: TColumn);
begin
if ADOTable3.IndexFieldNames = Column.Field.FieldName
then
ADOTable3.IndexFieldNames:= Column.Field.FieldName + ' DESC'
else
ADOTable3.IndexFieldNames:= Column.Field.FieldName;
end;
This code checks to see if the field title clicked is the current index, in which case the sort order is reversed (the 'DESC' command), otherwise the new field column is used as the index. (This does not appear to work with every data type, some produce an error.)
Transaction processing allows updates to be grouped together into a logical unit of work so a database can accept or reject them as a single entity or batch.
Many transactions in data processing systems require all parts of data transfer to be completed successfully, not just some of them. For example, any operation involving the transfer of data from one source to another must be completed in it entirety or some of the data may be lost. One example of this is a financial transaction where money is taken from one account and added to another: both operations must be completed otherwise money may be 'lost' from the first account, with no destination, or 'gained' by the second with no source. Similarly, if a contact name is removed from one table of 'possibles' or 'applicants' and placed in another table for 'definites' both parts of the operation must be completed if the integrity of the system is to be preserved and data are not simply lost. Another example might be where a transaction reduces a stock level and the system does not allow the stock level to fall below a certain level, in which case some transactions might have to be cancelled until the stock level is replenished. This is such a common issue in database processing that a special term and procedure exists for it: rollback, whereby if an error occurs at some point in a transaction the process is 'rolled back' to the starting point so nothing has changed.
ADO handles transaction processing with three methods of the ADOConnection component:
These methods can be attached to buttons on a form. Under the Jet 4.0 OLE DB Provider (the one we have been using) transactions can be nested to a depth of five, that is, the BeginTrans process can be started five times without a closing call to CommitTrans or RollBackTrans. The need to remember how many actions have been started in order to commit them or roll them back becomes quite complex so some extra processing would have to be done such as setting the Enabled property of the buttons to False or providing counters on screen. The BeginTrans function returns an integer which could be used to control the Commit and Rollback procedures.
The big difference when using transaction processing is that the database is not updated until the CommitTrans procedure is activated. In a system without transaction processing, using the Navigator bar to edit records, new data are added to the database automatically, without intervention from the user. In the example below, without transaction processing, when a new Invoice is created and items are added to it the underlying database is updated
One possible sequence of events might be:
This sequence should ensure that all items are processed. If any of the CommitTrans actions were replaced by a RollbackTrans then the action concerned would not be completed and the database would be unchanged. This could be particularly important if, for example, a wrong entry was made in the Quantity field, resulting in a reduction in the number of items in stock. On the form below the Stock field is updated when the Quantity field is changed so, without the rollback facility, users would have to be very careful when entering the quantity as repeated entries will take more items off the Stock. Using the transaction processing commands makes it possible to protect the data against such errors because the user can simply click the rollback button and the processing will be cancelled and any changes in the database will be undone. This is clearly a very useful, if not essential, facility in a database and much time and effort will be put into getting a transaction system working faultlessly.
Three buttons have been added to the form to allow for transaction processing. Further thought should be given to the design of the form, the code and the navigation bars.
In a multi-user database system, the sort found in most organisations, data must be protected from attempt to perform updates by more than one user at a time. Without some form of protection it would be possible for something like the following to occur:
The changes made by User B will be overridden by those made by User A. To guard against this sort of situation databases employ locking strategies, whereby users have exclusive editing rights to data items so they cannot be accessed by other users for simultaneous editing.
There are four types of lock available under ADO (plus the undefined state mentioned above), which in Delphi are known as:
The ReadOnly mode locks the data against any change and removes all editing permissions.
Pessimistic and Optimistic locking are similar but for the degree of expectation in the designer that access conflicts will occur. If the designer believes conflict from multiple users is likely then pessimistic locking will be applied and records will be locked until editing finishes and the user relinquishes the record. If other users attempt to edit the same record as the first user they will receive an exception error saying that the record is locked for editing by another user. One benefit of this is that a user knows that, once started, an editing operation can be completed. One disadvantage is that other users may be locked out of the same record for as long as the first user takes to complete the task; if the first user is slow or is interrupted (or goes to lunch!) the time period may be unacceptably long and some form of timer may have to be introduced. Pessimistic locking also requires a server-side cursor so if the designer wants to use aspects of client-side locking he will have to do without pessimistic locking.
You can test locking for yourself by setting the LockType property of an ADOTable to ltPessimistic and then running two copies of a program - when you try to edit the same record you will get an error message.
Filtering of an ADODataSet (or ADOTable) is based on the FilterGroup property and is used to display or process groups of records that have been selected on various criteria. The criteria include:
fgUnassigned Specifies that no filtering is in effect. This constant is used internally by the ADO dataset component.
fgNone Removes any current filtering and all rows are visible. Can also be done by setting the Filtered property to False.
fgPendingRecords Filters to show just the rows that have been changed and the changes have not been applied (UpdateBatch method) or canceled (CancelBatch).
fgAffectedRecords Filters to show just the rows affected by the last update.
fgFetchedRecords Filters to show just the rows in the current update cache. This is the results of the last call to retrieve rows from the database.
fgPredicate Filters to show just deleted rows.
fgConflictingRecords Filters to show just the rows that had changes made to them, but could not be applied due to errors on the apply attempt.
In situations where the user is not connected to a database (working off-line) or in web applications where connections are not sustained a batch update may be preferable or the only option available. The idea is that the user prepares a series of edits to data in memory and then submits them as a batch to the database when they are ready - as in the 'shopping basket' approach of e-commerce.
Batch updates can be enabled by setting the lock type in an application to ltBatchOptimistic, by setting the CursorLocation property to clUseClient and calling the ADOTable.UpdateBatch (or ADODataSet.UpdateBatch) method. To reject a batch use the CancelBatch or CancelUpdates methods. Another useful property is ADOTable.UpdatesPending, which records whether or not a batch of records has been sent to the server for updating the central database. This can be used where the user attempts to close a form without posting the updates.
Batch updates employ optimistic locking, which is where the designer considers that there is a low probability of conflicts over data resources. Thus users are allowed to edit any record at any time and conflicts are dealt with when the records are saved. In particular, if a record is opened by two users and a change is saved by one user the second user will not be able to save any changes in the same field until the updated record is re-read. Under ADO the second user will, in fact, be able to update data in a different field of a record being edited by another user. ADO offers four dynamic update criteria for controlling the way updates are performed:
Choice of criteria for updates is down to decisions made by the designer for the conditions that will arise in any given context.
A disconnected recordset is a group of records in memory that has had its link to the central database removed. This may be done deliberately to keep down the number of connections to a database, thus making it more efficient, or to create a 'briefcase' application where data is saved locally for use off-line. When a set of records has been retrieved from a server there is no reason to maintain the connection until the user wants to update them; there is no harm in closing the connection until the update is requested and there is a clear advantage in performance terms in keeping the number of active connections to a minimum. Someone working away from a central database, such as a salesperson, may save a set of records on a laptop computer for use on the road, update them as required and then submit them to update the central records later - this is the 'briefcase' model, as if the user had put some paper records in a briefcase.
The code to disconnect from a database use:
ADOTable1.Connection:=nil;
To remove the data from view use:
ADOTable1.Close;
To reconnect the database use:
ADOTable1.Open;
For a batch update after an off-line 'briefcase' style operation:
procedure TForm1.Save3Click(Sender: TObject);
begin
ADOConnection1.Connected:=True;
ADOTable1.Connection1:=ADOConnection1;
try
ADOTable1.UpdateBatch;
finally
ADOTable1.Connection:=nil;
ADOConnection1.Connected:=False;
end;
end;
(The ADODataSet object might be used as well as ADOTable.)
A 'persistent' recordset is created with the SaveToFile method of the ADOTable or ADORecordSet components. A file can be opened with the corresponding LoadFromFile method. A recordset can be saved in either Advanced Data Table Gram format (ADTG) or as XML:
ADOTable1.SaveToFile ('filename.adtg', pfADTG);
ADOTable1.SaveToFile ('filename.xml', pfXML);
ADO's XML format is specific to Microsoft so may not be readable by other applications (in fact the XML formats of ADO 2.1 and ADO 2.5 are also incompatible), though Delphi does include an XML translator. XML files can be opened in Internet Explorer:
XML files can be processed to extract the data stored and XML is expected to play a major part in the development of automated communication of data within and between organisations.
The 'briefcase' model can be implemented by making Save and Open options available for every table in the database and making the application available on a user's machine such as a laptop. Where an application is running locally in 'briefcase' mode it will need to use the LoadFromFile method instead of having the Connected property of its Connection components set to True. It will also need to use SaveToFile instead of UpdateBatch when data is saved. The application could detect whether it is to be used in 'briefcase' mode by waiting for a failure to connect to the database or by detecting a local file or flag whose presence confirms the mode.
We can develop a separate form for the three tables in the invoice examples or we can add the features we have considered here to the main form with all three tables. To develop the main form we need to add a new element to the menu structure:
To disconnect from the database but leave the data in memory:
procedure TForm1.Save1Click(Sender: TObject);
begin
ADOTable1.Connection:=nil;
ADOTable2.Connection:=nil;
ADOTable3.Connection:=nil;
end;
To re-connect to the database:
procedure TForm1.Save1Click(Sender: TObject);
begin
ADOTable1.Open;
ADOTable2.Open;
ADOTable3.Open;
end;
To perform a batch update to the invoices table:
procedure TForm1.Save3Click(Sender: TObject);
begin
if form1.ADOConnection1.Connected=False then
begin
ADOTable1.Connection:=form1.ADOConnection1;
form1.ADOConnection1.Connected:=true;
end;
try
ADOTable1.UpdateBatch;
finally
ADOTable1.Connection:=nil;
end;
end;
(The code to update the other tables would be practically the same.) To save to an XML file (change the file name for the different tables):
procedure TForm1.SaveFile1Click(Sender: TObject);
begin
ADOTable1.SaveToFile('invoices.xml', pfXML);
end;
To save to ADTG (change the file name for the different tables):
procedure TForm6.SavetoADTG1Click(Sender: TObject);
begin
ADOTable1.SaveToFile('invoices.adtg', pfADTG);
end;
To open an ADTG file (change the file name for the different tables):
procedure TForm6.LoadFRomFile1Click(Sender: TObject);
begin
ADOTable1.LoadFromFile('invoices.adtg');
end;
For remote working we need a simple form that will read a data file and save it:
Notice that the Refresh button of the Navigator Bar has been removed because it does not work in conjunction with an off-line file.
Now we can use the two applications together to perform remote updates to a file and batch updates to the main database.
The records can be updated by using the Tab key to move to a new row or by using the Navigator Bar. The data display is not connected to the database so the Invoice ID (AutoNumber) and Total Cost fields are not updated at this stage, they should be left blank (make them read only). When updates have been made, either as edits to existing records or deletions or new records, the new file should be saved. The saved file would have to be copied to a suitable location (such as the home directory of the on-line application) so it can be read into the main application.
On opening the main application the updated records will not be available so the ADTG file will have to be opened. The latest edits can now be seen and the next step is to perform the UpdateBatch operation. The user could now close the application and re-open it to see the new data or choose Disconnect and Connect from the menu (may have to choose Connect twice). At this point the Invoice ID should have been added and the Total Cost field will have been set to £0.00.
The main database form could also be used for working off-line as the Disconnect, Connect, Open File and Batch Update options are all available.
Any edits performed on-line should be saved to the ADTG file and copied to the off-line computer before setting out on the road. We have thus constructed a 'briefcase' application. A complete application for off-line use would, of course, include other files and tables and it may be that this is not such a good example, the production of invoices would be much better done on-line. This case study illustrates general principles and your project work could include more realistic examples.
One such example might be a database of clients in an investment company. When members of the sales team go out to see clients they can take a copy of the latest data on file with them, amend it as required with changes to existing customers and entry of new ones and then update the central database on their return. Another example might be an electronic mark book or grading system in a school where the teachers take a copy of a file home with them, enter new data off-line and then update it next morning (the Microsoft Class Server product, based on SQL Server, does similar things).