Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 4.2.x will end 22 April 2024 (12 months).
- Bug fixes for security issues in 4.2.x will end 7 October 2024 (18 months).
- PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.1.x is supported too.
/lib/adodb/drivers/ -> adodb-mssqlnative.inc.php (source)
Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402] [Versions 401 and 402]
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 && is_resource($this->_queryID)) { 188 return sqlsrv_rows_affected($this->_queryID); 189 } 190 return false; 191 } 192 193 function GenID($seq='adodbseq',$start=1) { 194 switch($this->mssql_version){ 195 case 9: 196 case 10: 197 return $this->GenID2008($seq, $start); 198 break; 199 default: 200 return $this->GenID2012($seq, $start); 201 break; 202 } 203 } 204 205 function CreateSequence($seq='adodbseq',$start=1) 206 { 207 switch($this->mssql_version){ 208 case 9: 209 case 10: 210 return $this->CreateSequence2008($seq, $start); 211 break; 212 default: 213 return $this->CreateSequence2012($seq, $start); 214 break; 215 } 216 } 217 218 /** 219 * For Server 2005,2008, duplicate a sequence with an identity table 220 */ 221 function CreateSequence2008($seq='adodbseq',$start=1) 222 { 223 if($this->debug) ADOConnection::outp("<hr>CreateSequence($seq,$start)"); 224 sqlsrv_begin_transaction($this->_connectionID); 225 $start -= 1; 226 $this->Execute("create table $seq (id int)");//was float(53) 227 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 228 if (!$ok) { 229 if($this->debug) ADOConnection::outp("<hr>Error: ROLLBACK"); 230 sqlsrv_rollback($this->_connectionID); 231 return false; 232 } 233 sqlsrv_commit($this->_connectionID); 234 return true; 235 } 236 237 /** 238 * Proper Sequences Only available to Server 2012 and up 239 */ 240 function CreateSequence2012($seq='adodbseq',$start=1){ 241 if (!$this->sequences){ 242 $sql = "SELECT name FROM sys.sequences"; 243 $this->sequences = $this->GetCol($sql); 244 } 245 $ok = $this->Execute("CREATE SEQUENCE $seq START WITH $start INCREMENT BY 1"); 246 if (!$ok) 247 die("CANNOT CREATE SEQUENCE" . print_r(sqlsrv_errors(),true)); 248 $this->sequences[] = $seq; 249 } 250 251 /** 252 * For Server 2005,2008, duplicate a sequence with an identity table 253 */ 254 function GenID2008($seq='adodbseq',$start=1) 255 { 256 if($this->debug) ADOConnection::outp("<hr>CreateSequence($seq,$start)"); 257 sqlsrv_begin_transaction($this->_connectionID); 258 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1"); 259 if (!$ok) { 260 $start -= 1; 261 $this->Execute("create table $seq (id int)");//was float(53) 262 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 263 if (!$ok) { 264 if($this->debug) ADOConnection::outp("<hr>Error: ROLLBACK"); 265 sqlsrv_rollback($this->_connectionID); 266 return false; 267 } 268 } 269 $num = $this->GetOne("select id from $seq"); 270 sqlsrv_commit($this->_connectionID); 271 return $num; 272 } 273 /** 274 * Only available to Server 2012 and up 275 * Cannot do this the normal adodb way by trapping an error if the 276 * sequence does not exist because sql server will auto create a 277 * sequence with the starting number of -9223372036854775808 278 */ 279 function GenID2012($seq='adodbseq',$start=1) 280 { 281 282 /* 283 * First time in create an array of sequence names that we 284 * can use in later requests to see if the sequence exists 285 * the overhead is creating a list of sequences every time 286 * we need access to at least 1. If we really care about 287 * performance, we could maybe flag a 'nocheck' class variable 288 */ 289 if (!$this->sequences){ 290 $sql = "SELECT name FROM sys.sequences"; 291 $this->sequences = $this->GetCol($sql); 292 } 293 if (!is_array($this->sequences) 294 || is_array($this->sequences) && !in_array($seq,$this->sequences)){ 295 $this->CreateSequence2012($seq, $start); 296 297 } 298 $num = $this->GetOne("SELECT NEXT VALUE FOR $seq"); 299 return $num; 300 } 301 302 // Format date column in sql string given an input format that understands Y M D 303 function SQLDate($fmt, $col=false) 304 { 305 if (!$col) { 306 $col = $this->sysTimeStamp; 307 } 308 $s = ''; 309 310 $ConvertableFmt=array( 311 "m/d/Y"=>101, "m/d/y"=>101 // US 312 ,"Y.m.d"=>102, "y.m.d"=>102 // ANSI 313 ,"d/m/Y"=>103, "d/m/y"=>103 // French /english 314 ,"d.m.Y"=>104, "d.m.y"=>104 // German 315 ,"d-m-Y"=>105, "d-m-y"=>105 // Italian 316 ,"m-d-Y"=>110, "m-d-y"=>110 // US Dash 317 ,"Y/m/d"=>111, "y/m/d"=>111 // Japan 318 ,"Ymd"=>112, "ymd"=>112 // ISO 319 ,"H:i:s"=>108 // Time 320 ); 321 if (key_exists($fmt,$ConvertableFmt)) { 322 return "convert (varchar ,$col," . $ConvertableFmt[$fmt] . ")"; 323 } 324 325 $len = strlen($fmt); 326 for ($i=0; $i < $len; $i++) { 327 if ($s) $s .= '+'; 328 $ch = $fmt[$i]; 329 switch($ch) { 330 case 'Y': 331 case 'y': 332 $s .= "datename(yyyy,$col)"; 333 break; 334 case 'M': 335 $s .= "convert(char(3),$col,0)"; 336 break; 337 case 'm': 338 $s .= "replace(str(month($col),2),' ','0')"; 339 break; 340 case 'Q': 341 case 'q': 342 $s .= "datename(quarter,$col)"; 343 break; 344 case 'D': 345 case 'd': 346 $s .= "replace(str(day($col),2),' ','0')"; 347 break; 348 case 'h': 349 $s .= "substring(convert(char(14),$col,0),13,2)"; 350 break; 351 352 case 'H': 353 $s .= "replace(str(datepart(hh,$col),2),' ','0')"; 354 break; 355 356 case 'i': 357 $s .= "replace(str(datepart(mi,$col),2),' ','0')"; 358 break; 359 case 's': 360 $s .= "replace(str(datepart(ss,$col),2),' ','0')"; 361 break; 362 case 'a': 363 case 'A': 364 $s .= "substring(convert(char(19),$col,0),18,2)"; 365 break; 366 case 'l': 367 $s .= "datename(dw,$col)"; 368 break; 369 default: 370 if ($ch == '\\') { 371 $i++; 372 $ch = substr($fmt,$i,1); 373 } 374 $s .= $this->qstr($ch); 375 break; 376 } 377 } 378 return $s; 379 } 380 381 382 function BeginTrans() 383 { 384 if ($this->transOff) return true; 385 $this->transCnt += 1; 386 if ($this->debug) ADOConnection::outp('<hr>begin transaction'); 387 sqlsrv_begin_transaction($this->_connectionID); 388 return true; 389 } 390 391 function CommitTrans($ok=true) 392 { 393 if ($this->transOff) return true; 394 if ($this->debug) ADOConnection::outp('<hr>commit transaction'); 395 if (!$ok) return $this->RollbackTrans(); 396 if ($this->transCnt) $this->transCnt -= 1; 397 sqlsrv_commit($this->_connectionID); 398 return true; 399 } 400 401 function RollbackTrans() 402 { 403 if ($this->transOff) return true; 404 if ($this->debug) ADOConnection::outp('<hr>rollback transaction'); 405 if ($this->transCnt) $this->transCnt -= 1; 406 sqlsrv_rollback($this->_connectionID); 407 return true; 408 } 409 410 function SetTransactionMode( $transaction_mode ) 411 { 412 $this->_transmode = $transaction_mode; 413 if (empty($transaction_mode)) { 414 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED'); 415 return; 416 } 417 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode; 418 $this->Execute("SET TRANSACTION ".$transaction_mode); 419 } 420 421 /* 422 Usage: 423 424 $this->BeginTrans(); 425 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables 426 427 # some operation on both tables table1 and table2 428 429 $this->CommitTrans(); 430 431 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp 432 */ 433 function RowLock($tables,$where,$col='1 as adodbignore') 434 { 435 if ($col == '1 as adodbignore') $col = 'top 1 null as ignore'; 436 if (!$this->transCnt) $this->BeginTrans(); 437 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where"); 438 } 439 440 function SelectDB($dbName) 441 { 442 $this->database = $dbName; 443 if ($this->_connectionID) { 444 $rs = $this->Execute('USE '.$dbName); 445 if($rs) { 446 return true; 447 } else return false; 448 } 449 else return false; 450 } 451 452 function ErrorMsg() 453 { 454 $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL); 455 if($retErrors != null) { 456 foreach($retErrors as $arrError) { 457 $this->_errorMsg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n"; 458 $this->_errorMsg .= "Error Code: ".$arrError[ 'code']."\n"; 459 $this->_errorMsg .= "Message: ".$arrError[ 'message']."\n"; 460 } 461 } 462 return $this->_errorMsg; 463 } 464 465 function ErrorNo() 466 { 467 $err = sqlsrv_errors(SQLSRV_ERR_ALL); 468 if ($err && $err[0]) 469 return $err[0]['code']; 470 else 471 return 0; 472 } 473 474 // returns true or false 475 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) 476 { 477 if (!function_exists('sqlsrv_connect')) 478 { 479 if ($this->debug) 480 ADOConnection::outp('Microsoft SQL Server native driver (mssqlnative) not installed'); 481 return null; 482 } 483 484 if (!empty($this->port)) 485 /* 486 * Port uses a comma 487 */ 488 $argHostname .= ",".$this->port; 489 490 $connectionInfo = $this->connectionInfo; 491 $connectionInfo["Database"] = $argDatabasename; 492 if ((string)$argUsername != '' || (string)$argPassword != '') 493 { 494 /* 495 * If they pass either a userid or password, we assume 496 * SQL Server authentication 497 */ 498 $connectionInfo["UID"] = $argUsername; 499 $connectionInfo["PWD"] = $argPassword; 500 501 if ($this->debug) 502 ADOConnection::outp('userid or password supplied, attempting connection with SQL Server Authentication'); 503 504 } 505 else 506 { 507 /* 508 * If they don't pass either value, we won't add them to the 509 * connection parameters. This will then force an attempt 510 * to use windows authentication 511 */ 512 if ($this->debug) 513 514 ADOConnection::outp('No userid or password supplied, attempting connection with Windows Authentication'); 515 } 516 517 518 /* 519 * Now merge in the passed connection parameters setting 520 */ 521 foreach ($this->connectionParameters as $options) 522 { 523 foreach($options as $parameter=>$value) 524 $connectionInfo[$parameter] = $value; 525 } 526 527 if ($this->debug) ADOConnection::outp("connecting to host: $argHostname params: ".var_export($connectionInfo,true)); 528 if(!($this->_connectionID = @sqlsrv_connect($argHostname,$connectionInfo))) 529 { 530 if ($this->debug) 531 ADOConnection::outp( 'Connection Failed: '.print_r( sqlsrv_errors(), true)); 532 return false; 533 } 534 535 $this->ServerVersion(); 536 537 return true; 538 } 539 540 // returns true or false 541 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 542 { 543 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!) 544 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename); 545 } 546 547 548 function Prepare($sql) 549 { 550 return $sql; // prepare does not work properly with bind parameters as bind parameters are managed by sqlsrv_prepare! 551 } 552 553 // returns concatenated string 554 // MSSQL requires integers to be cast as strings 555 // automatically cast every datatype to VARCHAR(255) 556 // @author David Rogers (introspectshun) 557 function Concat() 558 { 559 $s = ""; 560 $arr = func_get_args(); 561 562 // Split single record on commas, if possible 563 if (sizeof($arr) == 1) { 564 foreach ($arr as $arg) { 565 $args = explode(',', $arg); 566 } 567 $arr = $args; 568 } 569 570 array_walk( 571 $arr, 572 function(&$value, $key) { 573 $value = "CAST(" . $value . " AS VARCHAR(255))"; 574 } 575 ); 576 $s = implode('+',$arr); 577 if (sizeof($arr) > 0) return "$s"; 578 579 return ''; 580 } 581 582 /* 583 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars 584 So all your blobs must be of type "image". 585 586 Remember to set in php.ini the following... 587 588 ; Valid range 0 - 2147483647. Default = 4096. 589 mssql.textlimit = 0 ; zero to pass through 590 591 ; Valid range 0 - 2147483647. Default = 4096. 592 mssql.textsize = 0 ; zero to pass through 593 */ 594 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 595 { 596 597 if (strtoupper($blobtype) == 'CLOB') { 598 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where"; 599 return $this->Execute($sql) != false; 600 } 601 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where"; 602 return $this->Execute($sql) != false; 603 } 604 605 /** 606 * Execute a query. 607 * 608 * If executing an INSERT statement and $hasInsertId is true, will set 609 * $lastInsId. 610 * 611 * @param string $sql 612 * @param array $inputarr 613 * @return resource|false Query Id if successful, otherwise false 614 */ 615 function _query($sql, $inputarr = false) 616 { 617 $this->_errorMsg = false; 618 619 if (is_array($sql)) { 620 $sql = $sql[1]; 621 } 622 623 // Handle native driver flaw for retrieving the last insert ID 624 if ($this->hasInsertID) { 625 // Check if it's an INSERT statement 626 $retrieveLastInsertID = preg_match( 627 '/^\W*insert[\s\w()[\]",.]+values\s*\((?:[^;\']|\'\'|(?:(?:\'\')*\'[^\']+\'(?:\'\')*))*;?$/i', 628 $sql 629 ); 630 if ($retrieveLastInsertID) { 631 // Append the identity SQL, so it is executed in the same 632 // scope as the insert query. 633 $sql .= '; ' . $this->identitySQL; 634 } 635 } else { 636 $retrieveLastInsertID = false; 637 } 638 639 if ($inputarr) { 640 // Ensure that the input array is indexed numerically, as required 641 // by sqlsrv_query(). If param() was used to create portable binds 642 // then the array might be associative. 643 $inputarr = array_values($inputarr); 644 $rez = sqlsrv_query($this->_connectionID, $sql, $inputarr); 645 } else { 646 $rez = sqlsrv_query($this->_connectionID, $sql); 647 } 648 649 $this->lastInsID = false; 650 if (!$rez) { 651 $rez = false; 652 } elseif ($retrieveLastInsertID) { 653 // Get the inserted id from the last result 654 // Note: loop is required as server may return more than one row, 655 // e.g. if triggers are involved (see #41) 656 while (sqlsrv_next_result($rez)) { 657 sqlsrv_fetch($rez); 658 $this->lastInsID = sqlsrv_get_field($rez, 0); 659 } 660 } 661 return $rez; 662 } 663 664 /** 665 * Rolls back pending transactions and closes the connection. 666 * 667 * @return bool True, unless the connection id is invalid 668 */ 669 function _close() 670 { 671 if ($this->transCnt) { 672 $this->RollbackTrans(); 673 } 674 if ($this->_connectionID) { 675 return sqlsrv_close($this->_connectionID); 676 } 677 $this->_connectionID = false; 678 return true; 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 public function metaForeignKeys($table, $owner = '', $upper = false, $associative = 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 * An SQL Statement that adds a specific number of 1014 * days or part to local datetime 1015 * 1016 * @param float $dayFraction 1017 * @param string $date 1018 * 1019 * @return string 1020 */ 1021 public function offsetDate($dayFraction, $date = false) 1022 { 1023 if (!$date) 1024 /* 1025 * Use GETDATE() via systTimestamp; 1026 */ 1027 $date = $this->sysTimeStamp; 1028 1029 /* 1030 * seconds, number of seconds, date base 1031 */ 1032 $dateFormat = "DATEADD(s, %s, %s)"; 1033 1034 /* 1035 * Adjust the offset back to seconds 1036 */ 1037 $fraction = $dayFraction * 24 * 3600; 1038 1039 return sprintf($dateFormat,$fraction,$date); 1040 1041 } 1042 1043 } 1044 1045 /*-------------------------------------------------------------------------------------- 1046 Class Name: Recordset 1047 --------------------------------------------------------------------------------------*/ 1048 1049 class ADORecordset_mssqlnative extends ADORecordSet { 1050 1051 var $databaseType = "mssqlnative"; 1052 var $canSeek = false; 1053 var $fieldOffset = 0; 1054 // _mths works only in non-localised system 1055 1056 /** 1057 * @var bool True if we have retrieved the fields metadata 1058 */ 1059 private $fieldObjectsRetrieved = false; 1060 1061 /* 1062 * Cross-reference the objects by name for easy access 1063 */ 1064 private $fieldObjectsIndex = array(); 1065 1066 /* 1067 * Cross references the dateTime objects for faster decoding 1068 */ 1069 private $dateTimeObjects = array(); 1070 1071 /* 1072 * flags that we have dateTimeObjects to handle 1073 */ 1074 private $hasDateTimeObjects = false; 1075 1076 /* 1077 * This is cross reference between how the types are stored 1078 * in SQL Server and their english-language description 1079 * -154 is a time field, see #432 1080 */ 1081 private $_typeConversion = array( 1082 -155 => 'datetimeoffset', 1083 -154 => 'char', 1084 -152 => 'xml', 1085 -151 => 'udt', 1086 -11 => 'uniqueidentifier', 1087 -10 => 'ntext', 1088 -9 => 'nvarchar', 1089 -8 => 'nchar', 1090 -7 => 'bit', 1091 -6 => 'tinyint', 1092 -5 => 'bigint', 1093 -4 => 'image', 1094 -3 => 'varbinary', 1095 -2 => 'timestamp', 1096 -1 => 'text', 1097 1 => 'char', 1098 2 => 'numeric', 1099 3 => 'decimal', 1100 4 => 'int', 1101 5 => 'smallint', 1102 6 => 'float', 1103 7 => 'real', 1104 12 => 'varchar', 1105 91 => 'date', 1106 93 => 'datetime' 1107 ); 1108 1109 1110 1111 1112 function __construct($id,$mode=false) 1113 { 1114 if ($mode === false) { 1115 global $ADODB_FETCH_MODE; 1116 $mode = $ADODB_FETCH_MODE; 1117 1118 } 1119 $this->fetchMode = $mode; 1120 parent::__construct($id); 1121 } 1122 1123 1124 function _initrs() 1125 { 1126 $this->_numOfRows = -1;//not supported 1127 // Cache the metadata right now 1128 $this->_fetchField(); 1129 1130 } 1131 1132 1133 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se> 1134 // get next resultset - requires PHP 4.0.5 or later 1135 function NextRecordSet() 1136 { 1137 if (!sqlsrv_next_result($this->_queryID)) return false; 1138 $this->_inited = false; 1139 $this->bind = false; 1140 $this->_currentRow = -1; 1141 $this->Init(); 1142 return true; 1143 } 1144 1145 /* Use associative array to get fields array */ 1146 function Fields($colname) 1147 { 1148 if (!is_array($this->fields)) 1149 /* 1150 * Too early 1151 */ 1152 return; 1153 if ($this->fetchMode != ADODB_FETCH_NUM) 1154 return $this->fields[$colname]; 1155 1156 if (!$this->bind) { 1157 $this->bind = array(); 1158 for ($i=0; $i < $this->_numOfFields; $i++) { 1159 $o = $this->FetchField($i); 1160 $this->bind[strtoupper($o->name)] = $i; 1161 } 1162 } 1163 1164 return $this->fields[$this->bind[strtoupper($colname)]]; 1165 } 1166 1167 /** 1168 * Returns: an object containing field information. 1169 * 1170 * Get column information in the Recordset object. fetchField() 1171 * can be used in order to obtain information about fields in a 1172 * certain query result. If the field offset isn't specified, 1173 * the next field that wasn't yet retrieved by fetchField() 1174 * is retrieved. 1175 * 1176 * @param int $fieldOffset (optional default=-1 for all 1177 * @return mixed an ADOFieldObject, or array of objects 1178 */ 1179 private function _fetchField($fieldOffset = -1) 1180 { 1181 if ($this->fieldObjectsRetrieved) { 1182 if ($this->fieldObjectsCache) { 1183 // Already got the information 1184 if ($fieldOffset == -1) { 1185 return $this->fieldObjectsCache; 1186 } else { 1187 return $this->fieldObjectsCache[$fieldOffset]; 1188 } 1189 } else { 1190 // No metadata available 1191 return false; 1192 } 1193 } 1194 1195 $this->fieldObjectsRetrieved = true; 1196 /* 1197 * Retrieve all metadata in one go. This is always returned as a 1198 * numeric array. 1199 */ 1200 $fieldMetaData = sqlsrv_field_metadata($this->_queryID); 1201 1202 if (!$fieldMetaData) { 1203 // Not a statement that gives us metaData 1204 return false; 1205 } 1206 1207 $this->_numOfFields = count($fieldMetaData); 1208 foreach ($fieldMetaData as $key=>$value) { 1209 $fld = new ADOFieldObject; 1210 // Caution - keys are case-sensitive, must respect casing of values 1211 $fld->name = $value['Name']; 1212 $fld->max_length = $value['Size']; 1213 $fld->column_source = $value['Name']; 1214 $fld->type = $this->_typeConversion[$value['Type']]; 1215 1216 $this->fieldObjectsCache[$key] = $fld; 1217 $this->fieldObjectsIndex[$fld->name] = $key; 1218 } 1219 if ($fieldOffset == -1) { 1220 return $this->fieldObjectsCache; 1221 } 1222 1223 return $this->fieldObjectsCache[$fieldOffset]; 1224 } 1225 1226 /* 1227 * Fetchfield copies the oracle method, it loads the field information 1228 * into the _fieldobjs array once, to save multiple calls to the 1229 * sqlsrv_field_metadata function 1230 * 1231 * @param int $fieldOffset (optional) 1232 * 1233 * @return adoFieldObject 1234 * 1235 * @author KM Newnham 1236 * @date 02/20/2013 1237 */ 1238 function fetchField($fieldOffset = -1) 1239 { 1240 return $this->fieldObjectsCache[$fieldOffset]; 1241 } 1242 1243 function _seek($row) 1244 { 1245 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams. 1246 } 1247 1248 // speedup 1249 function MoveNext() 1250 { 1251 if ($this->EOF) 1252 return false; 1253 1254 $this->_currentRow++; 1255 1256 if ($this->_fetch()) 1257 return true; 1258 $this->EOF = true; 1259 1260 return false; 1261 } 1262 1263 function _fetch($ignore_fields=false) 1264 { 1265 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 1266 if ($this->fetchMode & ADODB_FETCH_NUM) 1267 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH); 1268 else 1269 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC); 1270 1271 if (is_array($this->fields)) 1272 { 1273 1274 if (ADODB_ASSOC_CASE == ADODB_ASSOC_CASE_LOWER) 1275 $this->fields = array_change_key_case($this->fields,CASE_LOWER); 1276 else if (ADODB_ASSOC_CASE == ADODB_ASSOC_CASE_UPPER) 1277 $this->fields = array_change_key_case($this->fields,CASE_UPPER); 1278 1279 } 1280 } 1281 else 1282 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC); 1283 1284 if (!$this->fields) 1285 return false; 1286 1287 return $this->fields; 1288 } 1289 1290 /** 1291 * close() only needs to be called if you are worried about using too much 1292 * memory while your script is running. All associated result memory for 1293 * the specified result identifier will automatically be freed. 1294 * 1295 * @return bool tru if we succeeded in closing down 1296 */ 1297 function _close() 1298 { 1299 /* 1300 * If we are closing down a failed query, collect any 1301 * error messages. This is a hack fix to the "close too early" 1302 * problem so this might go away later 1303 */ 1304 $this->connection->errorMsg(); 1305 if(is_resource($this->_queryID)) { 1306 $rez = sqlsrv_free_stmt($this->_queryID); 1307 $this->_queryID = false; 1308 return $rez; 1309 } 1310 1311 return true; 1312 } 1313 1314 } 1315 1316 1317 class ADORecordSet_array_mssqlnative extends ADORecordSet_array {} 1318 1319 /* 1320 Code Example 1: 1321 1322 select object_name(constid) as constraint_name, 1323 object_name(fkeyid) as table_name, 1324 col_name(fkeyid, fkey) as column_name, 1325 object_name(rkeyid) as referenced_table_name, 1326 col_name(rkeyid, rkey) as referenced_column_name 1327 from sysforeignkeys 1328 where object_name(fkeyid) = x 1329 order by constraint_name, table_name, referenced_table_name, keyno 1330 1331 Code Example 2: 1332 select constraint_name, 1333 column_name, 1334 ordinal_position 1335 from information_schema.key_column_usage 1336 where constraint_catalog = db_name() 1337 and table_name = x 1338 order by constraint_name, ordinal_position 1339 1340 http://www.databasejournal.com/scripts/article.php/1440551 1341 */
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
