Using Transactions

Transactions are an integral part of a client/server database. IBExpress provides the TIBTransaction component to manage transactions. Each TIBTransaction component handles one transaction on the database to which it is assigned.

A TIBTransaction component enables you to specify the properties of a transaction. These are stored in the Params property of the component. TIBTransaction has a built-in editor that enables you to use four predefined transaction settings. In Bug Tracker, we will use the Read Committed setting. InterBase and IBExpress both default to the transaction to Snapshot. Changing to Read Committed is a normal first step when dropping a new TIBTransaction onto a data module or form.

CAUTION

Most problems involving disappearing data or incorrectly updated information can be traced back to an error in implementing a transaction. With the version of IBExpress that we used for this section (version 4.1), we were occasionally surprised by an unexpected commit that occurred on a record post.

This problem was because of a known logic bug in IBExpress wherein a dataset will commit a transaction that it autostarts if there are no other active datasets when it closes . This bug was scheduled for repair in a later update of IBExpress, and might have been fixed in C++ Builder 6. In the meantime, it is a good idea not to allow datasets to autostart transactions, but instead to start them yourself.

If you run across a problem such as this, the TIBSQLMonitor component is invaluable for tracing the transaction events that are occurring.

Top

Accessing Interbase

IBExpress offers many different ways of working with the information in a database. These include TIBTable , TIBQuery , and TIBDataSet . If a TIBTable or TIBQuery is used, the TIBUpdateSQL component can also be used.

`TIBUpdateSQL`

The TIBUpdateSQL component is designed for use with the TIBTable or TIBQuery component. It provides SQL statements to be executed to perform the actions needed for the four different operations you can perform on the database: deleting, inserting, updating, and refreshing.

The component has an editor (as seen in Figure 10.2) to supply the creation of these statements. The editor can only work when the component has been assigned to a TIBTable or TIBQuery , and the TIBTable or TIBQuery has been set up for a specific table or query. This is because otherwise it will not know the names of the fields involved in the update, insert, or delete operations.

Figure 10.2. The `TIBUpdateSQL` dataset editor.

graphics/10fig02.gif

The editor will automatically create a base statement for each of the four operations, but it might be necessary to edit the automatically generated statements to take into account triggers, stored procedures, or other database-specific considerations. To accomplish this, the TIBUpdateSQL component supplies two sets of parameters to the SQL statements. The first of these sets is simply the field names relevant to the underlying table or query (such as :PRO_ID ). The second set appends the prefix OLD to the field name (such as :OLD_PRO_ID ) and represents the value of a field as it was prior to applying the cached updates.

`TIBTable`

Just like the standard C++Builder data access components , TIBTable tries to create a live (editable) dataset on a specified table or view. The tables available to the component can include the database's underlying system tables, if they are specified in the TableTypes property.

TIBTable attempts to return a live dataset, but because this is an actual client-server database rather than a desktop database, it might not be possible under certain conditions. This is especially true when referring to views. For these conditions, or when more flexibility is desired, TIBTable can use a TIBUpdateSQL component to enable specific SQL statements to execute the changes to the underlying dataset.

TIBTable is rarely a good component for client/server clients . Because TIBTable polls as much data to the client from the server as possible in a single try, it requires much more network traffic than a similar select * from <table> in a TIBQuery or TIBDataset .

The use of the Insert function of TIBTable is not recommended. Appending data to a specific position in a SQL backend has no real meaning. In a file-based database, such as Paradox, Insert occurs where indicated, but there is no natural meaning to row position in a client/server database such as Interbase, Lulu move because the table rows are spread out across multiple data pages and are rarely contiguous. If you try to use Insert() , InterBase will place the data wherever it wants. Although TIBTable will do a FetchAll and place the new record at the end of the internal record buffer, it is highly inefficient because of the processing required of the client and the network traffic generated.

`TIBQuery`

TIBQuery returns a read-only dataset based on any InterBase SQL statement. TIBQuery offers a higher degree of control over what data is presented than TIBTable . By using joins, you can specify SQL queries that combine information in several different tables. TIBQuery should be used instead of TIBTable whenever possible.

If the TIBQuery needs to insert, edit, or delete information, a TIBUpdateSQL component must be assigned to it. However, it is typically preferable to use a TIBDataSet , which combines the capabilities of a TIBQuery and TIBUpdateSQL , to accomplish these tasks .

`TIBDataSet`

TIBDataSet combines the data-selection features of a TIBQuery component with the data-editing features of a TIBUpdateSQL component. One difference between TIBDataset and TIBQuery is that the Params for TIBDataset are not TParams , but TIBExpressSQLDA instead. The TIBExpressSQLDA parameter type is required if you are using InterBase version 6.0 and need Int64 parameters. For the Bug Tracker application, we will make extensive use of TIBDataSet s.

`TIBSQL` and `TIBStoredProc`

The TIBSQL component offers a fast, low-overhead means of executing InterBase data operations. Descended from the TComponent object, the TIBSQL component does not support any of the data-enabled objects, and, thus, is primarily designed to return a quick value from a database or generator or to be used as a data pump.

TIBStoredProc can work with data-enabled objects, but a TIBQuery or TIBDataSet should be used for stored procedures that return multiple rows or components. Designed to simplify accessing stored procedures, TIBStoredProc enables you to specify the procedure to execute and the parameters for that procedure. Note that any values that are returned by the stored procedure will be accessible through the corresponding parameter.

`TIBEvents`

Using InterBase and stored procedures or triggers, events can be set up to be generated by the server. An example of a generated event is the command POST_EVENT 'BUG_DELETE' , where BUG_DELETE is the name of the event fired .

The TIBEvent component allows the client application to tell the server what events it wants to be notified of, receive that notification, and generate an OnEventAlert() event. Bug Tracker executes a simple ShowMessage() when a bug record is deleted, but more complex functions could easily be included.

Events of this sort are one of the most powerful features of Interbase. It is one of the few DBMS that enables you to register interest in particular internal database events. Such notifications make it much easier to create client/server, or multitier applications. Such applications can register their interest, for instance, in updates or Row insertions that might affect their user interface. This helps to alleviate a problem that often plagues client-server systems with client side user interfaces. Such systems usually do not know when another application or instance of the same application has modified database, and, therefore, they are unable to modify their user interfaces, or refresh their datasets, to display newly inserted or modified records.