convert sql query for different database

Question

Is there a tool to convert form one sql query of one Database to another .

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

for sqlserver

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

similarly it is different for oracle and sqlserver, 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 concatination is done using || while in sqlserver it is done using +

Answer 1

SwisSQL Console 5.0

Console offers an instant solution providing quick and reliable SQL query conversion utility that accelerates migration. Console supports migration across Oracle, SQL Server, IBM DB2, MySQL, Sybase, PostgreSQL, Informix and Netezza databases. This software also has features to test the converted SQLs in target databases.

Answer 2

Hi.

If I were to support multiple database management systems, I'd do it thoroughly, with a Data Access Layer for each system. It'd require some amount of work, of course, but the modularity would be quite beneficial.

One alternative I'm quite happy with, is DevExpress' XPO. It's an Object Relational Mapping system that supports multiple databases. You design your classes, define a proper connection string, and the database schema will be created for you, and you can apply crud to your classes easily in code. In order to use a different database system, only change the connection string!

And no, I'm not affiliated with DevExpress other than as a very pleased customer.

http://www.devexpress.com/Products/NET/ORM/info.xml

Answer 3

Hi, 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

Answer 4

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.