Real-world tools #2: Database connectivity

OK, so yesterday I was on pretty sure footing with talking about ReportBuilder. Some of your comments and emails reflected that this is a popular third-party toolset and ranks up there with other report writing tools in Delphi. At least, undoubtely, most of you have at least heard of it. Today, I'm going to discuss a component set at the other end of the scale, although I may be proved wrong.

One of Delphi 1's initial selling point was that it was for the design of database applications, and was of course shipped with the BDE to give us connectivity to most of the big databases around at that time: Access, SQL Server, Oracle and Borland's own Interbase and Paradox. Eventually, like most of you probably, I looked for an alternative. Something that didn't require me to install the BDE for a start. I don't remember when or why this need occurred (can you?) but eventually I sought out a third-party solution. And the choice was plentiful.

At that time, our software was based around Microsoft Access (snigger) , and so although the BDE could cut it, we just didn't want our software to be the reason that the BDE was installed on the end user machine. In fact, the prerequisite for any alternative was that there should be no installation of any other proprietary files, period.

This led us to Titan Access by Reggatta Systems. This was ideal for us at that time, as it used the Jet engine (DAO) itself and only required a few files to be installed which, if they had Office, were on the PC anyway.

This was a great alternative to the BDE, and the components were so close to the BDE ones that it took no time to change the product to use it. This was fine until the company and the software grew out of Access and towards MS SQL Server. We knew the size of the companies we were trying to get into would have had SQL Server installed for their other Enterprise-level applications (at least this was the feedback we were getting) so it was natural for us to go down this route.

And although this is about components, not databases, I'm glad we did.

At this point, we had to seek another alternative. Reggatta also provide the same component set for Sybase SQL Anywhere but we spent some time evaluating this database and didn't get on with it for reasons I can no longer remember (this was mid-2001). So we needed a solution that enabled us to use both Access and SQL Server: ODBC.

At that time, the ODBC had a terrible reputation for being slow and clunky, but we seemed to catch it just as it was on the rise and approval for it was improving. Thank God we were right, it still remains a viable alternative and Microsoft continue to update their drivers for it every time they issue a major release of IE or Windows. There was talk some years ago about them starting to drop support for it in favour of the upcoming newboy, ADO. That never happened and the two now co-exist perfectly happily.

There were plenty of ODBC Delphi component sets on the market and again I cannot remember what it was that led me down the route to ODBCExpress. I think there was an article in Delphi Informant about it that probably swayed me, not least because then at least I had a tutorial in getting it up and running. Also, at that time, dbExpress had been launched but the SQL Server driver for it was "unavailable" and the way to use Access with it was simply to use an ODBC driver.

Since then, this component set has actually been one of the most reliable we have ever installed. I think we've only ever needed to upgrade to a new version once, and that was only because we were moving from Delphi 5 to Delphi 6.

Any weaknesses it has actually aren't in the components: they're in the drivers themselves. This is where you potentially have an issue. But the components are excellent, they do the job well and provide many of the options that ODBC supports. For those options that are database specific, it has the ability to tap into those as well so, for example, you can query the SQL Server ODBC driver about the database you are connected to, even though these calls are exclusive to that driver.

The strength of any ODBC component set is in the database drivers you are using. This is partly why we chose SQL Server 6 years ago, because we knew Microsoft would produce high quality drivers for it whereas we couldn't be sure of the quality of other database vendors.

The other beauty is that it has enabled us to toy with adding support for other databases (with a decent ODBC driver) to our software. This sounds good in theory but actually when you consider that none of them really stick to the same ANSI-92 SQL standards, you can end up with different SQL statements in your code depending on which database you're connecting to. Tim Anderson's blog had an article this week about SQLite and how they have had to deal with the same issue, so you wouldn't be alone in this!

The newsgroup support is adequate. It doesn't set the world on fire but Pieter Myburgh seems to single-handedly keep it alive and answer everyones' questions. But I do wonder how it would survive without him and you can tell when he is on holiday because the newsgroups don't get answered. Also, ODBCExpress used to be listed on Korbitec's website but it isn't any longer, even though Pieter's email address is still at Korbitec.

That said, the last release supported Delphi 2006 and it has a C++Builder version as well. There doesn't appear to be any movement for Delphi 2007 but given that it is a non-breaking release and the newsgroups suggest the 2006 version works fine under 2007, there's no need for them to bust out a new version.

This is a minor player in the toolset market and the database connectivity market doesn't seem to be as big as it used to be. Maybe because Borland released the dbExpress components and supported ADO out of the box with Delphi 6. And even more so when CodeGear announced DBX4 with Delphi 2007 for Win32.

I've thought about moving to one of those bundled Delphi offerings in order to save myself the worry should ODBCExpress go tilt. But I haven't for two reasons: firstly, I've used the same version of ODBCExpress for nearly 5 years without a problem - and that's without the source code (I know, what was I thinking?!); and secondly, because the database architectures offered with Delphi keep changing and I'm not sure I want to run the risk of changing everything over only to have it "deprecated" like the BDE.

What do you use? Why do you use it, why did you choose it and are you glad you did?

I'm going to be listening in one of the David I's CodeGear Delphi and C++Builder Roadmap Community Chat & Demos in a bit.