ADO or DBX using Delphi

Question

Which is better (and for what reasons) to use to connect to MS SQL, Oracle or Firebird from a Delphi Win32 application -- ADO or DBX (Database Express)?

Both allow you to connect to the major databases. I like the way ADO does it all with a connection string change and the fact that ADO and the drivers are included with Windows so nothing extra to deploy (it seems, correct me if I'm wrong).

DBX is also flexible and I can compile the drivers into my app, can I not?

I really am keen to have a single source if possible, with the ability to vary databases depending on the customer's IT department/preferences.

But which is easier to program, performs better, uses memory most efficiently? Any other things to differentiate them on?

Thanks, Richard

Answer 1

ADO is simple to use and is there, you only must make sure to install the correponding client driver in the client side.

I found DBX more flexible and it is better integrated within IDE and another technologies like DataSnap.

For the same purpose than you, I have used DBX with Third Party Drivers from DevArt. You can compile the drivers with your application if you buy the drivers sources.

Answer 2

General rule: every layer of components will possibly add an additional layer of bugs. Both ADO and DBX are component wrappers around standard database functionality, thus they're both equally strong. So the proper choice should be based on other factors, like the databases that you want to use. If you want to connect to MS-Access or SQL Server, ADO would be the better choice since it's more native for these databases. But Firebird and Oracle are more native for the DBX components.

I personally tend to use the raw ADO API's, though. Then again, I don't use data-aware components in my projects. It's less RAD, I know. But I often need to work this way because I generally write client/server applications with several layers between the database and the GUI, thus making things more complicated.

Answer 3

ADO is Microsoft world

DBX was created at the beginning (Delphi 6) for cross platform and Kylix

Answer 4

My two cents: DBX is significantly faster (on both oracle and sql), and significantly more finicky and harder to deploy.

If performance is a factor, I'd go with DBX. Otherwise, I'd just use ADO for simplicity's sake.

Answer 5

As others have said, DBX may have the edge in raw performance in certain cases or under specific circumstances, but ADO is the basis for a very larger number of applications in the world so although performance of ADO may be relatively poorer, clearly that does not mean "unacceptably" poor.

For myself, and informed by major projects I have worked on, the biggest "problem" with DBX is that no matter how good it may be, it is a key infrastructure technology provided by a language/tools company.

Anyone that built applications on the previous BDE technology will testify to the disruption caused when that technology is deprecated and no longer supported. Whilst no technology is immune from deprecation by it's provider, ADO clearly has the edge when it comes to industry support beyond the technology provider themselves.

For that reason I myself now always use ADO. Just changing the connection string isn't always the only thing to worry about when changing from one database type to another however. Stored procedure call syntax can vary from one ADO provider to another, and you still have to watch the SQL syntax you use if you intend deploying against multiple different SQL engines, where the SQL support may vary from on to another.

To mitigate these issues I use my own encapsulation of the ADO object model. This encapsulation does not attempt to mutate the object model into something that doesn't resemble ADO, it simply exposes those parts of ADO that I need to use directly in a more ObjectPascal friendly (and "type" safe) form (e.g enum types and sets for constants and flags etc, rather than just scores if not hundreds of integer constants).

My encapsulation also takes care of some of the minor variations in different provider behaviours/requirements, such as the previously mentioned differences in stored procedure call syntax.

I should say also that similar to another poster, I too long ago stopped used "data aware controls", which opens up this approach. If you need or wish to use data aware controls and wish to use ADO, then you cannot use ADO directly and must instead find some encapsulation that exposes ADO thru the VCL dataset model.

Answer 6

In the beginning of Delphi, people praised the multi-DBMS support in Delphi. Everyone loved the BDE (because that was the only way to do that).

But when looking at customers over more then the past decade, I have seen a steady decrease of multi-DBMS support in their applications.

The cost of supporting multiple DBMS from one application is high.

Not only because you have to have knowledge of each DBMS, but also because each DBMS has its own set of pecularities, where you have to adapt for in your data access layer. These not only include differences in syntax and underlying data types, but also optimization strategies.

Also, some DBMS work better with ADO, some better with a direct connection (like skipping your Oracle client all together).

Finally testing all the combinations of your software with multiple DBMS systems is very intensive.

I've been involved in a few projects where we had to change the DBMS backend and/or the data access technology (from i.e. BDE to DBX, or from DBX to a direct connection). Changing the backend always was much more painfull than changing the data access technology. Multi-tier approaches made them somewhat easier, but increased the degrees of freedom and therefor the testing efforts.

Some of products that I do see that support multi-DBMS are in vertical market applications where the final customer already has their own DBMS infrastructure and the application needs to adapt to that. For instance in Dutch governmental areas, Oracle has been really strong, but SQL Server has established quite a user base as well.

So you need to think about what combinations of DBMS you want to support, not only in terms of functionality, but also in terms of cost.

If you stick to one DBMS, then it makes no sense to go for a generic data access layer like BDE, DBX or ADO: it pays off doing a connection as direct as possible. My experience has taught me that these combinations do work well:

• Interbase or Firebird with FIBPlus, AnyDAC, IBO or IBX*
• Oracle with AnyDAC, DOA or ODAC

• Microsoft SQL Server with ADO

  • IBX does not like Firebird very much.

Hope this gives you some insight in the possibilities and limitations of supporting multiple DBMS from your Delphi applications.

--jeroen