10

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.

Improve this question
2
  • 2
    The question is: would you trust your 'production' databases with an automatically generated script... Commented Sep 7, 2009 at 14:56
  • 1
    If it passed QA's testing procedures, of course. Commented Sep 7, 2009 at 15:04

5 Answers 5

Reset to default
3

For conversion to Oracle you can try the Migration Workbench.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Your Link is dead. Please Update it.
Or, you know, you could update it...you have enough reputation.
Because i am searching for the Same :)
3

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

Improve this answer

2 Comments

Large query not supporting
I cannot change any thing in this side even 1 line query , it say -- SQLINES LICENSE FOR EVALUATION USE ONLY and keep the original query
1

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

Improve this answer

1 Comment

funny because it is only used by one user (the application) and any of the commands executed are very simple, it merely acts as a persistence layer.
1

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.