Convert MS SQL script to Mysql and Oracle

Question

One of the applications I develop gets installed locally on the customers site. This means that we have to support MySQL,MSSQL and Oracle as not all the customers use the same database engine.

I'm writing a patch for the application, part of which involves executing a 5000 line sql script to make modifications to the database. The script is written using MSSQL syntax, however I was wondering if there is an application to automatically converting MSSQL syntax to that of Oracle and/or MySQL to save me having to manually do it.

Answer 1

I'm writing a patch for the application, part of which involves executing a 5000 line sql script to make modifications to the database. The script is written using MSSQL syntax, however I was wondering if there is an application to automatically converting MSSQL syntax to that of Oracle and/or MySQL to save me having to manually do it.

It's hardly possible.

The common subset of SQL all these systems support is quite little.

Since you have 5000 lines of code in your script, most probably it's out of this subset.

The worst part is that it's usually not only the syntax differences, but rather major conceptual ones.

• Oracle, for instance, supports MERGE statement which allows inserting, deleting and updating in a single query.

  Neither SQL Server 2005 nor MySQL support one.

• MySQL supports deleting from several tables at once: not available in Oracle or SQL Server.

• SQL Server, unlike Oracle and MySQL, can update CTE's that use analytic functions.

Answer 2

And even more concerning is that the databases support different locking mechanisms, so you may be able to get some tables and indexes in there, but you might introduce some issues that are very difficult to debug/replicate when you expose it into the wild.

You need as 3 experts to sort that lot out - one each of SQL Server, MySQL and Oracle. That or your database needs to run with a maximum of one user, with poor performance and very average database mechanisms.

Sorry...

Answer 3

I don't know of a database engine programming syntax converter. However, since you're working on a multi-database platform, I would consider using Enterprise Library and its application block. This will save you headaches, you got to trust me!

I am aware that you parhaps don't have enough time for your coming releases so you will have to setup the scripts manually, but do yourself a favour, and consider using Microsoft Enterprise Library.

Another possible way is to bring these engines as close as possible to each other in ressemblance so that you may work more conveniently with the three of them. Thus, it requires you to analyze and to decide what function should be created where and why? This is hard work, seriously.

In my own experience, I no longer develop without Enterprise Library, at least the Data Application block (DAAB).

Here the link:

1. http://www.microsoft.com/downloads/details.aspx?FamilyId=1643758B-2986-47F7-B529-3E41584B6CE5&displaylang=en
2. http://www.codeplex.com/entlib

Let me know if this helped you. If you need further assistance for setting up your Data Access layer with Enterprise Library, let me know.

Answer 4

For conversion to Oracle you can try the Migration Workbench.