Convert SQL query for a different database Convert SQL query for a different database sql sql

Convert SQL query for a different database

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.

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

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.