RE: ODBC FROM iSeries - huh?

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: John Taylor
>
> Yup. Practically speaking though, it's rarely an issue because
> ODBC drivers
> are client side middlewhere, and client OS' such as Win/32 and
> *nix are well
> represented by the DBMS providers. Those who wish to use OS/400
> as a client
> to an MS-Access database are SOL, but how big can that demand really be?

Well, one of the side arguments came when Walden (absolutely correctly)
called me on my blanket statement that the iSeries can communicate to other
databases via ODBC.  It seems that I was just plain wrong on that point,
especially if you're talking about native ODBC drivers on the iSeries.  My
bad, and Walden took me to task for it.


> Yes on the point that ODBC is not well supported (if at all) on
> OS/400. But
> you don't necessarily need an ODBC driver to access a different  database.
> ODBC is simply an API... an implementation of the ANSI/ISO SQL Call Level
> Interface. In theory (I haven't tried) one should be able to access any DB
> server that implements the SQL CLI using OS/400 as a client.

Ah, now this is interesting.  I am definitely a neophyte here.  While I
understand the basic concept of the CLI, I'm not very familiar with the
implementation.  Does the SQLRPG precompiler generate calls to the CLI, or
is there another layer underneath that?  In any event, it shouldn't be too
hard to find a reference for SQL CLI, find a freeware PC DBMS that supports
CLI, and see if the iSeries can access it.


> Therefore, you can't use CLI on OS/400 to talk to
> an MS-Access database, because there's nothing on the other end
> to hear you.

Okay, this made light bulbs go off.  I can understand this completely.  A
DBMS includes an SQL engine, which should by default respond to CLI
requests.  This in turn provides ODBC support, because ODBC is just a
wrapper around CLI.  But in the case of desktop databases, the entire SQL
implementation is housed inside the ODBC driver.  If there were an actual
SQL engine around the smaller databases, they too would in theory respond to
SQL CLI calls.

This begins to make more sense to me.

And I'll bet that the true commercial databases such as Oracle, Informix,
Sybase, et al, all have actual SQL engines and hence should communicate via
ANSI CLI.

Wow.  Wow.  This is very cool.

That raises a different question - is there a "generic" ODBC driver that
simply converts ODBC calls to ANSI SQL CLI?  If so, and if this driver were
ported to the iSeries, wouldn't it magically provide ODBC access to any DBMS
with a true SQL engine?

Just wondering.

Joe