C++ Builder Developer's Journal / Cheating with TUpdateSQL

January 1998

Cheating with TUpdateSQL

by Gerry Myers

I recently had to add database support to a C++Builder application. I'd really gotten to like the VCL and I knew C++Builder had built-in database components, so I wasn't worried. My database needed to display its rows in different sorted orders, so I decided to use the TQuery component. I dropped the component onto my form, set the required properties (Database and SQL), linked in a data source and grid, and clicked Run. When the program opened, I thought, "Wow! I just added database access to my program in less than five minutes. I have the rest of the week to goof off." My database grid displayed the proper sorted order (thanks to the SQL statement in the TQuery component), and I could scroll through the records with no problems.

Next, I decided to change a value in one of the grid fields just to verify that everything worked, but the grid wouldn't let me change the data. I could select and highlight any cell, but that was it.

I consoled myself with the idea that I must not have set the grid's ReadOnly property to false--or perhaps I forgot to set the TQuery's RequestLive property to true. Unfortunately, it wasn't that easy. All my component properties were set correctly. The grid was not set for ReadOnly, and the TQuery was requesting a live resultant set from the SQL's SELECT statement.

When I dug into the Help files and accompanying C++Builder database manual, I found that the Borland Database Engine (BDE) will attempt to return a live resultant set from a SELECT query, but in some cases it just can't. If the SELECT statement contains an ORDER BY clause (which mine did), the BDE returns a non-live resultant set. Non-live means that the data can be displayed--in a grid or edit field, for instance--but can't be modified.

If you're considering using C++Builder for database applications, you're probably thinking the same thing I was: What good is a query component if the data flows only one way? Luckily for you and me, the Borland folks recognized this limitation as well and provided a back door to non-live data sets. This capability is wrapped in a VCL component called TUpdateSQL.

In this article, we'll introduce you to the TUpdateSQL component and demonstrate how to use it to do the "dirty work" for the TQuery. It turns out that integrating the TUpdateSQL component with TQuery is surprisingly easy. By the time I had everything working in my program, I still had a few days left to goof off--but don't tell anyone.

A query's resultant set

Before we dive into the TUpdateSQL component, let's discuss a few bits of background information. First, you need to understand that data returned from a query is called a resultant set. If the query contains a SELECT SQL statement, the query returns a resultant set (data from the database) that you can use and/or display. However, if the query contains a DELETE, INSERT, or UPDATE SQL statement, a resultant set isn't returned (that is, no data are returned). As far as this article is concerned, the important thing to remember is that a SELECT query returns a resultant set. Resultant sets come in two flavors: live and not live. A simple SELECT query returns a live resultant set. This data can be displayed, modified, and saved back to the database. If the SELECT query contains qualifiers or constraints like ORDER BY or sub-queries, it returns a non-live resultant set. Figure A shows examples of simple and complex queries.

Figure A: These examples illustrate simple and complex SQL SELECT queries.

// Simple SELECT query returning
// a live resultant set.
SELECT MeasId, Description 
  FROM MeasTable
  WHERE GroupIndex = Pressure

// Complex SELECT query returning
// a non-live resultant set due to
// the ORDER BY clause.
SELECT MeasId, Description
  FROM MeasTable
  WHERE GroupIndex = Pressure
  ORDER BY MeasId

// Complex SELECT query returning
// a non-live resultant set due to
// the sub-query.
SELECT MeasId, Description
  FROM MeasTable
  WHERE GroupIndex =
    SELECT GroupIndex 
      FROM GroupTable
      WHERE TestIndex = 2

When you drop a TQuery component on your form and scroll through its properties, you'll come across a property called RequestLive, as you can see in Figure B.

Figure B: The TQuery's properties include RequestLive.
[ Figure B ]

This property defaults to false, which tells the BDE that you don't need a live resultant set even if you are legally entitled to one. This result is good if you want read-only data, but that isn't what I needed. When you set RequestLive equal to true, the BDE will try to return a live resultant set. However, if your SELECT query statement is complex, the BDE can't oblige you. This is where I sat--I was requesting a live resultant set, but because my SELECT query was complex, the BDE could only return a non-live resultant set. What to do?

Cached updates

The TQuery's CachedUpdates property also plays a role in modifying non-live resultant sets. You generally use this property when accessing your database over a network. You can tell the BDE (by setting CachedUpdates equal to true) that you want it to locally hold all changes that you or the user makes to the data. The BDE caches the updates in a local buffer and writes them to the database as a single transaction when you tell it to later. This process cuts down on network traffic. CachedUpdates has another application, though, when working with local databases. The BDE won't let you modify a non-live resultant set--however, it can hold all your changes in a local buffer, since that doesn't violate the non-live resultant set rule. The actual data in the database isn't changed, so the SQL cops are happy.

Back in my program, I set the TQuery's CachedUpdates property to true. When I ran the program, the grid still wouldn't let me change any data values. I knew I was on the right track, but something else was missing.

TUpdateSQL

As I read through the Help files concerning resultant sets and cached updates, I discovered a component called TUpdateSQL. You can link this component to a TQuery component and use it to make changes to a non-live result set. To allow the TQuery and TUpdateSQL components to work correctly together, the TQuery must cache its updates. It looked as though I was on the right track. How exactly does the TUpdateSQL link to the TQuery? This is where the "back door" comes in. Whenever the user or the code makes a data modification to a TQuery resultant set, the component pointed to in the TQuery's UpdateObject property is executed. As you can see in Figure B, the TQuery's UpdateObject property is set to my new TUpdateSQL component. Each time the user changes the data by editing the values in the grid cells, my TUpdateSQL is executed. The TUpdateSQL actually makes the change to the database. This is kind of like the Godfather who can't go out and do his own dirty work--he sends out his hit man, instead. Each time a record is changed, the TUpdateSQL is executed to take care of this one change to the database.

The TUpdateSQL property list shown in Figure C is short, so there's not much to learn.

Figure C: The TUpdateSQL component has relatively few properties.
[ Figure C ]

As the properties show, you can use TUpdateSQL for deleting, inserting, and generally modifying a database. Each of the properties can contain separate SQL statements. I used the following SQL statement to allow the user's changes in the grid to be written to the cached update buffer:

UPDATE Measure.DB
  SET Reading = :Reading
  WHERE MeasId = :OLD_MeasId

The only field that I allowed to change was Reading.

A couple of things may look strange to you in this SQL statement. The first is :Reading, which is a variable for the database field called Reading. The TQuery component supplies this variable when the TUpdateSQL component is executed. It holds the value in the Reading field that was just entered by the user or modified by the program. The other oddity is :OLD_MeasId--this is the value from the database's MeasId field before the current change was made to the database. The TQuery component also supplies this value.

In plain English, my SQL statement finds the record in the underlying database that matches the MeasId of the current record (the WHERE part). It then updates the value of that record's Reading field with the new value (the SET part). So far, the data change goes only into the cached buffer with the other modifications--and not yet all the way to the underlying database.

TUpdateSQL includes an SQL generator dialog box, shown in Figure D, which will create for you an SQL statement similar to my example.

Figure D: TUpdateSQL provides this dialog box, which generates SQL statements.
[ Figure D ]

You simply make a few selections in the dialog box and select the Generate SQL button. To open the dialog box, double-click on the TUpdateSQL component on your form. You make your selections on the Options tab. The SQL tab lets you see and modify the generated SQL statement. The TQuery component that's linked to your TUpdateSQL provides the values in the Key Fields and Update Fields lists. If you like, you can manually enter your own SQL statement into the appropriate TUpdateSQL property (DeleteSQL, InsertSQL, or ModifySQL).

Apply/Commit cached updates

s As we mentioned, the Cached Updates buffer holds any changes to the data made by the user (through the grid) or by the application. If you ran your program at this point, it would appear that everything was working. You'd be able to make changes to the grid's Reading field (thanks to the TUpdateSQL component), but that's as far as it would go. Since the new values haven't been written to the actual database, the updates would be lost when you exited the program. To save changes from the Cached Update buffer to the actual database, you must tell the database to apply and commit them. The following code accomplishes this task:

// Setting tiDirtyRead is 
// required for Paradox tables.
Query1->Database->
    TransIsolation = tiDirtyRead;

// Tell the database to Apply and 
// Commit the cached updates.
Query1->Database->
    ApplyUpdates(
        OPENARRAY(TDBDataSet*, 
            (Query1)));

(For Paradox tables, the transaction isolation property--TransIsolation--must be set to tiDirtyRead.) This code allows other applications to read the applied (but maybe not committed) data from the actual database. The second line of code commands the database to apply (and commit) the data in the Cached Update buffer. The single database method ApplyUpdates() both applies and commits. It's acceptable to instead call the dataset's ApplyUpdates() method, but the changes won't be permanent and may be subject to a later rollback. If you do use the dataset's ApplyUpdates() method, you must also call the database's Commit() method to make the changes permanent. You'd enter this code in your application at the point that you want to make the updates permanent--for instance, when the program exits, when a button or menu is chosen, or when the grid loses focus.

Putting it all together

In this article, we discuss the elements that allow you to modify data from a non-live resultant set. Here's a handy reference guide to those elements.

Drop a TQuery and a TDataSource component onto your form and set their properties to point to your database. Enter an SQL statement to return the desired non-live resultant set.
Set the TQuery's RequestLive and CachedUpdates properties equal to true.
Drop a TUpdateSQL component on your form.
Set the TQuery's UpdateObject property to point to your TUpdateSQL component (link the TUpdateSQL to your TQuery).
Double-click the TUpdateSQL component to start up the SQL generator, then make your field selections. An alternative is to double-click one of the TUpdateSQL's SQL properties and manually enter your SQL statement.
Drop a data-aware grid (or other data-aware component) onto your form and set its properties to point to your TQuery. Set its ReadOnly property to false.
Run the program and make changes. Exit and restart the application to verify that your updates were in fact written to the database even though the TQuery returned a non-live resultant set.

Conclusion

Sometimes it can be frustrating to integrate a database system into your application when the rules of SQL seem to get in the way. However, you can use the TUpdateSQL component in many situations to update almost any dataset. TUpdateSQL is powerful, but it can also be confusing, since it doesn't operate on its own behalf. Instead it's linked to a TQuery component and carries out actions that the TQuery isn't allowed to perform. When I use TUpdateSQL, I feel as though I'm cheating, since the data returned from my query isn't intended to be modified. Come to think of it, maybe that's why I like using it so much--we programmers don't have many chances to enjoy a feeling of power and control!