#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    kuwait
    Posts
    1
    Rep Power
    0

    Question 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
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62

    L


    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,643
    Rep Power
    4248
    You also have a subselect in your query. Subselects are currently not supported in MySQL (at least pre 4.x versions).
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    lol
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo