RE: ODBC FROM iSeries - huh?

>From: Joe Pluta [mailto:joepluta@PlutaBrothers.com]
>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?

Actually I'm afraid it doesn't work that way. For the rest of this I'm
ignoring single-level drivers like Access, I'm only referring to "real"
databases like SQLServer or Oracle or Informix or ...

It would seem that the smart thing for database vendors to do would have
been for them to write server-side translators from the CLI interfaces to
their individual databases. Then all someone would need was a client-side
driver that translated from ODBC to CLI. However, as it happens each vendor
write a client-side translator from ODBC to their proprietary network
interface.

That is to say SQL Server doesn't listen for CLI calls, it listens for
SQL-Server calls. It's the ODBC drivers job to translate from ODBC to
SQL-Server.

Also, as someone pointed out ODBC refers to Microsoft's implementation of
X/Open.

-Walden

------------
Walden H Leverich III
President
Tech Software
(516) 627-3800 x11
(208) 692-3308 eFax
WaldenL@TechSoftInc.com
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)



-----Original Message-----
From: Joe Pluta [mailto:joepluta@PlutaBrothers.com]
Sent: Wednesday, April 10, 2002 00:19
To: midrange-l@midrange.com
Subject: RE: ODBC FROM iSeries - huh?


> 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

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.