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: Sterling Hughes <sterling@php.net> |
17 // | Maintainer: Daniel Convissor <danielc@php.net> |
18 // +----------------------------------------------------------------------+
22 require_once 'DB/common.php';
25 * Database independent query interface definition for PHP's Microsoft SQL Server
31 * @author Sterling Hughes <sterling@php.net>
33 class DB_mssql extends DB_common
38 var $phptype, $dbsyntax;
39 var $prepare_tokens = array();
40 var $prepare_types = array();
41 var $transaction_opcount = 0;
42 var $autocommit = true;
51 $this->phptype = 'mssql';
52 $this->dbsyntax = 'mssql';
53 $this->features = array(
56 'transactions' => true,
59 // XXX Add here error codes ie: 'S100E' => DB_ERROR_SYNTAX
60 $this->errorcode_map = array(
61 170 => DB_ERROR_SYNTAX,
62 207 => DB_ERROR_NOSUCHFIELD,
63 208 => DB_ERROR_NOSUCHTABLE,
64 245 => DB_ERROR_INVALID_NUMBER,
65 515 => DB_ERROR_CONSTRAINT_NOT_NULL,
66 547 => DB_ERROR_CONSTRAINT,
67 2627 => DB_ERROR_CONSTRAINT,
68 2714 => DB_ERROR_ALREADY_EXISTS,
69 3701 => DB_ERROR_NOSUCHTABLE,
70 8134 => DB_ERROR_DIVZERO,
77 function connect($dsninfo, $persistent = false)
79 if (!DB::assertExtension('mssql') && !DB::assertExtension('sybase')
80 && !DB::assertExtension('sybase_ct'))
82 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
84 $this->dsn = $dsninfo;
85 $dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
86 $dbhost .= $dsninfo['port'] ? ':' . $dsninfo['port'] : '';
88 $connect_function = $persistent ? 'mssql_pconnect' : 'mssql_connect';
90 if ($dbhost && $dsninfo['username'] && $dsninfo['password']) {
91 $conn = @$connect_function($dbhost, $dsninfo['username'],
92 $dsninfo['password']);
93 } elseif ($dbhost && $dsninfo['username']) {
94 $conn = @$connect_function($dbhost, $dsninfo['username']);
96 $conn = @$connect_function($dbhost);
99 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
100 null, @mssql_get_last_message());
102 if ($dsninfo['database']) {
103 if (!@mssql_select_db($dsninfo['database'], $conn)) {
104 return $this->raiseError(DB_ERROR_NODBSELECTED, null, null,
105 null, @mssql_get_last_message());
107 $this->_db = $dsninfo['database'];
109 $this->connection = $conn;
116 function disconnect()
118 $ret = @mssql_close($this->connection);
119 $this->connection = null;
126 function simpleQuery($query)
128 $ismanip = DB::isManip($query);
129 $this->last_query = $query;
130 if (!@mssql_select_db($this->_db, $this->connection)) {
131 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
133 $query = $this->modifyQuery($query);
134 if (!$this->autocommit && $ismanip) {
135 if ($this->transaction_opcount == 0) {
136 $result = @mssql_query('BEGIN TRAN', $this->connection);
138 return $this->mssqlRaiseError();
141 $this->transaction_opcount++;
143 $result = @mssql_query($query, $this->connection);
145 return $this->mssqlRaiseError();
147 // Determine which queries that should return data, and which
148 // should return an error code only.
149 return $ismanip ? DB_OK : $result;
156 * Move the internal mssql result pointer to the next available result
158 * @param a valid fbsql result resource
162 * @return true if a result is available otherwise return false
164 function nextResult($result)
166 return @mssql_next_result($result);
173 * Fetch a row and insert the data into an existing array.
175 * Formating of the array and the data therein are configurable.
176 * See DB_result::fetchInto() for more information.
178 * @param resource $result query result identifier
179 * @param array $arr (reference) array where data from the row
181 * @param int $fetchmode how the resulting array should be indexed
182 * @param int $rownum the row number to fetch
184 * @return mixed DB_OK on success, null when end of result set is
185 * reached or on failure
187 * @see DB_result::fetchInto()
190 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
192 if ($rownum !== null) {
193 if (!@mssql_data_seek($result, $rownum)) {
197 if ($fetchmode & DB_FETCHMODE_ASSOC) {
198 $arr = @mssql_fetch_array($result, MSSQL_ASSOC);
199 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
200 $arr = array_change_key_case($arr, CASE_LOWER);
203 $arr = @mssql_fetch_row($result);
206 /* This throws informative error messages,
208 if ($msg = @mssql_get_last_message()) {
209 return $this->raiseError($msg);
214 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
215 $this->_rtrimArrayValues($arr);
217 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
218 $this->_convertNullArrayValuesToEmpty($arr);
226 function freeResult($result)
228 return @mssql_free_result($result);
234 function numCols($result)
236 $cols = @mssql_num_fields($result);
238 return $this->mssqlRaiseError();
246 function numRows($result)
248 $rows = @mssql_num_rows($result);
249 if ($rows === false) {
250 return $this->mssqlRaiseError();
259 * Enable/disable automatic commits
261 function autoCommit($onoff = false)
263 // XXX if $this->transaction_opcount > 0, we should probably
264 // issue a warning here.
265 $this->autocommit = $onoff ? true : false;
273 * Commit the current transaction.
277 if ($this->transaction_opcount > 0) {
278 if (!@mssql_select_db($this->_db, $this->connection)) {
279 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
281 $result = @mssql_query('COMMIT TRAN', $this->connection);
282 $this->transaction_opcount = 0;
284 return $this->mssqlRaiseError();
294 * Roll back (undo) the current transaction.
298 if ($this->transaction_opcount > 0) {
299 if (!@mssql_select_db($this->_db, $this->connection)) {
300 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
302 $result = @mssql_query('ROLLBACK TRAN', $this->connection);
303 $this->transaction_opcount = 0;
305 return $this->mssqlRaiseError();
312 // {{{ affectedRows()
315 * Gets the number of rows affected by the last query.
316 * if the last query was a select, returns 0.
318 * @return number of rows affected by the last query or DB_ERROR
320 function affectedRows()
322 if (DB::isManip($this->last_query)) {
323 $res = @mssql_query('select @@rowcount', $this->connection);
325 return $this->mssqlRaiseError();
327 $ar = @mssql_fetch_row($res);
331 @mssql_free_result($res);
344 * Returns the next free id in a sequence
346 * @param string $seq_name name of the sequence
347 * @param boolean $ondemand when true, the seqence is automatically
348 * created if it does not exist
350 * @return int the next id number in the sequence. DB_Error if problem.
353 * @see DB_common::nextID()
356 function nextId($seq_name, $ondemand = true)
358 $seqname = $this->getSequenceName($seq_name);
359 if (!@mssql_select_db($this->_db, $this->connection)) {
360 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
364 $this->pushErrorHandling(PEAR_ERROR_RETURN);
365 $result = $this->query("INSERT INTO $seqname (vapor) VALUES (0)");
366 $this->popErrorHandling();
367 if ($ondemand && DB::isError($result) &&
368 ($result->getCode() == DB_ERROR || $result->getCode() == DB_ERROR_NOSUCHTABLE))
371 $result = $this->createSequence($seq_name);
372 if (DB::isError($result)) {
373 return $this->raiseError($result);
375 } elseif (!DB::isError($result)) {
376 $result =& $this->query("SELECT @@IDENTITY FROM $seqname");
382 if (DB::isError($result)) {
383 return $this->raiseError($result);
385 $result = $result->fetchRow(DB_FETCHMODE_ORDERED);
390 * Creates a new sequence
392 * @param string $seq_name name of the new sequence
394 * @return int DB_OK on success. A DB_Error object is returned if
398 * @see DB_common::createSequence()
401 function createSequence($seq_name)
403 $seqname = $this->getSequenceName($seq_name);
404 return $this->query("CREATE TABLE $seqname ".
405 '([id] [int] IDENTITY (1, 1) NOT NULL ,' .
406 '[vapor] [int] NULL)');
410 // {{{ dropSequence()
415 * @param string $seq_name name of the sequence to be deleted
417 * @return int DB_OK on success. DB_Error if problems.
420 * @see DB_common::dropSequence()
423 function dropSequence($seq_name)
425 $seqname = $this->getSequenceName($seq_name);
426 return $this->query("DROP TABLE $seqname");
433 * Determine MS SQL Server error code by querying @@ERROR.
435 * @return mixed mssql's native error code or DB_ERROR if unknown.
437 function errorNative()
439 $res = @mssql_query('select @@ERROR as ErrorCode', $this->connection);
443 $row = @mssql_fetch_row($res);
451 * Determine PEAR::DB error code from mssql's native codes.
453 * If <var>$nativecode</var> isn't known yet, it will be looked up.
455 * @param mixed $nativecode mssql error code, if known
456 * @return integer an error number from a DB error constant
459 function errorCode($nativecode = null)
462 $nativecode = $this->errorNative();
464 if (isset($this->errorcode_map[$nativecode])) {
465 return $this->errorcode_map[$nativecode];
472 // {{{ mssqlRaiseError()
475 * Gather information about an error, then use that info to create a
476 * DB error object and finally return that object.
478 * @param integer $code PEAR error number (usually a DB constant) if
479 * manually raising an error
480 * @return object DB error object
483 * @see DB_common::raiseError()
485 function mssqlRaiseError($code = null)
487 $message = @mssql_get_last_message();
489 $code = $this->errorNative();
491 return $this->raiseError($this->errorCode($code), null, null, null,
499 * Returns information about a table or a result set.
501 * NOTE: only supports 'table' and 'flags' if <var>$result</var>
504 * @param object|string $result DB_result object from a query or a
505 * string containing the name of a table
506 * @param int $mode a valid tableInfo mode
507 * @return array an associative array with the information requested
508 * or an error object if something is wrong
511 * @see DB_common::tableInfo()
513 function tableInfo($result, $mode = null)
515 if (isset($result->result)) {
517 * Probably received a result object.
518 * Extract the result resource identifier.
520 $id = $result->result;
522 } elseif (is_string($result)) {
524 * Probably received a table name.
525 * Create a result resource identifier.
527 if (!@mssql_select_db($this->_db, $this->connection)) {
528 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
530 $id = @mssql_query("SELECT * FROM $result WHERE 1=0",
535 * Probably received a result resource identifier.
537 * Depricated. Here for compatibility only.
543 if (!is_resource($id)) {
544 return $this->mssqlRaiseError(DB_ERROR_NEED_MORE_DATA);
547 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
548 $case_func = 'strtolower';
550 $case_func = 'strval';
553 $count = @mssql_num_fields($id);
555 // made this IF due to performance (one if is faster than $count if's)
557 for ($i=0; $i<$count; $i++) {
558 $res[$i]['table'] = $got_string ? $case_func($result) : '';
559 $res[$i]['name'] = $case_func(@mssql_field_name($id, $i));
560 $res[$i]['type'] = @mssql_field_type($id, $i);
561 $res[$i]['len'] = @mssql_field_length($id, $i);
562 // We only support flags for tables
563 $res[$i]['flags'] = $got_string ? $this->_mssql_field_flags($result, $res[$i]['name']) : '';
567 $res['num_fields']= $count;
569 for ($i=0; $i<$count; $i++) {
570 $res[$i]['table'] = $got_string ? $case_func($result) : '';
571 $res[$i]['name'] = $case_func(@mssql_field_name($id, $i));
572 $res[$i]['type'] = @mssql_field_type($id, $i);
573 $res[$i]['len'] = @mssql_field_length($id, $i);
574 // We only support flags for tables
575 $res[$i]['flags'] = $got_string ? $this->_mssql_field_flags($result, $res[$i]['name']) : '';
577 if ($mode & DB_TABLEINFO_ORDER) {
578 $res['order'][$res[$i]['name']] = $i;
580 if ($mode & DB_TABLEINFO_ORDERTABLE) {
581 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
586 // free the result only if we were called on a table
588 @mssql_free_result($id);
594 // {{{ getSpecialQuery()
597 * Returns the query needed to get some backend info
598 * @param string $type What kind of info you want to retrieve
599 * @return string The SQL query string
601 function getSpecialQuery($type)
605 return "select name from sysobjects where type = 'U' order by name";
607 return "select name from sysobjects where type = 'V'";
614 // {{{ _mssql_field_flags()
617 * Get the flags for a field, currently supports "not_null", "primary_key",
618 * "auto_increment" (mssql identity), "timestamp" (mssql timestamp),
619 * "unique_key" (mssql unique index, unique check or primary_key) and
620 * "multiple_key" (multikey index)
622 * mssql timestamp is NOT similar to the mysql timestamp so this is maybe
623 * not useful at all - is the behaviour of mysql_field_flags that primary
624 * keys are alway unique? is the interpretation of multiple_key correct?
626 * @param string The table name
627 * @param string The field
628 * @author Joern Barthel <j_barthel@web.de>
631 function _mssql_field_flags($table, $column)
633 static $tableName = null;
634 static $flags = array();
636 if ($table != $tableName) {
641 // get unique and primary keys
642 $res = $this->getAll("EXEC SP_HELPINDEX[$table]", DB_FETCHMODE_ASSOC);
644 foreach ($res as $val) {
645 $keys = explode(', ', $val['index_keys']);
647 if (sizeof($keys) > 1) {
648 foreach ($keys as $key) {
649 $this->_add_flag($flags[$key], 'multiple_key');
653 if (strpos($val['index_description'], 'primary key')) {
654 foreach ($keys as $key) {
655 $this->_add_flag($flags[$key], 'primary_key');
657 } elseif (strpos($val['index_description'], 'unique')) {
658 foreach ($keys as $key) {
659 $this->_add_flag($flags[$key], 'unique_key');
664 // get auto_increment, not_null and timestamp
665 $res = $this->getAll("EXEC SP_COLUMNS[$table]", DB_FETCHMODE_ASSOC);
667 foreach ($res as $val) {
668 $val = array_change_key_case($val, CASE_LOWER);
669 if ($val['nullable'] == '0') {
670 $this->_add_flag($flags[$val['column_name']], 'not_null');
672 if (strpos($val['type_name'], 'identity')) {
673 $this->_add_flag($flags[$val['column_name']], 'auto_increment');
675 if (strpos($val['type_name'], 'timestamp')) {
676 $this->_add_flag($flags[$val['column_name']], 'timestamp');
681 if (array_key_exists($column, $flags)) {
682 return(implode(' ', $flags[$column]));
691 * Adds a string to the flags array if the flag is not yet in there
692 * - if there is no flag present the array is created.
694 * @param reference Reference to the flag-array
695 * @param value The flag value
697 * @author Joern Barthel <j_barthel@web.de>
699 function _add_flag(&$array, $value)
701 if (!is_array($array)) {
702 $array = array($value);
703 } elseif (!in_array($value, $array)) {
704 array_push($array, $value);
709 // {{{ quoteIdentifier()
712 * Quote a string so it can be safely used as a table / column name
714 * Quoting style depends on which database driver is being used.
716 * @param string $str identifier name to be quoted
718 * @return string quoted identifier string
723 function quoteIdentifier($str)
725 return '[' . str_replace(']', ']]', $str) . ']';