Forum OpenACS Development: ODBC and ACS 4? (SAP or SQL Server 7)

Collapse
Posted by Jerry Asher on
I have taken Rob Mayoff's DB2 driver, and made it ODBC 3.0 compliant. Why is this relevant to OpenACS?
  • SAP, and SQL Server can be supported with an ODBC driver.
  • Outer Joins can be supported via ODBC.
  • ns_ora like bind variables are already present (using a similar but different syntax)
  • ns_ora like hooks for LOB support are already present (but currently the LOB <--> file hooks are disabled)
  • So my question is, what else would a driver or db need to provide to enable OpenACS 4.0 support?

    If anyone is interested, you can download the driver at: http://downloads.hollyjerry.org:16080/files/aolserver3.2/odbc2.tar It's experimental at the moment. It should have the same performance characteristics as Rob's DB2 driver and the nsodbc module. So enjoy, and please let me know how it works.

Collapse
Posted by Tom Jackson on

Wow, that is very nice of you! Thanks. Now I have to try out SAP.

Collapse
Posted by Jerry Asher on
Well you're welcome, but before you load up SAP, you will need a *nix ODBC client and driver manager.

I built and tested this software using Easysoft's ODBC bridge which is proprietary.  There are a variety of libre options, including unixODBC, which is used in the Easysoft package.  But I have no good information on how well the free products work.  The websites are abysmal and haven't been updated in eons, and I didn't have the time to wade through it.

You can get a gratis one connection license from easysoft with which to play and test, and you can get a 30 day eval of their unlimited connection license.

Sigh,

Collapse
Posted by Don Baccus on
So my question is, what else would a driver or db need to provide to enable OpenACS 4.0 support?
Let's see...
  • 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)
  • These come to mind immediately. The ACS traditionally leans heavily on db-specific features, in essence you need to be able to tunnel through the portable ODBC layer to be able to fully support [Open]ACS 4.

    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.

    Rewriting query files for a particular RDBMS isn't going to be a huge problem now that we've got the infrastructure mostly in place to do it. For a "real" product supporting a particular RDBMS I think this will be the preferred method.

Collapse
Posted by Jerry Asher on
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.

Collapse
Posted by Don Baccus on
What does ODBC gain me, again?

I still need to build db-specific queries.  The fact that I  can tunnel through ODBC to deliver these queries doesn't speed up the porting process.  The porting process involves building those queries,  not worrying about how I deliver them to the underlying RDMBS.

How does ODBC help me with generic queries that don't need to be ported?  Hmmm...not at all, since they're generic and don't need to be  ported there's already no work involved.  Hard to improve on "zero effort required".

There are only two meaningful issues:

1. how hard is a native driver to write?
2. how much performance do you lose?

As far as #1 goes, the answer typically is "not hard".  Avoiding it might be nice, particularly for prototyping a port.  As you point out some db's already use essentially ODBC.  I believe the external sybase  driver is such an example.

External drivers do carry a significant performance hit in the OpenNSD world above and beyond the minor hit imposed by the extra ODBC level.  It used to be an order-of-magnitude difference (see old threads of mine at web/db).  Jim or Kriston uncovered an embarrassment that enabled them to lower it to a factor of two.  That's still a heavy
hit.

Unless you have a threaded ODBC client you'd have to use an external driver.

Now, some db's don't support threaded clients anyway so you'd be stuck  using an external driver regardless.  In this case ODBC might be just  the ticket.

I don't see what this has to do with porting OpenACS 4.x to a different database, though, unless you consider writing a driver to be  a significant portion of the effort.  I don't, and a driver would be useful to all OpenNSD users, not just the OpenACS 4.x project.

Regardless of driver, generic SQL92 queries will work with no porting effort, while non-generic queries will need to be rewritten into a form acceptable to the underlying RDBMS.  The delivery system's not relevant to OpenACS.

Collapse
Posted by Henry Minsky on
The point that stands out to me about the usefulness of ODBC is not
as a means to build an  ACS that can use any lowest-common-denominator
DB, but the usefulness
of being able to open connections to  other databases simultaneously.

Although I haven't had to do this lately (i.e., in the last two years that
I was working for aD), it seems like an extremely useful thing to be able
to call out to "auxiliary" databases via ODBC. In the case that you had
some specific data, like credit card transactions, living in some external
system DB like Microsoft SQL Server. I haven't been following the ODBC
support for AOLserver
very carefully, so this is actually the first I heard of the DB2 or nsodbc
module.

When I worked on MetaHTML (www.metahtml.com) (an early ACS-like
application scripting environment using our own homegrown HTML
syntax scripting language, sort of like ColdFusion), I wrote  the ODBC
glue  and generic SQL API  for it.  We used the ODBC drivers
from OpenLink  (www.openlinksw.com) which at the time were
the only drivers available for Unix.  They seemed like a very
reasonable company, and had good technical support.

Collapse
Posted by Don Baccus on
Yes, Henry's described one very good reason why an ODBC driver can be very useful. I've not meant to imply that an ODBC driver's not useful, only that I don't see it would help with porting OpenACS to a new database (assuming one doesn't mind writing a native mode driver).
Collapse
Posted by Roger Williams on
Where can I download a copy of the referenced DB2 driver?

TIA.

Regards..

Collapse
Posted by Jerry Asher on
Rob's DB2 driver is at http://dqd.com/~mayoff/aolserver/.  It's also pretty much the same as the ODBC driver I created above which lives at http://downloads.hollyjerry.org:16080/files/aolserver3.2/odbc2.tar.  You may wish to diff the two, because if I recall correctly I found a fairly significant bug in Rob's when returning biggish values, and the change log at ~mayoff/aolserver doesn't indicate that that has been updated yet.
Collapse
Posted by Andrew Piskorski on
FYI, anyone interested in AOLserver ODBC drivers might like to check out this thread over on web/db. Or at least, I would like you to check out that thread, because maybe you'd end up helping me out. ;-)

Incidentally, I became interested in AOLserver ODBC drivers for exacly the reason Henry brought up - in order to query "auxiliary" databases via ODBC.

(And yes, Don, I know that ACS Tcl / AOLserver questions are probably more productively asked here on openacs.org than on aD.com, but I hadn't learned that yet when I started that thread. :-)