I want to write some queries which can work in almost all the databases without any SQLExceptions. So, where can I get the ANSI standards to write the queries ?
+3
A:
Not sure that'll help you.
Vendors are touch and go as far as standards implementation and often the standards themselves are imprecise enough such that you could never write a query that would work with all implementors.
For example, SQL 92 defines the concatenation operator as || but neither MySQL nor MSSQL use this (Oracle does). Vendor independent string concatenation is impossible.
Similarly, a standard escape character is not specified so how you handled that might not work in all vendors.
Having said that:
SQL 92: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Wiki article with links to SQL 99 ISO documents: http://en.wikipedia.org/wiki/SQL:1999
Graphain
2010-07-01 05:13:10
Thanks for your reply
Multiplexer
2010-07-01 05:14:52
What did you have in mind as 'a standard escape character'? SQL uses double quotes as standard to delimit identifiers; MS SQL Server uses '[]' instead. A number of DBMS support both single quoted strings (as required by the standard) and double quoted strings (which are supposed to be for delimited identifiers according to the standard).
Jonathan Leffler
2010-07-01 05:26:39
@Jonathan Leffler - That's exactly the problem, it's vendor specific so you can't write a general query.
Graphain
2010-07-01 05:40:31
Well, the standard is quite clear; the trouble is that vendor's do not reliably implement the standard. Backticks, square brackets, ... why?
Jonathan Leffler
2010-07-01 06:58:52
+2
A:
From wikipedia:
The SQL standard is not freely available. The whole standard may be purchased from the ISO as ISO/IEC 9075(1-4,9-11,13,14):2008.
Nevertheless I would not advise you to follow this strategy because no database engine follows any SQL standard (SQL 99, 2003, etc.) to the letter. All of them take liberties in the way they handle instructions or define variables (for example, when comparing two strings different engines handle case sensitivity differently). A method that is very efficient with one engine can be terrible inefficient for another.
A suggestion would be to develop a standard group of queries and develop different classes that contain the specific implementation of that query for a certain target RDBMS.
Hope this helped
Kiranu
2010-07-01 05:13:22
I remember reading that but had no trouble finding most of the relevant vendor-common SQL standards online.
Graphain
2010-07-01 05:20:10
You can find a very detailed documentation on each vendor's take on SQL. But the official standard is for sale by ISO (which is great for adoption obviously)
Kiranu
2010-07-01 05:21:45
+1
A:
The Sql-92 standard is probably the one you want to target. I believe it's supported most of the major RDBMSs.
Here is a less terse link. Sample content:
- PostgreSQL Has views. Breaks standard by not allowing updates to views...
- DB2 Conforms to at least SQL-92.
- MSSQL Conforms to at least SQL-92.
- MySQL Conforms to at least SQL-92.
- Oracle Conforms to at least SQL-92.
- Informix Conforms to at least SQL-92.
Something else you might consider, if you're using .NET, is to use the factory pattern in System.Data.Common which does a good job of abstracting provider specifics for a number of RDBMSs.
Paul Sasik
2010-07-01 05:14:54
I like the conform bit although it's not strictly true. neither MSSQL nor MySQL support || concatenation for instance.
Graphain
2010-07-01 05:18:19
DB2 does not support INTERVAL types which are (an optional?) part of SQL-92. There is an entry-level SQL-92 (approximately the same as SQL-89), and most of the DBMS do support that. All support extensions over that. No-one supports all of SQL-92 (CREATE ASSERTION, anybody?)
Jonathan Leffler
2010-07-01 05:20:37
There are inconsistencies but i think that if you write vanilla CRUD queries you should be OK. Also, .NET (since 2.0) provides a good way of abstracting away provider specifics: http://msdn.microsoft.com/en-us/library/cy5kfe3c.aspx
Paul Sasik
2010-07-01 05:33:40
+2
A:
Check out the BNF of the core SQL grammars available at http://savage.net.au/SQL/
This is part of the answer - the rest, as pointed out by Kiranu and Graphain, is that different vendors implement the standard differently. No DBMS adheres perfectly to even SQL-92, though most are pretty close.
One observation: the SQL standard says nothing about indexes - so there is no standard syntax for creating an index. It also says nothing about how to create a database; each vendor has their own mechanisms for doing that.
Jonathan Leffler
2010-07-01 05:19:47
A:
If you are trying to make a product that will work against multiple databases I think trying to only use standard sql is not the way to go, as other answers have indicated, due to the different 'interpretations' of the standard. Instead you should if possible have some kind of data access layer in your application which has different implementations specific for each database. Depending on what you are trying to do, there are tools such as Hibernate which will so a lot of the heavy lifting in regards to this for you.
Craig
2010-07-01 05:28:25