Convert SQL query for a different database

Question

Is there a tool to convert from one SQL query of one database to another?

For SQLite

CREATE TABLE ConstantValues(
    Id int AUTOINCREMENT primary key,
    VariableName varchar(50),
    Values varchar(150)
)

For SQL Server

CREATE TABLE ConstantValues(
    Id INTEGER identity(1,1) primary key,
    VariableName varchar(50),
    Values varchar(150)
)

Similarly it is different for Oracle and SQL Server. Also in the foreign key constraints declaration, if there is a tool so that we can get SQL from any database to any database, it would be really helpful for me.

I have created a function like this, but it doesn't seem to be a good solution:

private string changeSQL(string sql)
{
    switch (dbtype)
    {
        case dbType.SQLite:
            sql = sql.Replace(" int ", " INTEGER ");
            sql = sql.Replace(" identity(1,1) ", " AUTOINCREMENT ");
            break;
        case dbType.MsAscess:
            sql = sql.Replace(" int ", " ");
            sql = sql.Replace(" identity(1,1) ", "");
            sql = sql.Replace("AUTOINCREMENT", "AUTOINCREMENT");

            break;
    }
    return (sql);
}

Similarly for SQLite, concatenation is done using || while in SQL Server it is done using +.

Answer 1

This isn't an automated tool, but the best resource I've found for understanding the differences between the different SQL implementations is O'Reilley's SQL Cookbook by Anthony Molinaro. http://oreilly.com/catalog/9780596009762/

He shows how to solve many different problems with clever SQL queries, including presenting side by side solutions for Oracle, SQL Server, DB2, MySQL and Postgres when they use different syntax or proprietary features. Changing your code to work with a different database is alot easier given descriptions of how they systems differ.

Molinaro also does a nice job of explaining windowing queries (or what Oracle calls analytic queries) which are well worth the time to learn, as you can accomplish things very efficiently with a query that previously required custom client code that wasted bandwidth and time.

Answer 2

first you need to know and understand that every SQL engine works with a different SQL grammar. Despite the SQL ANSI standard, no language on earth respects it 100%. Moreover, every large and known SQL engine adds own methods and stuff to the original grammar.

So, if you want to do a conversion, the easiest way is to achieve a middle SQL layer. That means, to create an agnostic SQL grammar out of the very common features in every well known SQL engine (it would result in something like SQL ansi plus every feature present in every engine, like TOP). Once you have this, you have to make the conversion to this middle layer, and from this middle layer for each SQL variation you need.

I told you this because I needed this exact thing at my work, and that was the only way to actually achieve it, and made it reusable. Having a tool gives you the job to actually manually convert every single query, and make huge SWITCHs just to choose the query, or to have an inherited class for every engine.

I tell you what I've done: I created the BNF of my SQL middle grammar, then created a tree parser with GoldParser for C#. Then I created individual rules for each rule in the grammar to be converted to each SQL dialect. It's a huge and tedious job, I know. But they'd paid me to do it...

If you don't have the time to accomplish this, you could use ODBC. Every SQL engine has an ODBC connector, and the ODBC itself will act as a middle abstract layer. But, it's not as happy as it sounds, because only simple queries will maintain this illusion... hard stuff like UNION, JOINs, and metadata creation won't be the same.

I hope it helped,

good luck