Creating MySQL and SQLServer compatible objects

Question

I have a need to support two separate database engines - MySQL and SQL Server. I do not want to maintain two different sets of scripts to create database objects (tables, views, stored procedures etc...), if possible.

I wanted to find out if someone has experience in doing such a thing and what were the learnings. Any links to articles/ documents related to the same is also appreciated.

Some of the issues I have run into are 1. MySQl has implicit defaults for columns while SQL Server does not 2. MySQL Timestamp column is different from SQL Server timestamp column 3. The way MySQL references objects in a different database is different from how SQL Server does - differentdb.table (mysql) vs differentdb.dbo.table (sql server) 4. The temp table creation syntax is different in the two database engines

This list is by no means exhaustive :)

TIA

Answer 1

A solution would be to not use any SQL script to create your database objects, but use some kind of abstraction layer between your application and the database :

• It should take as input some "normalized" schema -- for instance, an XML description of your database structure
• And either output SQL scripts (Different for each database type), or directly act on the database.

For more informations, you might want to take a look at the wikipedia entry Database abstraction layer.

Not sure which language you are using, but, for instance, in PHP, the ORM Framework Doctrine provides such an Abstraction Layer.