Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2008
    Posts
    1

    Unanswered: Oracle and MySQL compatible SQL?

    Not sure what forum is best to post this in, but here's the situation.

    I need to migrate a database from MySQL to Oracle and update the SQL statements in the code to work with Oracle. I'm new to Oracle and have limited experience with databases in general, but I think I've worked out most of the issues with migrating the table structure and data.

    With the SQL, though, I'm told we want to make the statements work with both Oracle and MySQL, in case we want to use the software with a MySQL database again at some point. Currently, the SQL uses some MySQL-specific syntax such as REPLACE INTO, LIMIT n, and LAST_INSERT_ID(). Replacing those with any ease seems to require MERGE INTO, ROWNUM / ROW_NUMBER(), and grabbing the CURRVAL of specific sequences after inserts. I looked into using stored procedures to hide the database-specific stuff and just call a procedure with the same name, arguments and results, but for various reasons that is not going to be an option either.

    I guess my question is, is replacing these with extremely generic SQL that will work in both databases feasible and worth doing, or would it be better to have some setting indicating which database is being used and just check that setting and use the appropriate DB-specific statement in each case?

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I'd go with "would it be better to have some setting indicating which database is being used and just check that setting and use the appropriate DB-specific statement in each case".

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cgdecker
    ...is replacing these with extremely generic SQL that will work in both databases feasible and worth doing?
    no, not really

    it would be best to write two separate application-level objects, one for oracle and one for mysql, and call them from the rest of the application with the same parameters, so that you can plug either one into your app and have it access the respective dbms efficiently

    (aside: isn't this called object-oriented programming? in this case, the object would be either the mysql module or the oracle module, but to the rest of the app it has the same interface)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I seriously doubt that anyone could write a "procedure" with two different flavor of SQL (Oracle & MYSQL) contained within.
    I suspect you'd get COMPILE errors for the SQL in the "other DB's" flavor.

    You could not even get away with creating SQL statements "on the fly" because EXECUTE IMMEDIATE is not valid for MYSQL procedures.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •