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

By DatabaseJournal.com Staff

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




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