dennislaymi — 2011-06-03T02:00:20-04:00 — #1
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!
r937 — 2011-06-03T06:34:30-04:00 — #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
dennislaymi — 2011-06-03T06:40:44-04:00 — #3
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,
ibazz — 2011-06-03T08:15:24-04:00 — #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?
r937 — 2011-06-03T08:53:03-04:00 — #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
jurn — 2011-06-03T09:41:07-04:00 — #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
oddz — 2011-06-04T22:27:23-04:00 — #7