SQL neutral provider

Question

I am currently working on a project for a web application that can be installed on several different servers with different software configurations. I want to make my application as flexible as possible, allowing the user to install various SQL servers. The problem is the SQL syntax used by any two server providers that do not match. 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

Is there a standard way to abstract the creation of statements for different providers? Any online resources or books discussing this issue? Any tips or comments from smart-alec that I find helpful?

Additional Information: I am writing my application in vanilla ASP running on a Windows server.

Thanks, Spara

Answer 1

You can comply with ANSI SQL 92. All of the major RDBMSs that I know will support this.

However, there are many things that individual RDBMS vendors have added to improve their own taste for SQL. That's where you run into.

You may need to branch out in the code depending on the DBMS you are connecting to and generate / select the appropriate SQL statement at this point.

A better option would be to create a DAL for each supported RDBMS. Deploy the DAL interface through the DAL to make them homogeneous. This should be easier than switching to code.

I suggest that instead of catering to everyone, you should write your code in such a way that you support the top one or two systems that you plan to deploy, and add support for other DBMSs as needed.

Answer 2

I suggest you use ORM (linq, nhibernate, etc.) to abstract the SQL dialect, instead of writing plain vanilla SQL.

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

Answer 3

You know, I'm sure you could get along strictly using ansi sql, it will just take effort and possibly extra work. i.e.

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

There will be workarounds in ansi, because indeed all of the specific db constructs are ways to reduce and / or reduce existing methods of accessing or describing data. Another option may be to restrict access to various databases to stored processes and user functions. That way, you can write scripts for the dbs group that you know will be used with the requirement that your db-specific script be executed before the application runs. Just an idea.