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: Stig Bakken <ssb@php.net> |
17 // | Maintainer: Daniel Convissor <danielc@php.net> |
18 // +----------------------------------------------------------------------+
23 // More info on ODBC errors could be found here:
24 // http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_err_odbc_5stz.asp
26 // XXX ERRORMSG: The error message from the odbc function should
27 // be registered here.
29 require_once 'DB/common.php';
32 * Database independent query interface definition for PHP's ODBC
38 * @author Stig Bakken <ssb@php.net>
40 class DB_odbc extends DB_common
45 var $phptype, $dbsyntax;
54 $this->phptype = 'odbc';
55 $this->dbsyntax = 'sql92';
56 $this->features = array(
59 'transactions' => false,
62 $this->errorcode_map = array(
63 '01004' => DB_ERROR_TRUNCATED,
64 '07001' => DB_ERROR_MISMATCH,
65 '21S01' => DB_ERROR_MISMATCH,
66 '21S02' => DB_ERROR_MISMATCH,
67 '22003' => DB_ERROR_INVALID_NUMBER,
68 '22005' => DB_ERROR_INVALID_NUMBER,
69 '22008' => DB_ERROR_INVALID_DATE,
70 '22012' => DB_ERROR_DIVZERO,
71 '23000' => DB_ERROR_CONSTRAINT,
72 '23502' => DB_ERROR_CONSTRAINT_NOT_NULL,
73 '23503' => DB_ERROR_CONSTRAINT,
74 '23505' => DB_ERROR_CONSTRAINT,
75 '24000' => DB_ERROR_INVALID,
76 '34000' => DB_ERROR_INVALID,
77 '37000' => DB_ERROR_SYNTAX,
78 '42000' => DB_ERROR_SYNTAX,
79 '42601' => DB_ERROR_SYNTAX,
80 'IM001' => DB_ERROR_UNSUPPORTED,
81 'S0000' => DB_ERROR_NOSUCHTABLE,
82 'S0001' => DB_ERROR_ALREADY_EXISTS,
83 'S0002' => DB_ERROR_NOSUCHTABLE,
84 'S0011' => DB_ERROR_ALREADY_EXISTS,
85 'S0012' => DB_ERROR_NOT_FOUND,
86 'S0021' => DB_ERROR_ALREADY_EXISTS,
87 'S0022' => DB_ERROR_NOSUCHFIELD,
88 'S1000' => DB_ERROR_CONSTRAINT_NOT_NULL,
89 'S1009' => DB_ERROR_INVALID,
90 'S1090' => DB_ERROR_INVALID,
91 'S1C00' => DB_ERROR_NOT_CAPABLE
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('odbc')) {
110 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
113 $this->dsn = $dsninfo;
114 if ($dsninfo['dbsyntax']) {
115 $this->dbsyntax = $dsninfo['dbsyntax'];
117 switch ($this->dbsyntax) {
119 $this->features = array(
122 'transactions' => true
126 // the Navision driver doesn't support fetch row by number
127 $this->features['limit'] = false;
131 * This is hear for backwards compatibility.
132 * Should have been using 'database' all along, but used hostspec.
134 if ($dsninfo['database']) {
135 $odbcdsn = $dsninfo['database'];
136 } elseif ($dsninfo['hostspec']) {
137 $odbcdsn = $dsninfo['hostspec'];
139 $odbcdsn = 'localhost';
142 if ($this->provides('pconnect')) {
143 $connect_function = $persistent ? 'odbc_pconnect' : 'odbc_connect';
145 $connect_function = 'odbc_connect';
147 $conn = @$connect_function($odbcdsn, $dsninfo['username'],
148 $dsninfo['password']);
149 if (!is_resource($conn)) {
150 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
151 null, $this->errorNative());
153 $this->connection = $conn;
160 function disconnect()
162 $err = @odbc_close($this->connection);
163 $this->connection = null;
171 * Send a query to ODBC and return the results as a ODBC resource
174 * @param $query the SQL query
176 * @return int returns a valid ODBC result for successful SELECT
177 * queries, DB_OK for other successful queries. A DB error code
178 * is returned on failure.
180 function simpleQuery($query)
182 $this->last_query = $query;
183 $query = $this->modifyQuery($query);
184 $result = @odbc_exec($this->connection, $query);
186 return $this->odbcRaiseError(); // XXX ERRORMSG
188 // Determine which queries that should return data, and which
189 // should return an error code only.
190 if (DB::isManip($query)) {
191 $this->manip_result = $result; // For affectedRows()
194 $this->row[(int)$result] = 0;
195 $this->manip_result = 0;
203 * Move the internal odbc result pointer to the next available result
205 * @param a valid fbsql result resource
209 * @return true if a result is available otherwise return false
211 function nextResult($result)
213 return @odbc_next_result($result);
220 * Fetch a row and insert the data into an existing array.
222 * Formating of the array and the data therein are configurable.
223 * See DB_result::fetchInto() for more information.
225 * @param resource $result query result identifier
226 * @param array $arr (reference) array where data from the row
228 * @param int $fetchmode how the resulting array should be indexed
229 * @param int $rownum the row number to fetch
231 * @return mixed DB_OK on success, null when end of result set is
232 * reached or on failure
234 * @see DB_result::fetchInto()
237 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
240 if ($rownum !== null) {
241 $rownum++; // ODBC first row is 1
242 if (version_compare(phpversion(), '4.2.0', 'ge')) {
243 $cols = @odbc_fetch_into($result, $arr, $rownum);
245 $cols = @odbc_fetch_into($result, $rownum, $arr);
248 $cols = @odbc_fetch_into($result, $arr);
252 /* XXX FIXME: doesn't work with unixODBC and easysoft
253 (get corrupted $errno values)
254 if ($errno = @odbc_error($this->connection)) {
255 return $this->RaiseError($errno);
259 if ($fetchmode !== DB_FETCHMODE_ORDERED) {
260 for ($i = 0; $i < count($arr); $i++) {
261 $colName = @odbc_field_name($result, $i+1);
262 $a[$colName] = $arr[$i];
264 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
265 $a = array_change_key_case($a, CASE_LOWER);
269 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
270 $this->_rtrimArrayValues($arr);
272 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
273 $this->_convertNullArrayValuesToEmpty($arr);
281 function freeResult($result)
283 unset($this->row[(int)$result]);
284 return @odbc_free_result($result);
290 function numCols($result)
292 $cols = @odbc_num_fields($result);
294 return $this->odbcRaiseError();
300 // {{{ affectedRows()
303 * Returns the number of rows affected by a manipulative query
304 * (INSERT, DELETE, UPDATE)
305 * @return mixed int affected rows, 0 when non manip queries or
308 function affectedRows()
310 if (empty($this->manip_result)) { // In case of SELECT stms
313 $nrows = @odbc_num_rows($this->manip_result);
315 return $this->odbcRaiseError();
324 * ODBC may or may not support counting rows in the result set of
327 * @param $result the odbc result resource
328 * @return the number of rows, or 0
330 function numRows($result)
332 $nrows = @odbc_num_rows($result);
334 return $this->odbcRaiseError(DB_ERROR_UNSUPPORTED);
340 // {{{ quoteIdentifier()
343 * Quote a string so it can be safely used as a table / column name
345 * Quoting style depends on which dbsyntax was passed in the DSN.
347 * Use 'mssql' as the dbsyntax in the DB DSN only if you've unchecked
348 * "Use ANSI quoted identifiers" when setting up the ODBC data source.
350 * @param string $str identifier name to be quoted
352 * @return string quoted identifier string
357 function quoteIdentifier($str)
359 switch ($this->dsn['dbsyntax']) {
361 return '[' . $str . ']';
364 return '[' . str_replace(']', ']]', $str) . ']';
367 return '`' . $str . '`';
369 return '"' . str_replace('"', '""', $str) . '"';
377 * @deprecated Deprecated in release 1.6.0
380 function quote($str) {
381 return $this->quoteSmart($str);
388 * Get the native error code of the last error (if any) that
389 * occured on the current connection.
393 * @return int ODBC error code
395 function errorNative()
397 if (!isset($this->connection) || !is_resource($this->connection)) {
398 return @odbc_error() . ' ' . @odbc_errormsg();
400 return @odbc_error($this->connection) . ' ' . @odbc_errormsg($this->connection);
407 * Returns the next free id in a sequence
409 * @param string $seq_name name of the sequence
410 * @param boolean $ondemand when true, the seqence is automatically
411 * created if it does not exist
413 * @return int the next id number in the sequence. DB_Error if problem.
416 * @see DB_common::nextID()
419 function nextId($seq_name, $ondemand = true)
421 $seqname = $this->getSequenceName($seq_name);
424 $this->pushErrorHandling(PEAR_ERROR_RETURN);
425 $result = $this->query("update ${seqname} set id = id + 1");
426 $this->popErrorHandling();
427 if ($ondemand && DB::isError($result) &&
428 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
430 $this->pushErrorHandling(PEAR_ERROR_RETURN);
431 $result = $this->createSequence($seq_name);
432 $this->popErrorHandling();
433 if (DB::isError($result)) {
434 return $this->raiseError($result);
436 $result = $this->query("insert into ${seqname} (id) values(0)");
442 if (DB::isError($result)) {
443 return $this->raiseError($result);
446 $result = $this->query("select id from ${seqname}");
447 if (DB::isError($result)) {
451 $row = $result->fetchRow(DB_FETCHMODE_ORDERED);
452 if (DB::isError($row || !$row)) {
460 * Creates a new sequence
462 * @param string $seq_name name of the new sequence
464 * @return int DB_OK on success. A DB_Error object is returned if
468 * @see DB_common::createSequence()
471 function createSequence($seq_name)
473 $seqname = $this->getSequenceName($seq_name);
474 return $this->query("CREATE TABLE ${seqname} ".
475 '(id integer NOT NULL,'.
476 ' PRIMARY KEY(id))');
480 // {{{ dropSequence()
485 * @param string $seq_name name of the sequence to be deleted
487 * @return int DB_OK on success. DB_Error if problems.
490 * @see DB_common::dropSequence()
493 function dropSequence($seq_name)
495 $seqname = $this->getSequenceName($seq_name);
496 return $this->query("DROP TABLE ${seqname}");
502 function autoCommit($onoff = false)
504 if (!@odbc_autocommit($this->connection, $onoff)) {
505 return $this->odbcRaiseError();
515 if (!@odbc_commit($this->connection)) {
516 return $this->odbcRaiseError();
526 if (!@odbc_rollback($this->connection)) {
527 return $this->odbcRaiseError();
533 // {{{ odbcRaiseError()
536 * Gather information about an error, then use that info to create a
537 * DB error object and finally return that object.
539 * @param integer $errno PEAR error number (usually a DB constant) if
540 * manually raising an error
541 * @return object DB error object
543 * @see DB_common::errorCode()
544 * @see DB_common::raiseError()
546 function odbcRaiseError($errno = null)
548 if ($errno === null) {
549 switch ($this->dbsyntax) {
551 if ($this->options['portability'] & DB_PORTABILITY_ERRORS) {
552 $this->errorcode_map['07001'] = DB_ERROR_NOSUCHFIELD;
554 // Doing this in case mode changes during runtime.
555 $this->errorcode_map['07001'] = DB_ERROR_MISMATCH;
558 $errno = $this->errorCode(odbc_error($this->connection));
560 return $this->raiseError($errno, null, null, null,
561 $this->errorNative());