Re: SQL Standard

On 07 Nov 2001, Chariya Peterson <Chariya.Peterson_at_noaa.gov> spake and said:

> I am looking for information on SQL standard. Would
> appreciate it if anyone could post url's and or faqs for me?
> I am gathering information on how much of the standard (what
> ever exists, which ever version) is supported by each RDBMS
> (at least by Oracle, Informix, DB2, Sybase, My-SQL), and
> information on each RDBMS extension beyond the standard.
> I'd like to know how realistic it is to develop software
> whose database queries are RDBMS independent.

You can buy the Standard from an ANSI/ISO affiliate near you. But then you'll have to turn into a language lawyer to make sense of it. However, this is a good book:

  A Guide to the Sql Standard:
    A User's Guide to the Standard
    Database Language Sql
  Chris J. Date, Hugh Darwen (Contributor)   Addison-Wesley, 1997.
  ISBN: 0201964260 AFAIK, most DB vendors claim compliance with SQL-92 at the most minimal level: 'entry-level'. It has been quite a while since I looked at that, though. And most DB venders do extend SQL quite a bit, especially with regard to stored procedures, triggers, etc.

Oracle may be (this should yank some chains!) the biggest offender in this regard, since they've turned SQL into a fullblown,  ADA-derived programming language.

Micro$oft, on the other hand has committed to bringing SQL Server to full ANSI-compliance. Which isn't to say that they'll get rid of their extensions. :)

So you can write code that complies with 'entry-level' SQL-92 and expect to have it -- more-or-less -- run. It will almost certainly be easier to port to the target RDBMS. But I think that what will bite you more is the non-standard way in which different RDBMSs do stuff internally. Collating Sequence varies. Some RDBMSs are case-sensitive WRT character string comparisons; others aren't. M$ SQL Server 2000 allows for multiple collating sequences and comparison rules -- just to make things difficult.

A more profitable method, IMHO, for building portable databases is to enforce a couple of simple rules:

• Thou Shalt Not Access the Database Except Via Stored Procedures.
• Thou Shalt Isolate Thy Database Access Layer In A Wrapper And Place It In A Library, Where All Who Will May Fearlessly Use It Without Awareness Of What Goes On Inside the Wrapper.

This isolates the problematic stuff. You can exploit the target DBMSs features. When it's time to move to a different database, the changes are all isolated from the people using the database.

Done right, you can do some pretty major remodelling of the database and have the applications that talk to it remain pretty much in the dark about just what you've done -- as long as you leave the API alone.

This does imply that the API exposed to the client represent logical actions within the domain of the application rather than simple database CRUDs.

Moreover, wrapping the actual queries as stored procedures makes the inevitable upgrades easier. In M$ SQL Server, for instance, the 'upgrade wizard' that converts from SQL Server v7.0 to 2000 scans your whole schema (including stored procedure and trigger source) and identifies problem areas that require attention.

And it simplifies security -- each application only needs GRANT EXECUTE for each stored procedure they need to access.

--