Search moodle.org's
Developer Documentation
Developer Documentation
See Release Notes
Long Term Support Release
- Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
- Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
- PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.
/lib/adodb/drivers/ -> adodb-mssqlnative.inc.php (source)
Differences Between: [Versions 39 and 311] [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 and 403]
1 <?php 2 /* 3 @version v5.20.16 12-Jan-2020 4 @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved. 5 @copyright (c) 2014 Damien Regad, Mark Newnham and the ADOdb community 6 Released under both BSD license and Lesser GPL library license. 7 Whenever there is any discrepancy between the two licenses, 8 the BSD license will take precedence. 9 Set tabs to 4 for best viewing. 10 11 Latest version is available at http://adodb.org/ 12 13 Native mssql driver. Requires mssql client. Works on Windows. 14 http://www.microsoft.com/sql/technologies/php/default.mspx 15 To configure for Unix, see 16 http://phpbuilder.com/columns/alberto20000919.php3 17 18 $stream = sqlsrv_get_field($stmt, $index, SQLSRV_SQLTYPE_STREAM(SQLSRV_ENC_BINARY)); 19 stream_filter_append($stream, "convert.iconv.ucs-2/utf-8"); // Voila, UTF-8 can be read directly from $stream 20 21 */ 22 23 // security - hide paths 24 if (!defined('ADODB_DIR')) die(); 25 26 if (!function_exists('sqlsrv_configure')) { 27 die("mssqlnative extension not installed"); 28 } 29 30 if (!function_exists('sqlsrv_set_error_handling')) { 31 function sqlsrv_set_error_handling($constant) { 32 sqlsrv_configure("WarningsReturnAsErrors", $constant); 33 } 34 } 35 if (!function_exists('sqlsrv_log_set_severity')) { 36 function sqlsrv_log_set_severity($constant) { 37 sqlsrv_configure("LogSeverity", $constant); 38 } 39 } 40 if (!function_exists('sqlsrv_log_set_subsystems')) { 41 function sqlsrv_log_set_subsystems($constant) { 42 sqlsrv_configure("LogSubsystems", $constant); 43 } 44 } 45 46 47 //---------------------------------------------------------------- 48 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002 49 // and this causes tons of problems because localized versions of 50 // MSSQL will return the dates in dmy or mdy order; and also the 51 // month strings depends on what language has been configured. The 52 // following two variables allow you to control the localization 53 // settings - Ugh. 54 // 55 // MORE LOCALIZATION INFO 56 // ---------------------- 57 // To configure datetime, look for and modify sqlcommn.loc, 58 // typically found in c:\mssql\install 59 // Also read : 60 // http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918 61 // Alternatively use: 62 // CONVERT(char(12),datecol,120) 63 // 64 // Also if your month is showing as month-1, 65 // e.g. Jan 13, 2002 is showing as 13/0/2002, then see 66 // http://phplens.com/lens/lensforum/msgs.php?id=7048&x=1 67 // it's a localisation problem. 68 //---------------------------------------------------------------- 69 70 71 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc 72 if (ADODB_PHPVER >= 0x4300) { 73 // docs say 4.2.0, but testing shows only since 4.3.0 does it work! 74 ini_set('mssql.datetimeconvert',0); 75 } else { 76 global $ADODB_mssql_mths; // array, months must be upper-case 77 $ADODB_mssql_date_order = 'mdy'; 78 $ADODB_mssql_mths = array( 79 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6, 80 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12); 81 } 82 83 class ADODB_mssqlnative extends ADOConnection { 84 var $databaseType = "mssqlnative"; 85 var $dataProvider = "mssqlnative"; 86 var $replaceQuote = "''"; // string to use to replace quotes 87 var $fmtDate = "'Y-m-d'"; 88 var $fmtTimeStamp = "'Y-m-d\TH:i:s'"; 89 var $hasInsertID = true; 90 var $substr = "substring"; 91 var $length = 'len'; 92 var $hasAffectedRows = true; 93 var $poorAffectedRows = false; 94 var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'"; 95 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'))"; 96 var $metaColumnsSQL = 97 "select c.name, 98 t.name as type, 99 c.length, 100 c.xprec as precision, 101 c.xscale as scale, 102 c.isnullable as nullable, 103 c.cdefault as default_value, 104 c.xtype, 105 t.length as type_length, 106 sc.is_identity 107 from syscolumns c 108 join systypes t on t.xusertype=c.xusertype 109 join sysobjects o on o.id=c.id 110 join sys.tables st on st.name=o.name 111 join sys.columns sc on sc.object_id = st.object_id and sc.name=c.name 112 where o.name='%s'"; 113 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE 114 var $hasGenID = true; 115 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)'; 116 var $sysTimeStamp = 'GetDate()'; 117 var $maxParameterLen = 4000; 118 var $arrayClass = 'ADORecordSet_array_mssqlnative'; 119 var $uniqueSort = true; 120 var $leftOuter = '*='; 121 var $rightOuter = '=*'; 122 var $ansiOuter = true; // for mssql7 or later 123 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000 124 var $uniqueOrderBy = true; 125 var $_bindInputArray = true; 126 var $_dropSeqSQL = "drop table %s"; 127 var $connectionInfo = array(); 128 var $cachedSchemaFlush = false; 129 var $sequences = false; 130 var $mssql_version = ''; 131 132 function __construct() 133 { 134 if ($this->debug) { 135 ADOConnection::outp("<pre>"); 136 sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL ); 137 sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL ); 138 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL); 139 sqlsrv_configure('WarningsReturnAsErrors', 0); 140 } else { 141 sqlsrv_set_error_handling(0); 142 sqlsrv_log_set_severity(0); 143 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL); 144 sqlsrv_configure('WarningsReturnAsErrors', 0); 145 } 146 } 147 148 /** 149 * Initializes the SQL Server version. 150 * Dies if connected to a non-supported version (2000 and older) 151 */ 152 function ServerVersion() { 153 $data = $this->ServerInfo(); 154 preg_match('/^\d{2}/', $data['version'], $matches); 155 $version = (int)reset($matches); 156 157 // We only support SQL Server 2005 and up 158 if($version < 9) { 159 die("SQL SERVER VERSION {$data['version']} NOT SUPPORTED IN mssqlnative DRIVER"); 160 } 161 162 $this->mssql_version = $version; 163 } 164 165 function ServerInfo() { 166 global $ADODB_FETCH_MODE; 167 static $arr = false; 168 if (is_array($arr)) 169 return $arr; 170 if ($this->fetchMode === false) { 171 $savem = $ADODB_FETCH_MODE; 172 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 173 } elseif ($this->fetchMode >=0 && $this->fetchMode <=2) { 174 $savem = $this->fetchMode; 175 } else 176 $savem = $this->SetFetchMode(ADODB_FETCH_NUM); 177 178 $arrServerInfo = sqlsrv_server_info($this->_connectionID); 179 $ADODB_FETCH_MODE = $savem; 180 $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase']; 181 $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']); 182 return $arr; 183 } 184 185 function IfNull( $field, $ifNull ) 186 { 187 return " ISNULL($field, $ifNull) "; // if MS SQL Server 188 } 189 190 function _insertid() 191 { 192 // SCOPE_IDENTITY() 193 // Returns the last IDENTITY value inserted into an IDENTITY column in 194 // the same scope. A scope is a module -- a stored procedure, trigger, 195 // function, or batch. Thus, two statements are in the same scope if 196 // they are in the same stored procedure, function, or batch. 197 return $this->lastInsertID; 198 } 199 200 function _affectedrows() 201 { 202 if ($this->_queryID) 203 return sqlsrv_rows_affected($this->_queryID); 204 } 205 206 function GenID($seq='adodbseq',$start=1) { 207 if (!$this->mssql_version) 208 $this->ServerVersion(); 209 switch($this->mssql_version){ 210 case 9: 211 case 10: 212 return $this->GenID2008($seq, $start); 213 break; 214 default: 215 return $this->GenID2012($seq, $start); 216 break; 217 } 218 } 219 220 function CreateSequence($seq='adodbseq',$start=1) 221 { 222 if (!$this->mssql_version) 223 $this->ServerVersion(); 224 225 switch($this->mssql_version){ 226 case 9: 227 case 10: 228 return $this->CreateSequence2008($seq, $start); 229 break; 230 default: 231 return $this->CreateSequence2012($seq, $start); 232 break; 233 } 234 235 } 236 237 /** 238 * For Server 2005,2008, duplicate a sequence with an identity table 239 */ 240 function CreateSequence2008($seq='adodbseq',$start=1) 241 { 242 if($this->debug) ADOConnection::outp("<hr>CreateSequence($seq,$start)"); 243 sqlsrv_begin_transaction($this->_connectionID); 244 $start -= 1; 245 $this->Execute("create table $seq (id int)");//was float(53) 246 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 247 if (!$ok) { 248 if($this->debug) ADOConnection::outp("<hr>Error: ROLLBACK"); 249 sqlsrv_rollback($this->_connectionID); 250 return false; 251 } 252 sqlsrv_commit($this->_connectionID); 253 return true; 254 } 255 256 /** 257 * Proper Sequences Only available to Server 2012 and up 258 */ 259 function CreateSequence2012($seq='adodbseq',$start=1){ 260 if (!$this->sequences){ 261 $sql = "SELECT name FROM sys.sequences"; 262 $this->sequences = $this->GetCol($sql); 263 } 264 $ok = $this->Execute("CREATE SEQUENCE $seq START WITH $start INCREMENT BY 1"); 265 if (!$ok) 266 die("CANNOT CREATE SEQUENCE" . print_r(sqlsrv_errors(),true)); 267 $this->sequences[] = $seq; 268 } 269 270 /** 271 * For Server 2005,2008, duplicate a sequence with an identity table 272 */ 273 function GenID2008($seq='adodbseq',$start=1) 274 { 275 if($this->debug) ADOConnection::outp("<hr>CreateSequence($seq,$start)"); 276 sqlsrv_begin_transaction($this->_connectionID); 277 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1"); 278 if (!$ok) { 279 $start -= 1; 280 $this->Execute("create table $seq (id int)");//was float(53) 281 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 282 if (!$ok) { 283 if($this->debug) ADOConnection::outp("<hr>Error: ROLLBACK"); 284 sqlsrv_rollback($this->_connectionID); 285 return false; 286 } 287 } 288 $num = $this->GetOne("select id from $seq"); 289 sqlsrv_commit($this->_connectionID); 290 return $num; 291 } 292 /** 293 * Only available to Server 2012 and up 294 * Cannot do this the normal adodb way by trapping an error if the 295 * sequence does not exist because sql server will auto create a 296 * sequence with the starting number of -9223372036854775808 297 */ 298 function GenID2012($seq='adodbseq',$start=1) 299 { 300 301 /* 302 * First time in create an array of sequence names that we 303 * can use in later requests to see if the sequence exists 304 * the overhead is creating a list of sequences every time 305 * we need access to at least 1. If we really care about 306 * performance, we could maybe flag a 'nocheck' class variable 307 */ 308 if (!$this->sequences){ 309 $sql = "SELECT name FROM sys.sequences"; 310 $this->sequences = $this->GetCol($sql); 311 } 312 if (!is_array($this->sequences) 313 || is_array($this->sequences) && !in_array($seq,$this->sequences)){ 314 $this->CreateSequence2012($seq, $start); 315 316 } 317 $num = $this->GetOne("SELECT NEXT VALUE FOR $seq"); 318 return $num; 319 } 320 321 // Format date column in sql string given an input format that understands Y M D 322 function SQLDate($fmt, $col=false) 323 { 324 if (!$col) $col = $this->sysTimeStamp; 325 $s = ''; 326 327 $len = strlen($fmt); 328 for ($i=0; $i < $len; $i++) { 329 if ($s) $s .= '+'; 330 $ch = $fmt[$i]; 331 switch($ch) { 332 case 'Y': 333 case 'y': 334 $s .= "datename(yyyy,$col)"; 335 break; 336 case 'M': 337 $s .= "convert(char(3),$col,0)"; 338 break; 339 case 'm': 340 $s .= "replace(str(month($col),2),' ','0')"; 341 break; 342 case 'Q': 343 case 'q': 344 $s .= "datename(quarter,$col)"; 345 break; 346 case 'D': 347 case 'd': 348 $s .= "replace(str(day($col),2),' ','0')"; 349 break; 350 case 'h': 351 $s .= "substring(convert(char(14),$col,0),13,2)"; 352 break; 353 354 case 'H': 355 $s .= "replace(str(datepart(hh,$col),2),' ','0')"; 356 break; 357 358 case 'i': 359 $s .= "replace(str(datepart(mi,$col),2),' ','0')"; 360 break; 361 case 's': 362 $s .= "replace(str(datepart(ss,$col),2),' ','0')"; 363 break; 364 case 'a': 365 case 'A': 366 $s .= "substring(convert(char(19),$col,0),18,2)"; 367 break; 368 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 function RollbackTrans() 401 { 402 if ($this->transOff) return true; 403 if ($this->debug) ADOConnection::outp('<hr>rollback transaction'); 404 if ($this->transCnt) $this->transCnt -= 1; 405 sqlsrv_rollback($this->_connectionID); 406 return true; 407 } 408 409 function SetTransactionMode( $transaction_mode ) 410 { 411 $this->_transmode = $transaction_mode; 412 if (empty($transaction_mode)) { 413 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED'); 414 return; 415 } 416 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode; 417 $this->Execute("SET TRANSACTION ".$transaction_mode); 418 } 419 420 /* 421 Usage: 422 423 $this->BeginTrans(); 424 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables 425 426 # some operation on both tables table1 and table2 427 428 $this->CommitTrans(); 429 430 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp 431 */ 432 function RowLock($tables,$where,$col='1 as adodbignore') 433 { 434 if ($col == '1 as adodbignore') $col = 'top 1 null as ignore'; 435 if (!$this->transCnt) $this->BeginTrans(); 436 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where"); 437 } 438 439 function SelectDB($dbName) 440 { 441 $this->database = $dbName; 442 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions 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[0]) return $err[0]['code']; 469 else return 0; 470 } 471 472 // returns true or false 473 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) 474 { 475 if (!function_exists('sqlsrv_connect')) return null; 476 $connectionInfo = $this->connectionInfo; 477 $connectionInfo["Database"]=$argDatabasename; 478 $connectionInfo["UID"]=$argUsername; 479 $connectionInfo["PWD"]=$argPassword; 480 481 foreach ($this->connectionParameters as $parameter=>$value) 482 $connectionInfo[$parameter] = $value; 483 484 if ($this->debug) ADOConnection::outp("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true)); 485 //if ($this->debug) ADOConnection::outp("<hr>_connectionID before: ".serialize($this->_connectionID)); 486 if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) { 487 if ($this->debug) ADOConnection::outp( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true)); 488 return false; 489 } 490 //if ($this->debug) ADOConnection::outp(" _connectionID after: ".serialize($this->_connectionID)); 491 //if ($this->debug) ADOConnection::outp("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>"); 492 return true; 493 } 494 495 // returns true or false 496 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 497 { 498 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!) 499 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename); 500 } 501 502 function Prepare($sql) 503 { 504 return $sql; // prepare does not work properly with bind parameters as bind parameters are managed by sqlsrv_prepare! 505 506 $stmt = sqlsrv_prepare( $this->_connectionID, $sql); 507 if (!$stmt) return $sql; 508 return array($sql,$stmt); 509 } 510 511 // returns concatenated string 512 // MSSQL requires integers to be cast as strings 513 // automatically cast every datatype to VARCHAR(255) 514 // @author David Rogers (introspectshun) 515 function Concat() 516 { 517 $s = ""; 518 $arr = func_get_args(); 519 520 // Split single record on commas, if possible 521 if (sizeof($arr) == 1) { 522 foreach ($arr as $arg) { 523 $args = explode(',', $arg); 524 } 525 $arr = $args; 526 } 527 528 array_walk( 529 $arr, 530 function(&$value, $key) { 531 $value = "CAST(" . $value . " AS VARCHAR(255))"; 532 } 533 ); 534 $s = implode('+',$arr); 535 if (sizeof($arr) > 0) return "$s"; 536 537 return ''; 538 } 539 540 /* 541 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars 542 So all your blobs must be of type "image". 543 544 Remember to set in php.ini the following... 545 546 ; Valid range 0 - 2147483647. Default = 4096. 547 mssql.textlimit = 0 ; zero to pass through 548 549 ; Valid range 0 - 2147483647. Default = 4096. 550 mssql.textsize = 0 ; zero to pass through 551 */ 552 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 553 { 554 555 if (strtoupper($blobtype) == 'CLOB') { 556 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where"; 557 return $this->Execute($sql) != false; 558 } 559 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where"; 560 return $this->Execute($sql) != false; 561 } 562 563 // returns query ID if successful, otherwise false 564 function _query($sql,$inputarr=false) 565 { 566 $this->_errorMsg = false; 567 568 if (is_array($sql)) $sql = $sql[1]; 569 570 $insert = false; 571 // handle native driver flaw for retrieving the last insert ID 572 if(preg_match('/^\W*insert[\s\w()[\]",.]+values\s*\((?:[^;\']|\'\'|(?:(?:\'\')*\'[^\']+\'(?:\'\')*))*;?$/i', $sql)) { 573 $insert = true; 574 $sql .= '; '.$this->identitySQL; // select scope_identity() 575 } 576 if($inputarr) { 577 $rez = sqlsrv_query($this->_connectionID, $sql, $inputarr); 578 } else { 579 $rez = sqlsrv_query($this->_connectionID,$sql); 580 } 581 582 if ($this->debug) ADOConnection::outp("<hr>running query: ".var_export($sql,true)."<hr>input array: ".var_export($inputarr,true)."<hr>result: ".var_export($rez,true)); 583 584 if(!$rez) { 585 $rez = false; 586 } else if ($insert) { 587 // retrieve the last insert ID (where applicable) 588 while ( sqlsrv_next_result($rez) ) { 589 sqlsrv_fetch($rez); 590 $this->lastInsertID = sqlsrv_get_field($rez, 0); 591 } 592 } 593 return $rez; 594 } 595 596 // returns true or false 597 function _close() 598 { 599 if ($this->transCnt) $this->RollbackTrans(); 600 $rez = @sqlsrv_close($this->_connectionID); 601 $this->_connectionID = false; 602 return $rez; 603 } 604 605 // mssql uses a default date like Dec 30 2000 12:00AM 606 static function UnixDate($v) 607 { 608 return ADORecordSet_array_mssqlnative::UnixDate($v); 609 } 610 611 static function UnixTimeStamp($v) 612 { 613 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v); 614 } 615 616 function MetaIndexes($table,$primary=false, $owner = false) 617 { 618 $table = $this->qstr($table); 619 620 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno, 621 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, 622 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique 623 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id 624 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid 625 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid 626 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table 627 ORDER BY O.name, I.Name, K.keyno"; 628 629 global $ADODB_FETCH_MODE; 630 $save = $ADODB_FETCH_MODE; 631 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 632 if ($this->fetchMode !== FALSE) { 633 $savem = $this->SetFetchMode(FALSE); 634 } 635 636 $rs = $this->Execute($sql); 637 if (isset($savem)) { 638 $this->SetFetchMode($savem); 639 } 640 $ADODB_FETCH_MODE = $save; 641 642 if (!is_object($rs)) { 643 return FALSE; 644 } 645 646 $indexes = array(); 647 while ($row = $rs->FetchRow()) { 648 if (!$primary && $row[5]) continue; 649 650 $indexes[$row[0]]['unique'] = $row[6]; 651 $indexes[$row[0]]['columns'][] = $row[1]; 652 } 653 return $indexes; 654 } 655 656 function MetaForeignKeys($table, $owner=false, $upper=false) 657 { 658 global $ADODB_FETCH_MODE; 659 660 $save = $ADODB_FETCH_MODE; 661 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 662 $table = $this->qstr(strtoupper($table)); 663 664 $sql = 665 "select object_name(constid) as constraint_name, 666 col_name(fkeyid, fkey) as column_name, 667 object_name(rkeyid) as referenced_table_name, 668 col_name(rkeyid, rkey) as referenced_column_name 669 from sysforeignkeys 670 where upper(object_name(fkeyid)) = $table 671 order by constraint_name, referenced_table_name, keyno"; 672 673 $constraints =& $this->GetArray($sql); 674 675 $ADODB_FETCH_MODE = $save; 676 677 $arr = false; 678 foreach($constraints as $constr) { 679 //print_r($constr); 680 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3]; 681 } 682 if (!$arr) return false; 683 684 $arr2 = false; 685 686 foreach($arr as $k => $v) { 687 foreach($v as $a => $b) { 688 if ($upper) $a = strtoupper($a); 689 $arr2[$a] = $b; 690 } 691 } 692 return $arr2; 693 } 694 695 //From: Fernando Moreira <FMoreira@imediata.pt> 696 function MetaDatabases() 697 { 698 $this->SelectDB("master"); 699 $rs =& $this->Execute($this->metaDatabasesSQL); 700 $rows = $rs->GetRows(); 701 $ret = array(); 702 for($i=0;$i<count($rows);$i++) { 703 $ret[] = $rows[$i][0]; 704 } 705 $this->SelectDB($this->database); 706 if($ret) 707 return $ret; 708 else 709 return false; 710 } 711 712 // "Stein-Aksel Basma" <basma@accelero.no> 713 // tested with MSSQL 2000 714 function MetaPrimaryKeys($table, $owner=false) 715 { 716 global $ADODB_FETCH_MODE; 717 718 $schema = ''; 719 $this->_findschema($table,$schema); 720 if (!$schema) $schema = $this->database; 721 if ($schema) $schema = "and k.table_catalog like '$schema%'"; 722 723 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k, 724 information_schema.table_constraints tc 725 where tc.constraint_name = k.constraint_name and tc.constraint_type = 726 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position "; 727 728 $savem = $ADODB_FETCH_MODE; 729 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 730 $a = $this->GetCol($sql); 731 $ADODB_FETCH_MODE = $savem; 732 733 if ($a && sizeof($a)>0) return $a; 734 $false = false; 735 return $false; 736 } 737 738 739 function MetaTables($ttype=false,$showSchema=false,$mask=false) 740 { 741 if ($mask) { 742 $save = $this->metaTablesSQL; 743 $mask = $this->qstr(($mask)); 744 $this->metaTablesSQL .= " AND name like $mask"; 745 } 746 $ret = ADOConnection::MetaTables($ttype,$showSchema); 747 748 if ($mask) { 749 $this->metaTablesSQL = $save; 750 } 751 return $ret; 752 } 753 function MetaColumns($table, $upper=true, $schema=false){ 754 755 # start adg 756 static $cached_columns = array(); 757 if ($this->cachedSchemaFlush) 758 $cached_columns = array(); 759 760 if (array_key_exists($table,$cached_columns)){ 761 return $cached_columns[$table]; 762 } 763 # end adg 764 765 if (!$this->mssql_version) 766 $this->ServerVersion(); 767 768 $this->_findschema($table,$schema); 769 if ($schema) { 770 $dbName = $this->database; 771 $this->SelectDB($schema); 772 } 773 global $ADODB_FETCH_MODE; 774 $save = $ADODB_FETCH_MODE; 775 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 776 777 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false); 778 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table)); 779 780 if ($schema) { 781 $this->SelectDB($dbName); 782 } 783 784 if (isset($savem)) $this->SetFetchMode($savem); 785 $ADODB_FETCH_MODE = $save; 786 if (!is_object($rs)) { 787 $false = false; 788 return $false; 789 } 790 791 $retarr = array(); 792 while (!$rs->EOF){ 793 794 $fld = new ADOFieldObject(); 795 if (array_key_exists(0,$rs->fields)) { 796 $fld->name = $rs->fields[0]; 797 $fld->type = $rs->fields[1]; 798 $fld->max_length = $rs->fields[2]; 799 $fld->precision = $rs->fields[3]; 800 $fld->scale = $rs->fields[4]; 801 $fld->not_null =!$rs->fields[5]; 802 $fld->has_default = $rs->fields[6]; 803 $fld->xtype = $rs->fields[7]; 804 $fld->type_length = $rs->fields[8]; 805 $fld->auto_increment= $rs->fields[9]; 806 } else { 807 $fld->name = $rs->fields['name']; 808 $fld->type = $rs->fields['type']; 809 $fld->max_length = $rs->fields['length']; 810 $fld->precision = $rs->fields['precision']; 811 $fld->scale = $rs->fields['scale']; 812 $fld->not_null =!$rs->fields['nullable']; 813 $fld->has_default = $rs->fields['default_value']; 814 $fld->xtype = $rs->fields['xtype']; 815 $fld->type_length = $rs->fields['type_length']; 816 $fld->auto_increment= $rs->fields['is_identity']; 817 } 818 819 if ($save == ADODB_FETCH_NUM) 820 $retarr[] = $fld; 821 else 822 $retarr[strtoupper($fld->name)] = $fld; 823 824 $rs->MoveNext(); 825 826 } 827 $rs->Close(); 828 # start adg 829 $cached_columns[$table] = $retarr; 830 # end adg 831 return $retarr; 832 } 833 834 } 835 836 /*-------------------------------------------------------------------------------------- 837 Class Name: Recordset 838 --------------------------------------------------------------------------------------*/ 839 840 class ADORecordset_mssqlnative extends ADORecordSet { 841 842 var $databaseType = "mssqlnative"; 843 var $canSeek = false; 844 var $fieldOffset = 0; 845 // _mths works only in non-localised system 846 847 function __construct($id,$mode=false) 848 { 849 if ($mode === false) { 850 global $ADODB_FETCH_MODE; 851 $mode = $ADODB_FETCH_MODE; 852 853 } 854 $this->fetchMode = $mode; 855 return parent::__construct($id,$mode); 856 } 857 858 859 function _initrs() 860 { 861 global $ADODB_COUNTRECS; 862 # KMN # if ($this->connection->debug) ADOConnection::outp("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}"); 863 /*$retRowsAff = sqlsrv_rows_affected($this->_queryID);//"If you need to determine the number of rows a query will return before retrieving the actual results, appending a SELECT COUNT ... query would let you get that information, and then a call to next_result would move you to the "real" results." 864 ADOConnection::outp("rowsaff: ".serialize($retRowsAff)); 865 $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/ 866 $this->_numOfRows = -1;//not supported 867 $fieldmeta = sqlsrv_field_metadata($this->_queryID); 868 $this->_numOfFields = ($fieldmeta)? count($fieldmeta):-1; 869 # KMN # if ($this->connection->debug) ADOConnection::outp("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}"); 870 /* 871 * Copy the oracle method and cache the metadata at init time 872 */ 873 if ($this->_numOfFields>0) { 874 $this->_fieldobjs = array(); 875 $max = $this->_numOfFields; 876 for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i); 877 } 878 879 } 880 881 882 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se> 883 // get next resultset - requires PHP 4.0.5 or later 884 function NextRecordSet() 885 { 886 if (!sqlsrv_next_result($this->_queryID)) return false; 887 $this->_inited = false; 888 $this->bind = false; 889 $this->_currentRow = -1; 890 $this->Init(); 891 return true; 892 } 893 894 /* Use associative array to get fields array */ 895 function Fields($colname) 896 { 897 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname]; 898 if (!$this->bind) { 899 $this->bind = array(); 900 for ($i=0; $i < $this->_numOfFields; $i++) { 901 $o = $this->FetchField($i); 902 $this->bind[strtoupper($o->name)] = $i; 903 } 904 } 905 906 return $this->fields[$this->bind[strtoupper($colname)]]; 907 } 908 909 /* Returns: an object containing field information. 910 Get column information in the Recordset object. fetchField() can be used in order to obtain information about 911 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by 912 fetchField() is retrieved. 913 Designed By jcortinap#jc.com.mx 914 */ 915 function _FetchField($fieldOffset = -1) 916 { 917 $_typeConversion = array( 918 -155 => 'datetimeoffset', 919 -154 => 'char', 920 -152 => 'xml', 921 -151 => 'udt', 922 -11 => 'uniqueidentifier', 923 -10 => 'ntext', 924 -9 => 'nvarchar', 925 -8 => 'nchar', 926 -7 => 'bit', 927 -6 => 'tinyint', 928 -5 => 'bigint', 929 -4 => 'image', 930 -3 => 'varbinary', 931 -2 => 'timestamp', 932 -1 => 'text', 933 1 => 'char', 934 2 => 'numeric', 935 3 => 'decimal', 936 4 => 'int', 937 5 => 'smallint', 938 6 => 'float', 939 7 => 'real', 940 12 => 'varchar', 941 91 => 'date', 942 93 => 'datetime' 943 ); 944 945 $fa = @sqlsrv_field_metadata($this->_queryID); 946 if ($fieldOffset != -1) { 947 $fa = $fa[$fieldOffset]; 948 } 949 $false = false; 950 if (empty($fa)) { 951 $f = false;//PHP Notice: Only variable references should be returned by reference 952 } 953 else 954 { 955 // Convert to an object 956 $fa = array_change_key_case($fa, CASE_LOWER); 957 $fb = array(); 958 if ($fieldOffset != -1) 959 { 960 $fb = array( 961 'name' => $fa['name'], 962 'max_length' => $fa['size'], 963 'column_source' => $fa['name'], 964 'type' => $_typeConversion[$fa['type']] 965 ); 966 } 967 else 968 { 969 foreach ($fa as $key => $value) 970 { 971 $fb[] = array( 972 'name' => $value['name'], 973 'max_length' => $value['size'], 974 'column_source' => $value['name'], 975 'type' => $_typeConversion[$value['type']] 976 ); 977 } 978 } 979 $f = (object) $fb; 980 } 981 return $f; 982 } 983 984 /* 985 * Fetchfield copies the oracle method, it loads the field information 986 * into the _fieldobjs array once, to save multiple calls to the 987 * sqlsrv_field_metadata function 988 * 989 * @author KM Newnham 990 * @date 02/20/2013 991 */ 992 function FetchField($fieldOffset = -1) 993 { 994 return $this->_fieldobjs[$fieldOffset]; 995 } 996 997 function _seek($row) 998 { 999 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams. 1000 } 1001 1002 // speedup 1003 function MoveNext() 1004 { 1005 //# KMN # if ($this->connection->debug) ADOConnection::outp("movenext()"); 1006 //# KMN # if ($this->connection->debug) ADOConnection::outp("eof (beginning): ".$this->EOF); 1007 if ($this->EOF) return false; 1008 1009 $this->_currentRow++; 1010 // # KMN # if ($this->connection->debug) ADOConnection::outp("_currentRow: ".$this->_currentRow); 1011 1012 if ($this->_fetch()) return true; 1013 $this->EOF = true; 1014 //# KMN # if ($this->connection->debug) ADOConnection::outp("eof (end): ".$this->EOF); 1015 1016 return false; 1017 } 1018 1019 1020 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4 1021 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot! 1022 function _fetch($ignore_fields=false) 1023 { 1024 # KMN # if ($this->connection->debug) ADOConnection::outp("_fetch()"); 1025 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 1026 if ($this->fetchMode & ADODB_FETCH_NUM) { 1027 //# KMN # if ($this->connection->debug) ADOConnection::outp("fetch mode: both"); 1028 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH); 1029 } else { 1030 //# KMN # if ($this->connection->debug) ADOConnection::outp("fetch mode: assoc"); 1031 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC); 1032 } 1033 1034 if (is_array($this->fields)) { 1035 if (ADODB_ASSOC_CASE == 0) { 1036 foreach($this->fields as $k=>$v) { 1037 $this->fields[strtolower($k)] = $v; 1038 } 1039 } else if (ADODB_ASSOC_CASE == 1) { 1040 foreach($this->fields as $k=>$v) { 1041 $this->fields[strtoupper($k)] = $v; 1042 } 1043 } 1044 } 1045 } else { 1046 //# KMN # if ($this->connection->debug) ADOConnection::outp("fetch mode: num"); 1047 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC); 1048 } 1049 if(is_array($this->fields) && array_key_exists(1,$this->fields) && !array_key_exists(0,$this->fields)) {//fix fetch numeric keys since they're not 0 based 1050 $arrFixed = array(); 1051 foreach($this->fields as $key=>$value) { 1052 if(is_numeric($key)) { 1053 $arrFixed[$key-1] = $value; 1054 } else { 1055 $arrFixed[$key] = $value; 1056 } 1057 } 1058 //if($this->connection->debug) ADOConnection::outp("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true)); 1059 $this->fields = $arrFixed; 1060 } 1061 if(is_array($this->fields)) { 1062 foreach($this->fields as $key=>$value) { 1063 if (is_object($value) && method_exists($value, 'format')) {//is DateTime object 1064 $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z"); 1065 } 1066 } 1067 } 1068 if($this->fields === null) $this->fields = false; 1069 # KMN # if ($this->connection->debug) ADOConnection::outp("<hr>after _fetch, fields: <pre>".print_r($this->fields,true)." backtrace: ".adodb_backtrace(false)); 1070 return $this->fields; 1071 } 1072 1073 /* close() only needs to be called if you are worried about using too much memory while your script 1074 is running. All associated result memory for the specified result identifier will automatically be freed. */ 1075 function _close() 1076 { 1077 if(is_resource($this->_queryID)) { 1078 $rez = sqlsrv_free_stmt($this->_queryID); 1079 $this->_queryID = false; 1080 return $rez; 1081 } 1082 return true; 1083 } 1084 1085 // mssql uses a default date like Dec 30 2000 12:00AM 1086 static function UnixDate($v) 1087 { 1088 return ADORecordSet_array_mssqlnative::UnixDate($v); 1089 } 1090 1091 static function UnixTimeStamp($v) 1092 { 1093 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v); 1094 } 1095 } 1096 1097 1098 class ADORecordSet_array_mssqlnative extends ADORecordSet_array { 1099 function __construct($id=-1,$mode=false) 1100 { 1101 parent::__construct($id,$mode); 1102 } 1103 1104 // mssql uses a default date like Dec 30 2000 12:00AM 1105 static function UnixDate($v) 1106 { 1107 1108 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v); 1109 1110 global $ADODB_mssql_mths,$ADODB_mssql_date_order; 1111 1112 //Dec 30 2000 12:00AM 1113 if ($ADODB_mssql_date_order == 'dmy') { 1114 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) { 1115 return parent::UnixDate($v); 1116 } 1117 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1118 1119 $theday = $rr[1]; 1120 $themth = substr(strtoupper($rr[2]),0,3); 1121 } else { 1122 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) { 1123 return parent::UnixDate($v); 1124 } 1125 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1126 1127 $theday = $rr[2]; 1128 $themth = substr(strtoupper($rr[1]),0,3); 1129 } 1130 $themth = $ADODB_mssql_mths[$themth]; 1131 if ($themth <= 0) return false; 1132 // h-m-s-MM-DD-YY 1133 return adodb_mktime(0,0,0,$themth,$theday,$rr[3]); 1134 } 1135 1136 static function UnixTimeStamp($v) 1137 { 1138 1139 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v); 1140 1141 global $ADODB_mssql_mths,$ADODB_mssql_date_order; 1142 1143 //Dec 30 2000 12:00AM 1144 if ($ADODB_mssql_date_order == 'dmy') { 1145 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4}) +([0-9]{1,2}):([0-9]{1,2}) *([apAP]{0,1})|" 1146 ,$v, $rr)) return parent::UnixTimeStamp($v); 1147 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1148 1149 $theday = $rr[1]; 1150 $themth = substr(strtoupper($rr[2]),0,3); 1151 } else { 1152 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4}) +([0-9]{1,2}):([0-9]{1,2}) *([apAP]{0,1})|" 1153 ,$v, $rr)) return parent::UnixTimeStamp($v); 1154 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1155 1156 $theday = $rr[2]; 1157 $themth = substr(strtoupper($rr[1]),0,3); 1158 } 1159 1160 $themth = $ADODB_mssql_mths[$themth]; 1161 if ($themth <= 0) return false; 1162 1163 switch (strtoupper($rr[6])) { 1164 case 'P': 1165 if ($rr[4]<12) $rr[4] += 12; 1166 break; 1167 case 'A': 1168 if ($rr[4]==12) $rr[4] = 0; 1169 break; 1170 default: 1171 break; 1172 } 1173 // h-m-s-MM-DD-YY 1174 return adodb_mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]); 1175 } 1176 } 1177 1178 /* 1179 Code Example 1: 1180 1181 select object_name(constid) as constraint_name, 1182 object_name(fkeyid) as table_name, 1183 col_name(fkeyid, fkey) as column_name, 1184 object_name(rkeyid) as referenced_table_name, 1185 col_name(rkeyid, rkey) as referenced_column_name 1186 from sysforeignkeys 1187 where object_name(fkeyid) = x 1188 order by constraint_name, table_name, referenced_table_name, keyno 1189 1190 Code Example 2: 1191 select constraint_name, 1192 column_name, 1193 ordinal_position 1194 from information_schema.key_column_usage 1195 where constraint_catalog = db_name() 1196 and table_name = x 1197 order by constraint_name, ordinal_position 1198 1199 http://www.databasejournal.com/scripts/article.php/1440551 1200 */
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
