2 /* vim: set expandtab tabstop=4 shiftwidth=4 foldmethod=marker: */
3 // +----------------------------------------------------------------------+
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1997-2004 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 // | Maintainer: Daniel Convissor <danielc@php.net> |
18 // +----------------------------------------------------------------------+
20 // be aware... OCIError() only appears to return anything when given a
21 // statement, so functions return the generic DB_ERROR instead of more
22 // useful errors that have to do with feedback from the database.
24 require_once 'DB/common.php';
27 * Database independent query interface definition for PHP's Oracle 8
28 * call-interface extension.
30 * Definitely works with versions 8 and 9 of Oracle.
35 * @author James L. Pine <jlp@valinux.com>
37 class DB_oci8 extends DB_common
42 var $phptype, $dbsyntax;
43 var $manip_query = array();
44 var $prepare_types = array();
46 var $last_stmt = false;
49 * stores the $data passed to execute() in the oci8 driver
51 * Gets reset to array() when simpleQuery() is run.
53 * Needed in case user wants to call numRows() after prepare/execute
67 $this->phptype = 'oci8';
68 $this->dbsyntax = 'oci8';
69 $this->features = array(
72 'transactions' => true,
75 $this->errorcode_map = array(
76 1 => DB_ERROR_CONSTRAINT,
77 900 => DB_ERROR_SYNTAX,
78 904 => DB_ERROR_NOSUCHFIELD,
79 921 => DB_ERROR_SYNTAX,
80 923 => DB_ERROR_SYNTAX,
81 942 => DB_ERROR_NOSUCHTABLE,
82 955 => DB_ERROR_ALREADY_EXISTS,
83 1400 => DB_ERROR_CONSTRAINT_NOT_NULL,
84 1407 => DB_ERROR_CONSTRAINT_NOT_NULL,
85 1476 => DB_ERROR_DIVZERO,
86 1722 => DB_ERROR_INVALID_NUMBER,
87 2289 => DB_ERROR_NOSUCHTABLE,
88 2291 => DB_ERROR_CONSTRAINT,
89 2449 => DB_ERROR_CONSTRAINT,
97 * Connect to a database and log in as the specified user.
99 * @param $dsn the data source name (see DB::parseDSN for syntax)
100 * @param $persistent (optional) whether the connection should
103 * @return int DB_OK on success, a DB error code on failure
105 function connect($dsninfo, $persistent = false)
107 if (!DB::assertExtension('oci8')) {
108 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
110 $this->dsn = $dsninfo;
112 $connect_function = $persistent ? 'OCIPLogon' : 'OCILogon';
114 if ($dsninfo['hostspec']) {
115 $conn = @$connect_function($dsninfo['username'],
116 $dsninfo['password'],
117 $dsninfo['hostspec']);
118 } elseif ($dsninfo['username'] || $dsninfo['password']) {
119 $conn = @$connect_function($dsninfo['username'],
120 $dsninfo['password']);
124 if ($conn == false) {
126 $error = (is_array($error)) ? $error['message'] : null;
127 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
130 $this->connection = $conn;
138 * Log out and disconnect from the database.
140 * @return bool true on success, false if not connected.
142 function disconnect()
144 $ret = @OCILogOff($this->connection);
145 $this->connection = null;
153 * Send a query to oracle and return the results as an oci8 resource
156 * @param $query the SQL query
158 * @return int returns a valid oci8 result for successful SELECT
159 * queries, DB_OK for other successful queries. A DB error code
160 * is returned on failure.
162 function simpleQuery($query)
164 $this->_data = array();
165 $this->last_query = $query;
166 $query = $this->modifyQuery($query);
167 $result = @OCIParse($this->connection, $query);
169 return $this->oci8RaiseError();
171 if ($this->autoCommit) {
172 $success = @OCIExecute($result,OCI_COMMIT_ON_SUCCESS);
174 $success = @OCIExecute($result,OCI_DEFAULT);
177 return $this->oci8RaiseError($result);
179 $this->last_stmt=$result;
180 // Determine which queries that should return data, and which
181 // should return an error code only.
182 return DB::isManip($query) ? DB_OK : $result;
189 * Move the internal oracle result pointer to the next available result
191 * @param a valid oci8 result resource
195 * @return true if a result is available otherwise return false
197 function nextResult($result)
206 * Fetch a row and insert the data into an existing array.
208 * Formating of the array and the data therein are configurable.
209 * See DB_result::fetchInto() for more information.
211 * @param resource $result query result identifier
212 * @param array $arr (reference) array where data from the row
214 * @param int $fetchmode how the resulting array should be indexed
215 * @param int $rownum the row number to fetch
217 * @return mixed DB_OK on success, null when end of result set is
218 * reached or on failure
220 * @see DB_result::fetchInto()
223 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
225 if ($rownum !== null) {
226 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
228 if ($fetchmode & DB_FETCHMODE_ASSOC) {
229 $moredata = @OCIFetchInto($result,$arr,OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS);
230 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE &&
233 $arr = array_change_key_case($arr, CASE_LOWER);
236 $moredata = OCIFetchInto($result,$arr,OCI_RETURN_NULLS+OCI_RETURN_LOBS);
241 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
242 $this->_rtrimArrayValues($arr);
244 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
245 $this->_convertNullArrayValuesToEmpty($arr);
254 * Free the internal resources associated with $result.
256 * @param $result oci8 result identifier
258 * @return bool true on success, false if $result is invalid
260 function freeResult($result)
262 return @OCIFreeStatement($result);
266 * Free the internal resources associated with a prepared query.
268 * @param $stmt oci8 statement identifier
270 * @return bool true on success, false if $result is invalid
272 function freePrepared($stmt)
274 if (isset($this->prepare_types[(int)$stmt])) {
275 unset($this->prepare_types[(int)$stmt]);
276 unset($this->manip_query[(int)$stmt]);
286 function numRows($result)
288 // emulate numRows for Oracle. yuck.
289 if ($this->options['portability'] & DB_PORTABILITY_NUMROWS &&
290 $result === $this->last_stmt)
292 $countquery = 'SELECT COUNT(*) FROM ('.$this->last_query.')';
293 $save_query = $this->last_query;
294 $save_stmt = $this->last_stmt;
296 if (count($this->_data)) {
297 $smt = $this->prepare('SELECT COUNT(*) FROM ('.$this->last_query.')');
298 $count = $this->execute($smt, $this->_data);
300 $count =& $this->query($countquery);
303 if (DB::isError($count) ||
304 DB::isError($row = $count->fetchRow(DB_FETCHMODE_ORDERED)))
306 $this->last_query = $save_query;
307 $this->last_stmt = $save_stmt;
308 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
312 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
319 * Get the number of columns in a result set.
321 * @param $result oci8 result identifier
323 * @return int the number of columns per row in $result
325 function numCols($result)
327 $cols = @OCINumCols($result);
329 return $this->oci8RaiseError($result);
338 * Get the native error code of the last error (if any) that occured
339 * on the current connection. This does not work, as OCIError does
340 * not work unless given a statement. If OCIError does return
341 * something, so will this.
343 * @return int native oci8 error code
345 function errorNative()
347 if (is_resource($this->last_stmt)) {
348 $error = @OCIError($this->last_stmt);
350 $error = @OCIError($this->connection);
352 if (is_array($error)) {
353 return $error['code'];
362 * Prepares a query for multiple execution with execute().
364 * With oci8, this is emulated.
366 * prepare() requires a generic query as string like <code>
367 * INSERT INTO numbers VALUES (?, ?, ?)
368 * </code>. The <kbd>?</kbd> characters are placeholders.
370 * Three types of placeholders can be used:
371 * + <kbd>?</kbd> a quoted scalar value, i.e. strings, integers
372 * + <kbd>!</kbd> value is inserted 'as is'
373 * + <kbd>&</kbd> requires a file name. The file's contents get
374 * inserted into the query (i.e. saving binary
377 * Use backslashes to escape placeholder characters if you don't want
378 * them to be interpreted as placeholders. Example: <code>
379 * "UPDATE foo SET col=? WHERE col='over \& under'"
382 * @param string $query query to be prepared
383 * @return mixed DB statement resource on success. DB_Error on failure.
385 function prepare($query)
387 $tokens = preg_split('/((?<!\\\)[&?!])/', $query, -1,
388 PREG_SPLIT_DELIM_CAPTURE);
389 $binds = count($tokens) - 1;
394 foreach ($tokens as $key => $val) {
397 $types[$token++] = DB_PARAM_SCALAR;
398 unset($tokens[$key]);
401 $types[$token++] = DB_PARAM_OPAQUE;
402 unset($tokens[$key]);
405 $types[$token++] = DB_PARAM_MISC;
406 unset($tokens[$key]);
409 $tokens[$key] = preg_replace('/\\\([&?!])/', "\\1", $val);
410 if ($key != $binds) {
411 $newquery .= $tokens[$key] . ':bind' . $token;
413 $newquery .= $tokens[$key];
418 $this->last_query = $query;
419 $newquery = $this->modifyQuery($newquery);
420 if (!$stmt = @OCIParse($this->connection, $newquery)) {
421 return $this->oci8RaiseError();
423 $this->prepare_types[$stmt] = $types;
424 $this->manip_query[(int)$stmt] = DB::isManip($query);
432 * Executes a DB statement prepared with prepare().
434 * @param resource $stmt a DB statement resource returned from prepare()
435 * @param mixed $data array, string or numeric data to be used in
436 * execution of the statement. Quantity of items
437 * passed must match quantity of placeholders in
438 * query: meaning 1 for non-array items or the
439 * quantity of elements in the array.
440 * @return int returns an oci8 result resource for successful
441 * SELECT queries, DB_OK for other successful queries. A DB error
442 * code is returned on failure.
443 * @see DB_oci::prepare()
445 function &execute($stmt, $data = array())
447 if (!is_array($data)) {
448 $data = array($data);
451 $this->_data = $data;
453 $types =& $this->prepare_types[$stmt];
454 if (count($types) != count($data)) {
455 $tmp =& $this->raiseError(DB_ERROR_MISMATCH);
460 foreach ($data as $key => $value) {
461 if ($types[$i] == DB_PARAM_MISC) {
463 * Oracle doesn't seem to have the ability to pass a
464 * parameter along unchanged, so strip off quotes from start
465 * and end, plus turn two single quotes to one single quote,
466 * in order to avoid the quotes getting escaped by
467 * Oracle and ending up in the database.
469 $data[$key] = preg_replace("/^'(.*)'$/", "\\1", $data[$key]);
470 $data[$key] = str_replace("''", "'", $data[$key]);
471 } elseif ($types[$i] == DB_PARAM_OPAQUE) {
472 $fp = @fopen($data[$key], 'rb');
474 $tmp =& $this->raiseError(DB_ERROR_ACCESS_VIOLATION);
477 $data[$key] = fread($fp, filesize($data[$key]));
480 if (!@OCIBindByName($stmt, ':bind' . $i, $data[$key], -1)) {
481 $tmp = $this->oci8RaiseError($stmt);
486 if ($this->autoCommit) {
487 $success = @OCIExecute($stmt, OCI_COMMIT_ON_SUCCESS);
489 $success = @OCIExecute($stmt, OCI_DEFAULT);
492 $tmp = $this->oci8RaiseError($stmt);
495 $this->last_stmt = $stmt;
496 if ($this->manip_query[(int)$stmt]) {
499 $tmp =& new DB_result($this, $stmt);
508 * Enable/disable automatic commits
510 * @param $onoff true/false whether to autocommit
512 function autoCommit($onoff = false)
514 $this->autoCommit = (bool)$onoff;;
522 * Commit transactions on the current connection
524 * @return DB_ERROR or DB_OK
528 $result = @OCICommit($this->connection);
530 return $this->oci8RaiseError();
539 * Roll back all uncommitted transactions on the current connection.
541 * @return DB_ERROR or DB_OK
545 $result = @OCIRollback($this->connection);
547 return $this->oci8RaiseError();
553 // {{{ affectedRows()
556 * Gets the number of rows affected by the last query.
557 * if the last query was a select, returns 0.
559 * @return number of rows affected by the last query or DB_ERROR
561 function affectedRows()
563 if ($this->last_stmt === false) {
564 return $this->oci8RaiseError();
566 $result = @OCIRowCount($this->last_stmt);
567 if ($result === false) {
568 return $this->oci8RaiseError($this->last_stmt);
576 function modifyQuery($query)
578 // "SELECT 2+2" must be "SELECT 2+2 FROM dual" in Oracle
579 if (preg_match('/^\s*SELECT/i', $query) &&
580 !preg_match('/\sFROM\s/i', $query)) {
581 $query .= ' FROM dual';
587 // {{{ modifyLimitQuery()
590 * Emulate the row limit support altering the query
592 * @param string $query The query to treat
593 * @param int $from The row to start to fetch from
594 * @param int $count The offset
595 * @return string The modified query
597 * @author Tomas V.V.Cox <cox@idecnet.com>
599 function modifyLimitQuery($query, $from, $count)
601 // Let Oracle return the name of the columns instead of
602 // coding a "home" SQL parser
603 $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL";
604 if (!$result = @OCIParse($this->connection, $q_fields)) {
605 $this->last_query = $q_fields;
606 return $this->oci8RaiseError();
608 if (!@OCIExecute($result, OCI_DEFAULT)) {
609 $this->last_query = $q_fields;
610 return $this->oci8RaiseError($result);
612 $ncols = OCINumCols($result);
614 for ( $i = 1; $i <= $ncols; $i++ ) {
615 $cols[] = '"' . OCIColumnName($result, $i) . '"';
617 $fields = implode(', ', $cols);
618 // XXX Test that (tip by John Lim)
619 //if (preg_match('/^\s*SELECT\s+/is', $query, $match)) {
620 // // Introduce the FIRST_ROWS Oracle query optimizer
621 // $query = substr($query, strlen($match[0]), strlen($query));
622 // $query = "SELECT /* +FIRST_ROWS */ " . $query;
625 // Construct the query
626 // more at: http://marc.theaimsgroup.com/?l=php-db&m=99831958101212&w=2
627 // Perhaps this could be optimized with the use of Unions
628 $query = "SELECT $fields FROM".
629 " (SELECT rownum as linenum, $fields FROM".
631 ' WHERE rownum <= '. ($from + $count) .
632 ') WHERE linenum >= ' . ++$from;
640 * Returns the next free id in a sequence
642 * @param string $seq_name name of the sequence
643 * @param boolean $ondemand when true, the seqence is automatically
644 * created if it does not exist
646 * @return int the next id number in the sequence. DB_Error if problem.
649 * @see DB_common::nextID()
652 function nextId($seq_name, $ondemand = true)
654 $seqname = $this->getSequenceName($seq_name);
657 $this->expectError(DB_ERROR_NOSUCHTABLE);
658 $result =& $this->query("SELECT ${seqname}.nextval FROM dual");
660 if ($ondemand && DB::isError($result) &&
661 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
663 $result = $this->createSequence($seq_name);
664 if (DB::isError($result)) {
665 return $this->raiseError($result);
671 if (DB::isError($result)) {
672 return $this->raiseError($result);
674 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
679 * Creates a new sequence
681 * @param string $seq_name name of the new sequence
683 * @return int DB_OK on success. A DB_Error object is returned if
687 * @see DB_common::createSequence()
690 function createSequence($seq_name)
692 $seqname = $this->getSequenceName($seq_name);
693 return $this->query("CREATE SEQUENCE ${seqname}");
697 // {{{ dropSequence()
702 * @param string $seq_name name of the sequence to be deleted
704 * @return int DB_OK on success. DB_Error if problems.
707 * @see DB_common::dropSequence()
710 function dropSequence($seq_name)
712 $seqname = $this->getSequenceName($seq_name);
713 return $this->query("DROP SEQUENCE ${seqname}");
717 // {{{ oci8RaiseError()
720 * Gather information about an error, then use that info to create a
721 * DB error object and finally return that object.
723 * @param integer $errno PEAR error number (usually a DB constant) if
724 * manually raising an error
725 * @return object DB error object
726 * @see DB_common::errorCode()
727 * @see DB_common::raiseError()
729 function oci8RaiseError($errno = null)
731 if ($errno === null) {
732 $error = @OCIError($this->connection);
733 return $this->raiseError($this->errorCode($error['code']),
734 null, null, null, $error['message']);
735 } elseif (is_resource($errno)) {
736 $error = @OCIError($errno);
737 return $this->raiseError($this->errorCode($error['code']),
738 null, null, null, $error['message']);
740 return $this->raiseError($this->errorCode($errno));
744 // {{{ getSpecialQuery()
747 * Returns the query needed to get some backend info
748 * @param string $type What kind of info you want to retrieve
749 * @return string The SQL query string
751 function getSpecialQuery($type)
755 return 'SELECT table_name FROM user_tables';
765 * Returns information about a table or a result set.
767 * NOTE: only supports 'table' and 'flags' if <var>$result</var>
770 * NOTE: flags won't contain index information.
772 * @param object|string $result DB_result object from a query or a
773 * string containing the name of a table
774 * @param int $mode a valid tableInfo mode
775 * @return array an associative array with the information requested
776 * or an error object if something is wrong
779 * @see DB_common::tableInfo()
781 function tableInfo($result, $mode = null)
783 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
784 $case_func = 'strtolower';
786 $case_func = 'strval';
789 if (is_string($result)) {
791 * Probably received a table name.
792 * Create a result resource identifier.
794 $result = strtoupper($result);
795 $q_fields = 'SELECT column_name, data_type, data_length, '
797 . 'FROM user_tab_columns '
798 . "WHERE table_name='$result' ORDER BY column_id";
800 $this->last_query = $q_fields;
802 if (!$stmt = @OCIParse($this->connection, $q_fields)) {
803 return $this->oci8RaiseError(DB_ERROR_NEED_MORE_DATA);
805 if (!@OCIExecute($stmt, OCI_DEFAULT)) {
806 return $this->oci8RaiseError($stmt);
810 while (@OCIFetch($stmt)) {
811 $res[$i]['table'] = $case_func($result);
812 $res[$i]['name'] = $case_func(@OCIResult($stmt, 1));
813 $res[$i]['type'] = @OCIResult($stmt, 2);
814 $res[$i]['len'] = @OCIResult($stmt, 3);
815 $res[$i]['flags'] = (@OCIResult($stmt, 4) == 'N') ? 'not_null' : '';
817 if ($mode & DB_TABLEINFO_ORDER) {
818 $res['order'][$res[$i]['name']] = $i;
820 if ($mode & DB_TABLEINFO_ORDERTABLE) {
821 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
827 $res['num_fields'] = $i;
829 @OCIFreeStatement($stmt);
832 if (isset($result->result)) {
834 * Probably received a result object.
835 * Extract the result resource identifier.
837 $result = $result->result;
840 * ELSE, probably received a result resource identifier.
841 * Depricated. Here for compatibility only.
845 if ($result === $this->last_stmt) {
846 $count = @OCINumCols($result);
848 for ($i=0; $i<$count; $i++) {
849 $res[$i]['table'] = '';
850 $res[$i]['name'] = $case_func(@OCIColumnName($result, $i+1));
851 $res[$i]['type'] = @OCIColumnType($result, $i+1);
852 $res[$i]['len'] = @OCIColumnSize($result, $i+1);
853 $res[$i]['flags'] = '';
855 if ($mode & DB_TABLEINFO_ORDER) {
856 $res['order'][$res[$i]['name']] = $i;
858 if ($mode & DB_TABLEINFO_ORDERTABLE) {
859 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
864 $res['num_fields'] = $count;
868 return $this->raiseError(DB_ERROR_NOT_CAPABLE);