Home arrow PHP arrow Page 4 - 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 - How Do I Use This?
(Page 4 of 7 )

Let's say your have a PL/SQL stored procedure in oracle that fetches a mailing address by reading in two IN varchar values and 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 it's IN variables, and fetch the OUT variables into bound PHP variables. We'll ensure that all those 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 where it reads:

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


You'll notice that that line 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.
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