| [ Index ] |
PHP Cross Reference of moodle-2.8 |
[Summary view] [Print] [Text view]
1 <?php 2 /* 3 V5.19 23-Apr-2014 (c) 2000-2014 John Lim (jlim#natsoft.com). All rights reserved. 4 Released under both BSD license and Lesser GPL library license. 5 Whenever there is any discrepancy between the two licenses, 6 the BSD license will take precedence. 7 Set tabs to 4 for best viewing. 8 9 Latest version is available at http://adodb.sourceforge.net 10 11 Native mssql driver. Requires mssql client. Works on Windows. 12 http://www.microsoft.com/sql/technologies/php/default.mspx 13 To configure for Unix, see 14 http://phpbuilder.com/columns/alberto20000919.php3 15 16 $stream = sqlsrv_get_field($stmt, $index, SQLSRV_SQLTYPE_STREAM(SQLSRV_ENC_BINARY)); 17 stream_filter_append($stream, "convert.iconv.ucs-2/utf-8"); // Voila, UTF-8 can be read directly from $stream 18 19 */ 20 21 // security - hide paths 22 if (!defined('ADODB_DIR')) die(); 23 24 if (!function_exists('sqlsrv_configure')) { 25 die("mssqlnative extension not installed"); 26 } 27 28 if (!function_exists('sqlsrv_set_error_handling')) { 29 function sqlsrv_set_error_handling($constant) { 30 sqlsrv_configure("WarningsReturnAsErrors", $constant); 31 } 32 } 33 if (!function_exists('sqlsrv_log_set_severity')) { 34 function sqlsrv_log_set_severity($constant) { 35 sqlsrv_configure("LogSeverity", $constant); 36 } 37 } 38 if (!function_exists('sqlsrv_log_set_subsystems')) { 39 function sqlsrv_log_set_subsystems($constant) { 40 sqlsrv_configure("LogSubsystems", $constant); 41 } 42 } 43 44 45 //---------------------------------------------------------------- 46 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002 47 // and this causes tons of problems because localized versions of 48 // MSSQL will return the dates in dmy or mdy order; and also the 49 // month strings depends on what language has been configured. The 50 // following two variables allow you to control the localization 51 // settings - Ugh. 52 // 53 // MORE LOCALIZATION INFO 54 // ---------------------- 55 // To configure datetime, look for and modify sqlcommn.loc, 56 // typically found in c:\mssql\install 57 // Also read : 58 // http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918 59 // Alternatively use: 60 // CONVERT(char(12),datecol,120) 61 // 62 // Also if your month is showing as month-1, 63 // e.g. Jan 13, 2002 is showing as 13/0/2002, then see 64 // http://phplens.com/lens/lensforum/msgs.php?id=7048&x=1 65 // it's a localisation problem. 66 //---------------------------------------------------------------- 67 68 69 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc 70 if (ADODB_PHPVER >= 0x4300) { 71 // docs say 4.2.0, but testing shows only since 4.3.0 does it work! 72 ini_set('mssql.datetimeconvert',0); 73 } else { 74 global $ADODB_mssql_mths; // array, months must be upper-case 75 $ADODB_mssql_date_order = 'mdy'; 76 $ADODB_mssql_mths = array( 77 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6, 78 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12); 79 } 80 81 class ADODB_mssqlnative extends ADOConnection { 82 var $databaseType = "mssqlnative"; 83 var $dataProvider = "mssqlnative"; 84 var $replaceQuote = "''"; // string to use to replace quotes 85 var $fmtDate = "'Y-m-d'"; 86 var $fmtTimeStamp = "'Y-m-d\TH:i:s'"; 87 var $hasInsertID = true; 88 var $substr = "substring"; 89 var $length = 'len'; 90 var $hasAffectedRows = true; 91 var $poorAffectedRows = false; 92 var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'"; 93 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'))"; 94 var $metaColumnsSQL = 95 "select c.name, 96 t.name as type, 97 c.length, 98 c.xprec as precision, 99 c.xscale as scale, 100 c.isnullable as nullable, 101 c.cdefault as default_value, 102 c.xtype, 103 t.length as type_length, 104 sc.is_identity 105 from syscolumns c 106 join systypes t on t.xusertype=c.xusertype 107 join sysobjects o on o.id=c.id 108 join sys.tables st on st.name=o.name 109 join sys.columns sc on sc.object_id = st.object_id and sc.name=c.name 110 where o.name='%s'"; 111 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE 112 var $hasGenID = true; 113 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)'; 114 var $sysTimeStamp = 'GetDate()'; 115 var $maxParameterLen = 4000; 116 var $arrayClass = 'ADORecordSet_array_mssqlnative'; 117 var $uniqueSort = true; 118 var $leftOuter = '*='; 119 var $rightOuter = '=*'; 120 var $ansiOuter = true; // for mssql7 or later 121 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000 122 var $uniqueOrderBy = true; 123 var $_bindInputArray = true; 124 var $_dropSeqSQL = "drop table %s"; 125 var $connectionInfo = array(); 126 var $sequences = false; 127 var $mssql_version = ''; 128 129 function ADODB_mssqlnative() 130 { 131 if ($this->debug) { 132 error_log("<pre>"); 133 sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL ); 134 sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL ); 135 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL); 136 sqlsrv_configure('warnings_return_as_errors', 0); 137 } else { 138 sqlsrv_set_error_handling(0); 139 sqlsrv_log_set_severity(0); 140 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL); 141 sqlsrv_configure('warnings_return_as_errors', 0); 142 } 143 } 144 function ServerVersion() { 145 $data = $this->ServerInfo(); 146 if (preg_match('/^09/',$data['version'])){ 147 /* 148 * SQL Server 2005 149 */ 150 $this->mssql_version = 9; 151 } elseif (preg_match('/^10/',$data['version'])){ 152 /* 153 * SQL Server 2008 154 */ 155 $this->mssql_version = 10; 156 } elseif (preg_match('/^11/',$data['version'])){ 157 /* 158 * SQL Server 2012 159 */ 160 $this->mssql_version = 11; 161 } else 162 die("SQL SERVER VERSION {$data['version']} NOT SUPPORTED IN mssqlnative DRIVER"); 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(); 213 break; 214 case 11: 215 return $this->GenID2012(); 216 break; 217 } 218 } 219 220 function CreateSequence($seq='adodbseq',$start=1) 221 { 222 if (!$this->mssql_vesion) 223 $this->ServerVersion(); 224 225 switch($this->mssql_version){ 226 case 9: 227 case 10: 228 return $this->CreateSequence2008(); 229 break; 230 case 11: 231 return $this->CreateSequence2012(); 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) error_log("<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) error_log("<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='adodb',$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) error_log("<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) error_log("<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 true; 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='adodbseq',$start=1); 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) error_log('<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) error_log('<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) error_log('<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 } else { 462 $this->_errorMsg = "No errors found"; 463 } 464 return $this->_errorMsg; 465 } 466 467 function ErrorNo() 468 { 469 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode; 470 $err = sqlsrv_errors(SQLSRV_ERR_ALL); 471 if($err[0]) return $err[0]['code']; 472 else return -1; 473 } 474 475 // returns true or false 476 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) 477 { 478 if (!function_exists('sqlsrv_connect')) return null; 479 $connectionInfo = $this->connectionInfo; 480 $connectionInfo["Database"]=$argDatabasename; 481 $connectionInfo["UID"]=$argUsername; 482 $connectionInfo["PWD"]=$argPassword; 483 if ($this->debug) error_log("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true)); 484 //if ($this->debug) error_log("<hr>_connectionID before: ".serialize($this->_connectionID)); 485 if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) { 486 if ($this->debug) error_log( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true)); 487 return false; 488 } 489 //if ($this->debug) error_log(" _connectionID after: ".serialize($this->_connectionID)); 490 //if ($this->debug) error_log("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>"); 491 return true; 492 } 493 494 // returns true or false 495 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 496 { 497 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!) 498 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename); 499 } 500 501 function Prepare($sql) 502 { 503 return $sql; // prepare does not work properly with bind parameters as bind parameters are managed by sqlsrv_prepare! 504 505 $stmt = sqlsrv_prepare( $this->_connectionID, $sql); 506 if (!$stmt) return $sql; 507 return array($sql,$stmt); 508 } 509 510 // returns concatenated string 511 // MSSQL requires integers to be cast as strings 512 // automatically cast every datatype to VARCHAR(255) 513 // @author David Rogers (introspectshun) 514 function Concat() 515 { 516 $s = ""; 517 $arr = func_get_args(); 518 519 // Split single record on commas, if possible 520 if (sizeof($arr) == 1) { 521 foreach ($arr as $arg) { 522 $args = explode(',', $arg); 523 } 524 $arr = $args; 525 } 526 527 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";')); 528 $s = implode('+',$arr); 529 if (sizeof($arr) > 0) return "$s"; 530 531 return ''; 532 } 533 534 /* 535 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars 536 So all your blobs must be of type "image". 537 538 Remember to set in php.ini the following... 539 540 ; Valid range 0 - 2147483647. Default = 4096. 541 mssql.textlimit = 0 ; zero to pass through 542 543 ; Valid range 0 - 2147483647. Default = 4096. 544 mssql.textsize = 0 ; zero to pass through 545 */ 546 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 547 { 548 549 if (strtoupper($blobtype) == 'CLOB') { 550 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where"; 551 return $this->Execute($sql) != false; 552 } 553 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where"; 554 return $this->Execute($sql) != false; 555 } 556 557 // returns query ID if successful, otherwise false 558 function _query($sql,$inputarr=false) 559 { 560 $this->_errorMsg = false; 561 562 if (is_array($sql)) $sql = $sql[1]; 563 564 $insert = false; 565 // handle native driver flaw for retrieving the last insert ID 566 if(preg_match('/^\W*(insert [^;]+);?$/i', $sql)) { 567 $insert = true; 568 $sql .= '; '.$this->identitySQL; // select scope_identity() 569 } 570 if($inputarr) { 571 $rez = sqlsrv_query($this->_connectionID, $sql, $inputarr); 572 } else { 573 $rez = sqlsrv_query($this->_connectionID,$sql); 574 } 575 576 if ($this->debug) error_log("<hr>running query: ".var_export($sql,true)."<hr>input array: ".var_export($inputarr,true)."<hr>result: ".var_export($rez,true)); 577 578 if(!$rez) { 579 $rez = false; 580 } else if ($insert) { 581 // retrieve the last insert ID (where applicable) 582 sqlsrv_next_result($rez); 583 sqlsrv_fetch($rez); 584 $this->lastInsertID = sqlsrv_get_field($rez, 0); 585 } 586 return $rez; 587 } 588 589 // returns true or false 590 function _close() 591 { 592 if ($this->transCnt) $this->RollbackTrans(); 593 $rez = @sqlsrv_close($this->_connectionID); 594 $this->_connectionID = false; 595 return $rez; 596 } 597 598 // mssql uses a default date like Dec 30 2000 12:00AM 599 static function UnixDate($v) 600 { 601 return ADORecordSet_array_mssqlnative::UnixDate($v); 602 } 603 604 static function UnixTimeStamp($v) 605 { 606 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v); 607 } 608 609 function MetaIndexes($table,$primary=false, $owner = false) 610 { 611 $table = $this->qstr($table); 612 613 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno, 614 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, 615 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique 616 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id 617 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid 618 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid 619 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table 620 ORDER BY O.name, I.Name, K.keyno"; 621 622 global $ADODB_FETCH_MODE; 623 $save = $ADODB_FETCH_MODE; 624 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 625 if ($this->fetchMode !== FALSE) { 626 $savem = $this->SetFetchMode(FALSE); 627 } 628 629 $rs = $this->Execute($sql); 630 if (isset($savem)) { 631 $this->SetFetchMode($savem); 632 } 633 $ADODB_FETCH_MODE = $save; 634 635 if (!is_object($rs)) { 636 return FALSE; 637 } 638 639 $indexes = array(); 640 while ($row = $rs->FetchRow()) { 641 if (!$primary && $row[5]) continue; 642 643 $indexes[$row[0]]['unique'] = $row[6]; 644 $indexes[$row[0]]['columns'][] = $row[1]; 645 } 646 return $indexes; 647 } 648 649 function MetaForeignKeys($table, $owner=false, $upper=false) 650 { 651 global $ADODB_FETCH_MODE; 652 653 $save = $ADODB_FETCH_MODE; 654 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 655 $table = $this->qstr(strtoupper($table)); 656 657 $sql = 658 "select object_name(constid) as constraint_name, 659 col_name(fkeyid, fkey) as column_name, 660 object_name(rkeyid) as referenced_table_name, 661 col_name(rkeyid, rkey) as referenced_column_name 662 from sysforeignkeys 663 where upper(object_name(fkeyid)) = $table 664 order by constraint_name, referenced_table_name, keyno"; 665 666 $constraints =& $this->GetArray($sql); 667 668 $ADODB_FETCH_MODE = $save; 669 670 $arr = false; 671 foreach($constraints as $constr) { 672 //print_r($constr); 673 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3]; 674 } 675 if (!$arr) return false; 676 677 $arr2 = false; 678 679 foreach($arr as $k => $v) { 680 foreach($v as $a => $b) { 681 if ($upper) $a = strtoupper($a); 682 $arr2[$a] = $b; 683 } 684 } 685 return $arr2; 686 } 687 688 //From: Fernando Moreira <[email protected]> 689 function MetaDatabases() 690 { 691 $this->SelectDB("master"); 692 $rs =& $this->Execute($this->metaDatabasesSQL); 693 $rows = $rs->GetRows(); 694 $ret = array(); 695 for($i=0;$i<count($rows);$i++) { 696 $ret[] = $rows[$i][0]; 697 } 698 $this->SelectDB($this->database); 699 if($ret) 700 return $ret; 701 else 702 return false; 703 } 704 705 // "Stein-Aksel Basma" <[email protected]> 706 // tested with MSSQL 2000 707 function MetaPrimaryKeys($table, $owner=false) 708 { 709 global $ADODB_FETCH_MODE; 710 711 $schema = ''; 712 $this->_findschema($table,$schema); 713 if (!$schema) $schema = $this->database; 714 if ($schema) $schema = "and k.table_catalog like '$schema%'"; 715 716 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k, 717 information_schema.table_constraints tc 718 where tc.constraint_name = k.constraint_name and tc.constraint_type = 719 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position "; 720 721 $savem = $ADODB_FETCH_MODE; 722 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 723 $a = $this->GetCol($sql); 724 $ADODB_FETCH_MODE = $savem; 725 726 if ($a && sizeof($a)>0) return $a; 727 $false = false; 728 return $false; 729 } 730 731 732 function MetaTables($ttype=false,$showSchema=false,$mask=false) 733 { 734 if ($mask) { 735 $save = $this->metaTablesSQL; 736 $mask = $this->qstr(($mask)); 737 $this->metaTablesSQL .= " AND name like $mask"; 738 } 739 $ret = ADOConnection::MetaTables($ttype,$showSchema); 740 741 if ($mask) { 742 $this->metaTablesSQL = $save; 743 } 744 return $ret; 745 } 746 function MetaColumns($table, $upper=true, $schema=false){ 747 748 # start adg 749 static $cached_columns = array(); 750 if ($this->cachedSchemaFlush) 751 $cached_columns = array(); 752 753 if (array_key_exists($table,$cached_columns)){ 754 return $cached_columns[$table]; 755 } 756 # end adg 757 758 if (!$this->mssql_version) 759 $this->ServerVersion(); 760 761 $this->_findschema($table,$schema); 762 if ($schema) { 763 $dbName = $this->database; 764 $this->SelectDB($schema); 765 } 766 global $ADODB_FETCH_MODE; 767 $save = $ADODB_FETCH_MODE; 768 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 769 770 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false); 771 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table)); 772 773 if ($schema) { 774 $this->SelectDB($dbName); 775 } 776 777 if (isset($savem)) $this->SetFetchMode($savem); 778 $ADODB_FETCH_MODE = $save; 779 if (!is_object($rs)) { 780 $false = false; 781 return $false; 782 } 783 784 $retarr = array(); 785 while (!$rs->EOF){ 786 787 $fld = new ADOFieldObject(); 788 if (array_key_exists(0,$rs->fields)) { 789 $fld->name = $rs->fields[0]; 790 $fld->type = $rs->fields[1]; 791 $fld->max_length = $rs->fields[2]; 792 $fld->precision = $rs->fields[3]; 793 $fld->scale = $rs->fields[4]; 794 $fld->not_null =!$rs->fields[5]; 795 $fld->has_default = $rs->fields[6]; 796 $fld->xtype = $rs->fields[7]; 797 $fld->type_length = $rs->fields[8]; 798 $fld->auto_increment= $rs->fields[9]; 799 } else { 800 $fld->name = $rs->fields['name']; 801 $fld->type = $rs->fields['type']; 802 $fld->max_length = $rs->fields['length']; 803 $fld->precision = $rs->fields['precision']; 804 $fld->scale = $rs->fields['scale']; 805 $fld->not_null =!$rs->fields['nullable']; 806 $fld->has_default = $rs->fields['default_value']; 807 $fld->xtype = $rs->fields['xtype']; 808 $fld->type_length = $rs->fields['type_length']; 809 $fld->auto_increment= $rs->fields['is_identity']; 810 } 811 812 if ($save == ADODB_FETCH_NUM) 813 $retarr[] = $fld; 814 else 815 $retarr[strtoupper($fld->name)] = $fld; 816 817 $rs->MoveNext(); 818 819 } 820 $rs->Close(); 821 # start adg 822 $cached_columns[$table] = $retarr; 823 # end adg 824 return $retarr; 825 } 826 827 } 828 829 /*-------------------------------------------------------------------------------------- 830 Class Name: Recordset 831 --------------------------------------------------------------------------------------*/ 832 833 class ADORecordset_mssqlnative extends ADORecordSet { 834 835 var $databaseType = "mssqlnative"; 836 var $canSeek = false; 837 var $fieldOffset = 0; 838 // _mths works only in non-localised system 839 840 function ADORecordset_mssqlnative($id,$mode=false) 841 { 842 if ($mode === false) { 843 global $ADODB_FETCH_MODE; 844 $mode = $ADODB_FETCH_MODE; 845 846 } 847 $this->fetchMode = $mode; 848 return $this->ADORecordSet($id,$mode); 849 } 850 851 852 function _initrs() 853 { 854 global $ADODB_COUNTRECS; 855 # KMN # if ($this->connection->debug) error_log("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}"); 856 /*$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." 857 error_log("rowsaff: ".serialize($retRowsAff)); 858 $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/ 859 $this->_numOfRows = -1;//not supported 860 $fieldmeta = sqlsrv_field_metadata($this->_queryID); 861 $this->_numOfFields = ($fieldmeta)? count($fieldmeta):-1; 862 # KMN # if ($this->connection->debug) error_log("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}"); 863 /* 864 * Copy the oracle method and cache the metadata at init time 865 */ 866 if ($this->_numOfFields>0) { 867 $this->_fieldobjs = array(); 868 $max = $this->_numOfFields; 869 for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i); 870 } 871 872 } 873 874 875 //Contributed by "Sven Axelsson" <[email protected]> 876 // get next resultset - requires PHP 4.0.5 or later 877 function NextRecordSet() 878 { 879 if (!sqlsrv_next_result($this->_queryID)) return false; 880 $this->_inited = false; 881 $this->bind = false; 882 $this->_currentRow = -1; 883 $this->Init(); 884 return true; 885 } 886 887 /* Use associative array to get fields array */ 888 function Fields($colname) 889 { 890 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname]; 891 if (!$this->bind) { 892 $this->bind = array(); 893 for ($i=0; $i < $this->_numOfFields; $i++) { 894 $o = $this->FetchField($i); 895 $this->bind[strtoupper($o->name)] = $i; 896 } 897 } 898 899 return $this->fields[$this->bind[strtoupper($colname)]]; 900 } 901 902 /* Returns: an object containing field information. 903 Get column information in the Recordset object. fetchField() can be used in order to obtain information about 904 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by 905 fetchField() is retrieved. 906 Designed By jcortinap#jc.com.mx 907 */ 908 function _FetchField($fieldOffset = -1) 909 { 910 $_typeConversion = array( 911 -155 => 'datetimeoffset', 912 -154 => 'time', 913 -152 => 'xml', 914 -151 => 'udt', 915 -11 => 'uniqueidentifier', 916 -10 => 'ntext', 917 -9 => 'nvarchar', 918 -8 => 'nchar', 919 -7 => 'bit', 920 -6 => 'tinyint', 921 -5 => 'bigint', 922 -4 => 'image', 923 -3 => 'varbinary', 924 -2 => 'timestamp', 925 -1 => 'text', 926 1 => 'char', 927 2 => 'numeric', 928 3 => 'decimal', 929 4 => 'int', 930 5 => 'smallint', 931 6 => 'float', 932 7 => 'real', 933 12 => 'varchar', 934 91 => 'date', 935 93 => 'datetime' 936 ); 937 938 $fa = @sqlsrv_field_metadata($this->_queryID); 939 if ($fieldOffset != -1) { 940 $fa = $fa[$fieldOffset]; 941 } 942 $false = false; 943 if (empty($fa)) { 944 $f = false;//PHP Notice: Only variable references should be returned by reference 945 } 946 else 947 { 948 // Convert to an object 949 $fa = array_change_key_case($fa, CASE_LOWER); 950 $fb = array(); 951 if ($fieldOffset != -1) 952 { 953 $fb = array( 954 'name' => $fa['name'], 955 'max_length' => $fa['size'], 956 'column_source' => $fa['name'], 957 'type' => $_typeConversion[$fa['type']] 958 ); 959 } 960 else 961 { 962 foreach ($fa as $key => $value) 963 { 964 $fb[] = array( 965 'name' => $value['name'], 966 'max_length' => $value['size'], 967 'column_source' => $value['name'], 968 'type' => $_typeConversion[$value['type']] 969 ); 970 } 971 } 972 $f = (object) $fb; 973 } 974 return $f; 975 } 976 977 /* 978 * Fetchfield copies the oracle method, it loads the field information 979 * into the _fieldobjs array once, to save multiple calls to the 980 * sqlsrv_field_metadata function 981 * 982 * @author KM Newnham 983 * @date 02/20/2013 984 */ 985 function FetchField($fieldOffset = -1) 986 { 987 return $this->_fieldobjs[$fieldOffset]; 988 } 989 990 function _seek($row) 991 { 992 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams. 993 } 994 995 // speedup 996 function MoveNext() 997 { 998 //# KMN # if ($this->connection->debug) error_log("movenext()"); 999 //# KMN # if ($this->connection->debug) error_log("eof (beginning): ".$this->EOF); 1000 if ($this->EOF) return false; 1001 1002 $this->_currentRow++; 1003 // # KMN # if ($this->connection->debug) error_log("_currentRow: ".$this->_currentRow); 1004 1005 if ($this->_fetch()) return true; 1006 $this->EOF = true; 1007 //# KMN # if ($this->connection->debug) error_log("eof (end): ".$this->EOF); 1008 1009 return false; 1010 } 1011 1012 1013 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4 1014 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot! 1015 function _fetch($ignore_fields=false) 1016 { 1017 # KMN # if ($this->connection->debug) error_log("_fetch()"); 1018 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 1019 if ($this->fetchMode & ADODB_FETCH_NUM) { 1020 //# KMN # if ($this->connection->debug) error_log("fetch mode: both"); 1021 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH); 1022 } else { 1023 //# KMN # if ($this->connection->debug) error_log("fetch mode: assoc"); 1024 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC); 1025 } 1026 1027 if (is_array($this->fields)) { 1028 if (ADODB_ASSOC_CASE == 0) { 1029 foreach($this->fields as $k=>$v) { 1030 $this->fields[strtolower($k)] = $v; 1031 } 1032 } else if (ADODB_ASSOC_CASE == 1) { 1033 foreach($this->fields as $k=>$v) { 1034 $this->fields[strtoupper($k)] = $v; 1035 } 1036 } 1037 } 1038 } else { 1039 //# KMN # if ($this->connection->debug) error_log("fetch mode: num"); 1040 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC); 1041 } 1042 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 1043 $arrFixed = array(); 1044 foreach($this->fields as $key=>$value) { 1045 if(is_numeric($key)) { 1046 $arrFixed[$key-1] = $value; 1047 } else { 1048 $arrFixed[$key] = $value; 1049 } 1050 } 1051 //if($this->connection->debug) error_log("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true)); 1052 $this->fields = $arrFixed; 1053 } 1054 if(is_array($this->fields)) { 1055 foreach($this->fields as $key=>$value) { 1056 if (is_object($value) && method_exists($value, 'format')) {//is DateTime object 1057 $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z"); 1058 } 1059 } 1060 } 1061 if($this->fields === null) $this->fields = false; 1062 # KMN # if ($this->connection->debug) error_log("<hr>after _fetch, fields: <pre>".print_r($this->fields,true)." backtrace: ".adodb_backtrace(false)); 1063 return $this->fields; 1064 } 1065 1066 /* close() only needs to be called if you are worried about using too much memory while your script 1067 is running. All associated result memory for the specified result identifier will automatically be freed. */ 1068 function _close() 1069 { 1070 $rez = sqlsrv_free_stmt($this->_queryID); 1071 $this->_queryID = false; 1072 return $rez; 1073 } 1074 1075 // mssql uses a default date like Dec 30 2000 12:00AM 1076 static function UnixDate($v) 1077 { 1078 return ADORecordSet_array_mssqlnative::UnixDate($v); 1079 } 1080 1081 static function UnixTimeStamp($v) 1082 { 1083 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v); 1084 } 1085 } 1086 1087 1088 class ADORecordSet_array_mssqlnative extends ADORecordSet_array { 1089 function ADORecordSet_array_mssqlnative($id=-1,$mode=false) 1090 { 1091 $this->ADORecordSet_array($id,$mode); 1092 } 1093 1094 // mssql uses a default date like Dec 30 2000 12:00AM 1095 static function UnixDate($v) 1096 { 1097 1098 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v); 1099 1100 global $ADODB_mssql_mths,$ADODB_mssql_date_order; 1101 1102 //Dec 30 2000 12:00AM 1103 if ($ADODB_mssql_date_order == 'dmy') { 1104 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) { 1105 return parent::UnixDate($v); 1106 } 1107 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1108 1109 $theday = $rr[1]; 1110 $themth = substr(strtoupper($rr[2]),0,3); 1111 } else { 1112 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) { 1113 return parent::UnixDate($v); 1114 } 1115 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1116 1117 $theday = $rr[2]; 1118 $themth = substr(strtoupper($rr[1]),0,3); 1119 } 1120 $themth = $ADODB_mssql_mths[$themth]; 1121 if ($themth <= 0) return false; 1122 // h-m-s-MM-DD-YY 1123 return adodb_mktime(0,0,0,$themth,$theday,$rr[3]); 1124 } 1125 1126 static function UnixTimeStamp($v) 1127 { 1128 1129 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v); 1130 1131 global $ADODB_mssql_mths,$ADODB_mssql_date_order; 1132 1133 //Dec 30 2000 12:00AM 1134 if ($ADODB_mssql_date_order == 'dmy') { 1135 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})|" 1136 ,$v, $rr)) return parent::UnixTimeStamp($v); 1137 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1138 1139 $theday = $rr[1]; 1140 $themth = substr(strtoupper($rr[2]),0,3); 1141 } else { 1142 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})|" 1143 ,$v, $rr)) return parent::UnixTimeStamp($v); 1144 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1145 1146 $theday = $rr[2]; 1147 $themth = substr(strtoupper($rr[1]),0,3); 1148 } 1149 1150 $themth = $ADODB_mssql_mths[$themth]; 1151 if ($themth <= 0) return false; 1152 1153 switch (strtoupper($rr[6])) { 1154 case 'P': 1155 if ($rr[4]<12) $rr[4] += 12; 1156 break; 1157 case 'A': 1158 if ($rr[4]==12) $rr[4] = 0; 1159 break; 1160 default: 1161 break; 1162 } 1163 // h-m-s-MM-DD-YY 1164 return adodb_mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]); 1165 } 1166 } 1167 1168 /* 1169 Code Example 1: 1170 1171 select object_name(constid) as constraint_name, 1172 object_name(fkeyid) as table_name, 1173 col_name(fkeyid, fkey) as column_name, 1174 object_name(rkeyid) as referenced_table_name, 1175 col_name(rkeyid, rkey) as referenced_column_name 1176 from sysforeignkeys 1177 where object_name(fkeyid) = x 1178 order by constraint_name, table_name, referenced_table_name, keyno 1179 1180 Code Example 2: 1181 select constraint_name, 1182 column_name, 1183 ordinal_position 1184 from information_schema.key_column_usage 1185 where constraint_catalog = db_name() 1186 and table_name = x 1187 order by constraint_name, ordinal_position 1188 1189 http://www.databasejournal.com/scripts/article.php/1440551 1190 */
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Nov 28 20:29:05 2014 | Cross-referenced by PHPXref 0.7.1 |