Want to convert Oracle SQL to MySql SQL

Hi there,

I've created a web application. I used windows 2000, Oracle, JSP & servlets to develop the application. But the real server where I want to deploy this application is a linux based machine with MySql database. I tried to deploy the application. But the problem is - I wrote so many embedded Sqls which works perfectly in Oracle, but will not work in MySQL. The SQL is :-

select c.mobnum,c.msglan,c.datetime,c.status,a.gname,b.cnam from groups a,contacts b,history c where (c.oid=10001 and b.oid=10001 and a.oid=10001 and b.cnum=c.mobnum and b.gid = a.gid) or (c.oid=10001 and a.oid=10000 and b.oid=10000 and c.mobnum not in (select cnum from contacts where oid=10001)) order by a.gname;

Anyone pls give me some tips to convert this sql to another which would work perfectly with MySql. Thank you very much

Are you using Oracle's internal object identifiers (*.oid) to track rows, rather than a standard primary key? If so, then that's your problem. MySQL doesn't have such a syntax. I personally recommend staying as far from this kind of syntax as possible, because it's not portable SQL, and it also tends to subvert the purpose of a true relational database. You should be defining your primary keys and selecting data based on characteristics of your data itself, rather than just selecting data by row number.

IMHO, the only reason to mess with the "oid" stuff is if you really think you need to hack your database structure. Generally, its not good form.

If you really need the oid capability, you should look into using PostgreSQL, rather than MySQL. PostgreSQL is the closest you will get to Oracle in the open source world. It is actually a great database. After moving to PostgreSQL, I hope I never have to develop for MySQL again. It is a standard open source database available for almost any Linux distribution, so it should be no problem to set up for your server.

You also have a subselect in your query. Subselects are currently not supported in MySQL (at least pre 4.x versions).

Develop in Oracle and deploy in MySQL???? Arghhhhh

lol