PEAR::DB and stored procedure...
| From: | Nicolas Hoizey | Date: | Tue, 29 Jul 2003 11:42:59 +0000 |
| Subject: | PEAR::DB and stored procedure... | ||
| Groups: | php.pear.dev | ||
| Request: | Send a blank email to pear-dev+get-18875@lists.php.net to get a copy of this message | ||
Hi,
still having some problems with PEAR::DB's oci8 driver. The problem
occurs when we try to call stored procedures which have returns, these
being values or cursors.
It's because of the binding which is made with no data size (-1):
OCIBindByName($stmt, ":bind" . $i, $pdata[$i], -1)
It is fine for simple SQL queries, but not for stored procedures, for
which we need to specify the size of the returned data.
There is also a problem with cursors that have to be created (with
OCINewCursor) before the binding.
So we need to give the execute() method enough informations about what
it has to bind, which is not possible in current implementation.
Matthieu created a new executeSP() method, which source code is
bellow, but we really would like to have any feedback on this
regarding design and possible include in oci8.php.
function executeSP($stmt, $data = false)
{
$types = &$this->prepare_types[$stmt];
if (($size = sizeof($types)) != sizeof($data)) {
return $this->raiseError(DB_ERROR_MISMATCH);
}
$i = 0;
while(list($key,$value) = each($data)) {
$pdata[$key] = &$data[$key];
if ($types[$i] == DB_PARAM_OPAQUE) {
$fp = fopen($pdata[$i][1], "r");
$pdata[$i][1] = '';
if ($fp) {
while (($buf = fread($fp, 4096)) != false) {
$pdata[$i][1] .= $buf;
}
}
}
if(!isset($pdata[$key]['size'])) {
$length = -1;
} else {
$length = $pdata[$key]['size'];
}
if($pdata[$key]['type'] == 'cursor') {
$cursor = 1;
$pdata['cursor']['value'] =
OCINewCursor($this->connection);
if (!@OCIBindByName($stmt, ":bind" . $i,
$pdata['cursor']['value'], $length,
OCI_B_CURSOR)) {
return $this->oci8RaiseError($stmt);
}
} else {
if (!@OCIBindByName($stmt, ":bind" . $i,
$pdata[$key]['value'], $length)) {
return $this->oci8RaiseError($stmt);
}
}
$i++;
}
if ($this->autoCommit) {
if($cursor == 1) {
$success = @OCIExecute($stmt,
OCI_COMMIT_ON_SUCCESS);
$success = @OCIExecute($pdata['cursor']['value'],
OCI_COMMIT_ON_SUCCESS);
} else {
$success = @OCIExecute($stmt,
OCI_COMMIT_ON_SUCCESS);
}
} else {
if($cursor == 1) {
$success = @OCIExecute($stmt, OCI_DEFAULT);
$success = @OCIExecute($pdata['cursor']['value'],
OCI_DEFAULT);
} else {
$success = @OCIExecute($stmt, OCI_DEFAULT);
}
}
if (!$success) {
return $this->oci8RaiseError($stmt);
}
$this->last_stmt = $stmt;
if ($this->manip_query[(int)$stmt]) {
return DB_OK;
} else {
return new DB_result($this, $stmt);
}
}
Call it that way:
<?php
$query = "begin users.all_users(?,?,?); end;";
$params = array(
'lastname' => array ('value' => &$lastname, 'size'
=> 255),
'firstname' => array ('value' => &$firstname, 'size'
=> 255),
'cursor' => array('value' => &$c, 'type' =>
'cursor'));
$sth = $dbh->prepare($query);
$getRes = $dbh->executeSP($sth, &$params);
if ($getRes)
echo 'ALL_USERS -> OK :
'.$params['firstname']['value'].'
'.$params['lastname']['value'].'<br /><pre>';
while ($row = $c->fetchRow()) {
print_r($row);
}
?>
With the following package:
CREATE OR REPLACE PACKAGE USERS AS
TYPE rc_type IS REF CURSOR RETURN TEST%ROWTYPE;
PROCEDURE all_users (lastname OUT VARCHAR2, firstname OUT VARCHAR2, rc OUT
rc_type);
END USERS;
CREATE OR REPLACE PACKAGE BODY USERS AS
PROCEDURE all_users(lastname OUT VARCHAR2, firstname OUT VARCHAR2, rc OUT
rc_type) IS
BEGIN
lastname := 'test';
firstname := 'test';
OPEN rc FOR
SELECT * FROM test;
END;
END USERS;
Regards,
-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