Preparing the code to migrate from SQL to ORACLE

Databases
1

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

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

3

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

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

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

6

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

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.