« November 17th = PHP Continues, Wilbur DB Link | Main | Ebikin' »

November 28, 2005

PHP and Oracle - Running a Stored Procedure

It occurred to me that, in order for PHP to be useful at all, we needed to be able to either put it inside a stored procedure or at least be able to call a stored procedure from PHP. After a bit of messing around we determined that putting PHP inside a stored procedure is not going to work. The Apache web server does not know what to do with the PHP code. On the other hand, it IS possible to call and run a stored procedure from PHP.

We have had success in connecting to our Oracle database, running simple SQL queries, and displaying the results. The connection to the database was pretty straightforward using the db.php include file and db::connect() built-in function.

There was not much in the way of resources out there that talked about this particular problem. We did find a post that got us started, and used the oci_connect and ocilogon built-ins. These gave us fits, and we never did get these functions to work for us. We experienced a problem with the database connection, where it just would not work. Apparently, this has happened before, and there is no apparent cause for it. The connection just never gets made, no error, no nothing. Need more work on this problem before we go to a production environment.

We are getting errors in using functions like this: oci_new_collection(), but no errors in functions like this: ocilogon(), so we may have some kind of versionitis working against us.

In summary, we have learned that PHP can not be embedded within our stored procedures using our current configuration. Stored procedures can theoretically be called from PHP, but we have not settled on a reliable way to do this yet. The following is the code we have to work from for now:



require_once 'DB.php';
$dboptions = array(
'debug' => 2,
'portability' => DB_PORTABILITY_ALL,
);
$dsn='oci8://fic30:aeroace@aero.wou.edu';
$user="fic30";
$pwd="aeroace";
$sid="aero.wou.edu";
# $dbh= oci_connect($user,$pwd,$sid);
$dbh=ocilogon($user,$pwd,$sid);
$dbconn = DB::connect($dsn, $dboptions);
if (PEAR::isError($dbconn)) {die ($dbconn->getMessage());} #if we could not connect, quit the whole file and print message
else {echo "

Connection established

";}
$sql="BEGIN PHP2(:p_result_value); END;";
# $sql = "select * from um";
$stmt=OCIParse($dbh,$sql);
# $res=$dbconn->query($sql);
$result_values=oci_new_collection($dbh,"DALE_TABLE","FIC30");
OCIBindByName($stmt,"p_result_value",$result_values,-1,OCI_BNTY);
$rslt=OCIExecute($stmt);
echo "Number of elements in collection:";
print_r($result_values->size());
echo "";
echo"Results:";
print_r($result_values->getElem(0));
print_r($result_values->getElem(1));
?>

Posted by rossm at November 28, 2005 1:13 PM

Comments

Post a comment

Thanks for signing in, . Now you can comment. (sign out)

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)


Remember me?