Re: SQL Standard

Nicholas,
Unfortunately, not all vendors' stored procedures are as sophisticated as Oracle's. We use Informix in our current system. I like Informix verymuch, but I will avoid using stored procedure whenever I could. We do all processing in C++ with some perl for reporting, ftp automation, etc. The database is used for inventory and metadata, and also for process control. Stroe procedures / triggers usages are minimal, mostly for process control.

Nicholas Carey wrote:

> 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 full-
> blown, 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.
> --