Delphi connection to SQL Server - 'futureproof' replacement for BDE?

4
votes

we have an applcation of some size (about 1MLOC) that were started back in the days when BDE was just about to be deprecated. Nowadays we only use it to connect to SQL Server using ODBC. It has worked surprisingly well in spite of its deprecated status, and chances are it will keep on working for another 15 years. However, no one knows if or when it stops working. And if it stops, Embarcadero wouldn't be able to do much about it. So, it's a timebomb, and we need to replace it. But with what?

The ADO-components in Delphi looks promising. There are table and query components that resembles the BDE-components, and they are not third-party components made by a one-man shop that might loose interest. We also look forward to use connection-strings instead of the clunky ODBC-Administrator.

However, about a year ago Microsoft announced that OLE DB is deprecated, and for native development we should use SQL Server Native Client ODBC driver.

So, my qestion is, is the ADO-components in Delphi hardwired to OLD DB? Or would we not be using OLE DB if we select "SQL Server Native Client" in the list of drivers?

I expect/fear that to make use of the SQL Server Native Client ODBC driver, we have to set up a datasource in ODBC-Adminstrator, like we do now. Or is it possible to connect with ODBC using connection-strings?

And what Delphi-components exists that can connect to ODBC without using OLE DB? Yes, I know about dbExpress, but it looks like it would take us years to convert to it from BDE.

Thanks, LandShark

3
sql-serverdelphiadonativebde
Just to add to the list our Open Source components from Delphi 5 up to XE3. You can access SQL Server via OleDB or via ODBC (which is the future, since OleDB - and therefore ADO - has been deprecated officially by Microsoft). There is a TQuery replacement, which is much faster than the BDE version, and per-method compatible. But project scope is more code-oriented, not component/RAD. – Arnaud Bouchez

3 Answers

3
votes

We had a similar migration need 5 years ago and did quite a bit of research and testing on it. The easiest migration path from the BDE is to SDAC from devart (http://www.devart.com/sdac/). Devart has a BDE replacement utility that goes through your code and replaces the BDE components with equivalent SDAC components. It will get you about 90% of the way there then you have to manually make some changes to get everything to work (e.g., if you use fetchall, have to comment out all the fetchall code - but you will see a pattern and can fix the remaining code mostly through search and replace). Performance of the SDAC components is excellent, they support all the sql server calls and you can use encrypted connections over the internet. The components support Native SQL or OLEDB connections to SQL Server. They also work in detached mode using cached updates. Another option, if you plan on supporting other database platforms in addition to SQL server is to use UniDac - it's just like SDAC but will work with SQL Server, Oracle and others - very similar to the old BDE without the overhead.

2
votes

1) "native connection" does not mean use of ODBC or OLE DB, nor BDE and DBX. Native connection mean using special library that only can connect to MS SQL and that does not uses any standard server-agnostic pipileines. Contrary BDE and DBX and ADO and ODBC are generic libraries providing connection to any server you'd install plugin for.

Generally native libraries provide you closer integration with server and use of some features that generic libraries may miss (like events in firebird/interbase servers). They can also work faster for they do not need to transcode data streams and commands to/from the pipeline public API standards.

Public interfaces OTOH aid you with easier switch of servers and easier development of server-agnostic application.

2) Why do you think that BDE -> DBX conversion would be any harder than BDE -> ODBC or BDE -> ADO or BDE-> whatever ? The conversion is the conversion. ADO also has its share of limitations and gotcha's like DBX and like any other library.

3) DBX have MS SQL plugin. DevArt offers their suit of DBX plugins and may provide better one than stock Delphi DBX MS SQL support. Or maybe not.

4) Aside of those there are few well-known server-agnostic libraries

5) you can have a lot of direct ODBC connection on any component collector site like http://www.torry.net/pages.php?id=570

6) same for native MS SQL connectivity http://www.torry.net/pages.php?id=1513

However, evaluating those choices and making your decision requires inside context knowledge that only you can have.

0
votes

We had the same problem a few years ago. I suppose if there would be a perfect solution then there wouldn't be that many alternatives.

Anyway, we switched from BDE + ODBC to ADO + SQLOLEDB. The main reasons was that it was very reliable, easy to convert, didn't require anything extra to be installed on customer computers (such as BDE) and was faster than others shipped with delphi, including DBX (when using DisableControls).

Also as a side remark, if you want to use ODBC to configure the connection, you can use the ODBC manager to configure the values but read them from the registry and connect directly without ODBC. That helped us to do the switch.