ActiveX Data Objects

ActiveX Data Objects (or ADO) are a set of COM objects created by Microsoft for accessing different flavors of databases with one fairly simple set of methods. It's often mentioned as a part of ASP (Active Server Pages), but it's really it's own thing, useable from any (Microsoft) programming environment. It's just lumped with ASP because it's what makes ASP easy and popular. The other programming languages still use OLE DB more often than not anyway.

Even though the word(?) "ActiveX" is in the name, it's not quite that evil. It's a really easy and efficient way to set up a database for the web (server-side, compatible with any browser, unlike ActiveX Controls). Here's a quick overview of the objects from an ASP programmer's perspective:

• ADODB.Connection

  The Connection object is how the ASP pages connect to a database. Depending on what ODBC supports, there are quite a lot of data formats accessible: SQL Server, Microsoft Access, Excel spreadsheets and CSV files, Oracle, Sybase, FoxPro, and so on (most of those drivers come with MDAC, the ODBC distribution, hence the Microsoftiness - non-MS drivers are available, though).

  Here's how to use the Conn object (assuming ODBC is set up, using VBScript):
  

  Dim oConn	 ' Option Explicit!
  Set oConn = Server.CreateObject("ADODB.Connection")
  oConn.Open "DSN=pubs;UID=sa;PWD=;DRIVER={SQL Server}"	' Data Source Name may vary.
  REM ...this is where data would be queried, see below
  oConn.Close
  Set oConn = Nothing
  

• ADODB.Recordset

  The Recordset object is how to get results from the database. There are several ways to use the object, but the easiest is to just run a SQL query through it. Through ADO and ODBC, SQL (rather, Transact-SQL (T-SQL)) can be used on any available data source. This object can also save out any results to an XML file, but it's MSXML and always requires editing later.

  A recordset example:
  

  Dim oRS
  Dim strSql
  ' create Conn object as shown above
  strSql = "SELECT * FROM users"	' assuming there's a table called "users"...
  Set oRS = oConn.Execute(strSql)
  ' now we have an object (oRS) holding a collection of query results, which can
  ' be accessed in more ways than explicable.  easy way:
  While Not oRS.EOF
  	Response.Write oRS("passwd") & "<br />" & vbCrLf
  	oRS.MoveNext
  Wend
  Set oRS = Nothing
  ' close and free Conn object as shown above
  

• ADODB.Command

  The Command object is more or less useless using the above methods, unless you need to access stored procedures in an MSSQL DB. It's a little more than a pain in the ass, so I won't demonstrate. It also gives different ways of specifying the SQL query used in a Recordset, but at the cost of an extra object (and when you're developing for Windows NT, memory is always an issue).

There are several newer objects in the ADO package that do some really nice things (and some not-so-nice):

• ADOMD can create multi-dimensional recordsets for more complex structures and to access and alter the schema of a database.
• the Datashape (not actually ADO, but usually mentioned with it) is for data shaping, creating a potentially massive (like E2-size) recordset with parent-child relationships intact through one query. Good for exporting an entire database into proper XML (I.E., not Microsoft XML).
• More new objects are expected to come out with "ADO+" (yes, stupid names foreshadow the death of good technologies), part of their .NET marketing scheme.

Additionally, I've never had any problem with ADO - no mysterious errors or bugs, no sudden crashes from using it, or anything. I've had problems with ODBC and MS Transaction Server (and of course, NT) while using ADO, but ADO is one of the few Microsoft products that shines. It's easier than English and twice as effective. A reboot or two every month, and things work fine and fast.

It should be noted that most of my ADO usage has been on a dual processor Compaq Reliant with extra racing stripes on the drive array and DS-3 tattoos, so your mileage may vary when comparing to similar technologies. But practical before proud - my first ASP/ADO project was an eBay-style auction site (the client didn't care much for copyright, heh), and it only took me a week of 8 hour days to get from design to functional. I had a full month to test and tweak it and watch the graphic designers scramble, instead of the usual day-past-deadline "power button's green, it must work!" test. Yay, ADO.

ADO is a three-letter abbreviation for "ActiveX Data Objects". It is a Microsoft product. I agree that the repulsive pseudo-word "ActiveX" has earned its creator a well-deserved eternity in the lake of fire, but let's move on, shall we?

Thank you.

ADO supersedes the cleverly-named DAO ("Data Access Objects"). What they both are is a COM-wise (COM == "Component Object Model") interface¹ for ODBC ("Open Database Connectivity"), yet another Microsoft thing. I think they sometimes call ODBC a "standard", but it's hard to hear what they're saying over all the giggling. What ODBC is, is a generalized interface for RDBMSs². All of these little groups of capital letters are supposed to be part of something called "MDAC", or "Microsoft Data Access Components", which also includes "OLE DB"³ and "Remove Data Service".

Now that I've made everybody glad they don't have to deal with this stuff (or reminded them of how sorry they are that they do), what the hell is it, really?

The deal is this: Oracle, MS SQL Server, BDE⁴, and most other DBMSs implemented for Windows all provide ODBC "drivers". You can think of a "driver" as a sort of "adapter": Do you have one of those little cylindrical plugs that came with a pair of headphones, the ones with a 1/8" jack on one end and a 1/4" plug on the other? That's an adapter. Radio Shack makes a few dozen different variations on that theme, which accept all different kinds of plugs in the back -- but they all plug into that same headphone jack on your stereo, so you can plug anything into it that pleases you. ODBC works on a similar principle: Each vendor provides something with a "back" that talks in a private and peculiar way to his own database system, and a "front" which talks to ODBC in a way which is consistent. Therefore, a program can talk to Oracle and BDE in exactly the same way, even though they're really quite different inside. Of course, this turns out to be something of a "least common denominator" situation: It doesn't support 100% of what every database can actually do. It just supports all of the "normal" features that they all have in common.

That's not a bad thing at all, when you think about it. The problem is that ODBC is what we call a "low level" C API⁵: There's a lot of detail. You've got to write a lot of code and keep track of a lot of details in order to make anything happen. ADO hides all the mind-numbing fiddly stuff and lets you order ODBC around in relatively simple, broad terms. It's got a bunch of different "objects" representing a connection to a database, a "recordset", fields belonging to records, and so on and so forth. You can give it SQL queries and generally do what you need to with the database. It's handy, and it's far, far simpler to use than a C API for a database engine. Not least, you don't have to write in C when you're just doing throwaway code: You can write it in a very high level language that's well-suited to writing quickies.

I don't think you'd use it if you were doing anything really serious, though. There's probably a lot of overhead, and with these things you always lose fine control.

ADO is much used for doing web pages with ASP⁶. As with PHP and JSP, you can throw something together relatively quickly which will spit out a web page containing information from a database. You can also do the same with a script running outside a web server in the so-called Windows Scripting Host, so you can do a query and generate a report as text to stdout like God intended. Of course you don't have printf, but it's relatively trivial to whack up a printf for an interpreted language. I've got one around here somewhere that I wrote. One of these days I'll quick stuff it in a COM object when nobody's looking. And then you know what I'll have?

Far too much time on my hands.

¹ "Interface" in the general sense, not the COMmunistic IUnknown sense.

² "Relational database management systems" or something like that. BTW, AFAIK there's a reasonably lucid writeup in DBMS, IIRC.

³ Object Linking and Embedding Databases. OLE is what ActiveX used to be called. I think ActiveX is called COM now, or DCOM, or COM+, or some damn arbitrary thing. Nobody really cares. The names are all just marketing gibberish. They'll change again by this time next year.

⁴ Borland Database Engine -- you didn't think I was running out of these stupid things, did you? Fat chance! And I'm not even a database programmer!

⁵ Application Programming Interface. Very loosely speaking, an API is the "outside" of a code library. It's what a computer program talks to when it wants the library code to help it out by doing something boring, unpleasant, or pointless. With any luck, it'll be all three.

⁶ Active Server Pages, yet another miserable Microsoft product.