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 // +----------------------------------------------------------------------+
20 require_once 'DB/common.php';
23 * Database independent query interface definition for PHP's Microsoft SQL Server
29 * @author Sterling Hughes <sterling@php.net>
31 class DB_mssql extends DB_common
36 var $phptype, $dbsyntax;
37 var $prepare_tokens = array();
38 var $prepare_types = array();
39 var $transaction_opcount = 0;
40 var $autocommit = true;
49 $this->phptype = 'mssql';
50 $this->dbsyntax = 'mssql';
51 $this->features = array(
54 'transactions' => true,
57 // XXX Add here error codes ie: 'S100E' => DB_ERROR_SYNTAX
58 $this->errorcode_map = array(
59 170 => DB_ERROR_SYNTAX,
60 207 => DB_ERROR_NOSUCHFIELD,
61 208 => DB_ERROR_NOSUCHTABLE,
62 245 => DB_ERROR_INVALID_NUMBER,
63 515 => DB_ERROR_CONSTRAINT_NOT_NULL,
64 547 => DB_ERROR_CONSTRAINT,
65 2627 => DB_ERROR_CONSTRAINT,
66 2714 => DB_ERROR_ALREADY_EXISTS,
67 3701 => DB_ERROR_NOSUCHTABLE,
68 8134 => DB_ERROR_DIVZERO,
75 function connect($dsninfo, $persistent = false)
77 if (!DB::assertExtension('mssql') && !DB::assertExtension('sybase')
78 && !DB::assertExtension('sybase_ct'))
80 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
82 $this->dsn = $dsninfo;
83 $dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
84 $dbhost .= $dsninfo['port'] ? ':' . $dsninfo['port'] : '';
86 $connect_function = $persistent ? 'mssql_pconnect' : 'mssql_connect';
88 if ($dbhost && $dsninfo['username'] && $dsninfo['password']) {
89 $conn = @$connect_function($dbhost, $dsninfo['username'],
90 $dsninfo['password']);
91 } elseif ($dbhost && $dsninfo['username']) {
92 $conn = @$connect_function($dbhost, $dsninfo['username']);
94 $conn = @$connect_function($dbhost);
97 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
98 null, @mssql_get_last_message());
100 if ($dsninfo['database']) {
101 if (!@mssql_select_db($dsninfo['database'], $conn)) {
102 return $this->raiseError(DB_ERROR_NODBSELECTED, null, null,
103 null, @mssql_get_last_message());
105 $this->_db = $dsninfo['database'];
107 $this->connection = $conn;
114 function disconnect()
116 $ret = @mssql_close($this->connection);
117 $this->connection = null;
124 function simpleQuery($query)
126 $ismanip = DB::isManip($query);
127 $this->last_query = $query;
128 if (!@mssql_select_db($this->_db, $this->connection)) {
129 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
131 $query = $this->modifyQuery($query);
132 if (!$this->autocommit && $ismanip) {
133 if ($this->transaction_opcount == 0) {
134 $result = @mssql_query('BEGIN TRAN', $this->connection);
136 return $this->mssqlRaiseError();
139 $this->transaction_opcount++;
141 $result = @mssql_query($query, $this->connection);
143 return $this->mssqlRaiseError();
145 // Determine which queries that should return data, and which
146 // should return an error code only.
147 return $ismanip ? DB_OK : $result;
154 * Move the internal mssql result pointer to the next available result
156 * @param a valid fbsql result resource
160 * @return true if a result is available otherwise return false
162 function nextResult($result)
164 return @mssql_next_result($result);
171 * Fetch a row and insert the data into an existing array.
173 * Formating of the array and the data therein are configurable.
174 * See DB_result::fetchInto() for more information.
176 * @param resource $result query result identifier
177 * @param array $arr (reference) array where data from the row
179 * @param int $fetchmode how the resulting array should be indexed
180 * @param int $rownum the row number to fetch
182 * @return mixed DB_OK on success, null when end of result set is
183 * reached or on failure
185 * @see DB_result::fetchInto()
188 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
190 if ($rownum !== null) {
191 if (!@mssql_data_seek($result, $rownum)) {
195 if ($fetchmode & DB_FETCHMODE_ASSOC) {
196 $arr = @mssql_fetch_array($result, MSSQL_ASSOC);
197 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
198 $arr = array_change_key_case($arr, CASE_LOWER);
201 $arr = @mssql_fetch_row($result);
204 /* This throws informative error messages,
206 if ($msg = @mssql_get_last_message()) {
207 return $this->raiseError($msg);
212 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
213 $this->_rtrimArrayValues($arr);
215 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
216 $this->_convertNullArrayValuesToEmpty($arr);
224 function freeResult($result)
226 return @mssql_free_result($result);
232 function numCols($result)
234 $cols = @mssql_num_fields($result);
236 return $this->mssqlRaiseError();
244 function numRows($result)
246 $rows = @mssql_num_rows($result);
247 if ($rows === false) {
248 return $this->mssqlRaiseError();
257 * Enable/disable automatic commits
259 function autoCommit($onoff = false)
261 // XXX if $this->transaction_opcount > 0, we should probably
262 // issue a warning here.
263 $this->autocommit = $onoff ? true : false;
271 * Commit the current transaction.
275 if ($this->transaction_opcount > 0) {
276 if (!@mssql_select_db($this->_db, $this->connection)) {
277 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
279 $result = @mssql_query('COMMIT TRAN', $this->connection);
280 $this->transaction_opcount = 0;
282 return $this->mssqlRaiseError();
292 * Roll back (undo) the current transaction.
296 if ($this->transaction_opcount > 0) {
297 if (!@mssql_select_db($this->_db, $this->connection)) {
298 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
300 $result = @mssql_query('ROLLBACK TRAN', $this->connection);
301 $this->transaction_opcount = 0;
303 return $this->mssqlRaiseError();
310 // {{{ affectedRows()
313 * Gets the number of rows affected by the last query.
314 * if the last query was a select, returns 0.
316 * @return number of rows affected by the last query or DB_ERROR
318 function affectedRows()
320 if (DB::isManip($this->last_query)) {
321 $res = @mssql_query('select @@rowcount', $this->connection);
323 return $this->mssqlRaiseError();
325 $ar = @mssql_fetch_row($res);
329 @mssql_free_result($res);
342 * Returns the next free id in a sequence
344 * @param string $seq_name name of the sequence
345 * @param boolean $ondemand when true, the seqence is automatically
346 * created if it does not exist
348 * @return int the next id number in the sequence. DB_Error if problem.
351 * @see DB_common::nextID()
354 function nextId($seq_name, $ondemand = true)
356 $seqname = $this->getSequenceName($seq_name);
357 if (!@mssql_select_db($this->_db, $this->connection)) {
358 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
362 $this->pushErrorHandling(PEAR_ERROR_RETURN);
363 $result = $this->query("INSERT INTO $seqname (vapor) VALUES (0)");
364 $this->popErrorHandling();
365 if ($ondemand && DB::isError($result) &&
366 ($result->getCode() == DB_ERROR || $result->getCode() == DB_ERROR_NOSUCHTABLE))
369 $result = $this->createSequence($seq_name);
370 if (DB::isError($result)) {
371 return $this->raiseError($result);
373 } elseif (!DB::isError($result)) {
374 $result =& $this->query("SELECT @@IDENTITY FROM $seqname");
380 if (DB::isError($result)) {
381 return $this->raiseError($result);
383 $result = $result->fetchRow(DB_FETCHMODE_ORDERED);
388 * Creates a new sequence
390 * @param string $seq_name name of the new sequence
392 * @return int DB_OK on success. A DB_Error object is returned if
396 * @see DB_common::createSequence()
399 function createSequence($seq_name)
401 $seqname = $this->getSequenceName($seq_name);
402 return $this->query("CREATE TABLE $seqname ".
403 '([id] [int] IDENTITY (1, 1) NOT NULL ,' .
404 '[vapor] [int] NULL)');
408 // {{{ dropSequence()
413 * @param string $seq_name name of the sequence to be deleted
415 * @return int DB_OK on success. DB_Error if problems.
418 * @see DB_common::dropSequence()
421 function dropSequence($seq_name)
423 $seqname = $this->getSequenceName($seq_name);
424 return $this->query("DROP TABLE $seqname");
431 * Determine MS SQL Server error code by querying @@ERROR.
433 * @return mixed mssql's native error code or DB_ERROR if unknown.
435 function errorNative()
437 $res = @mssql_query('select @@ERROR as ErrorCode', $this->connection);
441 $row = @mssql_fetch_row($res);
449 * Determine PEAR::DB error code from mssql's native codes.
451 * If <var>$nativecode</var> isn't known yet, it will be looked up.
453 * @param mixed $nativecode mssql error code, if known
454 * @return integer an error number from a DB error constant
457 function errorCode($nativecode = null)
460 $nativecode = $this->errorNative();
462 if (isset($this->errorcode_map[$nativecode])) {
463 return $this->errorcode_map[$nativecode];
470 // {{{ mssqlRaiseError()
473 * Gather information about an error, then use that info to create a
474 * DB error object and finally return that object.
476 * @param integer $code PEAR error number (usually a DB constant) if
477 * manually raising an error
478 * @return object DB error object
481 * @see DB_common::raiseError()
483 function mssqlRaiseError($code = null)
485 $message = @mssql_get_last_message();
487 $code = $this->errorNative();
489 return $this->raiseError($this->errorCode($code), null, null, null,
497 * Returns information about a table or a result set.
499 * NOTE: only supports 'table' and 'flags' if <var>$result</var>
502 * @param object|string $result DB_result object from a query or a
503 * string containing the name of a table
504 * @param int $mode a valid tableInfo mode
505 * @return array an associative array with the information requested
506 * or an error object if something is wrong
509 * @see DB_common::tableInfo()
511 function tableInfo($result, $mode = null)
513 if (isset($result->result)) {
515 * Probably received a result object.
516 * Extract the result resource identifier.
518 $id = $result->result;
520 } elseif (is_string($result)) {
522 * Probably received a table name.
523 * Create a result resource identifier.
525 if (!@mssql_select_db($this->_db, $this->connection)) {
526 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
528 $id = @mssql_query("SELECT * FROM $result WHERE 1=0",
533 * Probably received a result resource identifier.
535 * Depricated. Here for compatibility only.
541 if (!is_resource($id)) {
542 return $this->mssqlRaiseError(DB_ERROR_NEED_MORE_DATA);
545 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
546 $case_func = 'strtolower';
548 $case_func = 'strval';
551 $count = @mssql_num_fields($id);
553 // made this IF due to performance (one if is faster than $count if's)
555 for ($i=0; $i<$count; $i++) {
556 $res[$i]['table'] = $got_string ? $case_func($result) : '';
557 $res[$i]['name'] = $case_func(@mssql_field_name($id, $i));
558 $res[$i]['type'] = @mssql_field_type($id, $i);
559 $res[$i]['len'] = @mssql_field_length($id, $i);
560 // We only support flags for tables
561 $res[$i]['flags'] = $got_string ? $this->_mssql_field_flags($result, $res[$i]['name']) : '';
565 $res['num_fields']= $count;
567 for ($i=0; $i<$count; $i++) {
568 $res[$i]['table'] = $got_string ? $case_func($result) : '';
569 $res[$i]['name'] = $case_func(@mssql_field_name($id, $i));
570 $res[$i]['type'] = @mssql_field_type($id, $i);
571 $res[$i]['len'] = @mssql_field_length($id, $i);
572 // We only support flags for tables
573 $res[$i]['flags'] = $got_string ? $this->_mssql_field_flags($result, $res[$i]['name']) : '';
575 if ($mode & DB_TABLEINFO_ORDER) {
576 $res['order'][$res[$i]['name']] = $i;
578 if ($mode & DB_TABLEINFO_ORDERTABLE) {
579 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
584 // free the result only if we were called on a table
586 @mssql_free_result($id);
592 // {{{ getSpecialQuery()
595 * Returns the query needed to get some backend info
596 * @param string $type What kind of info you want to retrieve
597 * @return string The SQL query string
599 function getSpecialQuery($type)
603 return "select name from sysobjects where type = 'U' order by name";
605 return "select name from sysobjects where type = 'V'";
612 // {{{ _mssql_field_flags()
615 * Get the flags for a field, currently supports "not_null", "primary_key",
616 * "auto_increment" (mssql identity), "timestamp" (mssql timestamp),
617 * "unique_key" (mssql unique index, unique check or primary_key) and
618 * "multiple_key" (multikey index)
620 * mssql timestamp is NOT similar to the mysql timestamp so this is maybe
621 * not useful at all - is the behaviour of mysql_field_flags that primary
622 * keys are alway unique? is the interpretation of multiple_key correct?
624 * @param string The table name
625 * @param string The field
626 * @author Joern Barthel <j_barthel@web.de>
629 function _mssql_field_flags($table, $column)
631 static $tableName = null;
632 static $flags = array();
634 if ($table != $tableName) {
639 // get unique and primary keys
640 $res = $this->getAll("EXEC SP_HELPINDEX[$table]", DB_FETCHMODE_ASSOC);
642 foreach ($res as $val) {
643 $keys = explode(', ', $val['index_keys']);
645 if (sizeof($keys) > 1) {
646 foreach ($keys as $key) {
647 $this->_add_flag($flags[$key], 'multiple_key');
651 if (strpos($val['index_description'], 'primary key')) {
652 foreach ($keys as $key) {
653 $this->_add_flag($flags[$key], 'primary_key');
655 } elseif (strpos($val['index_description'], 'unique')) {
656 foreach ($keys as $key) {
657 $this->_add_flag($flags[$key], 'unique_key');
662 // get auto_increment, not_null and timestamp
663 $res = $this->getAll("EXEC SP_COLUMNS[$table]", DB_FETCHMODE_ASSOC);
665 foreach ($res as $val) {
666 $val = array_change_key_case($val, CASE_LOWER);
667 if ($val['nullable'] == '0') {
668 $this->_add_flag($flags[$val['column_name']], 'not_null');
670 if (strpos($val['type_name'], 'identity')) {
671 $this->_add_flag($flags[$val['column_name']], 'auto_increment');
673 if (strpos($val['type_name'], 'timestamp')) {
674 $this->_add_flag($flags[$val['column_name']], 'timestamp');
679 if (array_key_exists($column, $flags)) {
680 return(implode(' ', $flags[$column]));
689 * Adds a string to the flags array if the flag is not yet in there
690 * - if there is no flag present the array is created.
692 * @param reference Reference to the flag-array
693 * @param value The flag value
695 * @author Joern Barthel <j_barthel@web.de>
697 function _add_flag(&$array, $value)
699 if (!is_array($array)) {
700 $array = array($value);
701 } elseif (!in_array($value, $array)) {
702 array_push($array, $value);
707 // {{{ quoteIdentifier()
710 * Quote a string so it can be safely used as a table / column name
712 * Quoting style depends on which database driver is being used.
714 * @param string $str identifier name to be quoted
716 * @return string quoted identifier string
721 function quoteIdentifier($str)
723 return '[' . str_replace(']', ']]', $str) . ']';