Convert MS SQL script to Mysql and Oracle

Question

One of the applications I develop gets installed locally on the customer's 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 for automatically converting MSSQL syntax to that of Oracle and/or MySQL to save me from having to manually do it.

Answer 1

For conversion to Oracle you can try the Migration Workbench.

Answer 2

This is an old topic, but i would recommend using the following tool: http://www.sqlines.com/online. I had a good experience using it. No problems found since 3 months ago (when i've started using it).

Answer 3

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 4

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.

EDIT 2015.01.16

Another best approach these days is the use of either NHibernate, Entity Framework or other ORM tools such as these.

With them, no longer need any SQL scripts, all is generated for you in depending on your RDBMS configuration.