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);
}
//-----------------------------------------Next: Switching Between Development, QA and Production Environments >>
More PHP Articles
More By Dante Lorenso
/ 14 
