Home arrow PHP arrow Page 5 - 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 - Those Pretty OCIBindByName Arguments
(Page 5 of 7 )

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 gonna 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.

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 can 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 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);
}

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

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