More porting issues

Saturday morning, 7:30 am. I'm sitting on the small balcony outside our house writing this on a mobile computer. It's very quiet; the only sounds I hear are the birds twittering in the tree and the occasional child. The dog and I have already been out for our walk. I'm using the mobile because my main computer is "in hospital": it's been conking out after about 30 minutes of use. Obviously the fan in the power supply is causing problems. Actually, according to the repairman, the fan is fine; the insides of the computer are full of dog hairs....

I am in the process of porting my flagship program from using the Borland Development Engine (BDE) to Firebird; I want to write about some of the "gotchas" which I've come across in the past few days, things which one doesn't read about in the various texts. Actually, the issues have nothing to do with Firebird and everything to do with the dbExpress components which I am using to connect to the Firebird server.

As any Delphi programming book which covers the topic will tell you, the dbExpress components are uni-directional; this makes them simpler and less demanding of system resources, but it also makes them problematic for someone used to the BDE query component. Here are two problems which I've come across.

1. Recordcount

The 'recordcount' function of a query gives the number of records which have been returned by the query, as its name might suggest. I use this function in two different ways: most times, I set a progress bar's length to the number of records returned so that the user can have feedback via the progress bar how the computation is progressing. Very important when there are 3000 records to be processed.... Less frequently, I use the function to see whether the query even returned a record set, in order to do some more computation.

The dbExpress TSqlComponent has a 'recordcount' function, but unfortunately most of the time it doesn't work. The documentation states that there is no function when the query is called with parameters or when it is a multi-table query (there is also a third case but I don't remember what it is). This means that the recordcount function is fairly useless, as it is extremely rare that I will program a function which uses only one table and has no parameters (it turns out that I do have one such query in my program, 'select key from table'). So how does one solve the problem?

When I initially want to see whether the query returned a record set before processing, I now use the 'eof' function; if 'eof' is true after opening the query, then there is no record set. Simple.

As for getting the number of records returned, the only solution which I've found is to define another query, the same as the original but simpler, which returns 'count (*)' instead of actual fields. After opening the query, the value of the aggregate is passed to the progress bar.

2. Sorting a record set displayed in a dbgrid by clicking on column titles

Delphi's dbgrid component exposes a method which is called when a column title is double clicked. I use this method in order to sort and display the record set, according to which column is clicked. The first gotcha which came when porting this was that the column array is now zero based instead of one based; this is strange as it has nothing to do with porting to dbExpress. It only became clear when clicking on one column caused the grid to sort by a different column.

In BDE programs, the query component connects to the database on one side and to a datasource component on the other side. Sorting the data required closing the query, changing the SQL statement to order by a different field and then reopening. Fairly simple. But when using dbExpress components, life is more difficult: the database is connected to a TSqlConnection which is connected to a TSqlDataset which is connected to a TDatasetProvider which is connected to a TClientDataset which is connected to the TDatasource which is connected to the dbgrid. The SQL statement which chooses the data is in the TSqlDataset, which is then buffered by the TClientDataset. It occurred to me that it would more efficient to acquire the data once from the database, and then sort it in memory via the TClientDataset ('cds').

In order to do this, I predefined several indices in the cds, each corresponding to a column to be displayed; some of these indices contain two fields, and some are defined as descending indices.

Now, when a column title is clicked, I simply change the index used in the cds, and hey presto! the grid is displayed, sorted by the new index. This is very fast as there is no need to reaccess the database.

Here is the code for the event (qPeople is the cds):

procedure TMainForm.DBGrid1TitleClick(Column: TColumn);
begin
 try
  dbgrid1.columns[prevcol].title.font.color:= clNavy
  except
 end;

with dbgrid1.datasource.dataset do
 begin
  column.Title.Font.color:= clRed;
  prevcol:= column.Index
 end;

with qPeople do
 try
  disablecontrols;
  close;
  case prevcol of
   0: indexname:= 'idx_date';
   1: indexname:= 'idx_zehut';
   2, 3: indexname:= 'idx_name';
   4: indexname:= 'idx_company';
   5: indexname:= 'idx_matara';
  end;

  open
 finally
  enablecontrols
 end;
end;