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 // +----------------------------------------------------------------------+
22 // be aware... OCIError() only appears to return anything when given a
23 // statement, so functions return the generic DB_ERROR instead of more
24 // useful errors that have to do with feedback from the database.
26 require_once 'DB/common.php';
29 * Database independent query interface definition for PHP's Oracle 8
30 * call-interface extension.
32 * Definitely works with versions 8 and 9 of Oracle.
37 * @author James L. Pine <jlp@valinux.com>
39 class DB_oci8 extends DB_common
44 var $phptype, $dbsyntax;
45 var $manip_query = array();
46 var $prepare_types = array();
48 var $last_stmt = false;
51 * stores the $data passed to execute() in the oci8 driver
53 * Gets reset to array() when simpleQuery() is run.
55 * Needed in case user wants to call numRows() after prepare/execute
69 $this->phptype = 'oci8';
70 $this->dbsyntax = 'oci8';
71 $this->features = array(
74 'transactions' => true,
77 $this->errorcode_map = array(
78 1 => DB_ERROR_CONSTRAINT,
79 900 => DB_ERROR_SYNTAX,
80 904 => DB_ERROR_NOSUCHFIELD,
81 921 => DB_ERROR_SYNTAX,
82 923 => DB_ERROR_SYNTAX,
83 942 => DB_ERROR_NOSUCHTABLE,
84 955 => DB_ERROR_ALREADY_EXISTS,
85 1400 => DB_ERROR_CONSTRAINT_NOT_NULL,
86 1407 => DB_ERROR_CONSTRAINT_NOT_NULL,
87 1476 => DB_ERROR_DIVZERO,
88 1722 => DB_ERROR_INVALID_NUMBER,
89 2289 => DB_ERROR_NOSUCHTABLE,
90 2291 => DB_ERROR_CONSTRAINT,
91 2449 => DB_ERROR_CONSTRAINT,
99 * Connect to a database and log in as the specified user.
101 * @param $dsn the data source name (see DB::parseDSN for syntax)
102 * @param $persistent (optional) whether the connection should
105 * @return int DB_OK on success, a DB error code on failure
107 function connect($dsninfo, $persistent = false)
109 if (!DB::assertExtension('oci8')) {
110 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
112 $this->dsn = $dsninfo;
114 $connect_function = $persistent ? 'OCIPLogon' : 'OCILogon';
116 if ($dsninfo['hostspec']) {
117 $conn = @$connect_function($dsninfo['username'],
118 $dsninfo['password'],
119 $dsninfo['hostspec']);
120 } elseif ($dsninfo['username'] || $dsninfo['password']) {
121 $conn = @$connect_function($dsninfo['username'],
122 $dsninfo['password']);
126 if ($conn == false) {
128 $error = (is_array($error)) ? $error['message'] : null;
129 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
132 $this->connection = $conn;
140 * Log out and disconnect from the database.
142 * @return bool true on success, false if not connected.
144 function disconnect()
146 $ret = @OCILogOff($this->connection);
147 $this->connection = null;
155 * Send a query to oracle and return the results as an oci8 resource
158 * @param $query the SQL query
160 * @return int returns a valid oci8 result for successful SELECT
161 * queries, DB_OK for other successful queries. A DB error code
162 * is returned on failure.
164 function simpleQuery($query)
166 $this->_data = array();
167 $this->last_query = $query;
168 $query = $this->modifyQuery($query);
169 $result = @OCIParse($this->connection, $query);
171 return $this->oci8RaiseError();
173 if ($this->autoCommit) {
174 $success = @OCIExecute($result,OCI_COMMIT_ON_SUCCESS);
176 $success = @OCIExecute($result,OCI_DEFAULT);
179 return $this->oci8RaiseError($result);
181 $this->last_stmt=$result;
182 // Determine which queries that should return data, and which
183 // should return an error code only.
184 return DB::isManip($query) ? DB_OK : $result;
191 * Move the internal oracle result pointer to the next available result
193 * @param a valid oci8 result resource
197 * @return true if a result is available otherwise return false
199 function nextResult($result)
208 * Fetch a row and insert the data into an existing array.
210 * Formating of the array and the data therein are configurable.
211 * See DB_result::fetchInto() for more information.
213 * @param resource $result query result identifier
214 * @param array $arr (reference) array where data from the row
216 * @param int $fetchmode how the resulting array should be indexed
217 * @param int $rownum the row number to fetch
219 * @return mixed DB_OK on success, null when end of result set is
220 * reached or on failure
222 * @see DB_result::fetchInto()
225 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
227 if ($rownum !== null) {
228 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
230 if ($fetchmode & DB_FETCHMODE_ASSOC) {
231 $moredata = @OCIFetchInto($result,$arr,OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS);
232 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE &&
235 $arr = array_change_key_case($arr, CASE_LOWER);
238 $moredata = OCIFetchInto($result,$arr,OCI_RETURN_NULLS+OCI_RETURN_LOBS);
243 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
244 $this->_rtrimArrayValues($arr);
246 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
247 $this->_convertNullArrayValuesToEmpty($arr);
256 * Free the internal resources associated with $result.
258 * @param $result oci8 result identifier
260 * @return bool true on success, false if $result is invalid
262 function freeResult($result)
264 return @OCIFreeStatement($result);
268 * Free the internal resources associated with a prepared query.
270 * @param $stmt oci8 statement identifier
272 * @return bool true on success, false if $result is invalid
274 function freePrepared($stmt)
276 if (isset($this->prepare_types[(int)$stmt])) {
277 unset($this->prepare_types[(int)$stmt]);
278 unset($this->manip_query[(int)$stmt]);
288 function numRows($result)
290 // emulate numRows for Oracle. yuck.
291 if ($this->options['portability'] & DB_PORTABILITY_NUMROWS &&
292 $result === $this->last_stmt)
294 $countquery = 'SELECT COUNT(*) FROM ('.$this->last_query.')';
295 $save_query = $this->last_query;
296 $save_stmt = $this->last_stmt;
298 if (count($this->_data)) {
299 $smt = $this->prepare('SELECT COUNT(*) FROM ('.$this->last_query.')');
300 $count = $this->execute($smt, $this->_data);
302 $count =& $this->query($countquery);
305 if (DB::isError($count) ||
306 DB::isError($row = $count->fetchRow(DB_FETCHMODE_ORDERED)))
308 $this->last_query = $save_query;
309 $this->last_stmt = $save_stmt;
310 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
314 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
321 * Get the number of columns in a result set.
323 * @param $result oci8 result identifier
325 * @return int the number of columns per row in $result
327 function numCols($result)
329 $cols = @OCINumCols($result);
331 return $this->oci8RaiseError($result);
340 * Get the native error code of the last error (if any) that occured
341 * on the current connection. This does not work, as OCIError does
342 * not work unless given a statement. If OCIError does return
343 * something, so will this.
345 * @return int native oci8 error code
347 function errorNative()
349 if (is_resource($this->last_stmt)) {
350 $error = @OCIError($this->last_stmt);
352 $error = @OCIError($this->connection);
354 if (is_array($error)) {
355 return $error['code'];
364 * Prepares a query for multiple execution with execute().
366 * With oci8, this is emulated.
368 * prepare() requires a generic query as string like <code>
369 * INSERT INTO numbers VALUES (?, ?, ?)
370 * </code>. The <kbd>?</kbd> characters are placeholders.
372 * Three types of placeholders can be used:
373 * + <kbd>?</kbd> a quoted scalar value, i.e. strings, integers
374 * + <kbd>!</kbd> value is inserted 'as is'
375 * + <kbd>&</kbd> requires a file name. The file's contents get
376 * inserted into the query (i.e. saving binary
379 * Use backslashes to escape placeholder characters if you don't want
380 * them to be interpreted as placeholders. Example: <code>
381 * "UPDATE foo SET col=? WHERE col='over \& under'"
384 * @param string $query query to be prepared
385 * @return mixed DB statement resource on success. DB_Error on failure.
387 function prepare($query)
389 $tokens = preg_split('/((?<!\\\)[&?!])/', $query, -1,
390 PREG_SPLIT_DELIM_CAPTURE);
391 $binds = count($tokens) - 1;
396 foreach ($tokens as $key => $val) {
399 $types[$token++] = DB_PARAM_SCALAR;
400 unset($tokens[$key]);
403 $types[$token++] = DB_PARAM_OPAQUE;
404 unset($tokens[$key]);
407 $types[$token++] = DB_PARAM_MISC;
408 unset($tokens[$key]);
411 $tokens[$key] = preg_replace('/\\\([&?!])/', "\\1", $val);
412 if ($key != $binds) {
413 $newquery .= $tokens[$key] . ':bind' . $token;
415 $newquery .= $tokens[$key];
420 $this->last_query = $query;
421 $newquery = $this->modifyQuery($newquery);
422 if (!$stmt = @OCIParse($this->connection, $newquery)) {
423 return $this->oci8RaiseError();
425 $this->prepare_types[$stmt] = $types;
426 $this->manip_query[(int)$stmt] = DB::isManip($query);
434 * Executes a DB statement prepared with prepare().
436 * @param resource $stmt a DB statement resource returned from prepare()
437 * @param mixed $data array, string or numeric data to be used in
438 * execution of the statement. Quantity of items
439 * passed must match quantity of placeholders in
440 * query: meaning 1 for non-array items or the
441 * quantity of elements in the array.
442 * @return int returns an oci8 result resource for successful
443 * SELECT queries, DB_OK for other successful queries. A DB error
444 * code is returned on failure.
445 * @see DB_oci::prepare()
447 function &execute($stmt, $data = array())
449 if (!is_array($data)) {
450 $data = array($data);
453 $this->_data = $data;
455 $types =& $this->prepare_types[$stmt];
456 if (count($types) != count($data)) {
457 $tmp =& $this->raiseError(DB_ERROR_MISMATCH);
462 foreach ($data as $key => $value) {
463 if ($types[$i] == DB_PARAM_MISC) {
465 * Oracle doesn't seem to have the ability to pass a
466 * parameter along unchanged, so strip off quotes from start
467 * and end, plus turn two single quotes to one single quote,
468 * in order to avoid the quotes getting escaped by
469 * Oracle and ending up in the database.
471 $data[$key] = preg_replace("/^'(.*)'$/", "\\1", $data[$key]);
472 $data[$key] = str_replace("''", "'", $data[$key]);
473 } elseif ($types[$i] == DB_PARAM_OPAQUE) {
474 $fp = @fopen($data[$key], 'rb');
476 $tmp =& $this->raiseError(DB_ERROR_ACCESS_VIOLATION);
479 $data[$key] = fread($fp, filesize($data[$key]));
482 if (!@OCIBindByName($stmt, ':bind' . $i, $data[$key], -1)) {
483 $tmp = $this->oci8RaiseError($stmt);
488 if ($this->autoCommit) {
489 $success = @OCIExecute($stmt, OCI_COMMIT_ON_SUCCESS);
491 $success = @OCIExecute($stmt, OCI_DEFAULT);
494 $tmp = $this->oci8RaiseError($stmt);
497 $this->last_stmt = $stmt;
498 if ($this->manip_query[(int)$stmt]) {
501 $tmp =& new DB_result($this, $stmt);
510 * Enable/disable automatic commits
512 * @param $onoff true/false whether to autocommit
514 function autoCommit($onoff = false)
516 $this->autoCommit = (bool)$onoff;;
524 * Commit transactions on the current connection
526 * @return DB_ERROR or DB_OK
530 $result = @OCICommit($this->connection);
532 return $this->oci8RaiseError();
541 * Roll back all uncommitted transactions on the current connection.
543 * @return DB_ERROR or DB_OK
547 $result = @OCIRollback($this->connection);
549 return $this->oci8RaiseError();
555 // {{{ affectedRows()
558 * Gets the number of rows affected by the last query.
559 * if the last query was a select, returns 0.
561 * @return number of rows affected by the last query or DB_ERROR
563 function affectedRows()
565 if ($this->last_stmt === false) {
566 return $this->oci8RaiseError();
568 $result = @OCIRowCount($this->last_stmt);
569 if ($result === false) {
570 return $this->oci8RaiseError($this->last_stmt);
578 function modifyQuery($query)
580 // "SELECT 2+2" must be "SELECT 2+2 FROM dual" in Oracle
581 if (preg_match('/^\s*SELECT/i', $query) &&
582 !preg_match('/\sFROM\s/i', $query)) {
583 $query .= ' FROM dual';
589 // {{{ modifyLimitQuery()
592 * Emulate the row limit support altering the query
594 * @param string $query The query to treat
595 * @param int $from The row to start to fetch from
596 * @param int $count The offset
597 * @return string The modified query
599 * @author Tomas V.V.Cox <cox@idecnet.com>
601 function modifyLimitQuery($query, $from, $count)
603 // Let Oracle return the name of the columns instead of
604 // coding a "home" SQL parser
605 $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL";
606 if (!$result = @OCIParse($this->connection, $q_fields)) {
607 $this->last_query = $q_fields;
608 return $this->oci8RaiseError();
610 if (!@OCIExecute($result, OCI_DEFAULT)) {
611 $this->last_query = $q_fields;
612 return $this->oci8RaiseError($result);
614 $ncols = OCINumCols($result);
616 for ( $i = 1; $i <= $ncols; $i++ ) {
617 $cols[] = '"' . OCIColumnName($result, $i) . '"';
619 $fields = implode(', ', $cols);
620 // XXX Test that (tip by John Lim)
621 //if (preg_match('/^\s*SELECT\s+/is', $query, $match)) {
622 // // Introduce the FIRST_ROWS Oracle query optimizer
623 // $query = substr($query, strlen($match[0]), strlen($query));
624 // $query = "SELECT /* +FIRST_ROWS */ " . $query;
627 // Construct the query
628 // more at: http://marc.theaimsgroup.com/?l=php-db&m=99831958101212&w=2
629 // Perhaps this could be optimized with the use of Unions
630 $query = "SELECT $fields FROM".
631 " (SELECT rownum as linenum, $fields FROM".
633 ' WHERE rownum <= '. ($from + $count) .
634 ') WHERE linenum >= ' . ++$from;
642 * Returns the next free id in a sequence
644 * @param string $seq_name name of the sequence
645 * @param boolean $ondemand when true, the seqence is automatically
646 * created if it does not exist
648 * @return int the next id number in the sequence. DB_Error if problem.
651 * @see DB_common::nextID()
654 function nextId($seq_name, $ondemand = true)
656 $seqname = $this->getSequenceName($seq_name);
659 $this->expectError(DB_ERROR_NOSUCHTABLE);
660 $result =& $this->query("SELECT ${seqname}.nextval FROM dual");
662 if ($ondemand && DB::isError($result) &&
663 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
665 $result = $this->createSequence($seq_name);
666 if (DB::isError($result)) {
667 return $this->raiseError($result);
673 if (DB::isError($result)) {
674 return $this->raiseError($result);
676 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
681 * Creates a new sequence
683 * @param string $seq_name name of the new sequence
685 * @return int DB_OK on success. A DB_Error object is returned if
689 * @see DB_common::createSequence()
692 function createSequence($seq_name)
694 $seqname = $this->getSequenceName($seq_name);
695 return $this->query("CREATE SEQUENCE ${seqname}");
699 // {{{ dropSequence()
704 * @param string $seq_name name of the sequence to be deleted
706 * @return int DB_OK on success. DB_Error if problems.
709 * @see DB_common::dropSequence()
712 function dropSequence($seq_name)
714 $seqname = $this->getSequenceName($seq_name);
715 return $this->query("DROP SEQUENCE ${seqname}");
719 // {{{ oci8RaiseError()
722 * Gather information about an error, then use that info to create a
723 * DB error object and finally return that object.
725 * @param integer $errno PEAR error number (usually a DB constant) if
726 * manually raising an error
727 * @return object DB error object
728 * @see DB_common::errorCode()
729 * @see DB_common::raiseError()
731 function oci8RaiseError($errno = null)
733 if ($errno === null) {
734 $error = @OCIError($this->connection);
735 return $this->raiseError($this->errorCode($error['code']),
736 null, null, null, $error['message']);
737 } elseif (is_resource($errno)) {
738 $error = @OCIError($errno);
739 return $this->raiseError($this->errorCode($error['code']),
740 null, null, null, $error['message']);
742 return $this->raiseError($this->errorCode($errno));
746 // {{{ getSpecialQuery()
749 * Returns the query needed to get some backend info
750 * @param string $type What kind of info you want to retrieve
751 * @return string The SQL query string
753 function getSpecialQuery($type)
757 return 'SELECT table_name FROM user_tables';
767 * Returns information about a table or a result set.
769 * NOTE: only supports 'table' and 'flags' if <var>$result</var>
772 * NOTE: flags won't contain index information.
774 * @param object|string $result DB_result object from a query or a
775 * string containing the name of a table
776 * @param int $mode a valid tableInfo mode
777 * @return array an associative array with the information requested
778 * or an error object if something is wrong
781 * @see DB_common::tableInfo()
783 function tableInfo($result, $mode = null)
785 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
786 $case_func = 'strtolower';
788 $case_func = 'strval';
791 if (is_string($result)) {
793 * Probably received a table name.
794 * Create a result resource identifier.
796 $result = strtoupper($result);
797 $q_fields = 'SELECT column_name, data_type, data_length, '
799 . 'FROM user_tab_columns '
800 . "WHERE table_name='$result' ORDER BY column_id";
802 $this->last_query = $q_fields;
804 if (!$stmt = @OCIParse($this->connection, $q_fields)) {
805 return $this->oci8RaiseError(DB_ERROR_NEED_MORE_DATA);
807 if (!@OCIExecute($stmt, OCI_DEFAULT)) {
808 return $this->oci8RaiseError($stmt);
812 while (@OCIFetch($stmt)) {
813 $res[$i]['table'] = $case_func($result);
814 $res[$i]['name'] = $case_func(@OCIResult($stmt, 1));
815 $res[$i]['type'] = @OCIResult($stmt, 2);
816 $res[$i]['len'] = @OCIResult($stmt, 3);
817 $res[$i]['flags'] = (@OCIResult($stmt, 4) == 'N') ? 'not_null' : '';
819 if ($mode & DB_TABLEINFO_ORDER) {
820 $res['order'][$res[$i]['name']] = $i;
822 if ($mode & DB_TABLEINFO_ORDERTABLE) {
823 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
829 $res['num_fields'] = $i;
831 @OCIFreeStatement($stmt);
834 if (isset($result->result)) {
836 * Probably received a result object.
837 * Extract the result resource identifier.
839 $result = $result->result;
842 * ELSE, probably received a result resource identifier.
843 * Depricated. Here for compatibility only.
847 if ($result === $this->last_stmt) {
848 $count = @OCINumCols($result);
850 for ($i=0; $i<$count; $i++) {
851 $res[$i]['table'] = '';
852 $res[$i]['name'] = $case_func(@OCIColumnName($result, $i+1));
853 $res[$i]['type'] = @OCIColumnType($result, $i+1);
854 $res[$i]['len'] = @OCIColumnSize($result, $i+1);
855 $res[$i]['flags'] = '';
857 if ($mode & DB_TABLEINFO_ORDER) {
858 $res['order'][$res[$i]['name']] = $i;
860 if ($mode & DB_TABLEINFO_ORDERTABLE) {
861 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
866 $res['num_fields'] = $count;
870 return $this->raiseError(DB_ERROR_NOT_CAPABLE);