3 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
6 * The PEAR DB driver for PHP's odbc extension
7 * for interacting with databases via ODBC connections
11 * LICENSE: This source file is subject to version 3.0 of the PHP license
12 * that is available through the world-wide-web at the following URI:
13 * http://www.php.net/license/3_0.txt. If you did not receive a copy of
14 * the PHP License and are unable to obtain it through the web, please
15 * send a note to license@php.net so we can mail you a copy immediately.
19 * @author Stig Bakken <ssb@php.net>
20 * @author Daniel Convissor <danielc@php.net>
21 * @copyright 1997-2007 The PHP Group
22 * @license http://www.php.net/license/3_0.txt PHP License 3.0
24 * @link http://pear.php.net/package/DB
28 * Obtain the DB_common class so it can be extended from
30 require_once 'DB/common.php';
33 * The methods PEAR DB uses to interact with PHP's odbc extension
34 * for interacting with databases via ODBC connections
36 * These methods overload the ones declared in DB_common.
38 * More info on ODBC errors could be found here:
39 * http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_err_odbc_5stz.asp
43 * @author Stig Bakken <ssb@php.net>
44 * @author Daniel Convissor <danielc@php.net>
45 * @copyright 1997-2007 The PHP Group
46 * @license http://www.php.net/license/3_0.txt PHP License 3.0
47 * @version Release: 1.8.2
48 * @link http://pear.php.net/package/DB
50 class DB_odbc extends DB_common
55 * The DB driver type (mysql, oci8, odbc, etc.)
58 var $phptype = 'odbc';
61 * The database syntax variant to be used (db2, access, etc.), if any
64 var $dbsyntax = 'sql92';
67 * The capabilities of this DB implementation
69 * The 'new_link' element contains the PHP version that first provided
70 * new_link support for this DBMS. Contains false if it's unsupported.
72 * Meaning of the 'limit' element:
73 * + 'emulate' = emulate with fetch row by number
74 * + 'alter' = alter the query
77 * NOTE: The feature set of the following drivers are different than
79 * + solid: 'transactions' = true
80 * + navision: 'limit' = false
84 var $features = array(
91 'transactions' => false,
95 * A mapping of native error codes to DB error codes
98 var $errorcode_map = array(
99 '01004' => DB_ERROR_TRUNCATED,
100 '07001' => DB_ERROR_MISMATCH,
101 '21S01' => DB_ERROR_VALUE_COUNT_ON_ROW,
102 '21S02' => DB_ERROR_MISMATCH,
103 '22001' => DB_ERROR_INVALID,
104 '22003' => DB_ERROR_INVALID_NUMBER,
105 '22005' => DB_ERROR_INVALID_NUMBER,
106 '22008' => DB_ERROR_INVALID_DATE,
107 '22012' => DB_ERROR_DIVZERO,
108 '23000' => DB_ERROR_CONSTRAINT,
109 '23502' => DB_ERROR_CONSTRAINT_NOT_NULL,
110 '23503' => DB_ERROR_CONSTRAINT,
111 '23504' => DB_ERROR_CONSTRAINT,
112 '23505' => DB_ERROR_CONSTRAINT,
113 '24000' => DB_ERROR_INVALID,
114 '34000' => DB_ERROR_INVALID,
115 '37000' => DB_ERROR_SYNTAX,
116 '42000' => DB_ERROR_SYNTAX,
117 '42601' => DB_ERROR_SYNTAX,
118 'IM001' => DB_ERROR_UNSUPPORTED,
119 'S0000' => DB_ERROR_NOSUCHTABLE,
120 'S0001' => DB_ERROR_ALREADY_EXISTS,
121 'S0002' => DB_ERROR_NOSUCHTABLE,
122 'S0011' => DB_ERROR_ALREADY_EXISTS,
123 'S0012' => DB_ERROR_NOT_FOUND,
124 'S0021' => DB_ERROR_ALREADY_EXISTS,
125 'S0022' => DB_ERROR_NOSUCHFIELD,
126 'S1009' => DB_ERROR_INVALID,
127 'S1090' => DB_ERROR_INVALID,
128 'S1C00' => DB_ERROR_NOT_CAPABLE,
132 * The raw database connection created by PHP
138 * The DSN information for connecting to a database
145 * The number of rows affected by a data manipulation query
156 * This constructor calls <kbd>$this->DB_common()</kbd>
169 * Connect to the database server, log in and open the database
171 * Don't call this method directly. Use DB::connect() instead.
173 * PEAR DB's odbc driver supports the following extra DSN options:
174 * + cursor The type of cursor to be used for this connection.
176 * @param array $dsn the data source name
177 * @param bool $persistent should the connection be persistent?
179 * @return int DB_OK on success. A DB_Error object on failure.
181 function connect($dsn, $persistent = false)
183 if (!PEAR::loadExtension('odbc')) {
184 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
188 if ($dsn['dbsyntax']) {
189 $this->dbsyntax = $dsn['dbsyntax'];
191 switch ($this->dbsyntax) {
195 $this->features['transactions'] = true;
198 $this->features['limit'] = false;
202 * This is hear for backwards compatibility. Should have been using
203 * 'database' all along, but prior to 1.6.0RC3 'hostspec' was used.
205 if ($dsn['database']) {
206 $odbcdsn = $dsn['database'];
207 } elseif ($dsn['hostspec']) {
208 $odbcdsn = $dsn['hostspec'];
210 $odbcdsn = 'localhost';
213 $connect_function = $persistent ? 'odbc_pconnect' : 'odbc_connect';
215 if (empty($dsn['cursor'])) {
216 $this->connection = @$connect_function($odbcdsn, $dsn['username'],
219 $this->connection = @$connect_function($odbcdsn, $dsn['username'],
224 if (!is_resource($this->connection)) {
225 return $this->raiseError(DB_ERROR_CONNECT_FAILED,
227 $this->errorNative());
236 * Disconnects from the database server
238 * @return bool TRUE on success, FALSE on failure
240 function disconnect()
242 $err = @odbc_close($this->connection);
243 $this->connection = null;
251 * Sends a query to the database server
253 * @param string the SQL query string
255 * @return mixed + a PHP result resrouce for successful SELECT queries
256 * + the DB_OK constant for other successful queries
257 * + a DB_Error object on failure
259 function simpleQuery($query)
261 $this->last_query = $query;
262 $query = $this->modifyQuery($query);
263 $result = @odbc_exec($this->connection, $query);
265 return $this->odbcRaiseError(); // XXX ERRORMSG
267 // Determine which queries that should return data, and which
268 // should return an error code only.
269 if ($this->_checkManip($query)) {
270 $this->affected = $result; // For affectedRows()
281 * Move the internal odbc result pointer to the next available result
283 * @param a valid fbsql result resource
287 * @return true if a result is available otherwise return false
289 function nextResult($result)
291 return @odbc_next_result($result);
298 * Places a row from the result set into the given array
300 * Formating of the array and the data therein are configurable.
301 * See DB_result::fetchInto() for more information.
303 * This method is not meant to be called directly. Use
304 * DB_result::fetchInto() instead. It can't be declared "protected"
305 * because DB_result is a separate object.
307 * @param resource $result the query result resource
308 * @param array $arr the referenced array to put the data in
309 * @param int $fetchmode how the resulting array should be indexed
310 * @param int $rownum the row number to fetch (0 = first row)
312 * @return mixed DB_OK on success, NULL when the end of a result set is
313 * reached or on failure
315 * @see DB_result::fetchInto()
317 function fetchInto($result, &$arr, $fetchmode, $rownum = null)
320 if ($rownum !== null) {
321 $rownum++; // ODBC first row is 1
322 if (version_compare(phpversion(), '4.2.0', 'ge')) {
323 $cols = @odbc_fetch_into($result, $arr, $rownum);
325 $cols = @odbc_fetch_into($result, $rownum, $arr);
328 $cols = @odbc_fetch_into($result, $arr);
333 if ($fetchmode !== DB_FETCHMODE_ORDERED) {
334 for ($i = 0; $i < count($arr); $i++) {
335 $colName = @odbc_field_name($result, $i+1);
336 $a[$colName] = $arr[$i];
338 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
339 $a = array_change_key_case($a, CASE_LOWER);
343 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
344 $this->_rtrimArrayValues($arr);
346 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
347 $this->_convertNullArrayValuesToEmpty($arr);
356 * Deletes the result set and frees the memory occupied by the result set
358 * This method is not meant to be called directly. Use
359 * DB_result::free() instead. It can't be declared "protected"
360 * because DB_result is a separate object.
362 * @param resource $result PHP's query result resource
364 * @return bool TRUE on success, FALSE if $result is invalid
366 * @see DB_result::free()
368 function freeResult($result)
370 return is_resource($result) ? odbc_free_result($result) : false;
377 * Gets the number of columns in a result set
379 * This method is not meant to be called directly. Use
380 * DB_result::numCols() instead. It can't be declared "protected"
381 * because DB_result is a separate object.
383 * @param resource $result PHP's query result resource
385 * @return int the number of columns. A DB_Error object on failure.
387 * @see DB_result::numCols()
389 function numCols($result)
391 $cols = @odbc_num_fields($result);
393 return $this->odbcRaiseError();
399 // {{{ affectedRows()
402 * Determines the number of rows affected by a data maniuplation query
404 * 0 is returned for queries that don't manipulate data.
406 * @return int the number of rows. A DB_Error object on failure.
408 function affectedRows()
410 if (empty($this->affected)) { // In case of SELECT stms
413 $nrows = @odbc_num_rows($this->affected);
415 return $this->odbcRaiseError();
424 * Gets the number of rows in a result set
426 * Not all ODBC drivers support this functionality. If they don't
427 * a DB_Error object for DB_ERROR_UNSUPPORTED is returned.
429 * This method is not meant to be called directly. Use
430 * DB_result::numRows() instead. It can't be declared "protected"
431 * because DB_result is a separate object.
433 * @param resource $result PHP's query result resource
435 * @return int the number of rows. A DB_Error object on failure.
437 * @see DB_result::numRows()
439 function numRows($result)
441 $nrows = @odbc_num_rows($result);
443 return $this->odbcRaiseError(DB_ERROR_UNSUPPORTED);
445 if ($nrows === false) {
446 return $this->odbcRaiseError();
452 // {{{ quoteIdentifier()
455 * Quotes a string so it can be safely used as a table or column name
457 * Use 'mssql' as the dbsyntax in the DB DSN only if you've unchecked
458 * "Use ANSI quoted identifiers" when setting up the ODBC data source.
460 * @param string $str identifier name to be quoted
462 * @return string quoted identifier string
464 * @see DB_common::quoteIdentifier()
465 * @since Method available since Release 1.6.0
467 function quoteIdentifier($str)
469 switch ($this->dsn['dbsyntax']) {
471 return '[' . $str . ']';
474 return '[' . str_replace(']', ']]', $str) . ']';
477 return '`' . $str . '`';
479 return '"' . str_replace('"', '""', $str) . '"';
487 * Returns the next free id in a sequence
489 * @param string $seq_name name of the sequence
490 * @param boolean $ondemand when true, the seqence is automatically
491 * created if it does not exist
493 * @return int the next id number in the sequence.
494 * A DB_Error object on failure.
496 * @see DB_common::nextID(), DB_common::getSequenceName(),
497 * DB_odbc::createSequence(), DB_odbc::dropSequence()
499 function nextId($seq_name, $ondemand = true)
501 $seqname = $this->getSequenceName($seq_name);
504 $this->pushErrorHandling(PEAR_ERROR_RETURN);
505 $result = $this->query("update ${seqname} set id = id + 1");
506 $this->popErrorHandling();
507 if ($ondemand && DB::isError($result) &&
508 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
510 $this->pushErrorHandling(PEAR_ERROR_RETURN);
511 $result = $this->createSequence($seq_name);
512 $this->popErrorHandling();
513 if (DB::isError($result)) {
514 return $this->raiseError($result);
516 $result = $this->query("insert into ${seqname} (id) values(0)");
522 if (DB::isError($result)) {
523 return $this->raiseError($result);
526 $result = $this->query("select id from ${seqname}");
527 if (DB::isError($result)) {
531 $row = $result->fetchRow(DB_FETCHMODE_ORDERED);
532 if (DB::isError($row || !$row)) {
540 * Creates a new sequence
542 * @param string $seq_name name of the new sequence
544 * @return int DB_OK on success. A DB_Error object on failure.
546 * @see DB_common::createSequence(), DB_common::getSequenceName(),
547 * DB_odbc::nextID(), DB_odbc::dropSequence()
549 function createSequence($seq_name)
551 return $this->query('CREATE TABLE '
552 . $this->getSequenceName($seq_name)
553 . ' (id integer NOT NULL,'
554 . ' PRIMARY KEY(id))');
558 // {{{ dropSequence()
563 * @param string $seq_name name of the sequence to be deleted
565 * @return int DB_OK on success. A DB_Error object on failure.
567 * @see DB_common::dropSequence(), DB_common::getSequenceName(),
568 * DB_odbc::nextID(), DB_odbc::createSequence()
570 function dropSequence($seq_name)
572 return $this->query('DROP TABLE ' . $this->getSequenceName($seq_name));
579 * Enables or disables automatic commits
581 * @param bool $onoff true turns it on, false turns it off
583 * @return int DB_OK on success. A DB_Error object if the driver
584 * doesn't support auto-committing transactions.
586 function autoCommit($onoff = false)
588 if (!@odbc_autocommit($this->connection, $onoff)) {
589 return $this->odbcRaiseError();
598 * Commits the current transaction
600 * @return int DB_OK on success. A DB_Error object on failure.
604 if (!@odbc_commit($this->connection)) {
605 return $this->odbcRaiseError();
614 * Reverts the current transaction
616 * @return int DB_OK on success. A DB_Error object on failure.
620 if (!@odbc_rollback($this->connection)) {
621 return $this->odbcRaiseError();
627 // {{{ odbcRaiseError()
630 * Produces a DB_Error object regarding the current problem
632 * @param int $errno if the error is being manually raised pass a
633 * DB_ERROR* constant here. If this isn't passed
634 * the error information gathered from the DBMS.
636 * @return object the DB_Error object
638 * @see DB_common::raiseError(),
639 * DB_odbc::errorNative(), DB_common::errorCode()
641 function odbcRaiseError($errno = null)
643 if ($errno === null) {
644 switch ($this->dbsyntax) {
646 if ($this->options['portability'] & DB_PORTABILITY_ERRORS) {
647 $this->errorcode_map['07001'] = DB_ERROR_NOSUCHFIELD;
649 // Doing this in case mode changes during runtime.
650 $this->errorcode_map['07001'] = DB_ERROR_MISMATCH;
653 $native_code = odbc_error($this->connection);
655 // S1000 is for "General Error." Let's be more specific.
656 if ($native_code == 'S1000') {
657 $errormsg = odbc_errormsg($this->connection);
658 static $error_regexps;
659 if (!isset($error_regexps)) {
660 $error_regexps = array(
661 '/includes related records.$/i' => DB_ERROR_CONSTRAINT,
662 '/cannot contain a Null value/i' => DB_ERROR_CONSTRAINT_NOT_NULL,
665 foreach ($error_regexps as $regexp => $code) {
666 if (preg_match($regexp, $errormsg)) {
667 return $this->raiseError($code,
669 $native_code . ' ' . $errormsg);
674 $errno = $this->errorCode($native_code);
678 $errno = $this->errorCode(odbc_error($this->connection));
681 return $this->raiseError($errno, null, null, null,
682 $this->errorNative());
689 * Gets the DBMS' native error code and message produced by the last query
691 * @return string the DBMS' error code and message
693 function errorNative()
695 if (!is_resource($this->connection)) {
696 return @odbc_error() . ' ' . @odbc_errormsg();
698 return @odbc_error($this->connection) . ' ' . @odbc_errormsg($this->connection);
705 * Returns information about a table or a result set
707 * @param object|string $result DB_result object from a query or a
708 * string containing the name of a table.
709 * While this also accepts a query result
710 * resource identifier, this behavior is
712 * @param int $mode a valid tableInfo mode
714 * @return array an associative array with the information requested.
715 * A DB_Error object on failure.
717 * @see DB_common::tableInfo()
718 * @since Method available since Release 1.7.0
720 function tableInfo($result, $mode = null)
722 if (is_string($result)) {
724 * Probably received a table name.
725 * Create a result resource identifier.
727 $id = @odbc_exec($this->connection, "SELECT * FROM $result");
729 return $this->odbcRaiseError();
732 } elseif (isset($result->result)) {
734 * Probably received a result object.
735 * Extract the result resource identifier.
737 $id = $result->result;
741 * Probably received a result resource identifier.
743 * Deprecated. Here for compatibility only.
749 if (!is_resource($id)) {
750 return $this->odbcRaiseError(DB_ERROR_NEED_MORE_DATA);
753 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
754 $case_func = 'strtolower';
756 $case_func = 'strval';
759 $count = @odbc_num_fields($id);
763 $res['num_fields'] = $count;
766 for ($i = 0; $i < $count; $i++) {
769 'table' => $got_string ? $case_func($result) : '',
770 'name' => $case_func(@odbc_field_name($id, $col)),
771 'type' => @odbc_field_type($id, $col),
772 'len' => @odbc_field_len($id, $col),
775 if ($mode & DB_TABLEINFO_ORDER) {
776 $res['order'][$res[$i]['name']] = $i;
778 if ($mode & DB_TABLEINFO_ORDERTABLE) {
779 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
783 // free the result only if we were called on a table
785 @odbc_free_result($id);
791 // {{{ getSpecialQuery()
794 * Obtains the query string needed for listing a given type of objects
796 * Thanks to symbol1@gmail.com and Philippe.Jausions@11abacus.com.
798 * @param string $type the kind of objects you want to retrieve
800 * @return string the list of objects requested
803 * @see DB_common::getListOf()
804 * @since Method available since Release 1.7.0
806 function getSpecialQuery($type)
810 if (!function_exists('odbc_data_source')) {
813 $res = @odbc_data_source($this->connection, SQL_FETCH_FIRST);
814 if (is_array($res)) {
815 $out = array($res['server']);
816 while($res = @odbc_data_source($this->connection,
819 $out[] = $res['server'];
823 return $this->odbcRaiseError();
827 case 'schema.tables':
838 * Removing non-conforming items in the while loop rather than
839 * in the odbc_tables() call because some backends choke on this:
840 * odbc_tables($this->connection, '', '', '', 'TABLE')
842 $res = @odbc_tables($this->connection);
844 return $this->odbcRaiseError();
847 while ($row = odbc_fetch_array($res)) {
848 if ($row['TABLE_TYPE'] != $keep) {
851 if ($type == 'schema.tables') {
852 $out[] = $row['TABLE_SCHEM'] . '.' . $row['TABLE_NAME'];
854 $out[] = $row['TABLE_NAME'];