Don,
There are two issues: ODBC capabilities and limitations and the underlying database. Regarding ODBC and the requirement to tunnel through the ODBC portability layer to get to the db.... That's an easy one. The most basic of ODBC calls is SQLExecDirect, which takes a (database specific if you wish) string you've consed up and executes it on the underlying database.
Actually it turns out that if in an AOLserver database driver, you've defined a DbFn_Exec callback, then the AOLserver db internals preferentially use that callback for everything, including selects. So there may be little difference from an ns_db select via an ODBC driver as via the postgres driver. Using ODBC sends the query to the db as a string via SQLExecDirect, and using the postgres driver sends it to the db as a string via PQexec.
In other words tunneling through ODBC is easy. So the question becomes, is what you want doable in the underlying db?
- The ability to create and drop tables, views, indexes (including special index types supported by a particular RDBMS), and functions in the native programmatic language.
- Table locking
- rownum or limit/offset
- hierarchical queries (for those RDBMS's that support them)
All of these are doable, to some extent in SQL Server 7. Some of it is more pessimal than others. Regarding table locking, sigh, that one is unfortunately easy, since in SQL Server 7, everyone appears to wait for everyone. That's not quite true, and in fact, Microsoft says that you can give "table hints" to various statements like select, update, and insert. The hardest requirement on your list is probably the hierarchical queries. SQL Server 7 does not offer a "connect by" statement, but when pressed, the SQL Server 7 docs suggest using recursive Transact-SQL (their version of pl/sql) and various temporary tables to substitute for set based queries (and they give some examples on how).
I'm unconvinced that going through an ODBC layer doesn't have fairly steep performance implications. It might be an interesting way to prototype support for other RDBMS's, however.
Me too. I was involved with a fairly large project that supported Oracle 7 and SQL Server 6.5 natively as well as through ODBC. We believed we saw about a ten percent drop in performance. Really for us, what hurt about ODBC was that bugs in a particular database driver would basically leave us to creating case statements: if this db, use this ODBC call, if that db, use that one instead. And what we wanted to do of course is just write one ODBC layer to handle any database that wasn't Oracle.
The counterpoint is merely that other folks (vendors mostly) have made claims that there really is no reason that ODBC shouldn't perform almost as well as a native driver. Evidently, the native driver interface to IBM DB/2 is essentially an ODBC interface. IBM suggests that the ODBC interface may be slower than a precompiled/prepared static embedded SQL interface, but the ACS has never used a static, embedded, prepared SQL interface. ftp://ftp.software.ibm.com/ps/products/db2/info/vr6/htm/db2l0/db2l008.htm#HDRCLIINT
All of this comes down to: so why use ODBC? As you say, it might be nice for prototyping ACS against certain databases, but it also might be used on very real projects. DB/2? ODBC. Want to support an ACS app that can talk to many different databases at the same time? SAP, Sybase, Interbase, Adabase, DB/2, Microsoft Access, Solid, Empress, Cloudscape. ODBC.
