3 // +----------------------------------------------------------------------+
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1997-2002 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 // +----------------------------------------------------------------------+
19 // $Id: mssql.php,v 1.2 2004-04-26 20:44:37 rurban Exp $
21 // Database independent query interface definition for PHP's Microsoft SQL Server
24 // Based on DB 1.3 from the pear.php.net repository.
25 // The only modifications made have been modification of the include paths.
27 rcs_id('$Id: mssql.php,v 1.2 2004-04-26 20:44:37 rurban Exp $');
28 rcs_id('From Pear CVS: Id: mssql.php,v 1.4 2002/05/23 09:09:39 cox Exp');
30 require_once 'DB/common.php';
32 class DB_mssql extends DB_common
35 var $phptype, $dbsyntax;
36 var $prepare_tokens = array();
37 var $prepare_types = array();
38 var $transaction_opcount = 0;
39 var $autocommit = true;
45 $this->phptype = 'mssql';
46 $this->dbsyntax = 'mssql';
47 $this->features = array(
50 'transactions' => true,
53 // XXX Add here error codes ie: 'S100E' => DB_ERROR_SYNTAX
54 $this->errorcode_map = array(
59 function connect($dsninfo, $persistent = false)
61 if (!DB::assertExtension('mssql') && !DB::assertExtension('sybase')
62 && !DB::assertExtension('sybase_ct'))
64 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
66 $this->dsn = $dsninfo;
67 $user = $dsninfo['username'];
68 $pw = $dsninfo['password'];
69 $dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
71 $connect_function = $persistent ? 'mssql_pconnect' : 'mssql_connect';
73 if ($dbhost && $user && $pw) {
74 $conn = @$connect_function($dbhost, $user, $pw);
75 } elseif ($dbhost && $user) {
76 $conn = @$connect_function($dbhost, $user);
78 $conn = @$connect_function($dbhost);
81 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
82 null, mssql_get_last_message());
84 if ($dsninfo['database']) {
85 if (!@mssql_select_db($dsninfo['database'], $conn)) {
86 return $this->raiseError(DB_ERROR_NODBSELECTED, null, null,
87 null, mssql_get_last_message());
89 $this->_db = $dsninfo['database'];
91 $this->connection = $conn;
97 $ret = @mssql_close($this->connection);
98 $this->connection = null;
102 function simpleQuery($query)
104 $ismanip = DB::isManip($query);
105 $this->last_query = $query;
106 if (!@mssql_select_db($this->_db, $this->connection)) {
107 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
109 $query = $this->modifyQuery($query);
110 if (!$this->autocommit && $ismanip) {
111 if ($this->transaction_opcount == 0) {
112 $result = @mssql_query('BEGIN TRAN', $this->connection);
114 return $this->mssqlRaiseError();
117 $this->transaction_opcount++;
119 $result = @mssql_query($query, $this->connection);
121 return $this->mssqlRaiseError();
123 // Determine which queries that should return data, and which
124 // should return an error code only.
125 return $ismanip ? DB_OK : $result;
131 * Move the internal mssql result pointer to the next available result
133 * @param a valid fbsql result resource
137 * @return true if a result is available otherwise return false
139 function nextResult($result)
141 return mssql_next_result($result);
146 function &fetchRow($result, $fetchmode = DB_FETCHMODE_DEFAULT, $rownum=null)
148 if ($fetchmode == DB_FETCHMODE_DEFAULT) {
149 $fetchmode = $this->fetchmode;
151 $res = $this->fetchInto ($result, $arr, $fetchmode, $rownum);
152 if ($res !== DB_OK) {
158 function fetchInto($result, &$ar, $fetchmode, $rownum=null)
160 if ($rownum !== null) {
161 if (!@mssql_data_seek($result, $rownum)) {
165 if ($fetchmode & DB_FETCHMODE_ASSOC) {
166 $ar = @mssql_fetch_array($result);
168 $ar = @mssql_fetch_row($result);
171 /* This throws informative error messages,
173 if ($msg = mssql_get_last_message()) {
174 return $this->raiseError($msg);
182 function freeResult($result)
184 if (is_resource($result)) {
185 return @mssql_free_result($result);
187 if (!isset($this->prepare_tokens[$result])) {
190 unset($this->prepare_tokens[$result]);
191 unset($this->prepare_types[$result]);
195 function numCols($result)
197 $cols = @mssql_num_fields($result);
199 return $this->mssqlRaiseError();
204 function numRows($result)
206 $rows = @mssql_num_rows($result);
207 if ($rows === false) {
208 return $this->mssqlRaiseError();
214 * Enable/disable automatic commits
216 function autoCommit($onoff = false)
218 // XXX if $this->transaction_opcount > 0, we should probably
219 // issue a warning here.
220 $this->autocommit = $onoff ? true : false;
228 * Commit the current transaction.
232 if ($this->transaction_opcount > 0) {
233 if (!@mssql_select_db($this->_db, $this->connection)) {
234 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
236 $result = @mssql_query('COMMIT TRAN', $this->connection);
237 $this->transaction_opcount = 0;
239 return $this->mssqlRaiseError();
249 * Roll back (undo) the current transaction.
253 if ($this->transaction_opcount > 0) {
254 if (!@mssql_select_db($this->_db, $this->connection)) {
255 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
257 $result = @mssql_query('ROLLBACK TRAN', $this->connection);
258 $this->transaction_opcount = 0;
260 return $this->mssqlRaiseError();
267 // {{{ affectedRows()
270 * Gets the number of rows affected by the last query.
271 * if the last query was a select, returns 0.
273 * @return number of rows affected by the last query or DB_ERROR
275 function affectedRows()
277 if (DB::isManip($this->last_query)) {
278 $res = @mssql_query('select @@rowcount', $this->connection);
280 return $this->mssqlRaiseError();
282 $ar = @mssql_fetch_row($res);
286 @mssql_free_result($res);
297 * Get the next value in a sequence. We emulate sequences
298 * for MSSQL. Will create the sequence if it does not exist.
302 * @param $seq_name the name of the sequence
304 * @param $ondemand whether to create the sequence table on demand
307 * @return a sequence integer, or a DB error
309 function nextId($seq_name, $ondemand = true)
311 $seqname = $this->getSequenceName($seq_name);
312 if (!@mssql_select_db($this->_db, $this->connection)) {
313 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
317 $this->pushErrorHandling(PEAR_ERROR_RETURN);
318 $result = $this->query("INSERT INTO $seqname (vapor) VALUES (0)");
319 $this->popErrorHandling();
320 if ($ondemand && DB::isError($result) &&
321 ($result->getCode() == DB_ERROR || $result->getCode() == DB_ERROR_NOSUCHTABLE))
324 $result = $this->createSequence($seq_name);
325 if (DB::isError($result)) {
329 $result = $this->query("SELECT @@IDENTITY FROM $seqname");
333 if (DB::isError($result)) {
334 return $this->raiseError($result);
336 $result = $result->fetchRow(DB_FETCHMODE_ORDERED);
341 // {{{ createSequence()
343 function createSequence($seq_name)
345 $seqname = $this->getSequenceName($seq_name);
346 return $this->query("CREATE TABLE $seqname ".
347 '([id] [int] IDENTITY (1, 1) NOT NULL ,' .
348 '[vapor] [int] NULL)');
351 // {{{ dropSequence()
353 function dropSequence($seq_name)
355 $seqname = $this->getSequenceName($seq_name);
356 return $this->query("DROP TABLE $seqname");
362 $this->pushErrorHandling(PEAR_ERROR_RETURN);
363 $error_code = $this->getOne('select @@ERROR as ErrorCode');
364 $this->popErrorHandling();
366 if (!isset($this->errorcode_map[$error_code])) {
372 function mssqlRaiseError($code = null)
374 if ($code !== null) {
375 $code = $this->errorCode();
376 if (DB::isError($code)) {
377 return $this->raiseError($code);
380 return $this->raiseError($code, null, null, null, mssql_get_last_message());
385 * Returns information about a table or a result set
387 * NOTE: doesn't support table name and flags if called from a db_result
389 * @param mixed $resource SQL Server result identifier or table name
390 * @param int $mode A valid tableInfo mode (DB_TABLEINFO_ORDERTABLE or
391 * DB_TABLEINFO_ORDER)
393 * @return array An array with all the information
396 function tableInfo($result, $mode = null)
404 * depending on $mode, metadata returns the following values:
406 * - mode is false (default):
408 * [0]["table"] table name
409 * [0]["name"] field name
410 * [0]["type"] field type
411 * [0]["len"] field length
412 * [0]["flags"] field flags
414 * - mode is DB_TABLEINFO_ORDER
416 * ["num_fields"] number of metadata records
417 * [0]["table"] table name
418 * [0]["name"] field name
419 * [0]["type"] field type
420 * [0]["len"] field length
421 * [0]["flags"] field flags
422 * ["order"][field name] index of field named "field name"
423 * The last one is used, if you have a field name, but no index.
424 * Test: if (isset($result['meta']['myfield'])) { ...
426 * - mode is DB_TABLEINFO_ORDERTABLE
427 * the same as above. but additionally
428 * ["ordertable"][table name][field name] index of field
431 * this is, because if you have fields from different
432 * tables with the same field name * they override each
433 * other with DB_TABLEINFO_ORDER
435 * you can combine DB_TABLEINFO_ORDER and
436 * DB_TABLEINFO_ORDERTABLE with DB_TABLEINFO_ORDER |
437 * DB_TABLEINFO_ORDERTABLE * or with DB_TABLEINFO_FULL
440 // if $result is a string, then we want information about a
441 // table without a resultset
443 if (is_string($result)) {
444 if (!@mssql_select_db($this->_db, $this->connection)) {
445 return $this->mssqlRaiseError(DB_ERROR_NODBSELECTED);
447 $id = mssql_query("SELECT * FROM $result", $this->connection);
449 return $this->mssqlRaiseError();
451 } else { // else we want information about a resultset
454 return $this->mssqlRaiseError();
458 $count = @mssql_num_fields($id);
460 // made this IF due to performance (one if is faster than $count if's)
463 for ($i=0; $i<$count; $i++) {
464 $res[$i]['table'] = (is_string($result)) ? $result : '';
465 $res[$i]['name'] = @mssql_field_name($id, $i);
466 $res[$i]['type'] = @mssql_field_type($id, $i);
467 $res[$i]['len'] = @mssql_field_length($id, $i);
468 $res[$i]['flags'] = '';
472 $res['num_fields']= $count;
474 for ($i=0; $i<$count; $i++) {
475 $res[$i]['table'] = (is_string($result)) ? $result : '';
476 $res[$i]['name'] = @mssql_field_name($id, $i);
477 $res[$i]['type'] = @mssql_field_type($id, $i);
478 $res[$i]['len'] = @mssql_field_length($id, $i);
479 $res[$i]['flags'] = '';
480 if ($mode & DB_TABLEINFO_ORDER) {
481 $res['order'][$res[$i]['name']] = $i;
483 if ($mode & DB_TABLEINFO_ORDERTABLE) {
484 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
489 // free the result only if we were called on a table
490 if (is_string($result)) {
491 @mssql_free_result($id);
496 // {{{ getSpecialQuery()
499 * Returns the query needed to get some backend info
500 * @param string $type What kind of info you want to retrieve
501 * @return string The SQL query string
503 function getSpecialQuery($type)
507 $sql = "select name from sysobjects where type = 'U' order by name";
510 $sql = "select name from sysobjects where type = 'V'";