Advantage Database: the good #.NET #Delphi

When our app was originally written years ago, it used Paradox to store its data. More recently, we've been porting our code to run on the Advantage database from Extended Systems — a genuine client/server database.

We spent a decent bit of time evaluating different database servers (including Microsoft SQL Server). Advantage came out as our top choice, and I thought it'd be worthwhile to share some of the biggest reasons why.

Note that I'm not getting a commission if any of my readers buys Advantage, or anything like that. I'm just starting with the good stuff. In later posts, I'll go into some of the problems we've run into with Advantage, but I want to set the context first — Advantage is a decent database system with some pretty good features.

BDE replacement, sort of. For Delphi shops (like us), Advantage provides TDataSet descendants that, much of the time, are pretty similar to their BDE counterparts. You can drop them in, almost, and expect them to work, if you're lucky. (More on this in a later post. But they did try.)

You can also use Advantage from Java (JDBC) and .NET (via the usual DataReader interfaces). I don't remember whether they support ODBC and/or OLE DB, or not; that's not a detail we really cared about.

ISAM access. If you're porting old code that does record-by-record processing, and don't want to rewrite it right away, Advantage will still accommodate that code quite nicely; it has all the usual operations like First, Next, Prior, FindKey, etc. They claim it's designed to get decent performance from these ISAM operations. Obviously, that's not the best way to write client/server code — way too many round trips — but it should work fine for the smaller stuff, and tide you over until you can get around to rewriting the bigger stuff with SQL or stored procs.

Good SQL support. It's not the fanciest out there, but it does support some nice features like conditional functions and nested queries. I've used better (MSSQL) and I've used worse (Paradox); Advantage, IMHO, is much closer to the "better" end of the spectrum (aside from its occasional... endearing quirks). They get good performance from their SQL engine, and you can view query plans and all that kind of stuff. Unlike some of the other databases we looked at, Advantage is not just an ISAM database with SQL tacked on as an afterthought; they've got a good engine in there.

Extended stored procedures. This is probably the coolest feature, from our point of view. You can write code that runs in-process on the server, and you can write it in whatever language you like — Delphi, C++, C#, whatever. As long as you can write either a DLL or a COM object, Advantage can run your stored proc on the server. Have ISAM code that runs slowly on the client, because of all that network overhead? No problem; run it on the server instead.

Free local edition. There's ADS (Advantage Database Server), and there's ALS (which they claim means "Advantage Local Server", but that's a dirty lie — ALS isn't a server, it's the absence of a server). ADS is client/server; ALS is not. When you're using ALS, your app opens the table files directly (and therefore isn't the best idea for lots of concurrent users, for the same reason Paradox isn't).

It's almost no effort to write one app that can run against either ALS or ADS, decide between them at runtime, or even use both at once. ALS is free (ADS, on the other hand, has per-seat licenses you have to buy — but their per-seat costs are way cheaper than MSSQL licenses), and it supports every feature of ADS except one. ALS even supports stored procedures, which get called just as if they were running on the server (it's just that they get called on the client instead). The only feature ALS doesn't support is transactions — the BeginTransaction, Commit, and Rollback calls will silently do nothing if you're running on ALS.