Vendor neutral SQL

Question

I'm currently working on a project for a web application that may be installed on several different servers with various software configurations. I want to make my application as flexible as possible by allowing the user to have various SQL servers installed. The problem is the SQL syntax used by any two server vendors does not match up. For a simple example, here is the same SELECT statement for MS SQL and MySQL:

MS SQL - SELECT TOP 1 * FROM MyTable ORDER BY DateCreated DESC

MySQL - SELECT * FROM MyTable ORDER BY DateCreated DESC LIMIT 1

Are there any standard way to abstract the statement creation for various vendors? Any online resources or books discussing this problem? Any hints or smart-alec remarks that I'd find useful?

Further information: I'm writing my we application in vanilla ASP running on a Windows server.

Thanks, Spara

Answer 1

You can conform to ANSI SQL 92. All major RDBMS I know will support that.

However, there are tons of things individual RDBMS makers have added to enhance their own flavor of SQL. That is where you get into a lurch.

You may have to branch out in code depending on the RDBMS you are connecting to and generate / choose the appropriate SQL statement at that point.

A better option would be to create a DAL for each supported RDBMS. Implement a DAL interface across the DALs to make them uniform. This should be easier than switching in code.

I suggest that instead of trying to please everybody, you should write your code such that you support the top one or two systems that you expect to deploy on, and add support for other RDBMS as and when required.

Answer 2

I suggest you use an ORM (linq, nhibernate etc) to abstract the SQL dialect away rather than trying to write plain vanilla SQL.

Edit: Is there an OR/M for Classic ASP?

Answer 3

You know, I bet you could get by with strictly ansi sql, it will just take some effort and probably extra work. i.e.

SELECT MAX(*) FROM mytable ORDER BY datecreated DESC;

There will be workarounds in ansi because really all of the db specific constructs are ways to shorten and or shortcut existing ways of getting at or describing data. Another option might be to restrict access to the various databases to stored procs and user-defined functions. That way, you could write scripts for a bunch of the dbs you know will be used with the requirement that your db specific script be run before the app will work. Just an idea.