CODEWITHSUNDEEP
sqlmysqlsql-serveroracle-database
user169743
user169743

Reputation: 4267

Convert MS SQL script to Mysql and Oracle

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.

Upvotes: 10

Views: 31150

Answers (5)

VulfCompressor
VulfCompressor

Reputation: 1410

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).

Upvotes: 3

anon
anon

Reputation:

For conversion to Oracle you can try the Migration Workbench.

Upvotes: 3

Will Marcouiller
Will Marcouiller

Reputation: 24132

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.

Upvotes: 1

Chris Gill
Chris Gill

Reputation: 2928

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...

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425341

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.

Upvotes: 0

Related Questions