3 // +----------------------------------------------------------------------+
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1997-2002 The PHP Group |
7 // +----------------------------------------------------------------------+
8 // | This source file is subject to version 2.02 of the PHP license, |
9 // | that is bundled with this package in the file LICENSE, and is |
10 // | available at through the world-wide-web at |
11 // | http://www.php.net/license/2_02.txt. |
12 // | If you did not receive a copy of the PHP license and are unable to |
13 // | obtain it through the world-wide-web, please send a note to |
14 // | license@php.net so we can mail you a copy immediately. |
15 // +----------------------------------------------------------------------+
16 // | Author: James L. Pine <jlp@valinux.com> |
17 // +----------------------------------------------------------------------+
19 // $Id: oci8.php,v 1.2 2004-04-26 20:44:37 rurban Exp $
21 // Database independent query interface definition for PHP's Oracle 8
22 // call-interface extension.
26 // be aware... OCIError() only appears to return anything when given a
27 // statement, so functions return the generic DB_ERROR instead of more
28 // useful errors that have to do with feedback from the database.
30 // Based on DB 1.3 from the pear.php.net repository.
31 // The only modifications made have been modification of the include paths.
33 rcs_id('$Id: oci8.php,v 1.2 2004-04-26 20:44:37 rurban Exp $');
34 rcs_id('From Pear CVS: Id: oci8.php,v 1.4 2002/07/02 16:39:16 mj Exp');
36 require_once 'DB/common.php';
38 class DB_oci8 extends DB_common
43 var $phptype, $dbsyntax;
44 var $manip_query = array();
45 var $prepare_types = array();
47 var $last_stmt = false;
55 $this->phptype = 'oci8';
56 $this->dbsyntax = 'oci8';
57 $this->features = array(
60 'transactions' => true,
63 $this->errorcode_map = array(
64 900 => DB_ERROR_SYNTAX,
65 904 => DB_ERROR_NOSUCHFIELD,
66 923 => DB_ERROR_SYNTAX,
67 942 => DB_ERROR_NOSUCHTABLE,
68 955 => DB_ERROR_ALREADY_EXISTS,
69 1476 => DB_ERROR_DIVZERO,
70 1722 => DB_ERROR_INVALID_NUMBER,
71 2289 => DB_ERROR_NOSUCHTABLE,
72 2291 => DB_ERROR_CONSTRAINT,
73 2449 => DB_ERROR_CONSTRAINT,
81 * Connect to a database and log in as the specified user.
83 * @param $dsn the data source name (see DB::parseDSN for syntax)
84 * @param $persistent (optional) whether the connection should
87 * @return int DB_OK on success, a DB error code on failure
89 function connect($dsninfo, $persistent = false)
91 if (!DB::assertExtension('oci8')) {
92 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
94 $this->dsn = $dsninfo;
95 $user = $dsninfo['username'];
96 $pw = $dsninfo['password'];
97 $hostspec = $dsninfo['hostspec'];
99 $connect_function = $persistent ? 'OCIPLogon' : 'OCILogon';
102 $conn = @$connect_function($user,$pw,$hostspec);
103 } elseif ($user || $pw) {
104 $conn = @$connect_function($user,$pw);
108 if ($conn == false) {
110 $error = (is_array($error)) ? $error['message'] : null;
111 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
114 $this->connection = $conn;
122 * Log out and disconnect from the database.
124 * @return bool TRUE on success, FALSE if not connected.
126 function disconnect()
128 $ret = @OCILogOff($this->connection);
129 $this->connection = null;
137 * Send a query to oracle and return the results as an oci8 resource
140 * @param $query the SQL query
142 * @return int returns a valid oci8 result for successful SELECT
143 * queries, DB_OK for other successful queries. A DB error code
144 * is returned on failure.
146 function simpleQuery($query)
148 $this->last_query = $query;
149 $query = $this->modifyQuery($query);
150 $result = @OCIParse($this->connection, $query);
152 return $this->oci8RaiseError();
154 if ($this->autoCommit) {
155 $success = @OCIExecute($result,OCI_COMMIT_ON_SUCCESS);
157 $success = @OCIExecute($result,OCI_DEFAULT);
160 return $this->oci8RaiseError($result);
162 $this->last_stmt=$result;
163 // Determine which queries that should return data, and which
164 // should return an error code only.
165 return DB::isManip($query) ? DB_OK : $result;
172 * Move the internal oracle result pointer to the next available result
174 * @param a valid oci8 result resource
178 * @return true if a result is available otherwise return false
180 function nextResult($result)
189 * Fetch a row and return as array.
191 * @param $result oci8 result identifier
192 * @param $fetchmode how the resulting array should be indexed
194 * @return int an array on success, a DB error code on failure, NULL
195 * if there is no more data
197 function &fetchRow($result, $fetchmode = DB_FETCHMODE_DEFAULT)
199 if ($fetchmode == DB_FETCHMODE_DEFAULT) {
200 $fetchmode = $this->fetchmode;
202 if ($fetchmode & DB_FETCHMODE_ASSOC) {
203 $moredata = @OCIFetchInto($result, $row, OCI_ASSOC + OCI_RETURN_NULLS + OCI_RETURN_LOBS);
205 $moredata = @OCIFetchInto($result, $row, OCI_RETURN_NULLS + OCI_RETURN_LOBS);
217 * Fetch a row and insert the data into an existing array.
219 * @param $result oci8 result identifier
220 * @param $arr (reference) array where data from the row is stored
221 * @param $fetchmode how the array data should be indexed
222 * @param $rownum the row number to fetch (not yet supported)
224 * @return int DB_OK on success, a DB error code on failure
226 function fetchInto($result, &$arr, $fetchmode = DB_FETCHMODE_DEFAULT, $rownum=NULL)
228 if ($rownum !== NULL) {
229 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
231 if ($fetchmode & DB_FETCHMODE_ASSOC) {
232 $moredata = @OCIFetchInto($result,$arr,OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS);
233 if ($moredata && $this->options['optimize'] == 'portability') {
234 $arr = array_change_key_case($arr, CASE_LOWER);
237 $moredata = @OCIFetchInto($result,$arr,OCI_RETURN_NULLS+OCI_RETURN_LOBS);
249 * Free the internal resources associated with $result.
251 * @param $result oci8 result identifier or DB statement identifier
253 * @return bool TRUE on success, FALSE if $result is invalid
255 function freeResult($result)
257 if (is_resource($result)) {
258 return @OCIFreeStatement($result);
260 if (!isset($this->prepare_tokens[(int)$result])) {
263 unset($this->prepare_tokens[(int)$result]);
264 unset($this->prepare_types[(int)$result]);
265 unset($this->manip_query[(int)$result]);
272 function numRows($result)
274 // emulate numRows for Oracle. yuck.
275 if ($this->options['optimize'] == 'portability' &&
276 $result === $this->last_stmt) {
277 $countquery = "SELECT COUNT(*) FROM (".$this->last_query.")";
278 $save_query = $this->last_query;
279 $save_stmt = $this->last_stmt;
280 $count = $this->query($countquery);
281 if (DB::isError($count) ||
282 DB::isError($row = $count->fetchRow(DB_FETCHMODE_ORDERED)))
284 $this->last_query = $save_query;
285 $this->last_stmt = $save_stmt;
286 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
290 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
297 * Get the number of columns in a result set.
299 * @param $result oci8 result identifier
301 * @return int the number of columns per row in $result
303 function numCols($result)
305 $cols = @OCINumCols($result);
307 return $this->oci8RaiseError($result);
316 * Get the native error code of the last error (if any) that occured
317 * on the current connection. This does not work, as OCIError does
318 * not work unless given a statement. If OCIError does return
319 * something, so will this.
321 * @return int native oci8 error code
323 function errorNative()
325 if (is_resource($this->last_stmt)) {
326 $error = @OCIError($this->last_stmt);
328 $error = @OCIError($this->connection);
330 if (is_array($error)) {
331 return $error['code'];
340 * Prepares a query for multiple execution with execute(). With
341 * oci8, this is emulated.
342 * @param $query query to be prepared
344 * @return DB statement resource
346 function prepare($query)
348 $tokens = split('[\&\?]', $query);
351 for ($i = 0; $i < strlen($query); $i++) {
352 switch ($query[$i]) {
354 $types[$token++] = DB_PARAM_SCALAR;
357 $types[$token++] = DB_PARAM_OPAQUE;
361 $binds = sizeof($tokens) - 1;
363 for ($i = 0; $i < $binds; $i++) {
364 $newquery .= $tokens[$i] . ":bind" . $i;
366 $newquery .= $tokens[$i];
367 $this->last_query = $query;
368 $newquery = $this->modifyQuery($newquery);
369 $stmt = @OCIParse($this->connection, $newquery);
370 $this->prepare_types[$stmt] = $types;
371 $this->manip_query[(int)$stmt] = DB::isManip($query);
379 * Executes a DB statement prepared with prepare().
381 * @param $stmt a DB statement resource (returned from prepare())
382 * @param $data data to be used in execution of the statement
384 * @return int returns an oci8 result resource for successful
385 * SELECT queries, DB_OK for other successful queries. A DB error
386 * code is returned on failure.
388 function execute($stmt, $data = false)
390 $types=&$this->prepare_types[$stmt];
391 if (($size = sizeof($types)) != sizeof($data)) {
392 return $this->raiseError(DB_ERROR_MISMATCH);
394 for ($i = 0; $i < $size; $i++) {
395 if (is_array($data)) {
396 $pdata[$i] = &$data[$i];
401 if ($types[$i] == DB_PARAM_OPAQUE) {
402 $fp = fopen($pdata[$i], "r");
405 while (($buf = fread($fp, 4096)) != false) {
410 if (!@OCIBindByName($stmt, ":bind" . $i, $pdata[$i], -1)) {
411 return $this->oci8RaiseError($stmt);
414 if ($this->autoCommit) {
415 $success = @OCIExecute($stmt, OCI_COMMIT_ON_SUCCESS);
418 $success = @OCIExecute($stmt, OCI_DEFAULT);
421 return $this->oci8RaiseError($stmt);
423 $this->last_stmt = $stmt;
424 if ($this->manip_query[(int)$stmt]) {
427 return new DB_result($this, $stmt);
435 * Enable/disable automatic commits
437 * @param $onoff true/false whether to autocommit
439 function autoCommit($onoff = false)
441 $this->autoCommit = (bool)$onoff;;
449 * Commit transactions on the current connection
451 * @return DB_ERROR or DB_OK
455 $result = @OCICommit($this->connection);
457 return $this->oci8RaiseError();
466 * Roll back all uncommitted transactions on the current connection.
468 * @return DB_ERROR or DB_OK
472 $result = @OCIRollback($this->connection);
474 return $this->oci8RaiseError();
480 // {{{ affectedRows()
483 * Gets the number of rows affected by the last query.
484 * if the last query was a select, returns 0.
486 * @return number of rows affected by the last query or DB_ERROR
488 function affectedRows()
490 if ($this->last_stmt === false) {
491 return $this->oci8RaiseError();
493 $result = @OCIRowCount($this->last_stmt);
494 if ($result === false) {
495 return $this->oci8RaiseError($this->last_stmt);
503 function modifyQuery($query)
505 // "SELECT 2+2" must be "SELECT 2+2 FROM dual" in Oracle
506 if (preg_match('/^\s*SELECT/i', $query) &&
507 !preg_match('/\sFROM\s/i', $query)) {
508 $query .= " FROM dual";
514 // {{{ modifyLimitQuery()
517 * Emulate the row limit support altering the query
519 * @param string $query The query to treat
520 * @param int $from The row to start to fetch from
521 * @param int $count The offset
522 * @return string The modified query
524 * @author Tomas V.V.Cox <cox@idecnet.com>
526 function modifyLimitQuery($query, $from, $count)
528 // Let Oracle return the name of the columns instead of
529 // coding a "home" SQL parser
530 $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL";
531 if (!$result = OCIParse($this->connection, $q_fields)) {
532 return $this->oci8RaiseError();
534 if (!OCIExecute($result, OCI_DEFAULT)) {
535 return $this->oci8RaiseError($result);
537 $ncols = OCINumCols($result);
539 for ( $i = 1; $i <= $ncols; $i++ ) {
540 $cols[] = OCIColumnName($result, $i);
542 $fields = implode(', ', $cols);
543 // XXX Test that (tip by John Lim)
544 //if(preg_match('/^\s*SELECT\s+/is', $query, $match)) {
545 // // Introduce the FIRST_ROWS Oracle query optimizer
546 // $query = substr($query, strlen($match[0]), strlen($query));
547 // $query = "SELECT /* +FIRST_ROWS */ " . $query;
550 // Construct the query
551 // more at: http://marc.theaimsgroup.com/?l=php-db&m=99831958101212&w=2
552 // Perhaps this could be optimized with the use of Unions
553 $from += 1; // in Oracle rownum starts at 1
554 $query = "SELECT $fields FROM".
555 " (SELECT rownum as linenum, $fields FROM".
557 " WHERE rownum <= ". ($from + $count) .
558 ") WHERE linenum >= $from";
566 * Get the next value in a sequence. We emulate sequences
567 * for MySQL. Will create the sequence if it does not exist.
571 * @param $seq_name the name of the sequence
573 * @param $ondemand whether to create the sequence table on demand
576 * @return a sequence integer, or a DB error
578 function nextId($seq_name, $ondemand = true)
580 $seqname = $this->getSequenceName($seq_name);
583 $this->expectError(DB_ERROR_NOSUCHTABLE);
584 $result = $this->query("SELECT ${seqname}.nextval FROM dual");
586 if ($ondemand && DB::isError($result) &&
587 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
589 $result = $this->createSequence($seq_name);
590 if (DB::isError($result)) {
591 return $this->raiseError($result);
597 if (DB::isError($result)) {
598 return $this->raiseError($result);
600 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
605 // {{{ createSequence()
607 function createSequence($seq_name)
609 $seqname = $this->getSequenceName($seq_name);
610 return $this->query("CREATE SEQUENCE ${seqname}");
614 // {{{ dropSequence()
616 function dropSequence($seq_name)
618 $seqname = $this->getSequenceName($seq_name);
619 return $this->query("DROP SEQUENCE ${seqname}");
623 // {{{ oci8RaiseError()
625 function oci8RaiseError($errno = null)
627 if ($errno === null) {
628 $error = @OCIError($this->connection);
629 return $this->raiseError($this->errorCode($error['code']),
630 null, null, null, $error['message']);
631 } elseif (is_resource($errno)) {
632 $error = @OCIError($errno);
633 return $this->raiseError($this->errorCode($error['code']),
634 null, null, null, $error['message']);
636 return $this->raiseError($this->errorCode($errno));
640 // {{{ getSpecialQuery()
643 * Returns the query needed to get some backend info
644 * @param string $type What kind of info you want to retrieve
645 * @return string The SQL query string
647 function getSpecialQuery($type)
651 $sql = "SELECT table_name FROM user_tables";
661 function tableInfo($result, $mode = null)
666 * depending on $mode, metadata returns the following values:
668 * - mode is false (default):
670 * [0]["table"] table name
671 * [0]["name"] field name
672 * [0]["type"] field type
673 * [0]["len"] field length
674 * [0]["nullable"] field can be null (boolean)
675 * [0]["format"] field precision if NUMBER
676 * [0]["default"] field default value
678 * - mode is DB_TABLEINFO_ORDER
680 * ["num_fields"] number of fields
681 * [0]["table"] table name
682 * [0]["name"] field name
683 * [0]["type"] field type
684 * [0]["len"] field length
685 * [0]["nullable"] field can be null (boolean)
686 * [0]["format"] field precision if NUMBER
687 * [0]["default"] field default value
688 * ['order'][field name] index of field named "field name"
689 * The last one is used, if you have a field name, but no index.
690 * Test: if (isset($result['order']['myfield'])) { ...
692 * - mode is DB_TABLEINFO_ORDERTABLE
693 * the same as above. but additionally
694 * ["ordertable"][table name][field name] index of field
697 * this is, because if you have fields from different
698 * tables with the same field name * they override each
699 * other with DB_TABLEINFO_ORDER
701 * you can combine DB_TABLEINFO_ORDER and
702 * DB_TABLEINFO_ORDERTABLE with DB_TABLEINFO_ORDER |
703 * DB_TABLEINFO_ORDERTABLE * or with DB_TABLEINFO_FULL
706 // if $result is a string, we collect info for a table only
707 if (is_string($result)) {
708 $result = strtoupper($result);
709 $q_fields = "select column_name, data_type, data_length, data_precision,
710 nullable, data_default from user_tab_columns
711 where table_name='$result' order by column_id";
712 if (!$stmt = OCIParse($this->connection, $q_fields)) {
713 return $this->oci8RaiseError();
715 if (!OCIExecute($stmt, OCI_DEFAULT)) {
716 return $this->oci8RaiseError($stmt);
718 while (OCIFetch($stmt)) {
719 $res[$count]['table'] = $result;
720 $res[$count]['name'] = @OCIResult($stmt, 1);
721 $res[$count]['type'] = @OCIResult($stmt, 2);
722 $res[$count]['len'] = @OCIResult($stmt, 3);
723 $res[$count]['format'] = @OCIResult($stmt, 4);
724 $res[$count]['nullable'] = (@OCIResult($stmt, 5) == 'Y') ? true : false;
725 $res[$count]['default'] = @OCIResult($stmt, 6);
726 if ($mode & DB_TABLEINFO_ORDER) {
727 $res['order'][$res[$count]['name']] = $count;
729 if ($mode & DB_TABLEINFO_ORDERTABLE) {
730 $res['ordertable'][$res[$count]['table']][$res[$count]['name']] = $count;
734 $res['num_fields'] = $count;
735 @OCIFreeStatement($stmt);
736 } else { // else we want information about a resultset
737 if ($result === $this->last_stmt) {
738 $count = @OCINumCols($result);
739 for ($i=0; $i<$count; $i++) {
740 $res[$i]['name'] = @OCIColumnName($result, $i+1);
741 $res[$i]['type'] = @OCIColumnType($result, $i+1);
742 $res[$i]['len'] = @OCIColumnSize($result, $i+1);
744 $q_fields = "select table_name, data_precision, nullable, data_default from user_tab_columns where column_name='".$res[$i]['name']."'";
745 if (!$stmt = OCIParse($this->connection, $q_fields)) {
746 return $this->oci8RaiseError();
748 if (!OCIExecute($stmt, OCI_DEFAULT)) {
749 return $this->oci8RaiseError($stmt);
752 $res[$i]['table'] = OCIResult($stmt, 1);
753 $res[$i]['format'] = OCIResult($stmt, 2);
754 $res[$i]['nullable'] = (OCIResult($stmt, 3) == 'Y') ? true : false;
755 $res[$i]['default'] = OCIResult($stmt, 4);
756 OCIFreeStatement($stmt);
758 if ($mode & DB_TABLEINFO_ORDER) {
759 $res['order'][$res[$i]['name']] = $i;
761 if ($mode & DB_TABLEINFO_ORDERTABLE) {
762 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
765 $res['num_fields'] = $count;
768 return $this->raiseError(DB_ERROR_NOT_CAPABLE);