SQL compliance
utish rajkarnikar
different databases like MS SQL, Oracle, DB2. Since most of the
databases support Entry level SQL 92, where can I find the repository
of all the statements and functions conforming to SQL 92 entry level?
I have to avoid all vendor specific statements and functions in my
code.
Thanx in advance.
Utishr
Jim Kennedy
a particular vendor's features and make exceptions where a vendor differs.
Remember people paid a lot of money for their RDBMS and for your application
to come along and make their investment worthless than what they paid is
just silly. You wouldn't do the same thing if it was a C compiler now would
you. (only write C that would work with all compilers just in case you
wanted to use another one, people would think you daft.) Also there are
issues beyond sql statements that are different in each db (eg treatement of
locks) that have major implications on your application. (unless you go to
<I'm going to vomit> do an auto commit strategy.)
Jim
--
Replace part of the email address: kennedy-down_...@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"utish rajkarnikar" <uti...@hotmail.com> wrote in message
news:8773ca89.03011...@posting.google.com...
Bob Hairgrove
wrote:
There is an "SQL validator" web page at the Mimer database site:
HTH
Bob Hairgrove
rhairgro...@Pleasebigfoot.com
--CELKO--
different databases like MS SQL, Oracle, DB2. <<
Look for the FIPS Flagger feature in your database. This will mark
any proprietary statements in your code. It is a requirement for
doing business with the U.S. Government so the major vendors all have
one.
There is a subtle difference between portable and standard code. Many
of the products will have proprietary code that can be easily
converted -- getdate(), TODAY, etc. are all vendor versions of the
standard CURRENT_TIMESTAMP. You can also contact Semantic Designs in
Austin, TX. They make a parser tool whcih can convert SQL dialects
automatically.
Unfortunately, the Clinton Administration stopped the FIPS-127 testing
programming, so you hazvet to depend on vendors telling you what level
SQL-92 they have.
With a good normalized schema design and a commitment to checking the
code, it is easy to write portable SQL. I have never had a report of
anyone having trouble moving the code in my books to a particular
platform.
The real cost is building a proprietary system and finding out that
you cannot port it (see the research from the SEI, et al) and that
nobody without special vendor knowledge can maintain it.
--CELKO--
you. (only write C that would work with all compilers just in case you
wanted to use another one, people would think you daft.) <<
Actually, I did exactrly that with Fortran, C and SQL and they thanked
me for it. You might want to read the history of the FIPS (Federal
Information Processing Strandards) program. Thinking like yours was
costing the government too much money, so they mandated conformance to
standards. This is why there is a FIPS Flagger in software today.
We found that 80% or more of the cost of an application is in
maintaining it, not in development. The inability to move easily from
one platform (hardware or software) is a killer.
D Guntermann
news:63zT9.307836$qF3.37210@sccrnsc04...
> Path doomed to low performance. Much better to code to be effective and
use
> a particular vendor's features and make exceptions where a vendor differs.
> Remember people paid a lot of money for their RDBMS and for your
application
> to come along and make their investment worthless than what they paid is
> just silly.
A valid perspective, but of course there is something to the notion that the
data will live longer than any particular application, including versions of
propietary DBMS's, and that a standard which allows users to access and
manipulate the data without a bunch of recoding for migration or
distributive operations might be worthy of consideration.
Optimization and performance are indeed important, but perhaps vendors are
getting a return on the investment they made with the optimizers that have
evolved thus far, at least to the extent that performance criteria could be
met without physical implementation details and extensions driving the
design. If money, time, complexity, and resources are no object, then you
can probably get away with not considering other criteria (portability,
scalability, affordability, etc.).
Don't get me wrong, because I am not saying you are necessarily wrong. It
is with some consternation that I recognize the temptation to use
specialized vendor extensions and syntax is too great in practice, but I
have seen the costs of migration efforts across DBMS implementations and it
is apparant to me that the costs of translating vendor implementation
differences is great, possibly even dwarfing any benefit of using those
exensions in the first place.
But of course, the vendors probably do this in the first place so that the
cost of changing is prohibitive.
You wouldn't do the same thing if it was a C compiler now would
> you.
C compilers are not general database management systems where several
applications or enterprise domains might be integrated. Comparing a
compiler, which exposes its hardware and language dependencies to the user,
to a database management system is rather inappropriate in my estimation.
(only write C that would work with all compilers just in case you
> wanted to use another one, people would think you daft.) Also there are
> issues beyond sql statements that are different in each db (eg treatement
of
> locks) that have major implications on your application.
As far as I know, the SQL standard still provides for specifying isolation
properties of user-defined transactions. Unfortunately, neither full
compliance nor consistent compliance with the standard has occurred up to
this point.
(unless you go to
> <I'm going to vomit> do an auto commit strategy.)
> Jim
Regards,
Daniel Guntermann
Jim Kennedy
However, having worked with a lot of different commercial databases
different concurrency models (for example) work differently. Additionally,
beyond the syntax of the SQL (lets just say you use a common syntax that
works across all platforms.) are different methods of interfacing with a
database (eg client side cursors, vs server side cursors) that will give you
great performance one place and lousy performance/scalability another.
Architecturally they are very different things and have nothing to do with
the SQL syntax or a FIPS flag.
I have seen this "requirement" too often and too often it has been the death
knell for the project. That said, I don't think one should use some feature
just to use some feature. Sure if you can reasonably (without killing
scalability and performance) then by all means take a more standards
approach. I am assuming you do not have infinite resources and just dealing
with all the QA headaches of actually trying to make sure these things work
and scale well on the 3 databases you mention is going to be quite a task.
(I'm surprised Mysql wasn't thrown in for good measure.)
If you are doing something for an academic purpose then go for it. If it is
a business reason, then seriously do some research on the differences
between the RDBMSs you have chosen and find someone else that has done it
also. Find out what pitfalls they ran into. (eg DB2 on a mainframe doesn't
like dynamic sql with server side cursors - all those Cobol developers get
pissed off because you lock the plan table until you end your
ransaction. ) And of course which version of what.
Been there done that. Learned a lot.
Jim
--
Replace part of the email address: kennedy-down_...@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"D Guntermann" <gunte...@hotmail.com> wrote in message
news:H8Ixw...@news.boeing.com...
Peter Gulutzan
> I have to write 'generic' SQL code which will be compatible with
> different databases like MS SQL, Oracle, DB2. Since most of the
> databases support Entry level SQL 92, where can I find the repository
> of all the statements and functions conforming to SQL 92 entry level?
Although some pirates (e.g. Carnegie-Mellon University) offer
public downloads, the product is copyright and should be bought.
You will be unable to buy it from Global Engineering since SQL-92
is obsolete (they only sell SQL-99 specs). So try Date + Darwen's
book: A Guide To The SQL Standard, Third Edition. In my opinion
it was the best SQL book ever (and I have read many and written a
few). However, I wouldn't recommend it nowadays except to people
who insist on "SQL-92" (as you do).
Peter Gulutzan
Most recent article: "SQL Naming Conventions" http://dbazine.com/gulutzan5.html
Marcus Baker
I generally work on three tier projects, so my view will be coloured here...
Jim Kennedy wrote:
> Path doomed to low performance. Much better to code to be effective and use
> a particular vendor's features and make exceptions where a vendor differs.
Not really. When building applications for clients or when in-house I
design in the abstract. The database abstraction is used by the
application and maps to "standard SQL". I would have to duplicate every
library otherwise. The only time we don't is when performance is
definitely going to bite us. Even then we first look at alternate
algorithms, caching in the application or denormalisation schemes long
before we resort to vendor specific tricks.
Those places where vendor specific things happen are treated as
exceptions and are again fenced off. Transactions for example.
> Remember people paid a lot of money for their RDBMS and for your application
> to come along and make their investment worthless than what they paid is
> just silly.
Full scale switches of database vendor are rare, but do happen (Yahoo).
What is more likely is that there is a mixed setup (usually MySQL and
something else it seems) and some data is to be moved from one to another.
> You wouldn't do the same thing if it was a C compiler now would
> you.
Uh?!
I absolutely would and I would not code anything other than ANSI C and
anyone working for me who didn't would be asked to change their ways or
find the exit. I don't do a lot of C++, but I always choose a
standardised language if one is available and avoid anything proprietry.
Libraries are more of a problem, but systems such as JDO are making
things easier. All such libraries are usually given generic wrappers
(Facade pattern). I would expect Oracle Cartridges to be treated the
same way.
>(only write C that would work with all compilers just in case you
> wanted to use another one, people would think you daft.)
What is daft is that proprietry extensions are poorly documented,
usually short lived ("huge" pointers anyone?) and prone to performance
problems of their own.
> Also there are
> issues beyond sql statements that are different in each db (eg treatement of
> locks) that have major implications on your application. (unless you go to
> <I'm going to vomit> do an auto commit strategy.)
This is where a standard SQL is so badly needed. It is not carte'
blanche to use each and every vendor extension chasing unmeasured
"perfomance" issues.
> Jim
>
yours, Marcus.
--
mar...@lastcraft.com is routed through spamcop.