Home arrow PHP arrow Page 2 - Abstracting Oracle Connectivity with PHP/OCI8
PHP

Abstracting Oracle Connectivity with PHP/OCI8


In this article Lorenzo explains a neat utility to simplify connecting to an Oracle database using PHP/OCI8. This is sure to make DB operations between PHP and Oracle much easier and faster.

Author Info:
By: Dante Lorenso
Rating: 4 stars4 stars4 stars4 stars4 stars / 19
January 08, 2003
TABLE OF CONTENTS:
  1. · Abstracting Oracle Connectivity with PHP/OCI8
  2. · Oracle How-To The Hard Way
  3. · Wrapping it up into a PHP Class
  4. · How Do I Use This?
  5. · Those Pretty OCIBindByName Arguments
  6. · Switching Between Development, QA and Production Environments
  7. · Conclusion

print this article
SEARCH DEVARTICLES

Abstracting Oracle Connectivity with PHP/OCI8 - Oracle How-To The Hard Way
(Page 2 of 7 )

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...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...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 changed forever.

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 simple 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) {
...
}

blog comments powered by Disqus
PHP ARTICLES

- Removing Singletons in PHP
- Singletons in PHP
- Implement Facebook Javascript SDK with PHP
- Making Usage Statistics in PHP
- Installing PHP under Windows: Further Config...
- File Version Management in PHP
- Statistical View of Data in a Clustered Bar ...
- Creating a Multi-File Upload Script in PHP
- Executing Microsoft SQL Server Stored Proced...
- Code 10x More Efficiently Using Data Access ...
- A Few Tips for Speeding Up PHP Code
- The Modular Web Page
- Quick E-Commerce with PHP and PayPal
- Regression Testing With JMeter
- Building an Iterator with PHP

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials