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 // +----------------------------------------------------------------------+
23 // be aware... OCIError() only appears to return anything when given a
24 // statement, so functions return the generic DB_ERROR instead of more
25 // useful errors that have to do with feedback from the database.
28 require_once 'DB/common.php';
31 * Database independent query interface definition for PHP's Oracle 8
32 * call-interface extension.
34 * Definitely works with versions 8 and 9 of Oracle.
39 * @author James L. Pine <jlp@valinux.com>
41 class DB_oci8 extends DB_common
46 var $phptype, $dbsyntax;
47 var $manip_query = array();
48 var $prepare_types = array();
50 var $last_stmt = false;
53 * stores the $data passed to execute() in the oci8 driver
55 * Gets reset to array() when simpleQuery() is run.
57 * Needed in case user wants to call numRows() after prepare/execute
71 $this->phptype = 'oci8';
72 $this->dbsyntax = 'oci8';
73 $this->features = array(
76 'transactions' => true,
79 $this->errorcode_map = array(
80 1 => DB_ERROR_CONSTRAINT,
81 900 => DB_ERROR_SYNTAX,
82 904 => DB_ERROR_NOSUCHFIELD,
83 921 => DB_ERROR_SYNTAX,
84 923 => DB_ERROR_SYNTAX,
85 942 => DB_ERROR_NOSUCHTABLE,
86 955 => DB_ERROR_ALREADY_EXISTS,
87 1400 => DB_ERROR_CONSTRAINT_NOT_NULL,
88 1407 => DB_ERROR_CONSTRAINT_NOT_NULL,
89 1476 => DB_ERROR_DIVZERO,
90 1722 => DB_ERROR_INVALID_NUMBER,
91 2289 => DB_ERROR_NOSUCHTABLE,
92 2291 => DB_ERROR_CONSTRAINT,
93 2449 => DB_ERROR_CONSTRAINT,
101 * Connect to a database and log in as the specified user.
103 * @param $dsn the data source name (see DB::parseDSN for syntax)
104 * @param $persistent (optional) whether the connection should
107 * @return int DB_OK on success, a DB error code on failure
109 function connect($dsninfo, $persistent = false)
111 if (!DB::assertExtension('oci8')) {
112 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
114 $this->dsn = $dsninfo;
116 $connect_function = $persistent ? 'OCIPLogon' : 'OCILogon';
118 if ($dsninfo['hostspec']) {
119 $conn = @$connect_function($dsninfo['username'],
120 $dsninfo['password'],
121 $dsninfo['hostspec']);
122 } elseif ($dsninfo['username'] || $dsninfo['password']) {
123 $conn = @$connect_function($dsninfo['username'],
124 $dsninfo['password']);
128 if ($conn == false) {
130 $error = (is_array($error)) ? $error['message'] : null;
131 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
134 $this->connection = $conn;
142 * Log out and disconnect from the database.
144 * @return bool true on success, false if not connected.
146 function disconnect()
148 $ret = @OCILogOff($this->connection);
149 $this->connection = null;
157 * Send a query to oracle and return the results as an oci8 resource
160 * @param $query the SQL query
162 * @return int returns a valid oci8 result for successful SELECT
163 * queries, DB_OK for other successful queries. A DB error code
164 * is returned on failure.
166 function simpleQuery($query)
168 $this->_data = array();
169 $this->last_query = $query;
170 $query = $this->modifyQuery($query);
171 $result = @OCIParse($this->connection, $query);
173 return $this->oci8RaiseError();
175 if ($this->autoCommit) {
176 $success = @OCIExecute($result,OCI_COMMIT_ON_SUCCESS);
178 $success = @OCIExecute($result,OCI_DEFAULT);
181 return $this->oci8RaiseError($result);
183 $this->last_stmt=$result;
184 // Determine which queries that should return data, and which
185 // should return an error code only.
186 return DB::isManip($query) ? DB_OK : $result;
193 * Move the internal oracle result pointer to the next available result
195 * @param a valid oci8 result resource
199 * @return true if a result is available otherwise return false
201 function nextResult($result)
210 * Fetch a row and insert the data into an existing array.
212 * Formating of the array and the data therein are configurable.
213 * See DB_result::fetchInto() for more information.
215 * @param resource $result query result identifier
216 * @param array $arr (reference) array where data from the row
218 * @param int $fetchmode how the resulting array should be indexed
219 * @param int $rownum the row number to fetch
221 * @return mixed DB_OK on success, null when end of result set is
222 * reached or on failure
224 * @see DB_result::fetchInto()
227 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
229 if ($rownum !== null) {
230 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
232 if ($fetchmode & DB_FETCHMODE_ASSOC) {
233 $moredata = @OCIFetchInto($result,$arr,OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS);
234 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE &&
237 $arr = array_change_key_case($arr, CASE_LOWER);
240 $moredata = OCIFetchInto($result,$arr,OCI_RETURN_NULLS+OCI_RETURN_LOBS);
245 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
246 $this->_rtrimArrayValues($arr);
248 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
249 $this->_convertNullArrayValuesToEmpty($arr);
258 * Free the internal resources associated with $result.
260 * @param $result oci8 result identifier
262 * @return bool true on success, false if $result is invalid
264 function freeResult($result)
266 return @OCIFreeStatement($result);
270 * Free the internal resources associated with a prepared query.
272 * @param $stmt oci8 statement identifier
274 * @return bool true on success, false if $result is invalid
276 function freePrepared($stmt)
278 if (isset($this->prepare_types[(int)$stmt])) {
279 unset($this->prepare_types[(int)$stmt]);
280 unset($this->manip_query[(int)$stmt]);
290 function numRows($result)
292 // emulate numRows for Oracle. yuck.
293 if ($this->options['portability'] & DB_PORTABILITY_NUMROWS &&
294 $result === $this->last_stmt)
296 $countquery = 'SELECT COUNT(*) FROM ('.$this->last_query.')';
297 $save_query = $this->last_query;
298 $save_stmt = $this->last_stmt;
300 if (count($this->_data)) {
301 $smt = $this->prepare('SELECT COUNT(*) FROM ('.$this->last_query.')');
302 $count = $this->execute($smt, $this->_data);
304 $count =& $this->query($countquery);
307 if (DB::isError($count) ||
308 DB::isError($row = $count->fetchRow(DB_FETCHMODE_ORDERED)))
310 $this->last_query = $save_query;
311 $this->last_stmt = $save_stmt;
312 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
316 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
323 * Get the number of columns in a result set.
325 * @param $result oci8 result identifier
327 * @return int the number of columns per row in $result
329 function numCols($result)
331 $cols = @OCINumCols($result);
333 return $this->oci8RaiseError($result);
342 * Get the native error code of the last error (if any) that occured
343 * on the current connection. This does not work, as OCIError does
344 * not work unless given a statement. If OCIError does return
345 * something, so will this.
347 * @return int native oci8 error code
349 function errorNative()
351 if (is_resource($this->last_stmt)) {
352 $error = @OCIError($this->last_stmt);
354 $error = @OCIError($this->connection);
356 if (is_array($error)) {
357 return $error['code'];
366 * Prepares a query for multiple execution with execute().
368 * With oci8, this is emulated.
370 * prepare() requires a generic query as string like <code>
371 * INSERT INTO numbers VALUES (?, ?, ?)
372 * </code>. The <kbd>?</kbd> characters are placeholders.
374 * Three types of placeholders can be used:
375 * + <kbd>?</kbd> a quoted scalar value, i.e. strings, integers
376 * + <kbd>!</kbd> value is inserted 'as is'
377 * + <kbd>&</kbd> requires a file name. The file's contents get
378 * inserted into the query (i.e. saving binary
381 * Use backslashes to escape placeholder characters if you don't want
382 * them to be interpreted as placeholders. Example: <code>
383 * "UPDATE foo SET col=? WHERE col='over \& under'"
386 * @param string $query query to be prepared
387 * @return mixed DB statement resource on success. DB_Error on failure.
389 function prepare($query)
391 $tokens = preg_split('/((?<!\\\)[&?!])/', $query, -1,
392 PREG_SPLIT_DELIM_CAPTURE);
393 $binds = count($tokens) - 1;
398 foreach ($tokens as $key => $val) {
401 $types[$token++] = DB_PARAM_SCALAR;
402 unset($tokens[$key]);
405 $types[$token++] = DB_PARAM_OPAQUE;
406 unset($tokens[$key]);
409 $types[$token++] = DB_PARAM_MISC;
410 unset($tokens[$key]);
413 $tokens[$key] = preg_replace('/\\\([&?!])/', "\\1", $val);
414 if ($key != $binds) {
415 $newquery .= $tokens[$key] . ':bind' . $token;
417 $newquery .= $tokens[$key];
422 $this->last_query = $query;
423 $newquery = $this->modifyQuery($newquery);
424 if (!$stmt = @OCIParse($this->connection, $newquery)) {
425 return $this->oci8RaiseError();
427 $this->prepare_types[$stmt] = $types;
428 $this->manip_query[(int)$stmt] = DB::isManip($query);
436 * Executes a DB statement prepared with prepare().
438 * @param resource $stmt a DB statement resource returned from prepare()
439 * @param mixed $data array, string or numeric data to be used in
440 * execution of the statement. Quantity of items
441 * passed must match quantity of placeholders in
442 * query: meaning 1 for non-array items or the
443 * quantity of elements in the array.
444 * @return int returns an oci8 result resource for successful
445 * SELECT queries, DB_OK for other successful queries. A DB error
446 * code is returned on failure.
447 * @see DB_oci::prepare()
449 function &execute($stmt, $data = array())
451 if (!is_array($data)) {
452 $data = array($data);
455 $this->_data = $data;
457 $types =& $this->prepare_types[$stmt];
458 if (count($types) != count($data)) {
459 $tmp =& $this->raiseError(DB_ERROR_MISMATCH);
464 foreach ($data as $key => $value) {
465 if ($types[$i] == DB_PARAM_MISC) {
467 * Oracle doesn't seem to have the ability to pass a
468 * parameter along unchanged, so strip off quotes from start
469 * and end, plus turn two single quotes to one single quote,
470 * in order to avoid the quotes getting escaped by
471 * Oracle and ending up in the database.
473 $data[$key] = preg_replace("/^'(.*)'$/", "\\1", $data[$key]);
474 $data[$key] = str_replace("''", "'", $data[$key]);
475 } elseif ($types[$i] == DB_PARAM_OPAQUE) {
476 $fp = @fopen($data[$key], 'rb');
478 $tmp =& $this->raiseError(DB_ERROR_ACCESS_VIOLATION);
481 $data[$key] = fread($fp, filesize($data[$key]));
484 if (!@OCIBindByName($stmt, ':bind' . $i, $data[$key], -1)) {
485 $tmp = $this->oci8RaiseError($stmt);
490 if ($this->autoCommit) {
491 $success = @OCIExecute($stmt, OCI_COMMIT_ON_SUCCESS);
493 $success = @OCIExecute($stmt, OCI_DEFAULT);
496 $tmp = $this->oci8RaiseError($stmt);
499 $this->last_stmt = $stmt;
500 if ($this->manip_query[(int)$stmt]) {
503 $tmp =& new DB_result($this, $stmt);
512 * Enable/disable automatic commits
514 * @param $onoff true/false whether to autocommit
516 function autoCommit($onoff = false)
518 $this->autoCommit = (bool)$onoff;;
526 * Commit transactions on the current connection
528 * @return DB_ERROR or DB_OK
532 $result = @OCICommit($this->connection);
534 return $this->oci8RaiseError();
543 * Roll back all uncommitted transactions on the current connection.
545 * @return DB_ERROR or DB_OK
549 $result = @OCIRollback($this->connection);
551 return $this->oci8RaiseError();
557 // {{{ affectedRows()
560 * Gets the number of rows affected by the last query.
561 * if the last query was a select, returns 0.
563 * @return number of rows affected by the last query or DB_ERROR
565 function affectedRows()
567 if ($this->last_stmt === false) {
568 return $this->oci8RaiseError();
570 $result = @OCIRowCount($this->last_stmt);
571 if ($result === false) {
572 return $this->oci8RaiseError($this->last_stmt);
580 function modifyQuery($query)
582 // "SELECT 2+2" must be "SELECT 2+2 FROM dual" in Oracle
583 if (preg_match('/^\s*SELECT/i', $query) &&
584 !preg_match('/\sFROM\s/i', $query)) {
585 $query .= ' FROM dual';
591 // {{{ modifyLimitQuery()
594 * Emulate the row limit support altering the query
596 * @param string $query The query to treat
597 * @param int $from The row to start to fetch from
598 * @param int $count The offset
599 * @return string The modified query
601 * @author Tomas V.V.Cox <cox@idecnet.com>
603 function modifyLimitQuery($query, $from, $count)
605 // Let Oracle return the name of the columns instead of
606 // coding a "home" SQL parser
607 $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL";
608 if (!$result = @OCIParse($this->connection, $q_fields)) {
609 $this->last_query = $q_fields;
610 return $this->oci8RaiseError();
612 if (!@OCIExecute($result, OCI_DEFAULT)) {
613 $this->last_query = $q_fields;
614 return $this->oci8RaiseError($result);
616 $ncols = OCINumCols($result);
618 for ( $i = 1; $i <= $ncols; $i++ ) {
619 $cols[] = '"' . OCIColumnName($result, $i) . '"';
621 $fields = implode(', ', $cols);
622 // XXX Test that (tip by John Lim)
623 //if (preg_match('/^\s*SELECT\s+/is', $query, $match)) {
624 // // Introduce the FIRST_ROWS Oracle query optimizer
625 // $query = substr($query, strlen($match[0]), strlen($query));
626 // $query = "SELECT /* +FIRST_ROWS */ " . $query;
629 // Construct the query
630 // more at: http://marc.theaimsgroup.com/?l=php-db&m=99831958101212&w=2
631 // Perhaps this could be optimized with the use of Unions
632 $query = "SELECT $fields FROM".
633 " (SELECT rownum as linenum, $fields FROM".
635 ' WHERE rownum <= '. ($from + $count) .
636 ') WHERE linenum >= ' . ++$from;
644 * Returns the next free id in a sequence
646 * @param string $seq_name name of the sequence
647 * @param boolean $ondemand when true, the seqence is automatically
648 * created if it does not exist
650 * @return int the next id number in the sequence. DB_Error if problem.
653 * @see DB_common::nextID()
656 function nextId($seq_name, $ondemand = true)
658 $seqname = $this->getSequenceName($seq_name);
661 $this->expectError(DB_ERROR_NOSUCHTABLE);
662 $result =& $this->query("SELECT ${seqname}.nextval FROM dual");
664 if ($ondemand && DB::isError($result) &&
665 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
667 $result = $this->createSequence($seq_name);
668 if (DB::isError($result)) {
669 return $this->raiseError($result);
675 if (DB::isError($result)) {
676 return $this->raiseError($result);
678 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
683 * Creates a new sequence
685 * @param string $seq_name name of the new sequence
687 * @return int DB_OK on success. A DB_Error object is returned if
691 * @see DB_common::createSequence()
694 function createSequence($seq_name)
696 $seqname = $this->getSequenceName($seq_name);
697 return $this->query("CREATE SEQUENCE ${seqname}");
701 // {{{ dropSequence()
706 * @param string $seq_name name of the sequence to be deleted
708 * @return int DB_OK on success. DB_Error if problems.
711 * @see DB_common::dropSequence()
714 function dropSequence($seq_name)
716 $seqname = $this->getSequenceName($seq_name);
717 return $this->query("DROP SEQUENCE ${seqname}");
721 // {{{ oci8RaiseError()
724 * Gather information about an error, then use that info to create a
725 * DB error object and finally return that object.
727 * @param integer $errno PEAR error number (usually a DB constant) if
728 * manually raising an error
729 * @return object DB error object
730 * @see DB_common::errorCode()
731 * @see DB_common::raiseError()
733 function oci8RaiseError($errno = null)
735 if ($errno === null) {
736 $error = @OCIError($this->connection);
737 return $this->raiseError($this->errorCode($error['code']),
738 null, null, null, $error['message']);
739 } elseif (is_resource($errno)) {
740 $error = @OCIError($errno);
741 return $this->raiseError($this->errorCode($error['code']),
742 null, null, null, $error['message']);
744 return $this->raiseError($this->errorCode($errno));
748 // {{{ getSpecialQuery()
751 * Returns the query needed to get some backend info
752 * @param string $type What kind of info you want to retrieve
753 * @return string The SQL query string
755 function getSpecialQuery($type)
759 return 'SELECT table_name FROM user_tables';
769 * Returns information about a table or a result set.
771 * NOTE: only supports 'table' and 'flags' if <var>$result</var>
774 * NOTE: flags won't contain index information.
776 * @param object|string $result DB_result object from a query or a
777 * string containing the name of a table
778 * @param int $mode a valid tableInfo mode
779 * @return array an associative array with the information requested
780 * or an error object if something is wrong
783 * @see DB_common::tableInfo()
785 function tableInfo($result, $mode = null)
787 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
788 $case_func = 'strtolower';
790 $case_func = 'strval';
793 if (is_string($result)) {
795 * Probably received a table name.
796 * Create a result resource identifier.
798 $result = strtoupper($result);
799 $q_fields = 'SELECT column_name, data_type, data_length, '
801 . 'FROM user_tab_columns '
802 . "WHERE table_name='$result' ORDER BY column_id";
804 $this->last_query = $q_fields;
806 if (!$stmt = @OCIParse($this->connection, $q_fields)) {
807 return $this->oci8RaiseError(DB_ERROR_NEED_MORE_DATA);
809 if (!@OCIExecute($stmt, OCI_DEFAULT)) {
810 return $this->oci8RaiseError($stmt);
814 while (@OCIFetch($stmt)) {
815 $res[$i]['table'] = $case_func($result);
816 $res[$i]['name'] = $case_func(@OCIResult($stmt, 1));
817 $res[$i]['type'] = @OCIResult($stmt, 2);
818 $res[$i]['len'] = @OCIResult($stmt, 3);
819 $res[$i]['flags'] = (@OCIResult($stmt, 4) == 'N') ? 'not_null' : '';
821 if ($mode & DB_TABLEINFO_ORDER) {
822 $res['order'][$res[$i]['name']] = $i;
824 if ($mode & DB_TABLEINFO_ORDERTABLE) {
825 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
831 $res['num_fields'] = $i;
833 @OCIFreeStatement($stmt);
836 if (isset($result->result)) {
838 * Probably received a result object.
839 * Extract the result resource identifier.
841 $result = $result->result;
844 * ELSE, probably received a result resource identifier.
845 * Depricated. Here for compatibility only.
849 if ($result === $this->last_stmt) {
850 $count = @OCINumCols($result);
852 for ($i=0; $i<$count; $i++) {
853 $res[$i]['table'] = '';
854 $res[$i]['name'] = $case_func(@OCIColumnName($result, $i+1));
855 $res[$i]['type'] = @OCIColumnType($result, $i+1);
856 $res[$i]['len'] = @OCIColumnSize($result, $i+1);
857 $res[$i]['flags'] = '';
859 if ($mode & DB_TABLEINFO_ORDER) {
860 $res['order'][$res[$i]['name']] = $i;
862 if ($mode & DB_TABLEINFO_ORDERTABLE) {
863 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
868 $res['num_fields'] = $count;
872 return $this->raiseError(DB_ERROR_NOT_CAPABLE);