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
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.