Re: PEAR::DB and Oracle stored procedures
| From: | Nicolas Hoizey | Date: | Wed, 23 Jul 2003 16:24:24 +0000 |
| Subject: | Re: PEAR::DB and Oracle stored procedures | ||
| References: | 1 | Groups: | php.pear.dev |
| Request: | Send a blank email to pear-dev+get-18588@lists.php.net to get a copy of this message | ||
Hi again,
> has anyone worked on an Oracle stored procedure call support
> for PEAR::DB ?
OK, no response yet, but we've now managed to execute stored procedures that
take 'in' data but returns nothing.
The problem we still have is with stored procedures returning 'out' data.
We want for example to get the firstname and lastname of a user that has the
'foo' id.
We use (successfully) the following with native oci8 :
<?php
$db = OCILogon(...);
$query = "begin get_user('foo', :lastname, :firstname); end;";
$stmt = OCIParse($db, $query);
OCIBindByName($stmt, ":lastname", &$lastname, 20);
OCIBindByName($stmt, ":firstname", &$firstname, 20);
$res = OCIExecute($stmt, OCI_DEFAULT);
if ($res) echo $firstname.' '.$lastname;
?>
With DB, we (try to) use the following :
<?php
require_once 'DB.php';
$dbh = DB::connect(...);
$query = "begin get_user('foo', ?, ?); end;";
$sth = $dbh->prepare($query);
$res = $dbh->execute($sth, array($lastname, $firstname));
if ($res) echo $firstname.' '.$lastname;
?>
We get nothing.
We have tried to find where in DB's source it doesn't work, it's in
'DB/oci8.php', the OCIExecute() call returns 'false'. All parameters seem good,
that's very strange.
Maybe is it because we use "array($lastname, $firstname)" ...
Here is the stored procedure source :
CREATE OR REPLACE PROCEDURE GET_USER (idIn IN VARCHAR2, lastname OUT VARCHAR2,
firstname OUT VARCHAR2)
IS
lastname_temp VARCHAR2(255);
firstname_temp VARCHAR2(255);
BEGIN
SELECT
lastname,
firstname
INTO
lastname_temp,
firstname_temp
FROM test
WHERE id = idIn;
lastname := lastname_temp;
firstname := firstname_temp;
END GET_USER;
/
Any help?
-Nicolas
--
Nicolas "Brush" HOIZEY
Free PHP projects http://www.phpheaven.net
Veille tous azimuts http://www.gasteroprod.com
Clever Age http://www.clever-age.com