3 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
6 * The PEAR DB driver for PHP's mssql extension
7 * for interacting with Microsoft SQL Server databases
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 Sterling Hughes <sterling@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';
31 require_once 'common.php';
34 * The methods PEAR DB uses to interact with PHP's mssql extension
35 * for interacting with Microsoft SQL Server databases
37 * These methods overload the ones declared in DB_common.
39 * DB's mssql driver is only for Microsfoft SQL Server databases.
41 * If you're connecting to a Sybase database, you MUST specify "sybase"
42 * as the "phptype" in the DSN.
44 * This class only works correctly if you have compiled PHP using
45 * --with-mssql=[dir_to_FreeTDS].
49 * @author Sterling Hughes <sterling@php.net>
50 * @author Daniel Convissor <danielc@php.net>
51 * @copyright 1997-2007 The PHP Group
52 * @license http://www.php.net/license/3_0.txt PHP License 3.0
53 * @version Release: 1.9.2
54 * @link http://pear.php.net/package/DB
56 class DB_mssql extends DB_common
61 * The DB driver type (mysql, oci8, odbc, etc.)
64 public $phptype = 'mssql';
67 * The database syntax variant to be used (db2, access, etc.), if any
70 public $dbsyntax = 'mssql';
73 * The capabilities of this DB implementation
75 * The 'new_link' element contains the PHP version that first provided
76 * new_link support for this DBMS. Contains false if it's unsupported.
78 * Meaning of the 'limit' element:
79 * + 'emulate' = emulate with fetch row by number
80 * + 'alter' = alter the query
85 public $features = array(
92 'transactions' => true,
96 * A mapping of native error codes to DB error codes
99 // XXX Add here error codes ie: 'S100E' => DB_ERROR_SYNTAX
100 public $errorcode_map = array(
101 102 => DB_ERROR_SYNTAX,
102 110 => DB_ERROR_VALUE_COUNT_ON_ROW,
103 155 => DB_ERROR_NOSUCHFIELD,
104 156 => DB_ERROR_SYNTAX,
105 170 => DB_ERROR_SYNTAX,
106 207 => DB_ERROR_NOSUCHFIELD,
107 208 => DB_ERROR_NOSUCHTABLE,
108 245 => DB_ERROR_INVALID_NUMBER,
109 319 => DB_ERROR_SYNTAX,
110 321 => DB_ERROR_NOSUCHFIELD,
111 325 => DB_ERROR_SYNTAX,
112 336 => DB_ERROR_SYNTAX,
113 515 => DB_ERROR_CONSTRAINT_NOT_NULL,
114 547 => DB_ERROR_CONSTRAINT,
115 1018 => DB_ERROR_SYNTAX,
116 1035 => DB_ERROR_SYNTAX,
117 1913 => DB_ERROR_ALREADY_EXISTS,
118 2209 => DB_ERROR_SYNTAX,
119 2223 => DB_ERROR_SYNTAX,
120 2248 => DB_ERROR_SYNTAX,
121 2256 => DB_ERROR_SYNTAX,
122 2257 => DB_ERROR_SYNTAX,
123 2627 => DB_ERROR_CONSTRAINT,
124 2714 => DB_ERROR_ALREADY_EXISTS,
125 3607 => DB_ERROR_DIVZERO,
126 3701 => DB_ERROR_NOSUCHTABLE,
127 7630 => DB_ERROR_SYNTAX,
128 8134 => DB_ERROR_DIVZERO,
129 9303 => DB_ERROR_SYNTAX,
130 9317 => DB_ERROR_SYNTAX,
131 9318 => DB_ERROR_SYNTAX,
132 9331 => DB_ERROR_SYNTAX,
133 9332 => DB_ERROR_SYNTAX,
134 15253 => DB_ERROR_SYNTAX,
138 * The raw database connection created by PHP
144 * The DSN information for connecting to a database
147 public $dsn = array();
151 * Should data manipulation queries be committed automatically?
155 public $autocommit = true;
158 * The quantity of transactions begun
160 * {@internal While this is private, it can't actually be designated
161 * private in PHP 5 because it is directly accessed in the test suite.}}
166 public $transaction_opcount = 0;
169 * The database specified in the DSN
171 * It's a fix to allow calls to different databases in the same script.
183 * This constructor calls <kbd>parent::__construct()</kbd>
187 public function __construct()
189 parent::__construct();
196 * Connect to the database server, log in and open the database
198 * Don't call this method directly. Use DB::connect() instead.
200 * @param array $dsn the data source name
201 * @param bool $persistent should the connection be persistent?
205 public function connect($dsn, $persistent = false)
207 if (!PEAR::loadExtension('mssql') && !PEAR::loadExtension('sybase')
208 && !PEAR::loadExtension('sybase_ct')) {
209 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
213 if ($dsn['dbsyntax']) {
214 $this->dbsyntax = $dsn['dbsyntax'];
218 $dsn['hostspec'] ? $dsn['hostspec'] : 'localhost',
219 $dsn['username'] ? $dsn['username'] : null,
220 $dsn['password'] ? $dsn['password'] : null,
223 $params[0] .= ((substr(PHP_OS, 0, 3) == 'WIN') ? ',' : ':')
227 $connect_function = $persistent ? 'mssql_pconnect' : 'mssql_connect';
229 $this->connection = @call_user_func_array($connect_function, $params);
231 if (!$this->connection) {
232 return $this->raiseError(
233 DB_ERROR_CONNECT_FAILED,
237 @mssql_get_last_message()
240 if ($dsn['database']) {
241 if (!@mssql_select_db($dsn['database'], $this->connection)) {
242 return $this->raiseError(
243 DB_ERROR_NODBSELECTED,
247 @mssql_get_last_message()
250 $this->_db = $dsn['database'];
259 * Disconnects from the database server
261 * @return bool TRUE on success, FALSE on failure
263 public function disconnect()
265 $ret = @mssql_close($this->connection);
266 $this->connection = null;
274 * Sends a query to the database server
276 * @param string the SQL query string
278 * @return mixed + a PHP result resrouce for successful SELECT queries
279 * + the DB_OK constant for other successful queries
280 * + a DB_Error object on failure
282 public function simpleQuery($query)
284 $ismanip = $this->_checkManip($query);
285 $this->last_query = $query;
286 if (!@mssql_select_db($this->_db, $this->connection)) {
287 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
289 $query = $this->modifyQuery($query);
290 if (!$this->autocommit && $ismanip) {
291 if ($this->transaction_opcount == 0) {
292 $result = @mssql_query('BEGIN TRAN', $this->connection);
294 return $this->mssqlRaiseError();
297 $this->transaction_opcount++;
299 $result = @mssql_query($query, $this->connection);
301 return $this->mssqlRaiseError();
303 // Determine which queries that should return data, and which
304 // should return an error code only.
305 return $ismanip ? DB_OK : $result;
312 * Produces a DB_Error object regarding the current problem
315 * @return object the DB_Error object
317 * @see DB_common::raiseError(),
318 * DB_mssql::errorNative(), DB_mssql::errorCode()
320 public function mssqlRaiseError($code = null)
322 $message = @mssql_get_last_message();
324 $code = $this->errorNative();
326 return $this->raiseError(
327 $this->errorCode($code, $message),
339 * Gets the DBMS' native error code produced by the last query
341 * @return int the DBMS' error code
343 public function errorNative()
345 $res = @mssql_query('select @@ERROR as ErrorCode', $this->connection);
349 $row = @mssql_fetch_row($res);
357 * Determines PEAR::DB error code from mssql's native codes.
359 * If <var>$nativecode</var> isn't known yet, it will be looked up.
361 * @param mixed $nativecode mssql error code, if known
363 * @return integer an error number from a DB error constant
366 public function errorCode($nativecode = null, $msg = '')
369 $nativecode = $this->errorNative();
371 if (isset($this->errorcode_map[$nativecode])) {
372 if ($nativecode == 3701
373 && preg_match('/Cannot drop the index/i', $msg)) {
374 return DB_ERROR_NOT_FOUND;
376 return $this->errorcode_map[$nativecode];
386 * Move the internal mssql result pointer to the next available result
388 * @param a valid fbsql result resource
392 * @return true if a result is available otherwise return false
394 public function nextResult($result)
396 return @mssql_next_result($result);
403 * Places a row from the result set into the given array
405 * Formating of the array and the data therein are configurable.
406 * See DB_result::fetchInto() for more information.
408 * This method is not meant to be called directly. Use
409 * DB_result::fetchInto() instead. It can't be declared "protected"
410 * because DB_result is a separate object.
412 * @param resource $result the query result resource
413 * @param array $arr the referenced array to put the data in
414 * @param int $fetchmode how the resulting array should be indexed
415 * @param int $rownum the row number to fetch (0 = first row)
417 * @return mixed DB_OK on success, NULL when the end of a result set is
418 * reached or on failure
420 * @see DB_result::fetchInto()
422 public function fetchInto($result, &$arr, $fetchmode, $rownum = null)
424 if ($rownum !== null) {
425 if (!@mssql_data_seek($result, $rownum)) {
429 if ($fetchmode & DB_FETCHMODE_ASSOC) {
430 $arr = @mssql_fetch_assoc($result);
431 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
432 $arr = array_change_key_case($arr, CASE_LOWER);
435 $arr = @mssql_fetch_row($result);
440 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
441 $this->_rtrimArrayValues($arr);
443 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
444 $this->_convertNullArrayValuesToEmpty($arr);
453 * Deletes the result set and frees the memory occupied by the result set
455 * This method is not meant to be called directly. Use
456 * DB_result::free() instead. It can't be declared "protected"
457 * because DB_result is a separate object.
459 * @param resource $result PHP's query result resource
461 * @return bool TRUE on success, FALSE if $result is invalid
463 * @see DB_result::free()
465 public function freeResult($result)
467 return is_resource($result) ? mssql_free_result($result) : false;
474 * Gets the number of columns in a result set
476 * This method is not meant to be called directly. Use
477 * DB_result::numCols() instead. It can't be declared "protected"
478 * because DB_result is a separate object.
480 * @param resource $result PHP's query result resource
484 * @see DB_result::numCols()
486 public function numCols($result)
488 $cols = @mssql_num_fields($result);
490 return $this->mssqlRaiseError();
499 * Gets the number of rows in a result set
501 * This method is not meant to be called directly. Use
502 * DB_result::numRows() instead. It can't be declared "protected"
503 * because DB_result is a separate object.
505 * @param resource $result PHP's query result resource
509 * @see DB_result::numRows()
511 public function numRows($result)
513 $rows = @mssql_num_rows($result);
514 if ($rows === false) {
515 return $this->mssqlRaiseError();
521 // {{{ affectedRows()
524 * Enables or disables automatic commits
526 * @param bool $onoff true turns it on, false turns it off
528 * @return int DB_OK on success. A DB_Error object if the driver
529 * doesn't support auto-committing transactions.
531 public function autoCommit($onoff = false)
533 // XXX if $this->transaction_opcount > 0, we should probably
534 // issue a warning here.
535 $this->autocommit = $onoff ? true : false;
543 * Commits the current transaction
547 public function commit()
549 if ($this->transaction_opcount > 0) {
550 if (!@mssql_select_db($this->_db, $this->connection)) {
551 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
553 $result = @mssql_query('COMMIT TRAN', $this->connection);
554 $this->transaction_opcount = 0;
556 return $this->mssqlRaiseError();
563 * Reverts the current transaction
567 public function rollback()
569 if ($this->transaction_opcount > 0) {
570 if (!@mssql_select_db($this->_db, $this->connection)) {
571 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
573 $result = @mssql_query('ROLLBACK TRAN', $this->connection);
574 $this->transaction_opcount = 0;
576 return $this->mssqlRaiseError();
583 // {{{ dropSequence()
586 * Determines the number of rows affected by a data maniuplation query
588 * 0 is returned for queries that don't manipulate data.
592 public function affectedRows()
594 if ($this->_last_query_manip) {
595 $res = @mssql_query('select @@rowcount', $this->connection);
597 return $this->mssqlRaiseError();
599 $ar = @mssql_fetch_row($res);
603 @mssql_free_result($res);
613 // {{{ escapeSimple()
616 * Returns the next free id in a sequence
618 * @param string $seq_name name of the sequence
619 * @param boolean $ondemand when true, the seqence is automatically
620 * created if it does not exist
623 * A DB_Error object on failure.
625 * @see DB_common::nextID(), DB_common::getSequenceName(),
626 * DB_mssql::createSequence(), DB_mssql::dropSequence()
628 public function nextId($seq_name, $ondemand = true)
630 $seqname = $this->getSequenceName($seq_name);
631 if (!@mssql_select_db($this->_db, $this->connection)) {
632 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
636 $this->pushErrorHandling(PEAR_ERROR_RETURN);
637 $result = $this->query("INSERT INTO $seqname (vapor) VALUES (0)");
638 $this->popErrorHandling();
639 if ($ondemand && DB::isError($result) &&
640 ($result->getCode() == DB_ERROR || $result->getCode() == DB_ERROR_NOSUCHTABLE)) {
642 $result = $this->createSequence($seq_name);
643 if (DB::isError($result)) {
644 return $this->raiseError($result);
646 } elseif (!DB::isError($result)) {
647 $result = $this->query("SELECT IDENT_CURRENT('$seqname')");
648 if (DB::isError($result)) {
649 /* Fallback code for MS SQL Server 7.0, which doesn't have
650 * IDENT_CURRENT. This is *not* safe for concurrent
651 * requests, and really, if you're using it, you're in a
652 * world of hurt. Nevertheless, it's here to ensure BC. See
653 * bug #181 for the gory details.*/
654 $result = $this->query("SELECT @@IDENTITY FROM $seqname");
661 if (DB::isError($result)) {
662 return $this->raiseError($result);
664 $result = $result->fetchRow(DB_FETCHMODE_ORDERED);
669 // {{{ quoteIdentifier()
672 * Creates a new sequence
674 * @param string $seq_name name of the new sequence
676 * @return int DB_OK on success. A DB_Error object on failure.
678 * @see DB_common::createSequence(), DB_common::getSequenceName(),
679 * DB_mssql::nextID(), DB_mssql::dropSequence()
681 public function createSequence($seq_name)
683 return $this->query('CREATE TABLE '
684 . $this->getSequenceName($seq_name)
685 . ' ([id] [int] IDENTITY (1, 1) NOT NULL,'
686 . ' [vapor] [int] NULL)');
690 // {{{ mssqlRaiseError()
695 * @param string $seq_name name of the sequence to be deleted
697 * @return int DB_OK on success. A DB_Error object on failure.
699 * @see DB_common::dropSequence(), DB_common::getSequenceName(),
700 * DB_mssql::nextID(), DB_mssql::createSequence()
702 public function dropSequence($seq_name)
704 return $this->query('DROP TABLE ' . $this->getSequenceName($seq_name));
711 * Escapes a string in a manner suitable for SQL Server.
713 * @param string $str the string to be escaped
714 * @return string the escaped string
716 * @see DB_common::quoteSmart()
717 * @since Method available since Release 1.6.0
719 public function escapeSimple($str)
722 array("'", "\\\r\n", "\\\n"),
723 array("''", "\\\\\r\n\r\n", "\\\\\n\n"),
732 * Quotes a string so it can be safely used as a table or column name
734 * @param string $str identifier name to be quoted
736 * @return string quoted identifier string
738 * @see DB_common::quoteIdentifier()
739 * @since Method available since Release 1.6.0
741 public function quoteIdentifier($str)
743 return '[' . str_replace(']', ']]', $str) . ']';
750 * Returns information about a table or a result set
752 * NOTE: only supports 'table' and 'flags' if <var>$result</var>
755 * @param object|string $result DB_result object from a query or a
756 * string containing the name of a table.
757 * While this also accepts a query result
758 * resource identifier, this behavior is
760 * @param int $mode a valid tableInfo mode
762 * @return array|object
763 * A DB_Error object on failure.
765 * @see DB_common::tableInfo()
767 public function tableInfo($result, $mode = null)
769 if (is_string($result)) {
771 * Probably received a table name.
772 * Create a result resource identifier.
774 if (!@mssql_select_db($this->_db, $this->connection)) {
775 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
778 "SELECT * FROM $result WHERE 1=0",
782 } elseif (isset($result->result)) {
784 * Probably received a result object.
785 * Extract the result resource identifier.
787 $id = $result->result;
791 * Probably received a result resource identifier.
793 * Deprecated. Here for compatibility only.
799 if (!is_resource($id)) {
800 return $this->mssqlRaiseError(DB_ERROR_NEED_MORE_DATA);
803 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
804 $case_func = 'strtolower';
806 $case_func = 'strval';
809 $count = @mssql_num_fields($id);
813 $res['num_fields'] = $count;
816 for ($i = 0; $i < $count; $i++) {
818 $flags = $this->_mssql_field_flags(
820 @mssql_field_name($id, $i)
822 if (DB::isError($flags)) {
830 'table' => $got_string ? $case_func($result) : '',
831 'name' => $case_func(@mssql_field_name($id, $i)),
832 'type' => @mssql_field_type($id, $i),
833 'len' => @mssql_field_length($id, $i),
836 if ($mode & DB_TABLEINFO_ORDER) {
837 $res['order'][$res[$i]['name']] = $i;
839 if ($mode & DB_TABLEINFO_ORDERTABLE) {
840 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
844 // free the result only if we were called on a table
846 @mssql_free_result($id);
852 // {{{ _mssql_field_flags()
855 * Get a column's flags
857 * Supports "not_null", "primary_key",
858 * "auto_increment" (mssql identity), "timestamp" (mssql timestamp),
859 * "unique_key" (mssql unique index, unique check or primary_key) and
860 * "multiple_key" (multikey index)
862 * mssql timestamp is NOT similar to the mysql timestamp so this is maybe
863 * not useful at all - is the behaviour of mysql_field_flags that primary
864 * keys are alway unique? is the interpretation of multiple_key correct?
866 * @param string $table the table name
867 * @param string $column the field name
869 * @return array|string
872 * @author Joern Barthel <j_barthel@web.de>
874 public function _mssql_field_flags($table, $column)
876 static $tableName = null;
877 static $flags = array();
879 if ($table != $tableName) {
883 // get unique and primary keys
884 $res = $this->getAll("EXEC SP_HELPINDEX $table", DB_FETCHMODE_ASSOC);
885 if (DB::isError($res)) {
889 foreach ($res as $val) {
890 $keys = explode(', ', $val['index_keys']);
892 if (sizeof($keys) > 1) {
893 foreach ($keys as $key) {
894 $this->_add_flag($flags[$key], 'multiple_key');
898 if (strpos($val['index_description'], 'primary key')) {
899 foreach ($keys as $key) {
900 $this->_add_flag($flags[$key], 'primary_key');
902 } elseif (strpos($val['index_description'], 'unique')) {
903 foreach ($keys as $key) {
904 $this->_add_flag($flags[$key], 'unique_key');
909 // get auto_increment, not_null and timestamp
910 $res = $this->getAll("EXEC SP_COLUMNS $table", DB_FETCHMODE_ASSOC);
911 if (DB::isError($res)) {
915 foreach ($res as $val) {
916 $val = array_change_key_case($val, CASE_LOWER);
917 if ($val['nullable'] == '0') {
918 $this->_add_flag($flags[$val['column_name']], 'not_null');
920 if (strpos($val['type_name'], 'identity')) {
921 $this->_add_flag($flags[$val['column_name']], 'auto_increment');
923 if (strpos($val['type_name'], 'timestamp')) {
924 $this->_add_flag($flags[$val['column_name']], 'timestamp');
929 if (array_key_exists($column, $flags)) {
930 return (implode(' ', $flags[$column]));
939 * Adds a string to the flags array if the flag is not yet in there
940 * - if there is no flag present the array is created
942 * @param array &$array the reference to the flag-array
943 * @param string $value the flag value
948 * @author Joern Barthel <j_barthel@web.de>
950 public function _add_flag(&$array, $value)
952 if (!is_array($array)) {
953 $array = array($value);
954 } elseif (!in_array($value, $array)) {
955 array_push($array, $value);
960 // {{{ getSpecialQuery()
963 * Obtains the query string needed for listing a given type of objects
965 * @param string $type the kind of objects you want to retrieve
967 * @return string the SQL query string or null if the driver doesn't
968 * support the object type requested
971 * @see DB_common::getListOf()
973 public function getSpecialQuery($type)
977 return "SELECT name FROM sysobjects WHERE type = 'U'"
980 return "SELECT name FROM sysobjects WHERE type = 'V'";