Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Nov 25, 2002

Abstracting Oracle Connectivity with PHP/OCI8 - Page 4

By DatabaseJournal.com Staff



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




Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date