Setting Up Bug Tracker

   

---

IBExpress components simplify many of the procedures involved in accessing InterBase data, but care must be taken in their use. If the components are set up properly, the end user need not be concerned with referential integrity, supplying all needed fields, transactions, or the like.

Bug Tracker is designed to isolate the user from these issues by presenting a clean, simple interface.

We will begin the setup with the TIBDataSet for the Program table. We start by dropping the component into our data module and assigning it to a connected TIBDatabase and active TIBTransaction (to simplify the setup process). After this is done, we need to specify our select statement. Because the component is attached to a live connection, we can use the CommandText editor to easily create our select statement, which in this case would be select * from PROGRAM .

update , delete , insert , refresh

Having specified the information the component will display, we need to establish what it will do on update , insert , delete , and refresh . As discussed earlier, the TIBUpdateSQL component can create base statements for these events. However, after these statements are created, it's critical that they be checked to ensure that they do not present an opportunity for the user to input incorrect information. The following code shows the IBExpress-provided modify statement.

 update PROGRAM  set      PRO_ID = :PRO_ID,      PRO_NAME = :PRO_NAME,      PRO_MADE = :PRO_MADE  where      PRO_ID = :OLD_PRO_ID 

There is a problem with this statement in that it theoretically enables the user to change the pro_id field, which is the primary key of the Program table. This should not be allowed, so we will remove the PRO_ID = :PRO_ID statement from the query.

We will also edit the insert statement. Remembering that we assign a new program's pro_id field with a value from pro_id_gen , we can automatically include that value in the insert statement. The following shows a sample of how we can do this:

 insert into PROGRAM      (PRO_ID, PRO_NAME, PRO_MADE)  values      ((select GEN_ID(PRO_ID_GEN,1) FROM RDB$DATABASE), :PRO_NAME,  :PRO_MADE) 

This statement could also exclude specifying the pro_id field because we have a "before insert" trigger for the Program table that will detect a NULL value for it and assign it the appropriate generator value anyway.

The delete statement is adequate for Bug Tracker, but the following refresh statement is not:

 select      PRO_ID,      PRO_NAME,      PRO_MADE  from PROGRAM  where      PRO_ID = :PRO_ID 

This statement creates a problem because the pro_id is not known on the client-side when a user inserts a record. The pro_id field in the grid would remain blank if we did an insert and refresh using this statement.

Using a secondary key, in this case the pro_name field, can solve this problem. The refresh select statement must return only one row. We can change the refresh statement to match up the pro_name field instead of the pro_id field because there is a constraint on the pro_name field limiting it to a one-row return, and because the pro_name field value is known to the client side. Duplication is prevented by the unique constraint on the pro_name field. Consideration should be given to providing secondary keys for use in refresh statements during the database design phase. For example, in the Revision table, we created the following constraint:

 add constraint con_rev_pro_id_r_number unique (pro_id,r_number) 

which enables us to use the following select statement and only return a singleton result set.

 where      PRO_ID = :PRO_ID      AND R_NUMBER = :R_NUMBER 

Fields

Database fields for IBExpress TDataSet descendants are specified in the same way as normal BDE components. However, we will make two changes to the pro_id field. Set Required to false will enable the user to insert a record without specifying a value for pro_id . Set ReadOnly to true will prevent users from changing the generated value. We will make similar changes to the Revision and Bugs tables.

It is important to make these changes. C++Builder takes no notice that a database has a trigger that will populate a field when it is inserted. If a field is designated as NOT NULL , the user will be forced to supply a value to insert the record, which will cause confusion when that supplied value is replaced .

Cached Updates

InterBase Express components support cached updates through the CachedUpdates property. Cached updates allow changes to the information contained in the dataset without the changes being applied to the actual database. The changed information in the dataset is not under transaction control until it is sent to the server by the ApplyUpdates() method.

For Bug Tracker, we will not use cached updates, but will instead use normal transaction control.

NOTE

As with BDE components, the use of the CachedUpdates property, and the ApplyUpdates() , and CommitUpdates() functions overlaps with transactions, but is essential for the correct use of TIBUpdateSQL components. Generally, it is a good idea to put code to ApplyUpdates() and CommitUpdates() in the AfterPost event handler for the data set.

Transactions and Data-Aware Components

For most simple data forms, the method shown in Listing 10.5, when attached to the TIBUpdateSQL 's AfterPost() event, will keep information on the client synchro-nized with the information that is on the server. This method was created using the qrProgram component, and it can be used by any TIBUpdateSQL or TIBQuery component. For refreshing after information has been posted, we set the ForcedRefresh property to true in our datasets.

Listing 10.5 The qrProgram Component's AfterPost Event

 void __fastcall TdmMain::qrProgramAfterPost(TDataSet *DataSet)  {      try      {          //commit the changes, and retain the transaction          trMain->CommitRetaining();      } catch (Exception &E)//if *any* error happens, we'll rollback and restart      {          trMain->RollbackRetaining();          ShowMessage("Error commiting changes.  "+E.Message);      }  }  

There is always an active transaction. When a change is made to the data covered by a transaction, the updates are applied and the transaction is committed and retained. The transaction immediately becomes active again, waiting for the next data entry or change. This helps prevent the possibility of a deadlock caused by two clients editing the same record. Also, after we change the information in a dataset that is used by another (for example, the qrBugs dataset uses qrRevision as a lookup), we call the qrProgramAfterPost() method and then refresh it in that dataset's AfterPost event. This is shown next. In this case, because grBugs uses grRevision , a change in the grRevision dataset should initiate a refreshing of grBugs .

 void __fastcall TdmMain::qrRevisionAfterPost(TDataSet *DataSet)  {      qrProgramAfterPost(DataSet);      if (!qrBugs->IsEmpty()){          qrBugs->Refresh();      }  } 

NOTE

InterBase 6 provides another method for handling transactions. The RollbackRetaining() method enables you to roll back the database, but keep the current transaction context. This saves overhead in not having to start a new transaction, such as after a normal rollback.

---

Top