SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Preparing the code to migrate from SQL to ORACLE

    Hello guys,

    I'm working in a project written in PHP/JAVA which uses SQL as DB for now but will be migrated to ORACLE pretty soon.
    So the idea is to write this version of the project using SQL but compatible with ORACLE so we could migrate DBs in a near future.

    We've started using MySql then we moved to SQL and now we would like to create a version using SQL but ready for ORACLE.

    I have never done it before so I'm a bit confused of what path should I take regarding to the query statements.

    Any suggestions are welcome!

    Thanks for you time!

    Dennis

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    just as an aside, please don't call the microsoft product "SQL" -- it is called SQL Server

    "SQL" is also part of the MySQL name, so just calling a database "SQL" isn't specific enough

    to answer your question, you ~cannot~ write the same SQL for both SQL Server and oracle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    Sorry about that.
    I meant SQL Server.

    I know I can't use the same SQL for SQL server and ORACLE.

    I am looking for a solution for that, something such as OQL with Hibernate or something.

    Really, I have never used OQL before so I'm looking for a experienced word about this issue.

    Thanks for replying,
    -Dennis

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it possible to achieve the OP's request by sticking with/reverting to MySQL and using standard SQL queries? Would that enable a smooth transfer to Oracle?

    bazz

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, assuming that "standard SQL" can get the job done

    let's take string concatenation for example

    in mysql, it's CONCAT(str1,str2)

    in sql server, it's str1 + str2

    in oracle, it's str1 || str2

    and this is only one such example, there are numerous similar pitfalls
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've only used Java + hibernate for ORM (object relational mapping). There are other Java ORM libraries you can use such as ibatis or commercial JPA implementations.
    I don't have much experience with PHP so can't comment on that.
    It helped us reduce the vendor lock-in problem, but it is not a panacea. you will probably have to remap some of the objects.
    it was only apparent when we tried switching databases that the problem is more than just SQL queries.
    You have to consider different reserved keywords, name length limitations, case sensitivity, sequence generation.
    And if your app relies on any database specific features / non-standard SQL you will have to write custom queries per target database

    Jurn

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,187
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    If you are not familiar with the differences between the two database vendors I would recommend a PHP ORM that normalizes the queries to use queries omitting vendor specific features. By PHP/JAVA I am going to assume you mean JavaScript, which is not the same as JAVA.
    The only code I hate more than my own is everyone else's.


Bookmarks

Posting Permissions

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