To BDE or not to BDE, that is the question

Every time I start a new database application I'm always asking myself the same question - Is it time to finally retire the BDE (Borland Database Engine) ?

I've been using the BDE now for well over 15 years - from right back in the days of Paradox and dBase flat files on BNC networks, through to now with client-server databases such as mySQL, Interbase and Microsoft SQL Server.  But is it time to move on in today's environment ?

My first attempt to move away from the BDE several years ago was use the dbGO components Borland offered and use ADO connections.  I ran in to several problems with that, although managed to get some working applications going, before deciding it offered no significant advantages over the BDE.

The next step was dbExpress.  Borland have been shipping dbExpress now since 2000 and trying to coax old faithfuls like me over to it ever since.  Amongst the claims they quote a number of advantages such as easier deployment, cross-platform support, better performance and wider driver support.  In my experience I believe all of these to be true, but the main thing holding me back was the existing applications I was maintaining, and my own set of libraries and functions I had developed over the years. 

The problem I've always encountered when starting of with dbExpress was updateable queries.  I guess I'd become rather spoilt over the years where I could just do something like the following -

1. TQuery *Qry=new TQuery(...);
2. Qry->SQL->Text="select * from tblMyTable where ...";
3. Qry->RequestLive=true;
4. Qry->Open();
5. Qry->Edit();
6. Qry->FieldByName("myField")->AsString="my value";
7. ... other updates...
8. Qry->Post();

The same code works equally well for inserts and updates.  No need to write the underlying "insert into" or "update" queries and worry about escaping data etc...

Trying this in dbExpress with the equivalent TSQLQuery,TSQLTable or TSQLDataSet fails with the error "Cannont perform this operation on a closed dataset".  This is because these components provide only unidirectional non-updateable data sets.

Updating Datasets with dbExpress

The recommended solution for updating or appending data is to use a TClientDataSet, TDataSetProvider and TSQLQuery.  You can find full instructions for that process here - Migrating BDE Applications to dbExpress

Call me lazy but I just don't want to do it after being spoilt all these years!

Introducing TSimpleDataSet

I guess Borland heard the protests of others like me and provided TSimpleDataSet which is effectively a combination of the 3 components.  

You simply set the Connection property to your TSQLConnection (or use the internal one) and then put your query in the CommandText of the DataSet property and it's as close to the BDE as you'll get.  One thing to be mindful of is that with dbExpress you must call ApplyUpdates.

1. SimpleDataSet1->DataSet->CommandText="select * from tblMyTable where...";
2. SimpleDataSet1->Open();
3. SimpleDataSet1->Edit();
4. SimpleDataSet1->FieldByName("myField")->AsString="my value";
5. SimpleDataSet1->Post();
6. SimpleDataSet1->ApplyUpdates(0);

For most cases TSimpleDataSet will get your out of trouble, but it does have some limitations which you can read about here - Migrating BDE Applications to dbExpress

Writing Your Own Class

Another option available is to simply write your own class to handle generating the respective update or insert into query.  In the end this is the path I chose as it gave me full control over the queries produced and allowed me to add some other functionality and mimic a similar class I'd developed in PHP as I never had the luxury of the BDE there anyway!