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