FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

On Thu, 06 Feb 2003 13:46:35 -0800, Peter Harvey
<pharvey AT codebydesign.com> wrote:
> > Hmm.  Maybe I should write something up for the www site, under
> > "Entertainment" or something, called "ODBC Considered Harmful".  It
> > would save us both time, eh?  :-)
> 
> Its good (or even great) at:
> 
> - providing a portable API (cross-database and cross-platform)
> - leveraging an existing standard (arguably the most commomly used 
> standard for accessing relational data)
> - optionaly providing version mapping and driver plugabiliy via a driver
> 
> manager

ODBC wins "best in show" for those things, not least because it's the only
contestant.  

I'm going to answer to a little bit, Peter, but I want you to know I
support the FreeTDS ODBC effort, for the above reason.  In many ways, it's
the only option.  

> The main problem with ODBC for most people comes from poor drivers not 
> from the ODBC standard itself.

These aren't separable.  ODBC is way complicated and has mutated not a few
times.  I did a little back-of-the-envelope work:  

$ wc -l src/dblib/*.c src/odbc/*.c |perl -pe'($lines) = split; if
(/dblib/) { $dtotal += $lines } else { $ototal += $lines if /odbc/; } END
{ print "dblib lines:\t$dtotal\nodbc lines: \t$ototal\n"; }'
    2811 src/dblib/bcp.c
    4203 src/dblib/dblib.c
      37 src/dblib/dbopen.c
     159 src/dblib/dbutil.c
     310 src/dblib/rpc.c
      98 src/dblib/xact.c
     333 src/odbc/connectparams.c
     263 src/odbc/convert_sql2string.c
     247 src/odbc/convert_tds2sql.c
     357 src/odbc/error.c
     242 src/odbc/native.c
    3227 src/odbc/odbc.c
     289 src/odbc/odbc_util.c
     415 src/odbc/prepare_query.c
     118 src/odbc/sql2tds.c
   13109 total
dblib lines:    7618
odbc lines:     5491

Our db-lib library can do anything our ODBC library can do, and it's much
smaller if you account for bcp.c (which our ODBC can't yet do).  But maybe
lines of code isn't such a good metric.  How about the object code?

$ ls -ks src/dblib/*.o src/odbc/*.o |perl -pe'($lines) = split; if
(/dblib/) { $dtotal += $lines } else { $ototal += $lines if /odbc/; } END
{ print "dblib Kbytes:\t$dtotal\nodbc Kbytes: \t$ototal\n"; }'
 69 src/dblib/bcp.o
120 src/dblib/dblib.o
 19 src/dblib/dbutil.o
 22 src/dblib/rpc.o
 20 src/dblib/xact.o
 24 src/odbc/connectparams.o
 24 src/odbc/convert_sql2string.o
 24 src/odbc/convert_tds2sql.o
 25 src/odbc/error.o
 20 src/odbc/native.o
 80 src/odbc/odbc.o
 25 src/odbc/odbc_util.o
 25 src/odbc/prepare_query.o
 22 src/odbc/sql2tds.o
dblib Kbytes:   250
odbc Kbytes:    269

More function in less space.  

I assert the ODBC standard is chock-full of choices that make it easy to
define and hard to implement, precisely the kind of choice one would make
if defining a standard for someone else to write.  Those choices lead
directly to drivers that are big and unreliable.  

Let just pick on one function, SQLGetInfo.  Here are its parameters:

1.  SQLHDBC hdbc
2.  SQLUSMALLINT fInfoType 
3.  SQLPOINTER rgbInfoValue
4.  SQLSMALLINT cbInfoValueMax
5.  SQLSMALLINT FAR * pcbInfoValue

How is anyone supposed to use this thing?  You pass in an enumerated type,
a pointer to the result, and a pointer to the size of the result.  It's a
very stable interface, from a designer's point of view, akin to a table:

create table info (
          type int
        , size int
        , data varbinary(10e6)
)

Like that table, SQLGetInfo holds everything nothing: all the
interpretation is moved to the code.  

Worse, the *semantics* of the function are intentionally slippery. 
Microsoft now defines over 150 inputs; the code has to infer the type of
the (essentially void*) pointer from the value of fInfoType.  But the
names of those inputs have changed, as have some of the corresponding
outputs.  But, oh, that's OK, because the environment tells you what
version of ODBC we're using....

This is good design?  Passing a void* back and forth between application
and driver, between vendors?  Casting by inference at runtime?  So we can
get *one* answer back?  Not in my book.  

To me, that specification is a designed-in disaster.  It would be a
miracle if two vendors pass 150 void pointers and shake hands on every
single one.  What happens instead  is, the driver hands back something the
application didn't (rightly or wrongly) expect, the application relies on
the pointer and ... segfaults.  Or something else.  The application
programmer blames "a bad driver".  I blame the design.  

The real world is full of widely used bad designs: RS-232, NTSC color
television ("never twice the same color"), and the X Window system spring
to mind.  They're hard to replace and they pay the bills.  But they're no
better for it, and neither are we.  

Thanks for listening.

--jkl