Converting from MySQL commands to Oracle: HELP!

[Mark Wilson CPU]

A colleague has written a prototype program in PHP, using a MySQL
database.
It's a relatively simple app, with a restricted set of mysql commands
used (see below). The MySQL DB is being replaced with an Oracle DB
(same schema). My plan
1) globally replace the few mysql commands with intermediate
equivalents (such as myDB_connect for mysql_connect)
2) those central functions would then (for now) call the original
mysql function to prove the code still works
3) replace the "innards" of the myDB_ commands with calls to the
Oracle equivalent, including connecting to the new DB
4) make sure it all still works!

Can someone provide me an equivalency for these in Oracle? Or, where
an equivalent is not available, a reasonable alternative
command/procedure? Thanks.

Commands used:

mysql_connect(" localhost", "username", "userpass")
mysql_error();
mysql_select_db ();
$arry = mysql_query($qu ery)
$var = mysql_fetch_row ($arry)
$numvars = mysql_num_rows( $ varsarry )
mysql_data_seek ( $arry, $day )
$line = mysql_fetch_arr ay($result, MYSQL_ASSOC)
$currdata = mysql_fetch_ass oc( $currentarray )

That's the lot... thanks!
- Mark

[Michael Vilain]

Re: Converting from MySQL commands to Oracle: HELP!

In article <3fb6a835.04120 21206.cb45022@p osting.google.c om>,
wilson@cpuworks .com (Mark Wilson CPU) wrote:
[color=blue]
> A colleague has written a prototype program in PHP, using a MySQL
> database.
> It's a relatively simple app, with a restricted set of mysql commands
> used (see below). The MySQL DB is being replaced with an Oracle DB
> (same schema). My plan
> 1) globally replace the few mysql commands with intermediate
> equivalents (such as myDB_connect for mysql_connect)
> 2) those central functions would then (for now) call the original
> mysql function to prove the code still works
> 3) replace the "innards" of the myDB_ commands with calls to the
> Oracle equivalent, including connecting to the new DB
> 4) make sure it all still works!
>
> Can someone provide me an equivalency for these in Oracle? Or, where
> an equivalent is not available, a reasonable alternative
> command/procedure? Thanks.
>
> Commands used:
>
> mysql_connect(" localhost", "username", "userpass")
> mysql_error();
> mysql_select_db ();
> $arry = mysql_query($qu ery)
> $var = mysql_fetch_row ($arry)
> $numvars = mysql_num_rows( $ varsarry )
> mysql_data_seek ( $arry, $day )
> $line = mysql_fetch_arr ay($result, MYSQL_ASSOC)
> $currdata = mysql_fetch_ass oc( $currentarray )
>
> That's the lot... thanks!
> - Mark[/color]

I'm sure someone has written the glue code to integrate 3-rd party DBMS'
into php and mod_php. Most of the database code out there is for the
free ones like MySQL and Postgres. Have you contacted Oracle? Maybe
they have something.

--
DeeDee, don't press that button! DeeDee! NO! Dee...

[Andy Hassall]

Re: Converting from MySQL commands to Oracle: HELP!

On 2 Dec 2004 12:06:35 -0800, wilson@cpuworks .com (Mark Wilson CPU) wrote:
[color=blue]
>A colleague has written a prototype program in PHP, using a MySQL
>database.
>It's a relatively simple app, with a restricted set of mysql commands
>used (see below). The MySQL DB is being replaced with an Oracle DB
>(same schema). My plan
>1) globally replace the few mysql commands with intermediate
>equivalents (such as myDB_connect for mysql_connect)
>2) those central functions would then (for now) call the original
>mysql function to prove the code still works
>3) replace the "innards" of the myDB_ commands with calls to the
>Oracle equivalent, including connecting to the new DB
>4) make sure it all still works!
>
>Can someone provide me an equivalency for these in Oracle? Or, where
>an equivalent is not available, a reasonable alternative
>command/procedure? Thanks.
>
>Commands used:
>
>mysql_connect( "localhost" , "username", "userpass")
>mysql_error( );
>mysql_select_d b();
>$arry = mysql_query($qu ery)
>$var = mysql_fetch_row ($arry)
>$numvars = mysql_num_rows( $ varsarry )
>mysql_data_see k( $arry, $day )
>$line = mysql_fetch_arr ay($result, MYSQL_ASSOC)
>$currdata = mysql_fetch_ass oc( $currentarray )[/color]

The Oracle extension (oci8) is documented here:

http://uk.php.net/oci8

You may find it worthwhile using a database abstraction layer; I rather like
ADOdb. http://adodb.sourceforge.net/

Changing the functions is definitely not the only thing you need to change; if
you treat Oracle as if it were MySQL you'll be in for some nasty surprises, and
if you have a DBA he'll probably start throwing things at you.

The main differences are probably the transaction/concurrency/locking model,
that you should use bind variables and not stuff values into SQL

There is no direct equivalent for mysql_num_rows or mysql_data_seek , as PHP
doesn't support Oracle scrollable cursors. If you want to emulate these
functions, you have to fetch the whole result set as an array first (which is
what MySQL does internally by default - unless you're using 'unbuffered
queries').

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Markku Uttula]

Re: Converting from MySQL commands to Oracle: HELP!

Andy Hassall wrote:[color=blue]
> The Oracle extension (oci8) is documented here:
>
> http://uk.php.net/oci8
>
> You may find it worthwhile using a database abstraction layer; I
> rather like ADOdb. http://adodb.sourceforge.net/[/color]

Seconded.
[color=blue]
> Changing the functions is definitely not the only thing you need to
> change; if you treat Oracle as if it were MySQL you'll be in for
> some
> nasty surprises, and if you have a DBA he'll probably start throwing
> things at you.[/color]

And in the worst case scenario the flying objects will be the least of
your problems. Using Oracle with same restrictions MySQL has will in
most cases create extreamely inefficient code. The efficiency *may* be
way lower than it would be if the stuff was still being run on MySQL.

An example from real life is when I was working on a project that had
"enterprise " and "lite" versions of the same software. Only main
difference was that the enterprise version was used Oracle and the
lite version was on MySQL of course. The codebase for both versions
was identical (of course lite version had some minor restrictions on
the amount of allowed client connections and other stuff like that).
However, because we were forced to use Oracle only in ways that were
possible to do with MySQL, we were barely able to achieve same speeds
on queries on Oracle - it just ain't designed to be used that way :)

Finally we managed to convince the clients that we needed to separate
the codebases and use the features of both databases up to their
maximum potential. This meant a major rewriting and restructuring of
the code to things that could be handled similarly (we made these into
suitable libraries) and those that needed different handling depending
on the database used. Now the lite version is truly great for smaller
organisations with hundreds of users. Enterprise version handles
easily hundreds of companies each of which have thousands of
companies. If we hadn't redesigned a lot of things, this would've been
an impossible goal to achieve.

Consider yourself having been warned :)

--
Markku Uttula

[Marcin Dobrucki]

Re: Converting from MySQL commands to Oracle: HELP!

Mark Wilson CPU wrote:[color=blue]
> A colleague has written a prototype program in PHP, using a MySQL
> database.
> It's a relatively simple app, with a restricted set of mysql commands
> used (see below). The MySQL DB is being replaced with an Oracle DB
> (same schema).[/color]


how about doing it via PEAR::DB?

http://pear.php.net/package/DB

/Marcin