Abstracting Oracle Connectivity with PHP/OCI8

Monday Nov 25th 2002 by DatabaseJournal.com Staff
Share:

Dante Lorenso presents several functions designed to help run Oracle queries without the drudgery of repetition. Take these functions, roll them into a PHP class, and voila! ... easy PHP/OCI8!

Dante Lorenso

The Problem

So, you have a huge project that you want to build. It's been determined that you want to use PHP, and your backend is Oracle. You've installed Oracle, you've installed your web server, and you've even gotten PHP to connect to your Oracle back end to do some simple queries. Now what?

If you are like most lazy programmers, you get something to work, then you look for a way to do the same thing without having to cut and paste code or retype the same thing over and over. Well, I've found that connecting to Oracle is typically done the same way over and over. So, I've written some functions to help me run some queries without the drudgery of repetition. Take these functions, roll them into a PHP class, and voila! ... easy PHP/OCI8!


Oracle How-To - The Hard Way

To do any database calls, the typical process is as follows:

  1. Connect to the database (OCILogon)
  2. Parse a SQL statement (OCIParse)
  3. Bind Variables if necessary (OCIBindByName)
  4. Execute query (OCIExecute)
  5. Fetch Results

Meanwhile, during each step in this process you need to check for errors and handle them appropriately. You might encounter an error during the OCILogon or during the OCIParse or during...well, you get the point.


Abstract the SQL Query Calls

What I've attempted to do is abstract this process into a single function prototyped as follows:


    /** PUBLIC (stmt_hndl)
     * Returns the statement handle created from the execution of the SQL
     * query.  If the statement fails, the method returns (false) and the
     * error is available in $this->ERROR.
     */
    function query($sid, $sql, &$bargs) {
    ...
    }

You may notice that this function takes three arguments: SID, SQL, and Bind Args.

SID - The SID is the Oracle database you want to connect to. You'll notice that I'm not sending the username and password. You'll see why when you read the next section on abstracting the OCILogin.

SQL - The SQL parameter is the SQL statement you want to run. This is a SELECT, INSERT, UPDATE command...whatever. You might even be calling a chunk of PL/SQL code. Regardless of the query, this function can handle it.

Bind Args - The third parameter is an array of Bind arguments. Bind arguments allow you to pass IN/OUT variables to your SQL queries and enable a syntax of doing queries that don't require you to escape all your inputs. Once I discovered the use of bind arguments, my Oracle world was forever changed.


Abstract the OCILogin (Database, Username, and Password)

Another common problem in the Oracle connectivity world is where the passwords are stored. I've seen websites where Oracle usernames and passwords were sprinkled all over the place in this script and in that class. This makes it very difficult to maintain passwords or let alone go through the nightmare of trying to change one! What I've done is create another function to act as a password vault. I store the Oracle SID, username, and password as an array of three values which can be fetched by a given key. This way any time I want to connect to the "XYZ" database, I simply ask this function for the Account database name, username and password and get them. Here is a prototype for that function:


    /** PRIVATE
     * Returns the SID, USERNAME, and PASSOWORD used to connect to a given
     * Oracle database.
     */
    function getDBAuth($sid) {
    ...
    }


Page 2: Wrapping it up into a PHP Class




Wrapping it up into a PHP Class

I've briefly pointed to a couple functions that we might want to build that will abstract some of the common Oracle connectivity issues. But the real magic starts to happen once we take these functions and roll them into a tight little ball of OCI8 and functioning code.

The idea here is that we'll create a class called 'OCIHook' that will act as our one-and-only method of talking to Oracle. If you do this, we are guaranteed that every Oracle call is done the same way and that all the logins and passwords will come out of our vault. Hopefully by using this simple API on top of the PHP built-in functionality, we'll save coding time and energy. So, let's get to it -- here is the OCI8Hook class:


//######################################################################
//##  Written by D. Dante Lorenso 
//##  Free.  Do what you want with this.  Send money if you can.
//######################################################################
class OCI8Hook {
	var $ERROR      = "";

    //----------------------------------------------------------------------
    /** PRIVATE
     * Returns the SID, USERNAME, and PASSOWORD used to connect to a given
     * Oracle database.
     */
    function getDBAuth($sid) {
        switch (strtoupper($sid)) {
        //case "DBXYZ":  return (array("usernam1",    "secret1", "DBXYZ"));
        case "DBXYZ":  return (array("usernam1",    "secret1", "TESTDB"));
        case "DBABC":  return (array("usernam2",    "secret2", "DBABC"));
        case "DB123":  return (array("usernam3",    "secret3", "DB123"));
        case "DBHJK":  return (array("usernam4",    "secret4", "DBHJK"));
        }
        
        // I don't know what to do with this host/SID.
        return(false);
    }

    //----------------------------------------------------------------------
    /** PRIVATE
     * Logs the current message in OCIError to the Apache Log file.  This is
     * done by first including an application-level error code, and the 
     * current PHP page identifier.
     */
    function dumpError($errcode, $errhndl=0) {
        // retrieve the error message...        
        $error = ($errhndl) ? OCIError($errhndl) : OCIError();

        // clean the message...
        $this->ERROR = trim($error["message"]);

        // log this error to Apache's error log
        error_log(sprintf("%s %s %s %s", 
            $errcode, $_SERVER["PHP_SELF"], $error["code"], $this->ERROR));

    }

    //----------------------------------------------------------------------
    /** PUBLIC (stmt_hndl)
     * Returns the statement handle created from the execution of the SQL
     * query.  If the statement fails, the method returns (false) and the
     * error is available in $this->ERROR.
     */
    function query($sid, $sql, &$bargs) {
        // clear any previous errors.
        $this->ERROR = "";

        // look up the username, password, and database for this SID
        $dbauth = $this->getDBAuth($sid);
        if (empty($dbauth) || ! is_array($dbauth)) {
            $this->ERROR = "Database Error(1).";
            return(false);
        }

        // connect to the database...
        $dbh = @OCILogon($dbauth[0], $dbauth[1], $dbauth[2]);
        if (! $dbh) {
            $this->dumpError("OCILogon");
            return (false);
        }

        // parse the SQL statement...
        $stmt = @OCIParse($dbh, $sql);
        if (! $stmt) {
            $this->dumpError("OCIParse", $stmt);
            return (false);
        }

        // Bind the args into the statement... (ARG[0], VALUE[1], LEN[2])
        foreach ($bargs as $barg) {
            $$barg[0] = $barg[1];
            @OCIBindByName($stmt, ":$barg[0]", $$barg[0], $barg[2]);
        }
		
        // execute sql query
        $rslt = @OCIExecute($stmt);
        if (! $rslt) {
            $this->dumpError("OCIExecute(STMT)", $stmt);
            $this->dumpError("OCIExecute(RSLT)", $rslt);
            return (false);
        }
		
        // if there are bind args, recover them...
        $r_bargs = array();
        foreach ($bargs as $barg) {
            $r_bargs[$barg[0]] = $$barg[0];
        }
        $bargs = $r_bargs;
        
        // return the sql statement handle upon success
        return ($stmt);
    }

    //----------------------------------------------------------------------
}

//######################################################################
//## END OF CLASS
//######################################################################


Page 3: How Do I Use This?




How Do I Use This?

Let's say you have a PL/SQL stored procedure in Oracle that fetches a mailing address by reading in two IN varchar values and then returns 4 OUT varchar2 values. Your procedure is defined as follows:


    PROCEDURE get_mailing_addr (
        in_comp_code    IN VARCHAR2,
        in_cust_code    IN VARCHAR2,
        line1           OUT VARCHAR2,
        line2           OUT VARCHAR2,
        csz             OUT VARCHAR2,
        zipcode         OUT VARCHAR2
    );

We can write PHP code that will act as a wrapper for this PL/SQL procedure. The PHP code will connect to the database, send its IN variables, and fetch the OUT variables into bound PHP variables. We'll ensure that all of the OUT variables are stored in a PHP array and returned to the calling function. The PHP function will be defined as follows:


    /** PUBLIC
     * Return Array containing "LINE1", "LINE2", "CSZ", and "ZIPCODE" as keys
     * upon success.
     * Returns false if database error.
     */
    function get_mailing_addr ($comp_code, $cust_code) { ... }

Now, here's what our PHP function body will look like:


    //--------------------------------------------------
    /** PUBLIC
     * Return Array containing "LINE1", "LINE2", "CSZ", and "ZIPCODE" as keys
     * upon success.
     * Returns false if database error.
     */
    function get_mailing_addr ($comp_code, $cust_code) {

        // build the query we'll be sending in...
        $sql = sprintf("
            BEGIN
            get_mailing_addr (
                :IN_COMP_CODE,
                :IN_CUST_CODE,
                :LINE1,
                :LINE2,
                :CSZ,
                :ZIPCODE);
            END;
        ");

        // Set up our Bind args...
        $bargs = array();
        array_push($bargs, array("IN_COMP_CODE", $comp_code, -1));
        array_push($bargs, array("IN_PREM_CODE", $cust_code, -1));
        array_push($bargs, array("LINE1", "", 64));
        array_push($bargs, array("LINE2", "", 64));
        array_push($bargs, array("CSZ", "", 128));
        array_push($bargs, array("ZIPCODE", "", 32));

        // run the query...
        $stmt = $this->query("DBXYZ", $sql, $bargs);
        if (! $stmt) return(false);
        
        // tidy up Line3 into CITY and STATE
        unset($bargs["IN_COMP_CODE"]);
        unset($bargs["IN_CUST_CODE"]);
        
        // return the bargs results...
        return($bargs);
    }
    //--------------------------------------------------


Where the Magic Happened

In case you missed it, the magic happened in the one line that reads:


    // run the query...
    $stmt = $this->query("DBXYZ", $sql, $bargs);
    if (! $stmt) return(false);

You'll notice that that line is called '$this->query'. Yes, that's right. The 'get_mailing_addr' function is inside another class which EXTENDS OCI8Hook! In fact, this is probably the easiest way to get this connectivity. Any time you want to create a library of PHP calls which WRAP some Oracle calls, just build a class to encapsulate all the functions into a single location, and make that class extend OCI8Hook. Suddenly you can build and run Oracle queries by simply recreating these functions. The connect, logon, bind, parse, and execute pieces of the queries are all handled for you.


Those Pretty OCIBindByName Arguments

OK, so you aren't gonna let me slide that neat trick in there without an explanation, eh? I created an array of bind variables called 'bargs' (short for bind arguments). The 'bargs' array is an array of arrays where each sub array contains:

  • 0) the bind argument name,
  • 1) the current value,
  • 2) the length of the variable.

For IN variables, you only need to define a bind argument as array("IN_NAME", $value, -1). The -1 means that the length of the variable is not going to change, so who cares.

For OUT variables, you need to define the bind args as array("OUT_NAME", "", 128) where the empty string "" is the current value, and the number 128 is the allocated space for your return data. Remember to make this a large enough value, or you won't get your output.

The cool thing about using BindByName variables is that you no longer have to do those yuck hacks like escaping quotes and things for your Oracle inputs. Also, you can now write fun little PL/SQL chunks that will do SELECT INTO and fetch your data that way.


Page 4: Examples




Examples - A Simple SELECT statement

In this example, I execute a simple SELECT statement. The input is a customer's login name, and I select out some made-up columns named COLA, COLB, and COLC from a made up table SAMPLETABLE.

A nice feature of this function is that I might get a return code of false. A 'FALSE' return code means that something failed. A failure might occur in the connect, bind, parse, execute...etc. Well, if I really cared, I could look at the $OCI8Hook->ERROR string to read that error message. Most of the time I DON'T care, though...so I give a happy error message to the user and read the Apache log files later.


    //----------------------------------------------------------------------
    /**
     * Will select a two dimensional array containing COLA then COLB
     * which should have access from this login. $data[COLA][COLB]...
     * Return:
     *      false   - System Error
     *      array   - Success.
     */
    function simple_select_example($login) {
        $sql = sprintf("
            SELECT  COLA, COLB, COLC
            FROM    SAMPLETABLE
            WHERE   LOGIN = LOWER(:IN_LOGIN)
        ");

        // Set up our Bind args...
        $bargs = array();
        array_push($bargs, array("IN_LOGIN", $login, -1));

        // run the query...
        $stmt   = $this->query("DBXYZ", $sql, $bargs);
        if (! $stmt) return(false);
        
        // loop through the returned rows and convert to a PHP array
        $data = array();
        while (@OCIFetchInto($stmt, $row, OCI_ASSOC | OCI_RETURN_NULLS)) {
            $data[$row["COLA"]][$row["COLB"]] = 1;
        }
        
        // return the data that we just fetched...
        return ($data);
    }
    //----------------------------------------------------------------------


Example - An INSERT statement with error checking

A lot of Oracle programmers want to have certainty that an insert or update operation was performed successfully. Here is an example where I wrap an INSERT statement in a chunk of PL/SQL so that I'll get a clear success code of '7' when the insert works. Remember that our OCI8Hook class always returns 'false' on error. So, if we get a FALSE from this function, we still assume an error. I need an error code which is NOT 0 or false or "" in order to ensure success. Here is my solution:


    //----------------------------------------------------------------------
    /*
     * Return
     *  0   - System Error
     *  7   - Success
     */
    function sample_insert($value1, $value2, $value3) {
        // build the query we'll be sending in...
        $sql = sprintf("
        BEGIN
            :RETURN_CODE := 0;
            INSERT INTO sampletable
                    (cola, colb, colc)
                VALUES (LOWER(:IN_VALUE1), UPPER(:IN_VALUE2), LOWER(:IN_VALUE3));
            :RETURN_CODE := 7;
        EXCEPTION
            WHEN OTHERS THEN
                :RETURN_CODE := 0;
        END;
        ");

        // Set up our Bind args...
        $bargs = array();
        array_push($bargs, array("IN_VALUE1", $value1, -1));
        array_push($bargs, array("IN_VALUE2", $value2, -1));
        array_push($bargs, array("IN_VALUE3", $value3, -1));
        array_push($bargs, array("RETURN_CODE", "", 32));

        // run the query...
        $stmt   = $this->query("DBXYZ", $sql, $bargs);
        if (! $stmt) return(false);
        
        // return the return code (if it's there)...
        if (array_key_exists("RETURN_CODE", $bargs)) {
            return ($bargs["RETURN_CODE"]);
        }

        // it doesn't seem to be working...
        return(false);
    }

    //----------------------------------------------------------------------


Page 5: Switching Between Dev, QA, and Production Environments




How to Switch Between Development, QA and Production Environments

We created that OCI8Hook class with database login and password abstraction for a reason. In many corporations, you'll have multiple environments. You'll want to build your code against the development environment, test it on a QA environment and finally, if all goes well, send it out to a production environment. Well, that's one more advantage to having your connection strings abstracted into the function getDBAuth($sid) { ... } function.

To toggle between enviroments, simply change the connect string for your DBNAME. Say I'm running all my queries against 'DBXYZ' with the following $stmt = $this->query("DBXYZ", $sql, $bargs);. Change the connect string from this:

    case "DBXYZ":  return (array("usernam1",    "secret1", "DBXYZ"));

to something like this:

    case "DBXYZ":  return (array("usernam1",    "secret1", "TESTDB"));

Or if you wanted to be really fancy, you might consider sticking an IF statement in there like this:

    case "DBXYZ":
        if (--I'm on the DEV environment--) {
            return (array("usernam1",    "secret1", "DEVDB"));
        else if (--I'm on the QA environment--) {
            return (array("usernam1",    "secret1", "TESTDB"));
        }
        else {
            return (array("usernam1",    "secret1", "PRODDB"));
        }
        ...

In Summary

The code I've shown you in this article may or may not work exactly from your cut-and-paste. However, I use this same structure for writing all the Oracle connectivity on my current projects...so I know it works. By abstracting the authentication to a single location, I can manage my application's connection strings much easier. By using the 'query' function for encapsulating all queries, I gain a significant speed up in application development time. It also helps to keep the code broken into distinct classes or 'objects'. When it comes time to hunting down problems and making enhancements, you'll be glad you abstracted the complicated portions of your PHP code so that you can concentrate on the true business logic.

Dante Lorenso, dante@lorenso.com


Back to Database Journal

Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved