Re: PHP and Oracle

From: B N <bjorn.nilsson_at_bct.ericsson.se>
Date: Wed, 11 Apr 2001 12:16:10 GMT
Message-ID: <3ad442e5.1990844663@news.ericsson.se>

On Tue, 10 Apr 2001 14:57:56 GMT, Simon O'Dochartaigh <odochartaigh_at_libero.it> wrote:

>Hi!
>A simple question for a lot of you but as a newbie I am a bit stumped..
>I have created a sort of admin section (add,edit,delete records from 3
>tables in one database) for a website using PHP4 and mySQL.
>Functions fine and I was very proud of myself for getting it all working:-)
>Then the people I did it for tell me that on their site the database is
>Oracle8i...
>My heart sank. I don't know anything about Oracle8i. Could someone
>answer a few questions for me.
>
>1. What do I have to change now in my PHP pages?
You don't have to change very much. The calls to the mysql SQL queries etc have to be changed to the equivialent OCI sql functions. You can find them in the documentation pages on www.php.net.
>I know I have to change the connection script but apart from that?
If you define the tables in the same way as in mySql it shouldn't be too hard.
>Do I have to rewrite my code or what? Or is it all SQL and thus database
>independent...

It is more or less database independent, so unless it's a very big application, which it doesn't seem to be from your description above, you probably don't have to change much.

>Restarting would be a major pain... :-(
>2. Can I use Oracle8i to develope locally with PWS4 and WIN98.
>I downloaded the personal edition and installed it but got a lot of
>errors, then it crashed my computer and to get everything ok again I had
>to re-install windows.... man. What exactly do I need to install?
>3. I have seen a perl script which converts mysqldump files to Oracle.
>How does one actually use this?

Where did you find that script?
Oracle does have a general tool which also works with mySql databases.

>
>Any help much appreciated...

I'm programming and administrating a php/mySql site at Ericsson at the moment, wherewe originally built in support for mySql, Oracle and Sybase. On each page I start with
require "settings.php3";

in which I
define("DATA_BASE_TYPE", "mysql" );

On every page where there is any database interaction I: require "DBConnxion.php3";

which have the following code at the top: if (DATA_BASE_TYPE == "mysql")

	{
		include "myDb.php3";
	}	
	elseif ( DATA_BASE_TYPE == "sybase" )
	{
		include "sybDb.php3";
	}
	elseif ( DATA_BASE_TYPE == "oracle" )
	{
		include "oraDb.php3";
	}

The myDB.php3 file contains:
function dbConnect()

	{
		global $dbh;
		$dbh = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) 
			or die("Unable to connect to MySQL: " .

mysql_error());                 

		mysql_select_db(DB_NAME, $dbh);
	}
    

    function lockTable( $tblName)
    {

                global $dbh;

        $lockTableSql = "
            lock tables $tblName write
        ";
        
        mysql_query( $lockTableSql, $dbh)
        or die( $lockTableSql.": ". mysql_error());

    }     

    function unlockTables()
    {

        $unlockTablesSql = "
            unlock tables
        ";
        mysql_query( $unlockTablesSql, $dbh)
        or die( $unlockTablesSql.": ". mysql_error());

    }     

    function wpaDb_query( $sql, $dbh)
    {

        return mysql_query( $sql, $dbh);     }     

    function wpaDb_error()
    {

        return mysql_error();
    }     

    function wpaDb_fetch_object($res)
    {

        return mysql_fetch_object($res);     }     

    function wpaDb_errno()
    {

        return mysql_errno();
    }

and a typical function in DBConnection.php3 looks like: function getLocation($locationId)

    {

        global $dbh;
        $getLocationSql = "
            select name, description, adminId, profTextId
            from locations
            where locationId = $locationId
        ";
        
        $res = wpaDb_query( $getLocationSql, $dbh)
        or die( "$getLocationSql: " . wpaDb_error());
        
        $obj = wpaDb_fetch_object($res);
        return $obj;

    }

On the actual webpage, e.g. myAnswers.php3, I:

require "settings.php3";
require "common.php3";	// holds headers footers etc.
require "DBconxion.php3";

pageInit(TRUE, TRUE);	//The page header that comes from common.php3

if (isSet($RESTORE))

    {

        dbConnect();
        restoreAnswers( $WPA_USER);
        Header("Location: ./profile.php3");

    }

pageFooter(); //Footer comes from common.php3

---

I hope that you find this helpful. As we still havn't had the need for an Oracle database, we didn't make any oraDB.php3 file, but that is just a matter of changing the names of the direct database function calls in the myDB.php3 file and saving it into the oraDB.php3 file.

//Björn Received on Wed Apr 11 2001 - 07:16:10 CDT