| [ Index ] |
PHP Cross Reference of Unnamed Project |
[Summary view] [Print] [Text view]
1 <?php 2 /* 3 @version v5.20.3 01-Jan-2016 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.sourceforge.net 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 $sequences = false; 129 var $mssql_version = ''; 130 131 function __construct() 132 { 133 if ($this->debug) { 134 ADOConnection::outp("<pre>"); 135 sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL ); 136 sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL ); 137 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL); 138 sqlsrv_configure('WarningsReturnAsErrors', 0); 139 } else { 140 sqlsrv_set_error_handling(0); 141 sqlsrv_log_set_severity(0); 142 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL); 143 sqlsrv_configure('WarningsReturnAsErrors', 0); 144 } 145 } 146 function ServerVersion() { 147 $data = $this->ServerInfo(); 148 if (preg_match('/^09/',$data['version'])){ 149 /* 150 * SQL Server 2005 151 */ 152 $this->mssql_version = 9; 153 } elseif (preg_match('/^10/',$data['version'])){ 154 /* 155 * SQL Server 2008 156 */ 157 $this->mssql_version = 10; 158 } elseif (preg_match('/^11/',$data['version'])){ 159 /* 160 * SQL Server 2012 161 */ 162 $this->mssql_version = 11; 163 } else 164 die("SQL SERVER VERSION {$data['version']} NOT SUPPORTED IN mssqlnative DRIVER"); 165 } 166 167 function ServerInfo() { 168 global $ADODB_FETCH_MODE; 169 static $arr = false; 170 if (is_array($arr)) 171 return $arr; 172 if ($this->fetchMode === false) { 173 $savem = $ADODB_FETCH_MODE; 174 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 175 } elseif ($this->fetchMode >=0 && $this->fetchMode <=2) { 176 $savem = $this->fetchMode; 177 } else 178 $savem = $this->SetFetchMode(ADODB_FETCH_NUM); 179 180 $arrServerInfo = sqlsrv_server_info($this->_connectionID); 181 $ADODB_FETCH_MODE = $savem; 182 $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase']; 183 $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']); 184 return $arr; 185 } 186 187 function IfNull( $field, $ifNull ) 188 { 189 return " ISNULL($field, $ifNull) "; // if MS SQL Server 190 } 191 192 function _insertid() 193 { 194 // SCOPE_IDENTITY() 195 // Returns the last IDENTITY value inserted into an IDENTITY column in 196 // the same scope. A scope is a module -- a stored procedure, trigger, 197 // function, or batch. Thus, two statements are in the same scope if 198 // they are in the same stored procedure, function, or batch. 199 return $this->lastInsertID; 200 } 201 202 function _affectedrows() 203 { 204 if ($this->_queryID) 205 return sqlsrv_rows_affected($this->_queryID); 206 } 207 208 function GenID($seq='adodbseq',$start=1) { 209 if (!$this->mssql_version) 210 $this->ServerVersion(); 211 switch($this->mssql_version){ 212 case 9: 213 case 10: 214 return $this->GenID2008(); 215 break; 216 case 11: 217 return $this->GenID2012(); 218 break; 219 } 220 } 221 222 function CreateSequence($seq='adodbseq',$start=1) 223 { 224 if (!$this->mssql_vesion) 225 $this->ServerVersion(); 226 227 switch($this->mssql_version){ 228 case 9: 229 case 10: 230 return $this->CreateSequence2008(); 231 break; 232 case 11: 233 return $this->CreateSequence2012(); 234 break; 235 } 236 237 } 238 239 /** 240 * For Server 2005,2008, duplicate a sequence with an identity table 241 */ 242 function CreateSequence2008($seq='adodbseq',$start=1) 243 { 244 if($this->debug) ADOConnection::outp("<hr>CreateSequence($seq,$start)"); 245 sqlsrv_begin_transaction($this->_connectionID); 246 $start -= 1; 247 $this->Execute("create table $seq (id int)");//was float(53) 248 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 249 if (!$ok) { 250 if($this->debug) ADOConnection::outp("<hr>Error: ROLLBACK"); 251 sqlsrv_rollback($this->_connectionID); 252 return false; 253 } 254 sqlsrv_commit($this->_connectionID); 255 return true; 256 } 257 258 /** 259 * Proper Sequences Only available to Server 2012 and up 260 */ 261 function CreateSequence2012($seq='adodb',$start=1){ 262 if (!$this->sequences){ 263 $sql = "SELECT name FROM sys.sequences"; 264 $this->sequences = $this->GetCol($sql); 265 } 266 $ok = $this->Execute("CREATE SEQUENCE $seq START WITH $start INCREMENT BY 1"); 267 if (!$ok) 268 die("CANNOT CREATE SEQUENCE" . print_r(sqlsrv_errors(),true)); 269 $this->sequences[] = $seq; 270 } 271 272 /** 273 * For Server 2005,2008, duplicate a sequence with an identity table 274 */ 275 function GenID2008($seq='adodbseq',$start=1) 276 { 277 if($this->debug) ADOConnection::outp("<hr>CreateSequence($seq,$start)"); 278 sqlsrv_begin_transaction($this->_connectionID); 279 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1"); 280 if (!$ok) { 281 $start -= 1; 282 $this->Execute("create table $seq (id int)");//was float(53) 283 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 284 if (!$ok) { 285 if($this->debug) ADOConnection::outp("<hr>Error: ROLLBACK"); 286 sqlsrv_rollback($this->_connectionID); 287 return false; 288 } 289 } 290 $num = $this->GetOne("select id from $seq"); 291 sqlsrv_commit($this->_connectionID); 292 return true; 293 } 294 /** 295 * Only available to Server 2012 and up 296 * Cannot do this the normal adodb way by trapping an error if the 297 * sequence does not exist because sql server will auto create a 298 * sequence with the starting number of -9223372036854775808 299 */ 300 function GenID2012($seq='adodbseq',$start=1) 301 { 302 303 /* 304 * First time in create an array of sequence names that we 305 * can use in later requests to see if the sequence exists 306 * the overhead is creating a list of sequences every time 307 * we need access to at least 1. If we really care about 308 * performance, we could maybe flag a 'nocheck' class variable 309 */ 310 if (!$this->sequences){ 311 $sql = "SELECT name FROM sys.sequences"; 312 $this->sequences = $this->GetCol($sql); 313 } 314 if (!is_array($this->sequences) 315 || is_array($this->sequences) && !in_array($seq,$this->sequences)){ 316 $this->CreateSequence2012($seq='adodbseq',$start=1); 317 318 } 319 $num = $this->GetOne("SELECT NEXT VALUE FOR $seq"); 320 return $num; 321 } 322 323 // Format date column in sql string given an input format that understands Y M D 324 function SQLDate($fmt, $col=false) 325 { 326 if (!$col) $col = $this->sysTimeStamp; 327 $s = ''; 328 329 $len = strlen($fmt); 330 for ($i=0; $i < $len; $i++) { 331 if ($s) $s .= '+'; 332 $ch = $fmt[$i]; 333 switch($ch) { 334 case 'Y': 335 case 'y': 336 $s .= "datename(yyyy,$col)"; 337 break; 338 case 'M': 339 $s .= "convert(char(3),$col,0)"; 340 break; 341 case 'm': 342 $s .= "replace(str(month($col),2),' ','0')"; 343 break; 344 case 'Q': 345 case 'q': 346 $s .= "datename(quarter,$col)"; 347 break; 348 case 'D': 349 case 'd': 350 $s .= "replace(str(day($col),2),' ','0')"; 351 break; 352 case 'h': 353 $s .= "substring(convert(char(14),$col,0),13,2)"; 354 break; 355 356 case 'H': 357 $s .= "replace(str(datepart(hh,$col),2),' ','0')"; 358 break; 359 360 case 'i': 361 $s .= "replace(str(datepart(mi,$col),2),' ','0')"; 362 break; 363 case 's': 364 $s .= "replace(str(datepart(ss,$col),2),' ','0')"; 365 break; 366 case 'a': 367 case 'A': 368 $s .= "substring(convert(char(19),$col,0),18,2)"; 369 break; 370 371 default: 372 if ($ch == '\\') { 373 $i++; 374 $ch = substr($fmt,$i,1); 375 } 376 $s .= $this->qstr($ch); 377 break; 378 } 379 } 380 return $s; 381 } 382 383 384 function BeginTrans() 385 { 386 if ($this->transOff) return true; 387 $this->transCnt += 1; 388 if ($this->debug) ADOConnection::outp('<hr>begin transaction'); 389 sqlsrv_begin_transaction($this->_connectionID); 390 return true; 391 } 392 393 function CommitTrans($ok=true) 394 { 395 if ($this->transOff) return true; 396 if ($this->debug) ADOConnection::outp('<hr>commit transaction'); 397 if (!$ok) return $this->RollbackTrans(); 398 if ($this->transCnt) $this->transCnt -= 1; 399 sqlsrv_commit($this->_connectionID); 400 return true; 401 } 402 function RollbackTrans() 403 { 404 if ($this->transOff) return true; 405 if ($this->debug) ADOConnection::outp('<hr>rollback transaction'); 406 if ($this->transCnt) $this->transCnt -= 1; 407 sqlsrv_rollback($this->_connectionID); 408 return true; 409 } 410 411 function SetTransactionMode( $transaction_mode ) 412 { 413 $this->_transmode = $transaction_mode; 414 if (empty($transaction_mode)) { 415 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED'); 416 return; 417 } 418 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode; 419 $this->Execute("SET TRANSACTION ".$transaction_mode); 420 } 421 422 /* 423 Usage: 424 425 $this->BeginTrans(); 426 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables 427 428 # some operation on both tables table1 and table2 429 430 $this->CommitTrans(); 431 432 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp 433 */ 434 function RowLock($tables,$where,$col='1 as adodbignore') 435 { 436 if ($col == '1 as adodbignore') $col = 'top 1 null as ignore'; 437 if (!$this->transCnt) $this->BeginTrans(); 438 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where"); 439 } 440 441 function SelectDB($dbName) 442 { 443 $this->database = $dbName; 444 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions 445 if ($this->_connectionID) { 446 $rs = $this->Execute('USE '.$dbName); 447 if($rs) { 448 return true; 449 } else return false; 450 } 451 else return false; 452 } 453 454 function ErrorMsg() 455 { 456 $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL); 457 if($retErrors != null) { 458 foreach($retErrors as $arrError) { 459 $this->_errorMsg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n"; 460 $this->_errorMsg .= "Error Code: ".$arrError[ 'code']."\n"; 461 $this->_errorMsg .= "Message: ".$arrError[ 'message']."\n"; 462 } 463 } else { 464 $this->_errorMsg = "No errors found"; 465 } 466 return $this->_errorMsg; 467 } 468 469 function ErrorNo() 470 { 471 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode; 472 $err = sqlsrv_errors(SQLSRV_ERR_ALL); 473 if($err[0]) return $err[0]['code']; 474 else return -1; 475 } 476 477 // returns true or false 478 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) 479 { 480 if (!function_exists('sqlsrv_connect')) return null; 481 $connectionInfo = $this->connectionInfo; 482 $connectionInfo["Database"]=$argDatabasename; 483 $connectionInfo["UID"]=$argUsername; 484 $connectionInfo["PWD"]=$argPassword; 485 486 foreach ($this->connectionParameters as $parameter=>$value) 487 $connectionInfo[$parameter] = $value; 488 489 if ($this->debug) ADOConnection::outp("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true)); 490 //if ($this->debug) ADOConnection::outp("<hr>_connectionID before: ".serialize($this->_connectionID)); 491 if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) { 492 if ($this->debug) ADOConnection::outp( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true)); 493 return false; 494 } 495 //if ($this->debug) ADOConnection::outp(" _connectionID after: ".serialize($this->_connectionID)); 496 //if ($this->debug) ADOConnection::outp("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>"); 497 return true; 498 } 499 500 // returns true or false 501 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 502 { 503 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!) 504 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename); 505 } 506 507 function Prepare($sql) 508 { 509 return $sql; // prepare does not work properly with bind parameters as bind parameters are managed by sqlsrv_prepare! 510 511 $stmt = sqlsrv_prepare( $this->_connectionID, $sql); 512 if (!$stmt) return $sql; 513 return array($sql,$stmt); 514 } 515 516 // returns concatenated string 517 // MSSQL requires integers to be cast as strings 518 // automatically cast every datatype to VARCHAR(255) 519 // @author David Rogers (introspectshun) 520 function Concat() 521 { 522 $s = ""; 523 $arr = func_get_args(); 524 525 // Split single record on commas, if possible 526 if (sizeof($arr) == 1) { 527 foreach ($arr as $arg) { 528 $args = explode(',', $arg); 529 } 530 $arr = $args; 531 } 532 533 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";')); 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(?:(?:(?:\'\')*\'[^\']+\'(?:\'\')*)|[^;\'])*;?$/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 => 'time', 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($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
| Generated: Thu Aug 11 10:00:09 2016 | Cross-referenced by PHPXref 0.7.1 |