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