Ansi 92 standards

[Guest_imported]

I am having three diff databases to handle with in my work where in portability is a big hurdle. I heard that if i follow the ansi-92 standards i can overcome this. Can anyone help me out where i can get more info on Ansi 92 standards. Would like to know what changes i need to do in my sql to follow ansi-92 standards

Appreciate a quick response.

Thanks
Vi

 

[r937]

you'll probably have to buy a book

see "Trying to find the SQL92 and SQL99 specifications online"

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid473888_tax285649,00.html

rudy

http://rudy.ca/

 

[Jocsta]

The bad news is that there appears to be no such thing as a globally supported ansi-92 standard that'll simply work with all databases.
I am currently working on a multi-platform, multi database product written in Java, and have had to resort to designing a query builder to create sql statements tailored to each individual database.
It depends a lot on the degree of functionality you wish to use and which databases you wish to support.
Unfortunately the 4 db's I must support are Oracle 8i, Oracle 9i, sqlserver and mysql, and they make use of quite different, but valid interpretations of the "standard" (the main culprit being Oracle 8i), or lack features that you could use to try to work around the problems (i.e. mysql).
<< JOC >>

 

[r937]

if you want to see the ludicrous ends to which the concept of portability can be pushed, check this article --

Database design for platform independence

http://builder.com.com/article.jhtml?id=u00320020405gcn01.htm

It starts out with great intentions, but immediately makes two suggestions that I would call &quot;howlers&quot; -- don't use stored procedures and don't use joins. I can understand where the author is coming from, but that's ridiculous, and as one person stated in the comments posted to the article, it shows &quot;how a corporate dictum like 'All database applications must be independent of vendor specific features' is not only impractical but dangerous. What pointy headed manager came up with this?&quot;

Even more hilarious were the descriptions of the types of joins (as if that mattered, since you shouldn't be using them) --

* Join
Joins are powerful SQL commands for creating tables from data that is retrieved from multiple sources.

* Equi-Join
Equi-Joins are formed by retrieving all the data from two separate sources and combining it into one, large table.

* Inner/Outer Join
Inner Joins are joined on the inner columns of two tables. Outer Joins are joined on the outer columns of two tables.

* Left/Right Join
Left Joins are joined on the left columns of two tables. Right Joins are joined on the right columns of two tables.

* Compound/Complex Joins
There are also other kinds of joins -- left/inner, left/outer, right/inner, and right/outer.

In the comments posted after the article, the author gets duly reamed for those definitions. It's sad, really.

There was a nice attempt to identify &quot;safe&quot; datatypes. Anyone who has migrated an autonumber or identity field to an Oracle sequence number will know the effect that this can have on SQL. But would you avoid using a generated number just to make the application &quot;cross-database&quot;? That'd be silly. Even the author says some datatypes, like Microsoft Access' AUTONUMBER, &quot;are quite handy.&quot;

rudy

(the bulk of the above is from my sql blog entry,

http://r937.com/20020620.cfm)

 

[karluk]

One possible approach is to have your database tell you what SQL is not ANSI compliant. In Oracle there is a command,

ALTER SESSION SET FLAGGER = FULL

Executing this command will cause Oracle to generate warnings on SQL that isn't ANSI-92 compliant. Probably other databases have similar commands to check ANSI compatibility, so you can get a pretty clear idea of what code will be compatible across databases, and will need modifications.

 

[Jocsta]

LOL Rudy, sounds like that came straight from my management/requirements gathering (aka stupid ideas) team.

 

[actnow]

hi,

may be it is too late, but there's a tool to translate different sql dialects. It's called &quot;MultiSQL&quot;, You can download it from

http://www.legitima.com.

With it You can also define our own ways to translate from one sql dialect to another, really nice even for non-sql-fqns like me.
;-)

 

[petersJazz]

There is a SQL core-92 and core-99 validator at the site

http://developer.mimer.com/validator/parser99/index.tml

. Send in your sql-query and se what it say.