Friday, June 22, 2018

ODBC and SQL

Data Driver Difficulties

Round about 1990, my brother's friend John had a company called Paradigm that was writing drivers for Microsoft.  Structured Query Language (SQL) was becoming very popular, with Oracle the clear leader.  There was a problem, however.  It seems that every time you came up with a new application that needed to access data, you had to write a new driver for every database you wanted it to access. If you had, say, 5 applications that needed access to data and 5 databases that you wanted them to access, you had to write 25 drivers.  If the numbers became 10 and 10, you needed 100 drivers. This simply would not scale!

SQL Access Group (SAG) Call Level Interface (CLI) - Alias ODBC

Microsoft was not alone in struggling with this.In 1989 the X-Open Open Access Group had formed a specialized standards body called the SQL Access Group, with the initial members being Oracle Corporation, Informix, Ingres, DEC, Tandem, Sun and HP.  Microsoft joined round about 1990.

This group came up with a call level interface (CLI) that they defined for SQL databases.  It was intended to allow programming languages to access a single driver written a database vendor.  With this approach, if you had the 5 apps and 5 DBs listed above, you would need 5 drivers, and your apps would all need to have an interface provided that would talk to the drivers. 5 drivers instead of 25. 10 drivers instead of 100.

The SQL Access Group finally came up with a name for their standard. They called it Open DataBase Connectivity, or ODBC.  In addition to the CLI, this interface expected you to support some level of SQL. There was Minimum, Core and Extended. We implemented Core.

John's company was contracted to write drivers for all of Microsoft's supported ISAM databases, including comma-separated text files, DBase, Excel, and others.  This meant that they had to write a full SQL engine for these, as comma-separated text files had no SQL processing capability. John was working with a gentleman named Jim, who took on the task and did an excellent job.

In an interestingly twist, when a company called Fincentric wanted to convert their application development environment from using ISAM to SQL Server, Jim also wrote the inverse, taking ISAM commands and converting them into efficient SQL statements.

ODBC for PICK

In 1991, before ODBC even had a name, a number of us met at the India Gate restaurant on Robson street downtown, and discussed the idea of writing a SQL engine and driver for PICK. (We actually recorded notes on a napkin!)  Because of my involvement with Synex and PK Harmony, I was the communication expert.  John was the C/C++ expert with Jim as a very helpful resource on writing SQL engines for non-SQL environments, and my brother and I were the PICK experts.

We decided that my brother Antoon would take on a project working with SQL Server for Vancouver General Hospital (VGH) in order to better understand SQL, so we could determine the feasibility of what we were contemplating.

Laboratory Information Management System (LIMS)

The project was code-named LIMS. The funny thing was that Paradigm was assigned a phone number by VGH that had formerly been for some other department there.  Occasionally, the programmers would get a call from someone who didn't realize the number had been reassigned. They'd answer it with LIMS and there'd be a long pause at the other end, while the caller tried to figure out what department at VGH was called "Limbs"!

As a result of his work on this project, Antoon came up with an initial architecture and design for an ANSI 92 compliant SQL engine prototype, which he began coding in PICK BASIC.

It didn't take too long to get to where "SELECT * FROM SALES" returned a meaningful result set. That was the easy part.

ODBC vs. IDAPI - Oddball vs. Diaper

Borland created their own interface called the Integrated Database Application Programming Interface (IDAPI), which never took off beyond their own databases.  If you ran Microsoft Word's spell checker on IDAPI, you got "diaper". If you ran it against ODBC you got "oddball".

Microsoft ODBC Driver Developer Kit

We were one of the first to get a copy of Microsoft's ODBC Driver Developer Kit. In order to boost productivity, John took their API and re-wrote the header files to support C++, but expose the interfaces as C. I began writing the driver, and developed a transfer protocol that would work with Serial I/O, which was all that most PICK systems would support at that time. I used my experience with Synex, but based on a variation of the Motorola IXO transfer protocol that would work with serial I/O limitation of most PICK systems.

Microsoft had 3 levels of conformance to their API. Core, Level 1 or Level 2. We implemented Level 1 conformance.

Microsoft Releases ODBC 1.0 in 1992

Finally, in 1992, Microsoft released ODBC 1.0. Sort of... They implemented their driver manager so that it would only support their drivers for the first version.  We had been ready to release with them, but found out only weeks before they released that they had decided to implement this limitation.  I scrambled to write an Excel add-in that would allow us to use our driver to load Excel spreadsheets, so our beta customers would be able to use it.

We still managed to get a number of customers up and running with our beta version. And so our ODBC journey was launched.

We Failed the Bozo Test for ODBC

One last funny story for this post: It was an industry inside joke that if you could spell the letter of ODBC in the right order you probably knew what it was.  The unknowing would always refer to it as OBDC.

We put together a marketing brochure, and went through probably about 50 edits.  Somehow, on the very last edit, someone reversed the D and the B and we were so bug-eyed we missed it.  Our brochure went out with OBDC and we couldn't correct anyone who phoned in!

We failed our own Bozo Test!

No comments:

Post a Comment