See Release Notes
Long Term Support Release
Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 403]
1 <?php 2 /** 3 * MSSQL driver via ODBC 4 * 5 * This file is part of ADOdb, a Database Abstraction Layer library for PHP. 6 * 7 * @package ADOdb 8 * @link https://adodb.org Project's web site and documentation 9 * @link https://github.com/ADOdb/ADOdb Source code and issue tracker 10 * 11 * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause 12 * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option, 13 * any later version. This means you can use it in proprietary products. 14 * See the LICENSE.md file distributed with this source code for details. 15 * @license BSD-3-Clause 16 * @license LGPL-2.1-or-later 17 * 18 * @copyright 2000-2013 John Lim 19 * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community 20 */ 21 22 // security - hide paths 23 if (!defined('ADODB_DIR')) die(); 24 25 if (!defined('_ADODB_ODBC_LAYER')) { 26 include_once(ADODB_DIR."/drivers/adodb-odbc.inc.php"); 27 } 28 29 30 class ADODB_odbc_mssql extends ADODB_odbc { 31 var $databaseType = 'odbc_mssql'; 32 var $fmtDate = "'Y-m-d'"; 33 var $fmtTimeStamp = "'Y-m-d\TH:i:s'"; 34 var $_bindInputArray = true; 35 var $metaDatabasesSQL = "select name from sysdatabases where name <> 'master'"; 36 var $metaTablesSQL="select name,case when type='U' then 'T' else 'V' end from sysobjects where (type='U' or type='V') and (name not in ('sysallocations','syscolumns','syscomments','sysdepends','sysfilegroups','sysfiles','sysfiles1','sysforeignkeys','sysfulltextcatalogs','sysindexes','sysindexkeys','sysmembers','sysobjects','syspermissions','sysprotects','sysreferences','systypes','sysusers','sysalternates','sysconstraints','syssegments','REFERENTIAL_CONSTRAINTS','CHECK_CONSTRAINTS','CONSTRAINT_TABLE_USAGE','CONSTRAINT_COLUMN_USAGE','VIEWS','VIEW_TABLE_USAGE','VIEW_COLUMN_USAGE','SCHEMATA','TABLES','TABLE_CONSTRAINTS','TABLE_PRIVILEGES','COLUMNS','COLUMN_DOMAIN_USAGE','COLUMN_PRIVILEGES','DOMAINS','DOMAIN_CONSTRAINTS','KEY_COLUMN_USAGE'))"; 37 var $metaColumnsSQL = # xtype==61 is datetime 38 "select c.name,t.name,c.length,c.isnullable, c.status, 39 (case when c.xusertype=61 then 0 else c.xprec end), 40 (case when c.xusertype=61 then 0 else c.xscale end) 41 from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'"; 42 var $hasTop = 'top'; // support mssql/interbase SELECT TOP 10 * FROM TABLE 43 var $sysDate = 'GetDate()'; 44 var $sysTimeStamp = 'GetDate()'; 45 var $leftOuter = '*='; 46 var $rightOuter = '=*'; 47 var $substr = 'substring'; 48 var $length = 'len'; 49 var $ansiOuter = true; // for mssql7 or later 50 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000 51 var $hasInsertID = true; 52 var $connectStmt = 'SET CONCAT_NULL_YIELDS_NULL OFF'; # When SET CONCAT_NULL_YIELDS_NULL is ON, 53 # concatenating a null value with a string yields a NULL result 54 55 // crashes php... 56 function ServerInfo() 57 { 58 global $ADODB_FETCH_MODE; 59 $save = $ADODB_FETCH_MODE; 60 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 61 $row = $this->GetRow("execute sp_server_info 2"); 62 $ADODB_FETCH_MODE = $save; 63 if (!is_array($row)) return false; 64 $arr['description'] = $row[2]; 65 $arr['version'] = ADOConnection::_findvers($arr['description']); 66 return $arr; 67 } 68 69 function IfNull( $field, $ifNull ) 70 { 71 return " ISNULL($field, $ifNull) "; // if MS SQL Server 72 } 73 74 protected function _insertID($table = '', $column = '') 75 { 76 // SCOPE_IDENTITY() 77 // Returns the last IDENTITY value inserted into an IDENTITY column in 78 // the same scope. A scope is a module -- a stored procedure, trigger, 79 // function, or batch. Thus, two statements are in the same scope if 80 // they are in the same stored procedure, function, or batch. 81 return $this->GetOne($this->identitySQL); 82 } 83 84 public function metaForeignKeys($table, $owner = '', $upper = false, $associative = false) 85 { 86 global $ADODB_FETCH_MODE; 87 88 $save = $ADODB_FETCH_MODE; 89 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 90 $table = $this->qstr(strtoupper($table)); 91 92 $sql = 93 "select object_name(constid) as constraint_name, 94 col_name(fkeyid, fkey) as column_name, 95 object_name(rkeyid) as referenced_table_name, 96 col_name(rkeyid, rkey) as referenced_column_name 97 from sysforeignkeys 98 where upper(object_name(fkeyid)) = $table 99 order by constraint_name, referenced_table_name, keyno"; 100 101 $constraints = $this->GetArray($sql); 102 103 $ADODB_FETCH_MODE = $save; 104 105 $arr = false; 106 foreach($constraints as $constr) { 107 //print_r($constr); 108 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3]; 109 } 110 if (!$arr) return false; 111 112 $arr2 = false; 113 114 foreach($arr as $k => $v) { 115 foreach($v as $a => $b) { 116 if ($upper) $a = strtoupper($a); 117 $arr2[$a] = $b; 118 } 119 } 120 return $arr2; 121 } 122 123 function MetaTables($ttype=false,$showSchema=false,$mask=false) 124 { 125 if ($mask) {//$this->debug=1; 126 $save = $this->metaTablesSQL; 127 $mask = $this->qstr($mask); 128 $this->metaTablesSQL .= " AND name like $mask"; 129 } 130 $ret = ADOConnection::MetaTables($ttype,$showSchema); 131 132 if ($mask) { 133 $this->metaTablesSQL = $save; 134 } 135 return $ret; 136 } 137 138 function MetaColumns($table, $normalize=true) 139 { 140 141 $this->_findschema($table,$schema); 142 if ($schema) { 143 $dbName = $this->database; 144 $this->SelectDB($schema); 145 } 146 global $ADODB_FETCH_MODE; 147 $save = $ADODB_FETCH_MODE; 148 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 149 150 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false); 151 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table)); 152 153 if ($schema) { 154 $this->SelectDB($dbName); 155 } 156 157 if (isset($savem)) $this->SetFetchMode($savem); 158 $ADODB_FETCH_MODE = $save; 159 if (!is_object($rs)) { 160 $false = false; 161 return $false; 162 } 163 164 $retarr = array(); 165 while (!$rs->EOF){ 166 $fld = new ADOFieldObject(); 167 $fld->name = $rs->fields[0]; 168 $fld->type = $rs->fields[1]; 169 170 $fld->not_null = (!$rs->fields[3]); 171 $fld->auto_increment = ($rs->fields[4] == 128); // sys.syscolumns status field. 0x80 = 128 ref: http://msdn.microsoft.com/en-us/library/ms186816.aspx 172 173 174 if (isset($rs->fields[5]) && $rs->fields[5]) { 175 if ($rs->fields[5]>0) $fld->max_length = $rs->fields[5]; 176 $fld->scale = $rs->fields[6]; 177 if ($fld->scale>0) $fld->max_length += 1; 178 } else 179 $fld->max_length = $rs->fields[2]; 180 181 182 if ($save == ADODB_FETCH_NUM) { 183 $retarr[] = $fld; 184 } else { 185 $retarr[strtoupper($fld->name)] = $fld; 186 } 187 $rs->MoveNext(); 188 } 189 190 $rs->Close(); 191 return $retarr; 192 193 } 194 195 196 function MetaIndexes($table,$primary=false, $owner=false) 197 { 198 $table = $this->qstr($table); 199 200 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno, 201 CASE WHEN I.indid BETWEEN 1 AND 254 AND (I.status & 2048 = 2048 OR I.Status = 16402 AND O.XType = 'V') THEN 1 ELSE 0 END AS IsPK, 202 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique 203 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id 204 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid 205 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid 206 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table 207 ORDER BY O.name, I.Name, K.keyno"; 208 209 global $ADODB_FETCH_MODE; 210 $save = $ADODB_FETCH_MODE; 211 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 212 if ($this->fetchMode !== FALSE) { 213 $savem = $this->SetFetchMode(FALSE); 214 } 215 216 $rs = $this->Execute($sql); 217 if (isset($savem)) { 218 $this->SetFetchMode($savem); 219 } 220 $ADODB_FETCH_MODE = $save; 221 222 if (!is_object($rs)) { 223 return FALSE; 224 } 225 226 $indexes = array(); 227 while ($row = $rs->FetchRow()) { 228 if (!$primary && $row[5]) continue; 229 230 $indexes[$row[0]]['unique'] = $row[6]; 231 $indexes[$row[0]]['columns'][] = $row[1]; 232 } 233 return $indexes; 234 } 235 236 function _query($sql,$inputarr=false) 237 { 238 if (is_string($sql)) $sql = str_replace('||','+',$sql); 239 return ADODB_odbc::_query($sql,$inputarr); 240 } 241 242 function SetTransactionMode( $transaction_mode ) 243 { 244 $this->_transmode = $transaction_mode; 245 if (empty($transaction_mode)) { 246 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED'); 247 return; 248 } 249 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode; 250 $this->Execute("SET TRANSACTION ".$transaction_mode); 251 } 252 253 // "Stein-Aksel Basma" <basma@accelero.no> 254 // tested with MSSQL 2000 255 function MetaPrimaryKeys($table, $owner = false) 256 { 257 global $ADODB_FETCH_MODE; 258 259 $schema = ''; 260 $this->_findschema($table,$schema); 261 //if (!$schema) $schema = $this->database; 262 if ($schema) $schema = "and k.table_catalog like '$schema%'"; 263 264 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k, 265 information_schema.table_constraints tc 266 where tc.constraint_name = k.constraint_name and tc.constraint_type = 267 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position "; 268 269 $savem = $ADODB_FETCH_MODE; 270 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; 271 $a = $this->GetCol($sql); 272 $ADODB_FETCH_MODE = $savem; 273 274 if ($a && sizeof($a)>0) return $a; 275 $false = false; 276 return $false; 277 } 278 279 function SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0) 280 { 281 $nrows = (int) $nrows; 282 $offset = (int) $offset; 283 if ($nrows > 0 && $offset <= 0) { 284 $sql = preg_replace( 285 '/(^\s*select\s+(distinctrow|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql); 286 $rs = $this->Execute($sql,$inputarr); 287 } else 288 $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache); 289 290 return $rs; 291 } 292 293 // Format date column in sql string given an input format that understands Y M D 294 function SQLDate($fmt, $col=false) 295 { 296 if (!$col) $col = $this->sysTimeStamp; 297 $s = ''; 298 299 $len = strlen($fmt); 300 for ($i=0; $i < $len; $i++) { 301 if ($s) $s .= '+'; 302 $ch = $fmt[$i]; 303 switch($ch) { 304 case 'Y': 305 case 'y': 306 $s .= "datename(yyyy,$col)"; 307 break; 308 case 'M': 309 $s .= "convert(char(3),$col,0)"; 310 break; 311 case 'm': 312 $s .= "replace(str(month($col),2),' ','0')"; 313 break; 314 case 'Q': 315 case 'q': 316 $s .= "datename(quarter,$col)"; 317 break; 318 case 'D': 319 case 'd': 320 $s .= "replace(str(day($col),2),' ','0')"; 321 break; 322 case 'h': 323 $s .= "substring(convert(char(14),$col,0),13,2)"; 324 break; 325 326 case 'H': 327 $s .= "replace(str(datepart(hh,$col),2),' ','0')"; 328 break; 329 330 case 'i': 331 $s .= "replace(str(datepart(mi,$col),2),' ','0')"; 332 break; 333 case 's': 334 $s .= "replace(str(datepart(ss,$col),2),' ','0')"; 335 break; 336 case 'a': 337 case 'A': 338 $s .= "substring(convert(char(19),$col,0),18,2)"; 339 break; 340 341 default: 342 if ($ch == '\\') { 343 $i++; 344 $ch = substr($fmt,$i,1); 345 } 346 $s .= $this->qstr($ch); 347 break; 348 } 349 } 350 return $s; 351 } 352 353 /** 354 * Returns a substring of a varchar type field 355 * 356 * The SQL server version varies because the length is mandatory, so 357 * we append a reasonable string length 358 * 359 * @param string $fld The field to sub-string 360 * @param int $start The start point 361 * @param int $length An optional length 362 * 363 * @return The SQL text 364 */ 365 function substr($fld,$start,$length=0) 366 { 367 if ($length == 0) 368 /* 369 * The length available to varchar is 2GB, but that makes no 370 * sense in a substring, so I'm going to arbitrarily limit 371 * the length to 1K, but you could change it if you want 372 */ 373 $length = 1024; 374 375 $text = "SUBSTRING($fld,$start,$length)"; 376 return $text; 377 } 378 379 /** 380 * Returns the maximum size of a MetaType C field. Because of the 381 * database design, SQL Server places no limits on the size of data inserted 382 * Although the actual limit is 2^31-1 bytes. 383 * 384 * @return int 385 */ 386 function charMax() 387 { 388 return ADODB_STRINGMAX_NOLIMIT; 389 } 390 391 /** 392 * Returns the maximum size of a MetaType X field. Because of the 393 * database design, SQL Server places no limits on the size of data inserted 394 * Although the actual limit is 2^31-1 bytes. 395 * 396 * @return int 397 */ 398 function textMax() 399 { 400 return ADODB_STRINGMAX_NOLIMIT; 401 } 402 403 // returns concatenated string 404 // MSSQL requires integers to be cast as strings 405 // automatically cast every datatype to VARCHAR(255) 406 // @author David Rogers (introspectshun) 407 function Concat() 408 { 409 $s = ""; 410 $arr = func_get_args(); 411 412 // Split single record on commas, if possible 413 if (sizeof($arr) == 1) { 414 foreach ($arr as $arg) { 415 $args = explode(',', $arg); 416 } 417 $arr = $args; 418 } 419 420 array_walk( 421 $arr, 422 function(&$value, $key) { 423 $value = "CAST(" . $value . " AS VARCHAR(255))"; 424 } 425 ); 426 $s = implode('+',$arr); 427 if (sizeof($arr) > 0) return "$s"; 428 429 return ''; 430 } 431 432 } 433 434 class ADORecordSet_odbc_mssql extends ADORecordSet_odbc { 435 436 var $databaseType = 'odbc_mssql'; 437 438 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body