Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 4.0.x will end 8 May 2023 (12 months).
- Bug fixes for security issues in 4.0.x will end 13 November 2023 (18 months).
- PHP version: minimum PHP 7.3.0 Note: the minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is also supported.
/lib/adodb/drivers/ -> adodb-mssqlnative.inc.php (source)
Differences Between: [Versions 310 and 400] [Versions 311 and 400] [Versions 39 and 400] [Versions 400 and 401] [Versions 400 and 402] [Versions 400 and 403]
1 <?php 2 /** 3 * Native MSSQL driver. 4 * 5 * Requires mssql client. Works on Windows. 6 * https://docs.microsoft.com/sql/connect/php 7 * 8 * This file is part of ADOdb, a Database Abstraction Layer library for PHP. 9 * 10 * @package ADOdb 11 * @link https://adodb.org Project's web site and documentation 12 * @link https://github.com/ADOdb/ADOdb Source code and issue tracker 13 * 14 * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause 15 * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option, 16 * any later version. This means you can use it in proprietary products. 17 * See the LICENSE.md file distributed with this source code for details. 18 * @license BSD-3-Clause 19 * @license LGPL-2.1-or-later 20 * 21 * @copyright 2000-2013 John Lim 22 * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community 23 */ 24 25 // security - hide paths 26 if (!defined('ADODB_DIR')) die(); 27 28 if (!function_exists('sqlsrv_configure')) { 29 die("mssqlnative extension not installed"); 30 } 31 32 if (!function_exists('sqlsrv_set_error_handling')) { 33 function sqlsrv_set_error_handling($constant) { 34 sqlsrv_configure("WarningsReturnAsErrors", $constant); 35 } 36 } 37 if (!function_exists('sqlsrv_log_set_severity')) { 38 function sqlsrv_log_set_severity($constant) { 39 sqlsrv_configure("LogSeverity", $constant); 40 } 41 } 42 if (!function_exists('sqlsrv_log_set_subsystems')) { 43 function sqlsrv_log_set_subsystems($constant) { 44 sqlsrv_configure("LogSubsystems", $constant); 45 } 46 } 47 48 class ADODB_mssqlnative extends ADOConnection { 49 var $databaseType = "mssqlnative"; 50 var $dataProvider = "mssqlnative"; 51 var $replaceQuote = "''"; // string to use to replace quotes 52 var $fmtDate = "'Y-m-d'"; 53 var $fmtTimeStamp = "'Y-m-d\TH:i:s'"; 54 /** 55 * Enabling InsertID capability will cause execution of an extra query 56 * {@see $identitySQL} after each INSERT statement. To improve performance 57 * when inserting a large number of records, you should switch this off by 58 * calling {@see enableLastInsertID enableLastInsertID(false)}. 59 * @var bool $hasInsertID 60 */ 61 var $hasInsertID = true; 62 var $substr = "substring"; 63 var $length = 'len'; 64 var $hasAffectedRows = true; 65 var $poorAffectedRows = false; 66 var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'"; 67 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','dtproperties'))"; 68 var $metaColumnsSQL = 69 "select c.name, 70 t.name as type, 71 c.length, 72 c.xprec as precision, 73 c.xscale as scale, 74 c.isnullable as nullable, 75 c.cdefault as default_value, 76 c.xtype, 77 t.length as type_length, 78 sc.is_identity 79 from syscolumns c 80 join systypes t on t.xusertype=c.xusertype 81 join sysobjects o on o.id=c.id 82 join sys.tables st on st.name=o.name 83 join sys.columns sc on sc.object_id = st.object_id and sc.name=c.name 84 where o.name='%s'"; 85 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE 86 var $hasGenID = true; 87 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)'; 88 var $sysTimeStamp = 'GetDate()'; 89 var $maxParameterLen = 4000; 90 var $arrayClass = 'ADORecordSet_array_mssqlnative'; 91 var $uniqueSort = true; 92 var $leftOuter = '*='; 93 var $rightOuter = '=*'; 94 var $ansiOuter = true; // for mssql7 or later 95 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000 96 var $uniqueOrderBy = true; 97 var $_bindInputArray = true; 98 var $_dropSeqSQL = "drop table %s"; 99 100 var $connectionInfo = array('ReturnDatesAsStrings'=>true); 101 var $cachedSchemaFlush = false; 102 103 var $sequences = false; 104 var $mssql_version = ''; 105 106 function __construct() 107 { 108 if ($this->debug) { 109 ADOConnection::outp("<pre>"); 110 sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL ); 111 sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL ); 112 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL); 113 sqlsrv_configure('WarningsReturnAsErrors', 0); 114 } else { 115 sqlsrv_set_error_handling(0); 116 sqlsrv_log_set_severity(0); 117 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL); 118 sqlsrv_configure('WarningsReturnAsErrors', 0); 119 } 120 } 121 122 /** 123 * Initializes the SQL Server version. 124 * Dies if connected to a non-supported version (2000 and older) 125 */ 126 function ServerVersion() { 127 $data = $this->ServerInfo(); 128 preg_match('/^\d{2}/', $data['version'], $matches); 129 $version = (int)reset($matches); 130 131 // We only support SQL Server 2005 and up 132 if($version < 9) { 133 die("SQL SERVER VERSION {$data['version']} NOT SUPPORTED IN mssqlnative DRIVER"); 134 } 135 136 $this->mssql_version = $version; 137 } 138 139 function ServerInfo() { 140 global $ADODB_FETCH_MODE; 141 static $arr = false; 142 if (is_array($arr)) 143 return $arr; 144 if ($this->fetchMode === false) { 145 $savem = $ADODB_FETCH_MODE; 146 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 147 } elseif ($this->fetchMode >=0 && $this->fetchMode <=2) { 148 $savem = $this->fetchMode; 149 } else 150 $savem = $this->SetFetchMode(ADODB_FETCH_NUM); 151 152 $arrServerInfo = sqlsrv_server_info($this->_connectionID); 153 $ADODB_FETCH_MODE = $savem; 154 $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase']; 155 $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']); 156 return $arr; 157 } 158 159 function IfNull( $field, $ifNull ) 160 { 161 return " ISNULL($field, $ifNull) "; // if MS SQL Server 162 } 163 164 public function enableLastInsertID($enable = true) { 165 $this->hasInsertID = $enable; 166 $this->lastInsID = false; 167 } 168 169 /** 170 * Get the last value inserted into an IDENTITY column. 171 * 172 * The value will actually be set in {@see _query()} when executing an 173 * INSERT statement, but only if the connection's $hasInsertId property 174 * is true; this can be set with {@see enableLastInsertId()}. 175 * 176 * @inheritDoc 177 */ 178 protected function _insertID($table = '', $column = '') 179 { 180 return $this->lastInsID; 181 } 182 183 function _affectedrows() 184 { 185 if ($this->_queryID) 186 return sqlsrv_rows_affected($this->_queryID); 187 } 188 189 function GenID($seq='adodbseq',$start=1) { 190 switch($this->mssql_version){ 191 case 9: 192 case 10: 193 return $this->GenID2008($seq, $start); 194 break; 195 default: 196 return $this->GenID2012($seq, $start); 197 break; 198 } 199 } 200 201 function CreateSequence($seq='adodbseq',$start=1) 202 { 203 switch($this->mssql_version){ 204 case 9: 205 case 10: 206 return $this->CreateSequence2008($seq, $start); 207 break; 208 default: 209 return $this->CreateSequence2012($seq, $start); 210 break; 211 } 212 } 213 214 /** 215 * For Server 2005,2008, duplicate a sequence with an identity table 216 */ 217 function CreateSequence2008($seq='adodbseq',$start=1) 218 { 219 if($this->debug) ADOConnection::outp("<hr>CreateSequence($seq,$start)"); 220 sqlsrv_begin_transaction($this->_connectionID); 221 $start -= 1; 222 $this->Execute("create table $seq (id int)");//was float(53) 223 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 224 if (!$ok) { 225 if($this->debug) ADOConnection::outp("<hr>Error: ROLLBACK"); 226 sqlsrv_rollback($this->_connectionID); 227 return false; 228 } 229 sqlsrv_commit($this->_connectionID); 230 return true; 231 } 232 233 /** 234 * Proper Sequences Only available to Server 2012 and up 235 */ 236 function CreateSequence2012($seq='adodbseq',$start=1){ 237 if (!$this->sequences){ 238 $sql = "SELECT name FROM sys.sequences"; 239 $this->sequences = $this->GetCol($sql); 240 } 241 $ok = $this->Execute("CREATE SEQUENCE $seq START WITH $start INCREMENT BY 1"); 242 if (!$ok) 243 die("CANNOT CREATE SEQUENCE" . print_r(sqlsrv_errors(),true)); 244 $this->sequences[] = $seq; 245 } 246 247 /** 248 * For Server 2005,2008, duplicate a sequence with an identity table 249 */ 250 function GenID2008($seq='adodbseq',$start=1) 251 { 252 if($this->debug) ADOConnection::outp("<hr>CreateSequence($seq,$start)"); 253 sqlsrv_begin_transaction($this->_connectionID); 254 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1"); 255 if (!$ok) { 256 $start -= 1; 257 $this->Execute("create table $seq (id int)");//was float(53) 258 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 259 if (!$ok) { 260 if($this->debug) ADOConnection::outp("<hr>Error: ROLLBACK"); 261 sqlsrv_rollback($this->_connectionID); 262 return false; 263 } 264 } 265 $num = $this->GetOne("select id from $seq"); 266 sqlsrv_commit($this->_connectionID); 267 return $num; 268 } 269 /** 270 * Only available to Server 2012 and up 271 * Cannot do this the normal adodb way by trapping an error if the 272 * sequence does not exist because sql server will auto create a 273 * sequence with the starting number of -9223372036854775808 274 */ 275 function GenID2012($seq='adodbseq',$start=1) 276 { 277 278 /* 279 * First time in create an array of sequence names that we 280 * can use in later requests to see if the sequence exists 281 * the overhead is creating a list of sequences every time 282 * we need access to at least 1. If we really care about 283 * performance, we could maybe flag a 'nocheck' class variable 284 */ 285 if (!$this->sequences){ 286 $sql = "SELECT name FROM sys.sequences"; 287 $this->sequences = $this->GetCol($sql); 288 } 289 if (!is_array($this->sequences) 290 || is_array($this->sequences) && !in_array($seq,$this->sequences)){ 291 $this->CreateSequence2012($seq, $start); 292 293 } 294 $num = $this->GetOne("SELECT NEXT VALUE FOR $seq"); 295 return $num; 296 } 297 298 // Format date column in sql string given an input format that understands Y M D 299 function SQLDate($fmt, $col=false) 300 { 301 if (!$col) { 302 $col = $this->sysTimeStamp; 303 } 304 $s = ''; 305 306 $ConvertableFmt=array( 307 "m/d/Y"=>101, "m/d/y"=>101 // US 308 ,"Y.m.d"=>102, "y.m.d"=>102 // ANSI 309 ,"d/m/Y"=>103, "d/m/y"=>103 // French /english 310 ,"d.m.Y"=>104, "d.m.y"=>104 // German 311 ,"d-m-Y"=>105, "d-m-y"=>105 // Italian 312 ,"m-d-Y"=>110, "m-d-y"=>110 // US Dash 313 ,"Y/m/d"=>111, "y/m/d"=>111 // Japan 314 ,"Ymd"=>112, "ymd"=>112 // ISO 315 ,"H:i:s"=>108 // Time 316 ); 317 if (key_exists($fmt,$ConvertableFmt)) { 318 return "convert (varchar ,$col," . $ConvertableFmt[$fmt] . ")"; 319 } 320 321 $len = strlen($fmt); 322 for ($i=0; $i < $len; $i++) { 323 if ($s) $s .= '+'; 324 $ch = $fmt[$i]; 325 switch($ch) { 326 case 'Y': 327 case 'y': 328 $s .= "datename(yyyy,$col)"; 329 break; 330 case 'M': 331 $s .= "convert(char(3),$col,0)"; 332 break; 333 case 'm': 334 $s .= "replace(str(month($col),2),' ','0')"; 335 break; 336 case 'Q': 337 case 'q': 338 $s .= "datename(quarter,$col)"; 339 break; 340 case 'D': 341 case 'd': 342 $s .= "replace(str(day($col),2),' ','0')"; 343 break; 344 case 'h': 345 $s .= "substring(convert(char(14),$col,0),13,2)"; 346 break; 347 348 case 'H': 349 $s .= "replace(str(datepart(hh,$col),2),' ','0')"; 350 break; 351 352 case 'i': 353 $s .= "replace(str(datepart(mi,$col),2),' ','0')"; 354 break; 355 case 's': 356 $s .= "replace(str(datepart(ss,$col),2),' ','0')"; 357 break; 358 case 'a': 359 case 'A': 360 $s .= "substring(convert(char(19),$col,0),18,2)"; 361 break; 362 case 'l': 363 $s .= "datename(dw,$col)"; 364 break; 365 default: 366 if ($ch == '\\') { 367 $i++; 368 $ch = substr($fmt,$i,1); 369 } 370 $s .= $this->qstr($ch); 371 break; 372 } 373 } 374 return $s; 375 } 376 377 378 function BeginTrans() 379 { 380 if ($this->transOff) return true; 381 $this->transCnt += 1; 382 if ($this->debug) ADOConnection::outp('<hr>begin transaction'); 383 sqlsrv_begin_transaction($this->_connectionID); 384 return true; 385 } 386 387 function CommitTrans($ok=true) 388 { 389 if ($this->transOff) return true; 390 if ($this->debug) ADOConnection::outp('<hr>commit transaction'); 391 if (!$ok) return $this->RollbackTrans(); 392 if ($this->transCnt) $this->transCnt -= 1; 393 sqlsrv_commit($this->_connectionID); 394 return true; 395 } 396 397 function RollbackTrans() 398 { 399 if ($this->transOff) return true; 400 if ($this->debug) ADOConnection::outp('<hr>rollback transaction'); 401 if ($this->transCnt) $this->transCnt -= 1; 402 sqlsrv_rollback($this->_connectionID); 403 return true; 404 } 405 406 function SetTransactionMode( $transaction_mode ) 407 { 408 $this->_transmode = $transaction_mode; 409 if (empty($transaction_mode)) { 410 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED'); 411 return; 412 } 413 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode; 414 $this->Execute("SET TRANSACTION ".$transaction_mode); 415 } 416 417 /* 418 Usage: 419 420 $this->BeginTrans(); 421 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables 422 423 # some operation on both tables table1 and table2 424 425 $this->CommitTrans(); 426 427 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp 428 */ 429 function RowLock($tables,$where,$col='1 as adodbignore') 430 { 431 if ($col == '1 as adodbignore') $col = 'top 1 null as ignore'; 432 if (!$this->transCnt) $this->BeginTrans(); 433 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where"); 434 } 435 436 function SelectDB($dbName) 437 { 438 $this->database = $dbName; 439 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions 440 if ($this->_connectionID) { 441 $rs = $this->Execute('USE '.$dbName); 442 if($rs) { 443 return true; 444 } else return false; 445 } 446 else return false; 447 } 448 449 function ErrorMsg() 450 { 451 $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL); 452 if($retErrors != null) { 453 foreach($retErrors as $arrError) { 454 $this->_errorMsg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n"; 455 $this->_errorMsg .= "Error Code: ".$arrError[ 'code']."\n"; 456 $this->_errorMsg .= "Message: ".$arrError[ 'message']."\n"; 457 } 458 } 459 return $this->_errorMsg; 460 } 461 462 function ErrorNo() 463 { 464 $err = sqlsrv_errors(SQLSRV_ERR_ALL); 465 if ($err && $err[0]) 466 return $err[0]['code']; 467 else 468 return 0; 469 } 470 471 // returns true or false 472 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) 473 { 474 if (!function_exists('sqlsrv_connect')) 475 { 476 if ($this->debug) 477 ADOConnection::outp('Microsoft SQL Server native driver (mssqlnative) not installed'); 478 return null; 479 } 480 481 if (!empty($this->port)) 482 /* 483 * Port uses a comma 484 */ 485 $argHostname .= ",".$this->port; 486 487 $connectionInfo = $this->connectionInfo; 488 $connectionInfo["Database"] = $argDatabasename; 489 if ((string)$argUsername != '' || (string)$argPassword != '') 490 { 491 /* 492 * If they pass either a userid or password, we assume 493 * SQL Server authentication 494 */ 495 $connectionInfo["UID"] = $argUsername; 496 $connectionInfo["PWD"] = $argPassword; 497 498 if ($this->debug) 499 ADOConnection::outp('userid or password supplied, attempting connection with SQL Server Authentication'); 500 501 } 502 else 503 { 504 /* 505 * If they don't pass either value, we won't add them to the 506 * connection parameters. This will then force an attempt 507 * to use windows authentication 508 */ 509 if ($this->debug) 510 511 ADOConnection::outp('No userid or password supplied, attempting connection with Windows Authentication'); 512 } 513 514 515 /* 516 * Now merge in the passed connection parameters setting 517 */ 518 foreach ($this->connectionParameters as $options) 519 { 520 foreach($options as $parameter=>$value) 521 $connectionInfo[$parameter] = $value; 522 } 523 524 if ($this->debug) ADOConnection::outp("connecting to host: $argHostname params: ".var_export($connectionInfo,true)); 525 if(!($this->_connectionID = @sqlsrv_connect($argHostname,$connectionInfo))) 526 { 527 if ($this->debug) 528 ADOConnection::outp( 'Connection Failed: '.print_r( sqlsrv_errors(), true)); 529 return false; 530 } 531 532 $this->ServerVersion(); 533 534 return true; 535 } 536 537 // returns true or false 538 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 539 { 540 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!) 541 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename); 542 } 543 544 545 function Prepare($sql) 546 { 547 return $sql; // prepare does not work properly with bind parameters as bind parameters are managed by sqlsrv_prepare! 548 } 549 550 // returns concatenated string 551 // MSSQL requires integers to be cast as strings 552 // automatically cast every datatype to VARCHAR(255) 553 // @author David Rogers (introspectshun) 554 function Concat() 555 { 556 $s = ""; 557 $arr = func_get_args(); 558 559 // Split single record on commas, if possible 560 if (sizeof($arr) == 1) { 561 foreach ($arr as $arg) { 562 $args = explode(',', $arg); 563 } 564 $arr = $args; 565 } 566 567 array_walk( 568 $arr, 569 function(&$value, $key) { 570 $value = "CAST(" . $value . " AS VARCHAR(255))"; 571 } 572 ); 573 $s = implode('+',$arr); 574 if (sizeof($arr) > 0) return "$s"; 575 576 return ''; 577 } 578 579 /* 580 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars 581 So all your blobs must be of type "image". 582 583 Remember to set in php.ini the following... 584 585 ; Valid range 0 - 2147483647. Default = 4096. 586 mssql.textlimit = 0 ; zero to pass through 587 588 ; Valid range 0 - 2147483647. Default = 4096. 589 mssql.textsize = 0 ; zero to pass through 590 */ 591 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 592 { 593 594 if (strtoupper($blobtype) == 'CLOB') { 595 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where"; 596 return $this->Execute($sql) != false; 597 } 598 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where"; 599 return $this->Execute($sql) != false; 600 } 601 602 /** 603 * Execute a query. 604 * 605 * If executing an INSERT statement and $hasInsertId is true, will set 606 * $lastInsId. 607 * 608 * @param string $sql 609 * @param array $inputarr 610 * @return resource|false Query Id if successful, otherwise false 611 */ 612 function _query($sql, $inputarr = false) 613 { 614 $this->_errorMsg = false; 615 616 if (is_array($sql)) { 617 $sql = $sql[1]; 618 } 619 620 // Handle native driver flaw for retrieving the last insert ID 621 if ($this->hasInsertID) { 622 // Check if it's an INSERT statement 623 $retrieveLastInsertID = preg_match( 624 '/^\W*insert[\s\w()[\]",.]+values\s*\((?:[^;\']|\'\'|(?:(?:\'\')*\'[^\']+\'(?:\'\')*))*;?$/i', 625 $sql 626 ); 627 if ($retrieveLastInsertID) { 628 // Append the identity SQL, so it is executed in the same 629 // scope as the insert query. 630 $sql .= '; ' . $this->identitySQL; 631 } 632 } else { 633 $retrieveLastInsertID = false; 634 } 635 636 if ($inputarr) { 637 // Ensure that the input array is indexed numerically, as required 638 // by sqlsrv_query(). If param() was used to create portable binds 639 // then the array might be associative. 640 $inputarr = array_values($inputarr); 641 $rez = sqlsrv_query($this->_connectionID, $sql, $inputarr); 642 } else { 643 $rez = sqlsrv_query($this->_connectionID, $sql); 644 } 645 646 if ($this->debug) { 647 ADOConnection::outp("<hr>running query: " . var_export($sql, true) 648 . "<hr>input array: " . var_export($inputarr, true) 649 . "<hr>result: " . var_export($rez, true) 650 ); 651 } 652 653 $this->lastInsID = false; 654 if (!$rez) { 655 $rez = false; 656 } elseif ($retrieveLastInsertID) { 657 // Get the inserted id from the last result 658 // Note: loop is required as server may return more than one row, 659 // e.g. if triggers are involved (see #41) 660 while (sqlsrv_next_result($rez)) { 661 sqlsrv_fetch($rez); 662 $this->lastInsID = sqlsrv_get_field($rez, 0, SQLSRV_PHPTYPE_INT); 663 } 664 } 665 return $rez; 666 } 667 668 // returns true or false 669 function _close() 670 { 671 if ($this->transCnt) { 672 $this->RollbackTrans(); 673 } 674 if($this->_connectionID) { 675 $rez = sqlsrv_close($this->_connectionID); 676 } 677 $this->_connectionID = false; 678 return $rez; 679 } 680 681 682 function MetaIndexes($table,$primary=false, $owner = false) 683 { 684 $table = $this->qstr($table); 685 686 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno, 687 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, 688 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique 689 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id 690 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid 691 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid 692 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table 693 ORDER BY O.name, I.Name, K.keyno"; 694 695 global $ADODB_FETCH_MODE; 696 $save = $ADODB_FETCH_MODE; 697 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 698 if ($this->fetchMode !== FALSE) { 699 $savem = $this->SetFetchMode(FALSE); 700 } 701 702 $rs = $this->Execute($sql); 703 if (isset($savem)) { 704 $this->SetFetchMode($savem); 705 } 706 $ADODB_FETCH_MODE = $save; 707 708 if (!is_object($rs)) { 709 return FALSE; 710 } 711 712 $indexes = array(); 713 while ($row = $rs->FetchRow()) { 714 if (!$primary && $row[5]) continue; 715 716 $indexes[$row[0]]['unique'] = $row[6]; 717 $indexes[$row[0]]['columns'][] = $row[1]; 718 } 719 return $indexes; 720 } 721 722 function MetaForeignKeys($table, $owner=false, $upper=false) 723 { 724 global $ADODB_FETCH_MODE; 725 726 $save = $ADODB_FETCH_MODE; 727 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 728 $table = $this->qstr(strtoupper($table)); 729 730 $sql = 731 "select object_name(constid) as constraint_name, 732 col_name(fkeyid, fkey) as column_name, 733 object_name(rkeyid) as referenced_table_name, 734 col_name(rkeyid, rkey) as referenced_column_name 735 from sysforeignkeys 736 where upper(object_name(fkeyid)) = $table 737 order by constraint_name, referenced_table_name, keyno"; 738 739 $constraints = $this->GetArray($sql); 740 741 $ADODB_FETCH_MODE = $save; 742 743 $arr = false; 744 foreach($constraints as $constr) { 745 //print_r($constr); 746 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3]; 747 } 748 if (!$arr) return false; 749 750 $arr2 = false; 751 752 foreach($arr as $k => $v) { 753 foreach($v as $a => $b) { 754 if ($upper) $a = strtoupper($a); 755 if (is_array($arr2[$a])) { // a previous foreign key was define for this reference table, we merge the new one 756 $arr2[$a] = array_merge($arr2[$a], $b); 757 } else { 758 $arr2[$a] = $b; 759 } 760 } 761 } 762 return $arr2; 763 } 764 765 //From: Fernando Moreira <FMoreira@imediata.pt> 766 function MetaDatabases() 767 { 768 $this->SelectDB("master"); 769 $rs = $this->Execute($this->metaDatabasesSQL); 770 $rows = $rs->GetRows(); 771 $ret = array(); 772 for($i=0;$i<count($rows);$i++) { 773 $ret[] = $rows[$i][0]; 774 } 775 $this->SelectDB($this->database); 776 if($ret) 777 return $ret; 778 else 779 return false; 780 } 781 782 // "Stein-Aksel Basma" <basma@accelero.no> 783 // tested with MSSQL 2000 784 function MetaPrimaryKeys($table, $owner=false) 785 { 786 global $ADODB_FETCH_MODE; 787 788 $schema = ''; 789 $this->_findschema($table,$schema); 790 if (!$schema) $schema = $this->database; 791 if ($schema) $schema = "and k.table_catalog like '$schema%'"; 792 793 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k, 794 information_schema.table_constraints tc 795 where tc.constraint_name = k.constraint_name and tc.constraint_type = 796 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position "; 797 798 $savem = $ADODB_FETCH_MODE; 799 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 800 $a = $this->GetCol($sql); 801 $ADODB_FETCH_MODE = $savem; 802 803 if ($a && sizeof($a)>0) return $a; 804 $false = false; 805 return $false; 806 } 807 808 809 function MetaTables($ttype=false,$showSchema=false,$mask=false) 810 { 811 if ($mask) { 812 $save = $this->metaTablesSQL; 813 $mask = $this->qstr(($mask)); 814 $this->metaTablesSQL .= " AND name like $mask"; 815 } 816 $ret = ADOConnection::MetaTables($ttype,$showSchema); 817 818 if ($mask) { 819 $this->metaTablesSQL = $save; 820 } 821 return $ret; 822 } 823 function MetaColumns($table, $upper=true, $schema=false){ 824 825 /* 826 * A simple caching mechanism, to be replaced in ADOdb V6 827 */ 828 static $cached_columns = array(); 829 if ($this->cachedSchemaFlush) 830 $cached_columns = array(); 831 832 if (array_key_exists($table,$cached_columns)){ 833 return $cached_columns[$table]; 834 } 835 836 837 $this->_findschema($table,$schema); 838 if ($schema) { 839 $dbName = $this->database; 840 $this->SelectDB($schema); 841 } 842 global $ADODB_FETCH_MODE; 843 $save = $ADODB_FETCH_MODE; 844 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 845 846 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false); 847 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table)); 848 849 if ($schema) { 850 $this->SelectDB($dbName); 851 } 852 853 if (isset($savem)) $this->SetFetchMode($savem); 854 $ADODB_FETCH_MODE = $save; 855 if (!is_object($rs)) { 856 $false = false; 857 return $false; 858 } 859 860 $retarr = array(); 861 while (!$rs->EOF){ 862 863 $fld = new ADOFieldObject(); 864 if (array_key_exists(0,$rs->fields)) { 865 $fld->name = $rs->fields[0]; 866 $fld->type = $rs->fields[1]; 867 $fld->max_length = $rs->fields[2]; 868 $fld->precision = $rs->fields[3]; 869 $fld->scale = $rs->fields[4]; 870 $fld->not_null =!$rs->fields[5]; 871 $fld->has_default = $rs->fields[6]; 872 $fld->xtype = $rs->fields[7]; 873 $fld->type_length = $rs->fields[8]; 874 $fld->auto_increment= $rs->fields[9]; 875 } else { 876 $fld->name = $rs->fields['name']; 877 $fld->type = $rs->fields['type']; 878 $fld->max_length = $rs->fields['length']; 879 $fld->precision = $rs->fields['precision']; 880 $fld->scale = $rs->fields['scale']; 881 $fld->not_null =!$rs->fields['nullable']; 882 $fld->has_default = $rs->fields['default_value']; 883 $fld->xtype = $rs->fields['xtype']; 884 $fld->type_length = $rs->fields['type_length']; 885 $fld->auto_increment= $rs->fields['is_identity']; 886 } 887 888 if ($save == ADODB_FETCH_NUM) 889 $retarr[] = $fld; 890 else 891 $retarr[strtoupper($fld->name)] = $fld; 892 893 $rs->MoveNext(); 894 895 } 896 $rs->Close(); 897 $cached_columns[$table] = $retarr; 898 899 return $retarr; 900 } 901 902 /** 903 * Returns a substring of a varchar type field 904 * 905 * The SQL server version varies because the length is mandatory, so 906 * we append a reasonable string length 907 * 908 * @param string $fld The field to sub-string 909 * @param int $start The start point 910 * @param int $length An optional length 911 * 912 * @return The SQL text 913 */ 914 function substr($fld,$start,$length=0) 915 { 916 if ($length == 0) 917 /* 918 * The length available to varchar is 2GB, but that makes no 919 * sense in a substring, so I'm going to arbitrarily limit 920 * the length to 1K, but you could change it if you want 921 */ 922 $length = 1024; 923 924 $text = "SUBSTRING($fld,$start,$length)"; 925 return $text; 926 } 927 928 /** 929 * Returns the maximum size of a MetaType C field. Because of the 930 * database design, SQL Server places no limits on the size of data inserted 931 * Although the actual limit is 2^31-1 bytes. 932 * 933 * @return int 934 */ 935 function charMax() 936 { 937 return ADODB_STRINGMAX_NOLIMIT; 938 } 939 940 /** 941 * Returns the maximum size of a MetaType X field. Because of the 942 * database design, SQL Server places no limits on the size of data inserted 943 * Although the actual limit is 2^31-1 bytes. 944 * 945 * @return int 946 */ 947 function textMax() 948 { 949 return ADODB_STRINGMAX_NOLIMIT; 950 } 951 /** 952 * Lists procedures, functions and methods in an array. 953 * 954 * @param string $procedureNamePattern (optional) 955 * @param string $catalog (optional) 956 * @param string $schemaPattern (optional) 957 958 * @return array of stored objects in current database. 959 * 960 */ 961 public function metaProcedures($procedureNamePattern = null, $catalog = null, $schemaPattern = null) 962 { 963 $metaProcedures = array(); 964 $procedureSQL = ''; 965 $catalogSQL = ''; 966 $schemaSQL = ''; 967 968 if ($procedureNamePattern) 969 $procedureSQL = "AND ROUTINE_NAME LIKE " . strtoupper($this->qstr($procedureNamePattern)); 970 971 if ($catalog) 972 $catalogSQL = "AND SPECIFIC_SCHEMA=" . strtoupper($this->qstr($catalog)); 973 974 if ($schemaPattern) 975 $schemaSQL = "AND ROUTINE_SCHEMA LIKE {$this->qstr($schemaPattern)}"; 976 977 $fields = " ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_SCHEMA,ROUTINE_CATALOG"; 978 979 $SQL = "SELECT $fields 980 FROM {$this->database}.information_schema.routines 981 WHERE 1=1 982 $procedureSQL 983 $catalogSQL 984 $schemaSQL 985 ORDER BY ROUTINE_NAME 986 "; 987 988 $result = $this->execute($SQL); 989 990 if (!$result) 991 return false; 992 while ($r = $result->fetchRow()){ 993 if (!isset($r[0])) 994 /* 995 * Convert to numeric 996 */ 997 $r = array_values($r); 998 999 $procedureName = $r[0]; 1000 $schemaName = $r[2]; 1001 $routineCatalog= $r[3]; 1002 $metaProcedures[$procedureName] = array('type'=> $r[1], 1003 'catalog' => $routineCatalog, 1004 'schema' => $schemaName, 1005 'remarks' => '', 1006 ); 1007 } 1008 1009 return $metaProcedures; 1010 } 1011 1012 } 1013 1014 /*-------------------------------------------------------------------------------------- 1015 Class Name: Recordset 1016 --------------------------------------------------------------------------------------*/ 1017 1018 class ADORecordset_mssqlnative extends ADORecordSet { 1019 1020 var $databaseType = "mssqlnative"; 1021 var $canSeek = false; 1022 var $fieldOffset = 0; 1023 // _mths works only in non-localised system 1024 1025 /** 1026 * @var bool True if we have retrieved the fields metadata 1027 */ 1028 private $fieldObjectsRetrieved = false; 1029 1030 /* 1031 * Cross-reference the objects by name for easy access 1032 */ 1033 private $fieldObjectsIndex = array(); 1034 1035 /* 1036 * Cross references the dateTime objects for faster decoding 1037 */ 1038 private $dateTimeObjects = array(); 1039 1040 /* 1041 * flags that we have dateTimeObjects to handle 1042 */ 1043 private $hasDateTimeObjects = false; 1044 1045 /* 1046 * This is cross reference between how the types are stored 1047 * in SQL Server and their english-language description 1048 * -154 is a time field, see #432 1049 */ 1050 private $_typeConversion = array( 1051 -155 => 'datetimeoffset', 1052 -154 => 'char', 1053 -152 => 'xml', 1054 -151 => 'udt', 1055 -11 => 'uniqueidentifier', 1056 -10 => 'ntext', 1057 -9 => 'nvarchar', 1058 -8 => 'nchar', 1059 -7 => 'bit', 1060 -6 => 'tinyint', 1061 -5 => 'bigint', 1062 -4 => 'image', 1063 -3 => 'varbinary', 1064 -2 => 'timestamp', 1065 -1 => 'text', 1066 1 => 'char', 1067 2 => 'numeric', 1068 3 => 'decimal', 1069 4 => 'int', 1070 5 => 'smallint', 1071 6 => 'float', 1072 7 => 'real', 1073 12 => 'varchar', 1074 91 => 'date', 1075 93 => 'datetime' 1076 ); 1077 1078 1079 1080 1081 function __construct($id,$mode=false) 1082 { 1083 if ($mode === false) { 1084 global $ADODB_FETCH_MODE; 1085 $mode = $ADODB_FETCH_MODE; 1086 1087 } 1088 $this->fetchMode = $mode; 1089 parent::__construct($id); 1090 } 1091 1092 1093 function _initrs() 1094 { 1095 $this->_numOfRows = -1;//not supported 1096 // Cache the metadata right now 1097 $this->_fetchField(); 1098 1099 } 1100 1101 1102 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se> 1103 // get next resultset - requires PHP 4.0.5 or later 1104 function NextRecordSet() 1105 { 1106 if (!sqlsrv_next_result($this->_queryID)) return false; 1107 $this->_inited = false; 1108 $this->bind = false; 1109 $this->_currentRow = -1; 1110 $this->Init(); 1111 return true; 1112 } 1113 1114 /* Use associative array to get fields array */ 1115 function Fields($colname) 1116 { 1117 if (!is_array($this->fields)) 1118 /* 1119 * Too early 1120 */ 1121 return; 1122 if ($this->fetchMode != ADODB_FETCH_NUM) 1123 return $this->fields[$colname]; 1124 1125 if (!$this->bind) { 1126 $this->bind = array(); 1127 for ($i=0; $i < $this->_numOfFields; $i++) { 1128 $o = $this->FetchField($i); 1129 $this->bind[strtoupper($o->name)] = $i; 1130 } 1131 } 1132 1133 return $this->fields[$this->bind[strtoupper($colname)]]; 1134 } 1135 1136 /** 1137 * Returns: an object containing field information. 1138 * 1139 * Get column information in the Recordset object. fetchField() 1140 * can be used in order to obtain information about fields in a 1141 * certain query result. If the field offset isn't specified, 1142 * the next field that wasn't yet retrieved by fetchField() 1143 * is retrieved. 1144 * 1145 * @param int $fieldOffset (optional default=-1 for all 1146 * @return mixed an ADOFieldObject, or array of objects 1147 */ 1148 private function _fetchField($fieldOffset = -1) 1149 { 1150 if ($this->fieldObjectsRetrieved) { 1151 if ($this->fieldObjectsCache) { 1152 // Already got the information 1153 if ($fieldOffset == -1) { 1154 return $this->fieldObjectsCache; 1155 } else { 1156 return $this->fieldObjectsCache[$fieldOffset]; 1157 } 1158 } else { 1159 // No metadata available 1160 return false; 1161 } 1162 } 1163 1164 $this->fieldObjectsRetrieved = true; 1165 /* 1166 * Retrieve all metadata in one go. This is always returned as a 1167 * numeric array. 1168 */ 1169 $fieldMetaData = sqlsrv_field_metadata($this->_queryID); 1170 1171 if (!$fieldMetaData) { 1172 // Not a statement that gives us metaData 1173 return false; 1174 } 1175 1176 $this->_numOfFields = count($fieldMetaData); 1177 foreach ($fieldMetaData as $key=>$value) { 1178 $fld = new ADOFieldObject; 1179 // Caution - keys are case-sensitive, must respect casing of values 1180 $fld->name = $value['Name']; 1181 $fld->max_length = $value['Size']; 1182 $fld->column_source = $value['Name']; 1183 $fld->type = $this->_typeConversion[$value['Type']]; 1184 1185 $this->fieldObjectsCache[$key] = $fld; 1186 $this->fieldObjectsIndex[$fld->name] = $key; 1187 } 1188 if ($fieldOffset == -1) { 1189 return $this->fieldObjectsCache; 1190 } 1191 1192 return $this->fieldObjectsCache[$fieldOffset]; 1193 } 1194 1195 /* 1196 * Fetchfield copies the oracle method, it loads the field information 1197 * into the _fieldobjs array once, to save multiple calls to the 1198 * sqlsrv_field_metadata function 1199 * 1200 * @param int $fieldOffset (optional) 1201 * 1202 * @return adoFieldObject 1203 * 1204 * @author KM Newnham 1205 * @date 02/20/2013 1206 */ 1207 function fetchField($fieldOffset = -1) 1208 { 1209 return $this->fieldObjectsCache[$fieldOffset]; 1210 } 1211 1212 function _seek($row) 1213 { 1214 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams. 1215 } 1216 1217 // speedup 1218 function MoveNext() 1219 { 1220 if ($this->EOF) 1221 return false; 1222 1223 $this->_currentRow++; 1224 1225 if ($this->_fetch()) 1226 return true; 1227 $this->EOF = true; 1228 1229 return false; 1230 } 1231 1232 function _fetch($ignore_fields=false) 1233 { 1234 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 1235 if ($this->fetchMode & ADODB_FETCH_NUM) 1236 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH); 1237 else 1238 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC); 1239 1240 if (is_array($this->fields)) 1241 { 1242 1243 if (ADODB_ASSOC_CASE == ADODB_ASSOC_CASE_LOWER) 1244 $this->fields = array_change_key_case($this->fields,CASE_LOWER); 1245 else if (ADODB_ASSOC_CASE == ADODB_ASSOC_CASE_UPPER) 1246 $this->fields = array_change_key_case($this->fields,CASE_UPPER); 1247 1248 } 1249 } 1250 else 1251 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC); 1252 1253 if (!$this->fields) 1254 return false; 1255 1256 return $this->fields; 1257 } 1258 1259 /** 1260 * close() only needs to be called if you are worried about using too much 1261 * memory while your script is running. All associated result memory for 1262 * the specified result identifier will automatically be freed. 1263 * 1264 * @return bool tru if we succeeded in closing down 1265 */ 1266 function _close() 1267 { 1268 /* 1269 * If we are closing down a failed query, collect any 1270 * error messages. This is a hack fix to the "close too early" 1271 * problem so this might go away later 1272 */ 1273 $this->connection->errorMsg(); 1274 if(is_resource($this->_queryID)) { 1275 $rez = sqlsrv_free_stmt($this->_queryID); 1276 $this->_queryID = false; 1277 return $rez; 1278 } 1279 1280 return true; 1281 } 1282 1283 } 1284 1285 1286 class ADORecordSet_array_mssqlnative extends ADORecordSet_array {} 1287 1288 /* 1289 Code Example 1: 1290 1291 select object_name(constid) as constraint_name, 1292 object_name(fkeyid) as table_name, 1293 col_name(fkeyid, fkey) as column_name, 1294 object_name(rkeyid) as referenced_table_name, 1295 col_name(rkeyid, rkey) as referenced_column_name 1296 from sysforeignkeys 1297 where object_name(fkeyid) = x 1298 order by constraint_name, table_name, referenced_table_name, keyno 1299 1300 Code Example 2: 1301 select constraint_name, 1302 column_name, 1303 ordinal_position 1304 from information_schema.key_column_usage 1305 where constraint_catalog = db_name() 1306 and table_name = x 1307 order by constraint_name, ordinal_position 1308 1309 http://www.databasejournal.com/scripts/article.php/1440551 1310 */
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
