MySQL ODBC Connector - Differences Between ORACLE SQL PLus and MYSQL SQL

Author

Differences Between ORACLE SQL PLus and MYSQL SQL

Phong Nguyen

2005-09-16, 9:23 am

All,

Could you please tell us what are the differences
between ORACLE SQL plus and MYSQL SQL? We developed
application using ColdFusion with Oracle. Now I like
to point my application to mysql database. How do I
fix my application to work with Mysql?

Thankyou

____________________
______________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Martijn Tonies

2005-09-16, 11:23 am

Hi,

> Could you please tell us what are the differences
> between ORACLE SQL plus and MYSQL SQL? We developed

"oracle sql plus" is a client side application to execute SQL statements.

"oracle sql" is Oracles version of standard SQL (which is not standard)
and "mysql sql" is MySQLs version of standard SQL, which is not
standard either.

> application using ColdFusion with Oracle. Now I like
> to point my application to mysql database. How do I
> fix my application to work with Mysql?

Remove any Oracle specific syntax, try to find the SQL standard
syntax for it - if available - and if that doesn't work, try the MySQL
version of the SQL.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www. databasedevelopmentf
orum.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2005-09-16, 11:23 am

--=_alternative 00503AE98525707E_=
Content-Type: text/plain; charset="US-ASCII"

Phong Nguyen <pnguyen5us@yahoo.com> wrote on 09/15/2005 10:56:57 AM:

> All,
>
> Could you please tell us what are the differences
> between ORACLE SQL plus and MYSQL SQL? We developed
> application using ColdFusion with Oracle. Now I like
> to point my application to mysql database. How do I
> fix my application to work with Mysql?
>
> Thankyou
>

You asked basically two questions:

a) what are the differences between ORACLE SQL plus and MYSQL?

First, I must refer you to the official MySQL documentation. Everything
you will need to know to answer your question will be in there. I am
assuming that you already understand Oracle SQL Plus, as you are using it.

http://dev.mysql.com/doc/mysql/en/index.html

It does not do a point-by-point comparison between the two products you
list but if you understand Oracle and read this to see how you form the
MySQL equivalent to each Oracle statement you use (you can skip around to
read just the parts you need then come back and get the rest later), the
differences will be readily apparent. If you do not already understand
your Oracle back-end, you have a serious disadvantage to making this a
smooth transition.

b) How do I fix my application to work with Mysql?

The simple answer is: Change your website's code to use a compatible MySQL
client library and modify all SQL statements to be MySQL compatible.

Exactly how you do that with the environment you specified, I don't have a
clue. I looked at Cold Fusion once (several years ago) so how that tool
helps to automate the development of the data storage and retrieval
process is beyond me. How much you need to change your SQL statements
depends on how they are with MySQL already. One well-known point on how
MySQL and Oracle differ is in how to define a JOIN in a SQL statement.
MySQL works best if you use the explicit "& #91;INNER|LEFT|RIGHT
] JOIN ... ON
...." format. AFAIK, Oracle does not support this syntax.

I wish you well. If you have any specific translation issues that you
cannot resolve by reading the manual, please bring it back to the list. I
am sure someone will be able to help.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 00503AE98525707E_=--

Josh Trutwin

2005-09-16, 8:23 pm

On Fri, 16 Sep 2005 10:32:23 -0400
SGreen@unimin.com wrote:

> You asked basically two questions:
>
> a) what are the differences between ORACLE SQL plus and MYSQL?

<snip>

> depends on how they are with MySQL already. One well-known point on
how
> MySQL and Oracle differ is in how to define a JOIN in a SQL
statement.
> MySQL works best if you use the explicit "& #91;INNER|LEFT|RIGHT
] JOIN
.... ON
> ..." format. AFAIK, Oracle does not support this syntax.

All these questions are also dependant on which version of which
software you are using. In Oracle 8i the above is certainly true, you
have to use the doofy (+) syntax for outer joins, etc. In Oracle
10g though the more verbose syntax is supported:

http://download-west.oracle.com/doc....htm#sthref7225

You can do LEFT INNER, RIGHT INNER, LEFT OUTER, RIGHT OUTER, FULL
OUTER, etc. using the same syntax as MySQL.

I'm not sure but some versions of MySQL also had different results
from Oracle depending on how you defined the join condition in an
OUTER join. So what version of MySQL you choose also has consequences
on portability (e.g. sub-queries, views, etc)

There are enough little differences between all DBMS's that make
porting a real challenge. Bottom line, you're going to have to do a
LOT of research and testing.

I would seriously consider looking at a database abstraction layer.
In ColdFusion I have no idea what is available, but for PhP you'd want
to look at something like ADOBD (http://adodb.sourceforge.net) or
PDO/MDB/Metabase, etc.

Good luck,

Josh

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links