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-mssql.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 To configure for Unix, see 15 http://phpbuilder.com/columns/alberto20000919.php3 16 17 */ 18 19 20 // security - hide paths 21 if (!defined('ADODB_DIR')) die(); 22 23 //---------------------------------------------------------------- 24 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002 25 // and this causes tons of problems because localized versions of 26 // MSSQL will return the dates in dmy or mdy order; and also the 27 // month strings depends on what language has been configured. The 28 // following two variables allow you to control the localization 29 // settings - Ugh. 30 // 31 // MORE LOCALIZATION INFO 32 // ---------------------- 33 // To configure datetime, look for and modify sqlcommn.loc, 34 // typically found in c:\mssql\install 35 // Also read : 36 // http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918 37 // Alternatively use: 38 // CONVERT(char(12),datecol,120) 39 //---------------------------------------------------------------- 40 41 42 ini_set('mssql.datetimeconvert',0); 43 44 class ADODB_mssql extends ADOConnection { 45 var $databaseType = "mssql"; 46 var $dataProvider = "mssql"; 47 var $replaceQuote = "''"; // string to use to replace quotes 48 var $fmtDate = "'Y-m-d'"; 49 var $fmtTimeStamp = "'Y-m-d\TH:i:s'"; 50 var $hasInsertID = true; 51 var $substr = "substring"; 52 var $length = 'len'; 53 var $hasAffectedRows = true; 54 var $metaDatabasesSQL = "select name from sysdatabases where name <> 'master'"; 55 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'))"; 56 var $metaColumnsSQL = # xtype==61 is datetime 57 "select c.name,t.name,c.length,c.isnullable, c.status, 58 (case when c.xusertype=61 then 0 else c.xprec end), 59 (case when c.xusertype=61 then 0 else c.xscale end) 60 from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'"; 61 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE 62 var $hasGenID = true; 63 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)'; 64 var $sysTimeStamp = 'GetDate()'; 65 var $maxParameterLen = 4000; 66 var $arrayClass = 'ADORecordSet_array_mssql'; 67 var $uniqueSort = true; 68 var $leftOuter = '*='; 69 var $rightOuter = '=*'; 70 var $ansiOuter = true; // for mssql7 or later 71 var $poorAffectedRows = true; 72 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000 73 var $uniqueOrderBy = true; 74 var $_bindInputArray = true; 75 var $forceNewConnect = false; 76 77 function ServerInfo() 78 { 79 global $ADODB_FETCH_MODE; 80 81 82 if ($this->fetchMode === false) { 83 $savem = $ADODB_FETCH_MODE; 84 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 85 } else 86 $savem = $this->SetFetchMode(ADODB_FETCH_NUM); 87 88 if (0) { 89 $stmt = $this->PrepareSP('sp_server_info'); 90 $val = 2; 91 $this->Parameter($stmt,$val,'attribute_id'); 92 $row = $this->GetRow($stmt); 93 } 94 95 $row = $this->GetRow("execute sp_server_info 2"); 96 97 98 if ($this->fetchMode === false) { 99 $ADODB_FETCH_MODE = $savem; 100 } else 101 $this->SetFetchMode($savem); 102 103 $arr['description'] = $row[2]; 104 $arr['version'] = ADOConnection::_findvers($arr['description']); 105 return $arr; 106 } 107 108 function IfNull( $field, $ifNull ) 109 { 110 return " ISNULL($field, $ifNull) "; // if MS SQL Server 111 } 112 113 function _insertid() 114 { 115 // SCOPE_IDENTITY() 116 // Returns the last IDENTITY value inserted into an IDENTITY column in 117 // the same scope. A scope is a module -- a stored procedure, trigger, 118 // function, or batch. Thus, two statements are in the same scope if 119 // they are in the same stored procedure, function, or batch. 120 if ($this->lastInsID !== false) { 121 return $this->lastInsID; // InsID from sp_executesql call 122 } else { 123 return $this->GetOne($this->identitySQL); 124 } 125 } 126 127 128 129 /** 130 * Correctly quotes a string so that all strings are escaped. 131 * We prefix and append to the string single-quotes. 132 * An example is $db->qstr("Don't bother"); 133 * 134 * @param string $s The string to quote 135 * @param bool $magic_quotes This param is not used since 5.21.0. 136 * It remains for backwards compatibility. 137 * 138 * @return string Quoted string to be sent back to database 139 * 140 * @noinspection PhpUnusedParameterInspection 141 */ 142 function qStr($s, $magic_quotes=false) 143 { 144 return "'" . str_replace("'", $this->replaceQuote, $s) . "'"; 145 } 146 147 function _affectedrows() 148 { 149 return $this->GetOne('select @@rowcount'); 150 } 151 152 var $_dropSeqSQL = "drop table %s"; 153 154 function CreateSequence($seq='adodbseq',$start=1) 155 { 156 157 $this->Execute('BEGIN TRANSACTION adodbseq'); 158 $start -= 1; 159 $this->Execute("create table $seq (id float(53))"); 160 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 161 if (!$ok) { 162 $this->Execute('ROLLBACK TRANSACTION adodbseq'); 163 return false; 164 } 165 $this->Execute('COMMIT TRANSACTION adodbseq'); 166 return true; 167 } 168 169 function GenID($seq='adodbseq',$start=1) 170 { 171 //$this->debug=1; 172 $this->Execute('BEGIN TRANSACTION adodbseq'); 173 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1"); 174 if (!$ok) { 175 $this->Execute("create table $seq (id float(53))"); 176 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 177 if (!$ok) { 178 $this->Execute('ROLLBACK TRANSACTION adodbseq'); 179 return false; 180 } 181 $this->Execute('COMMIT TRANSACTION adodbseq'); 182 return $start; 183 } 184 $num = $this->GetOne("select id from $seq"); 185 $this->Execute('COMMIT TRANSACTION adodbseq'); 186 return $num; 187 188 // in old implementation, pre 1.90, we returned GUID... 189 //return $this->GetOne("SELECT CONVERT(varchar(255), NEWID()) AS 'Char'"); 190 } 191 192 193 function SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0) 194 { 195 $nrows = (int) $nrows; 196 $offset = (int) $offset; 197 if ($nrows > 0 && $offset <= 0) { 198 $sql = preg_replace( 199 '/(^\s*select\s+(distinctrow|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql); 200 201 if ($secs2cache) 202 $rs = $this->CacheExecute($secs2cache, $sql, $inputarr); 203 else 204 $rs = $this->Execute($sql,$inputarr); 205 } else 206 $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache); 207 208 return $rs; 209 } 210 211 212 // Format date column in sql string given an input format that understands Y M D 213 function SQLDate($fmt, $col=false) 214 { 215 if (!$col) $col = $this->sysTimeStamp; 216 $s = ''; 217 218 $len = strlen($fmt); 219 for ($i=0; $i < $len; $i++) { 220 if ($s) $s .= '+'; 221 $ch = $fmt[$i]; 222 switch($ch) { 223 case 'Y': 224 case 'y': 225 $s .= "datename(yyyy,$col)"; 226 break; 227 case 'M': 228 $s .= "convert(char(3),$col,0)"; 229 break; 230 case 'm': 231 $s .= "replace(str(month($col),2),' ','0')"; 232 break; 233 case 'Q': 234 case 'q': 235 $s .= "datename(quarter,$col)"; 236 break; 237 case 'D': 238 case 'd': 239 $s .= "replace(str(day($col),2),' ','0')"; 240 break; 241 case 'h': 242 $s .= "substring(convert(char(14),$col,0),13,2)"; 243 break; 244 245 case 'H': 246 $s .= "replace(str(datepart(hh,$col),2),' ','0')"; 247 break; 248 249 case 'i': 250 $s .= "replace(str(datepart(mi,$col),2),' ','0')"; 251 break; 252 case 's': 253 $s .= "replace(str(datepart(ss,$col),2),' ','0')"; 254 break; 255 case 'a': 256 case 'A': 257 $s .= "substring(convert(char(19),$col,0),18,2)"; 258 break; 259 case 'l': 260 $s .= "datename(dw,$col)"; 261 break; 262 default: 263 if ($ch == '\\') { 264 $i++; 265 $ch = substr($fmt,$i,1); 266 } 267 $s .= $this->qstr($ch); 268 break; 269 } 270 } 271 return $s; 272 } 273 274 275 function BeginTrans() 276 { 277 if ($this->transOff) return true; 278 $this->transCnt += 1; 279 $ok = $this->Execute('BEGIN TRAN'); 280 return $ok; 281 } 282 283 function CommitTrans($ok=true) 284 { 285 if ($this->transOff) return true; 286 if (!$ok) return $this->RollbackTrans(); 287 if ($this->transCnt) $this->transCnt -= 1; 288 $ok = $this->Execute('COMMIT TRAN'); 289 return $ok; 290 } 291 function RollbackTrans() 292 { 293 if ($this->transOff) return true; 294 if ($this->transCnt) $this->transCnt -= 1; 295 $ok = $this->Execute('ROLLBACK TRAN'); 296 return $ok; 297 } 298 299 function SetTransactionMode( $transaction_mode ) 300 { 301 $this->_transmode = $transaction_mode; 302 if (empty($transaction_mode)) { 303 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED'); 304 return; 305 } 306 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode; 307 $this->Execute("SET TRANSACTION ".$transaction_mode); 308 } 309 310 /* 311 Usage: 312 313 $this->BeginTrans(); 314 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables 315 316 # some operation on both tables table1 and table2 317 318 $this->CommitTrans(); 319 320 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp 321 */ 322 function RowLock($tables,$where,$col='1 as adodbignore') 323 { 324 if ($col == '1 as adodbignore') $col = 'top 1 null as ignore'; 325 if (!$this->transCnt) $this->BeginTrans(); 326 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where"); 327 } 328 329 330 function MetaColumns($table, $normalize=true) 331 { 332 // $arr = ADOConnection::MetaColumns($table); 333 // return $arr; 334 335 $this->_findschema($table,$schema); 336 if ($schema) { 337 $dbName = $this->database; 338 $this->SelectDB($schema); 339 } 340 global $ADODB_FETCH_MODE; 341 $save = $ADODB_FETCH_MODE; 342 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 343 344 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false); 345 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table)); 346 347 if ($schema) { 348 $this->SelectDB($dbName); 349 } 350 351 if (isset($savem)) $this->SetFetchMode($savem); 352 $ADODB_FETCH_MODE = $save; 353 if (!is_object($rs)) { 354 $false = false; 355 return $false; 356 } 357 358 $retarr = array(); 359 while (!$rs->EOF){ 360 $fld = new ADOFieldObject(); 361 $fld->name = $rs->fields[0]; 362 $fld->type = $rs->fields[1]; 363 364 $fld->not_null = (!$rs->fields[3]); 365 $fld->auto_increment = ($rs->fields[4] == 128); // sys.syscolumns status field. 0x80 = 128 ref: http://msdn.microsoft.com/en-us/library/ms186816.aspx 366 367 if (isset($rs->fields[5]) && $rs->fields[5]) { 368 if ($rs->fields[5]>0) $fld->max_length = $rs->fields[5]; 369 $fld->scale = $rs->fields[6]; 370 if ($fld->scale>0) $fld->max_length += 1; 371 } else 372 $fld->max_length = $rs->fields[2]; 373 374 if ($save == ADODB_FETCH_NUM) { 375 $retarr[] = $fld; 376 } else { 377 $retarr[strtoupper($fld->name)] = $fld; 378 } 379 $rs->MoveNext(); 380 } 381 382 $rs->Close(); 383 return $retarr; 384 385 } 386 387 388 function MetaIndexes($table,$primary=false, $owner=false) 389 { 390 $table = $this->qstr($table); 391 392 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno, 393 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, 394 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique 395 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id 396 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid 397 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid 398 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table 399 ORDER BY O.name, I.Name, K.keyno"; 400 401 global $ADODB_FETCH_MODE; 402 $save = $ADODB_FETCH_MODE; 403 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 404 if ($this->fetchMode !== FALSE) { 405 $savem = $this->SetFetchMode(FALSE); 406 } 407 408 $rs = $this->Execute($sql); 409 if (isset($savem)) { 410 $this->SetFetchMode($savem); 411 } 412 $ADODB_FETCH_MODE = $save; 413 414 if (!is_object($rs)) { 415 return FALSE; 416 } 417 418 $indexes = array(); 419 while ($row = $rs->FetchRow()) { 420 if ($primary && !$row[5]) continue; 421 422 $indexes[$row[0]]['unique'] = $row[6]; 423 $indexes[$row[0]]['columns'][] = $row[1]; 424 } 425 return $indexes; 426 } 427 428 function MetaForeignKeys($table, $owner=false, $upper=false) 429 { 430 global $ADODB_FETCH_MODE; 431 432 $save = $ADODB_FETCH_MODE; 433 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 434 $table = $this->qstr(strtoupper($table)); 435 436 $sql = 437 "select object_name(constid) as constraint_name, 438 col_name(fkeyid, fkey) as column_name, 439 object_name(rkeyid) as referenced_table_name, 440 col_name(rkeyid, rkey) as referenced_column_name 441 from sysforeignkeys 442 where upper(object_name(fkeyid)) = $table 443 order by constraint_name, referenced_table_name, keyno"; 444 445 $constraints = $this->GetArray($sql); 446 447 $ADODB_FETCH_MODE = $save; 448 449 $arr = false; 450 foreach($constraints as $constr) { 451 //print_r($constr); 452 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3]; 453 } 454 if (!$arr) return false; 455 456 $arr2 = false; 457 458 foreach($arr as $k => $v) { 459 foreach($v as $a => $b) { 460 if ($upper) $a = strtoupper($a); 461 if (is_array($arr2[$a])) { // a previous foreign key was define for this reference table, we merge the new one 462 $arr2[$a] = array_merge($arr2[$a], $b); 463 } else { 464 $arr2[$a] = $b; 465 } 466 } 467 } 468 return $arr2; 469 } 470 471 //From: Fernando Moreira <FMoreira@imediata.pt> 472 function MetaDatabases() 473 { 474 if(@mssql_select_db("master")) { 475 $qry = $this->metaDatabasesSQL; 476 if($rs = @mssql_query($qry,$this->_connectionID)) { 477 $tmpAr = $ar = array(); 478 while($tmpAr = @mssql_fetch_row($rs)) { 479 $ar[]=$tmpAr[0]; 480 } 481 @mssql_select_db($this->database); 482 if(sizeof($ar)) { 483 return($ar); 484 } else { 485 return(false); 486 } 487 } else { 488 @mssql_select_db($this->database); 489 return(false); 490 } 491 } 492 return(false); 493 } 494 495 // "Stein-Aksel Basma" <basma@accelero.no> 496 // tested with MSSQL 2000 497 function MetaPrimaryKeys($table, $owner=false) 498 { 499 global $ADODB_FETCH_MODE; 500 501 $schema = ''; 502 $this->_findschema($table,$schema); 503 if (!$schema) $schema = $this->database; 504 if ($schema) $schema = "and k.table_catalog like '$schema%'"; 505 506 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k, 507 information_schema.table_constraints tc 508 where tc.constraint_name = k.constraint_name and tc.constraint_type = 509 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position "; 510 511 $savem = $ADODB_FETCH_MODE; 512 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 513 $a = $this->GetCol($sql); 514 $ADODB_FETCH_MODE = $savem; 515 516 if ($a && sizeof($a)>0) return $a; 517 $false = false; 518 return $false; 519 } 520 521 522 function MetaTables($ttype=false,$showSchema=false,$mask=false) 523 { 524 if ($mask) { 525 $save = $this->metaTablesSQL; 526 $mask = $this->qstr(($mask)); 527 $this->metaTablesSQL .= " AND name like $mask"; 528 } 529 $ret = ADOConnection::MetaTables($ttype,$showSchema); 530 531 if ($mask) { 532 $this->metaTablesSQL = $save; 533 } 534 return $ret; 535 } 536 537 function SelectDB($dbName) 538 { 539 $this->database = $dbName; 540 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions 541 if ($this->_connectionID) { 542 return @mssql_select_db($dbName); 543 } 544 else return false; 545 } 546 547 function ErrorMsg() 548 { 549 if (empty($this->_errorMsg)){ 550 $this->_errorMsg = mssql_get_last_message(); 551 } 552 return $this->_errorMsg; 553 } 554 555 function ErrorNo() 556 { 557 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode; 558 if (empty($this->_errorMsg)) { 559 $this->_errorMsg = mssql_get_last_message(); 560 } 561 $id = @mssql_query("select @@ERROR",$this->_connectionID); 562 if (!$id) return false; 563 $arr = mssql_fetch_array($id); 564 @mssql_free_result($id); 565 if (is_array($arr)) { 566 return $arr[0]; 567 } else { 568 return -1; 569 } 570 } 571 572 // returns true or false, newconnect supported since php 5.1.0. 573 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename,$newconnect=false) 574 { 575 if (!function_exists('mssql_pconnect')) return null; 576 if (!empty($this->port)) $argHostname .= ":".$this->port; 577 $this->_connectionID = mssql_connect($argHostname,$argUsername,$argPassword,$newconnect); 578 if ($this->_connectionID === false) return false; 579 if ($argDatabasename) return $this->SelectDB($argDatabasename); 580 return true; 581 } 582 583 584 // returns true or false 585 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 586 { 587 if (!function_exists('mssql_pconnect')) return null; 588 if (!empty($this->port)) $argHostname .= ":".$this->port; 589 $this->_connectionID = mssql_pconnect($argHostname,$argUsername,$argPassword); 590 if ($this->_connectionID === false) return false; 591 592 // persistent connections can forget to rollback on crash, so we do it here. 593 if ($this->autoRollback) { 594 $cnt = $this->GetOne('select @@TRANCOUNT'); 595 while (--$cnt >= 0) $this->Execute('ROLLBACK TRAN'); 596 } 597 if ($argDatabasename) return $this->SelectDB($argDatabasename); 598 return true; 599 } 600 601 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 602 { 603 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename, true); 604 } 605 606 function Prepare($sql) 607 { 608 $sqlarr = explode('?',$sql); 609 if (sizeof($sqlarr) <= 1) return $sql; 610 $sql2 = $sqlarr[0]; 611 for ($i = 1, $max = sizeof($sqlarr); $i < $max; $i++) { 612 $sql2 .= '@P'.($i-1) . $sqlarr[$i]; 613 } 614 return array($sql,$this->qstr($sql2),$max,$sql2); 615 } 616 617 function PrepareSP($sql,$param=true) 618 { 619 $stmt = mssql_init($sql,$this->_connectionID); 620 if (!$stmt) return $sql; 621 return array($sql,$stmt); 622 } 623 624 // returns concatenated string 625 // MSSQL requires integers to be cast as strings 626 // automatically cast every datatype to VARCHAR(255) 627 // @author David Rogers (introspectshun) 628 function Concat() 629 { 630 $s = ""; 631 $arr = func_get_args(); 632 633 // Split single record on commas, if possible 634 if (sizeof($arr) == 1) { 635 foreach ($arr as $arg) { 636 $args = explode(',', $arg); 637 } 638 $arr = $args; 639 } 640 641 array_walk( 642 $arr, 643 function(&$value, $key) { 644 $value = "CAST(" . $value . " AS VARCHAR(255))"; 645 } 646 ); 647 $s = implode('+',$arr); 648 if (sizeof($arr) > 0) return "$s"; 649 650 return ''; 651 } 652 653 /* 654 Usage: 655 $stmt = $db->PrepareSP('SP_RUNSOMETHING'); -- takes 2 params, @myid and @group 656 657 # note that the parameter does not have @ in front! 658 $db->Parameter($stmt,$id,'myid'); 659 $db->Parameter($stmt,$group,'group',false,64); 660 $db->Execute($stmt); 661 662 @param $stmt Statement returned by Prepare() or PrepareSP(). 663 @param $var PHP variable to bind to. Can set to null (for isNull support). 664 @param $name Name of stored procedure variable name to bind to. 665 @param [$isOutput] Indicates direction of parameter 0/false=IN 1=OUT 2= IN/OUT. This is ignored in oci8. 666 @param [$maxLen] Holds an maximum length of the variable. 667 @param [$type] The data type of $var. Legal values depend on driver. 668 669 See mssql_bind documentation at php.net. 670 */ 671 function Parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false) 672 { 673 $isNull = is_null($var); // php 4.0.4 and above... 674 675 if ($type === false) 676 switch(gettype($var)) { 677 default: 678 case 'string': $type = SQLVARCHAR; break; 679 case 'double': $type = SQLFLT8; break; 680 case 'integer': $type = SQLINT4; break; 681 case 'boolean': $type = SQLINT1; break; # SQLBIT not supported in 4.1.0 682 } 683 684 if ($this->debug) { 685 $prefix = ($isOutput) ? 'Out' : 'In'; 686 $ztype = (empty($type)) ? 'false' : $type; 687 ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);"); 688 } 689 /* 690 See PHPLens Issue No: 7231 691 692 RETVAL is HARD CODED into php_mssql extension: 693 The return value (a long integer value) is treated like a special OUTPUT parameter, 694 called "RETVAL" (without the @). See the example at mssql_execute to 695 see how it works. - type: one of this new supported PHP constants. 696 SQLTEXT, SQLVARCHAR,SQLCHAR, SQLINT1,SQLINT2, SQLINT4, SQLBIT,SQLFLT8 697 */ 698 if ($name !== 'RETVAL') $name = '@'.$name; 699 return mssql_bind($stmt[1], $name, $var, $type, $isOutput, $isNull, $maxLen); 700 } 701 702 /* 703 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars 704 So all your blobs must be of type "image". 705 706 Remember to set in php.ini the following... 707 708 ; Valid range 0 - 2147483647. Default = 4096. 709 mssql.textlimit = 0 ; zero to pass through 710 711 ; Valid range 0 - 2147483647. Default = 4096. 712 mssql.textsize = 0 ; zero to pass through 713 */ 714 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 715 { 716 717 if (strtoupper($blobtype) == 'CLOB') { 718 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where"; 719 return $this->Execute($sql) != false; 720 } 721 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where"; 722 return $this->Execute($sql) != false; 723 } 724 725 // returns query ID if successful, otherwise false 726 function _query($sql,$inputarr=false) 727 { 728 $this->_errorMsg = false; 729 if (is_array($inputarr)) { 730 731 # bind input params with sp_executesql: 732 # see http://www.quest-pipelines.com/newsletter-v3/0402_F.htm 733 # works only with sql server 7 and newer 734 $getIdentity = false; 735 if (!is_array($sql) && preg_match('/^\\s*insert/i', $sql)) { 736 $getIdentity = true; 737 $sql .= (preg_match('/;\\s*$/i', $sql) ? ' ' : '; ') . $this->identitySQL; 738 } 739 if (!is_array($sql)) $sql = $this->Prepare($sql); 740 $params = ''; 741 $decl = ''; 742 $i = 0; 743 foreach($inputarr as $v) { 744 if ($decl) { 745 $decl .= ', '; 746 $params .= ', '; 747 } 748 if (is_string($v)) { 749 $len = strlen($v); 750 if ($len == 0) $len = 1; 751 752 if ($len > 4000 ) { 753 // NVARCHAR is max 4000 chars. Let's use NTEXT 754 $decl .= "@P$i NTEXT"; 755 } else { 756 $decl .= "@P$i NVARCHAR($len)"; 757 } 758 759 if(substr($v,0,1) == "'" && substr($v,-1,1) == "'") 760 /* 761 * String is already fully quoted 762 */ 763 $inputVar = $v; 764 else 765 $inputVar = $db->this($v); 766 767 $params .= "@P$i=N" . $inputVar; 768 769 } else if (is_integer($v)) { 770 $decl .= "@P$i INT"; 771 $params .= "@P$i=".$v; 772 } else if (is_float($v)) { 773 $decl .= "@P$i FLOAT"; 774 $params .= "@P$i=".$v; 775 } else if (is_bool($v)) { 776 $decl .= "@P$i INT"; # Used INT just in case BIT in not supported on the user's MSSQL version. It will cast appropriately. 777 $params .= "@P$i=".(($v)?'1':'0'); # True == 1 in MSSQL BIT fields and acceptable for storing logical true in an int field 778 } else { 779 $decl .= "@P$i CHAR"; # Used char because a type is required even when the value is to be NULL. 780 $params .= "@P$i=NULL"; 781 } 782 $i += 1; 783 } 784 $decl = $this->qstr($decl); 785 if ($this->debug) ADOConnection::outp("<font size=-1>sp_executesql N{$sql[1]},N$decl,$params</font>"); 786 $rez = mssql_query("sp_executesql N{$sql[1]},N$decl,$params", $this->_connectionID); 787 if ($getIdentity) { 788 $arr = @mssql_fetch_row($rez); 789 $this->lastInsID = isset($arr[0]) ? $arr[0] : false; 790 @mssql_data_seek($rez, 0); 791 } 792 793 } else if (is_array($sql)) { 794 # PrepareSP() 795 $rez = mssql_execute($sql[1]); 796 $this->lastInsID = false; 797 798 } else { 799 $rez = mssql_query($sql,$this->_connectionID); 800 $this->lastInsID = false; 801 } 802 return $rez; 803 } 804 805 // returns true or false 806 function _close() 807 { 808 if ($this->transCnt) { 809 $this->RollbackTrans(); 810 } 811 if($this->_connectionID) { 812 $rez = mssql_close($this->_connectionID); 813 } 814 $this->_connectionID = false; 815 return $rez; 816 } 817 818 819 820 /** 821 * Returns a substring of a varchar type field 822 * 823 * The SQL server version varies because the length is mandatory, so 824 * we append a reasonable string length 825 * 826 * @param string $fld The field to sub-string 827 * @param int $start The start point 828 * @param int $length An optional length 829 * 830 * @return The SQL text 831 */ 832 function substr($fld,$start,$length=0) 833 { 834 if ($length == 0) 835 /* 836 * The length available to varchar is 2GB, but that makes no 837 * sense in a substring, so I'm going to arbitrarily limit 838 * the length to 1K, but you could change it if you want 839 */ 840 $length = 1024; 841 842 $text = "SUBSTRING($fld,$start,$length)"; 843 return $text; 844 } 845 } 846 847 /*-------------------------------------------------------------------------------------- 848 Class Name: Recordset 849 --------------------------------------------------------------------------------------*/ 850 851 class ADORecordset_mssql extends ADORecordSet { 852 853 var $databaseType = "mssql"; 854 var $canSeek = true; 855 var $hasFetchAssoc; // see PHPLens Issue No: 6083 856 // _mths works only in non-localised system 857 858 function __construct($id,$mode=false) 859 { 860 // freedts check... 861 $this->hasFetchAssoc = function_exists('mssql_fetch_assoc'); 862 863 if ($mode === false) { 864 global $ADODB_FETCH_MODE; 865 $mode = $ADODB_FETCH_MODE; 866 867 } 868 $this->fetchMode = $mode; 869 return parent::__construct($id); 870 } 871 872 873 function _initrs() 874 { 875 GLOBAL $ADODB_COUNTRECS; 876 $this->_numOfRows = ($ADODB_COUNTRECS)? @mssql_num_rows($this->_queryID):-1; 877 $this->_numOfFields = @mssql_num_fields($this->_queryID); 878 } 879 880 881 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se> 882 // get next resultset - requires PHP 4.0.5 or later 883 function NextRecordSet() 884 { 885 if (!mssql_next_result($this->_queryID)) return false; 886 $this->_inited = false; 887 $this->bind = false; 888 $this->_currentRow = -1; 889 $this->Init(); 890 return true; 891 } 892 893 /* Use associative array to get fields array */ 894 function Fields($colname) 895 { 896 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname]; 897 if (!$this->bind) { 898 $this->bind = array(); 899 for ($i=0; $i < $this->_numOfFields; $i++) { 900 $o = $this->FetchField($i); 901 $this->bind[strtoupper($o->name)] = $i; 902 } 903 } 904 905 return $this->fields[$this->bind[strtoupper($colname)]]; 906 } 907 908 /* Returns: an object containing field information. 909 Get column information in the Recordset object. fetchField() can be used in order to obtain information about 910 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by 911 fetchField() is retrieved. */ 912 913 function FetchField($fieldOffset = -1) 914 { 915 if ($fieldOffset != -1) { 916 $f = @mssql_fetch_field($this->_queryID, $fieldOffset); 917 } 918 else if ($fieldOffset == -1) { /* The $fieldOffset argument is not provided thus its -1 */ 919 $f = @mssql_fetch_field($this->_queryID); 920 } 921 $false = false; 922 if (empty($f)) return $false; 923 return $f; 924 } 925 926 function _seek($row) 927 { 928 return @mssql_data_seek($this->_queryID, $row); 929 } 930 931 // speedup 932 function MoveNext() 933 { 934 if ($this->EOF) return false; 935 936 $this->_currentRow++; 937 938 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 939 if ($this->fetchMode & ADODB_FETCH_NUM) { 940 //ADODB_FETCH_BOTH mode 941 $this->fields = @mssql_fetch_array($this->_queryID); 942 } 943 else { 944 if ($this->hasFetchAssoc) {// only for PHP 4.2.0 or later 945 $this->fields = @mssql_fetch_assoc($this->_queryID); 946 } else { 947 $flds = @mssql_fetch_array($this->_queryID); 948 if (is_array($flds)) { 949 $fassoc = array(); 950 foreach($flds as $k => $v) { 951 if (is_numeric($k)) continue; 952 $fassoc[$k] = $v; 953 } 954 $this->fields = $fassoc; 955 } else 956 $this->fields = false; 957 } 958 } 959 960 if (is_array($this->fields)) { 961 if (ADODB_ASSOC_CASE == 0) { 962 foreach($this->fields as $k=>$v) { 963 $kn = strtolower($k); 964 if ($kn <> $k) { 965 unset($this->fields[$k]); 966 $this->fields[$kn] = $v; 967 } 968 } 969 } else if (ADODB_ASSOC_CASE == 1) { 970 foreach($this->fields as $k=>$v) { 971 $kn = strtoupper($k); 972 if ($kn <> $k) { 973 unset($this->fields[$k]); 974 $this->fields[$kn] = $v; 975 } 976 } 977 } 978 } 979 } else { 980 $this->fields = @mssql_fetch_row($this->_queryID); 981 } 982 if ($this->fields) return true; 983 $this->EOF = true; 984 985 return false; 986 } 987 988 989 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4 990 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot! 991 function _fetch($ignore_fields=false) 992 { 993 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 994 if ($this->fetchMode & ADODB_FETCH_NUM) { 995 //ADODB_FETCH_BOTH mode 996 $this->fields = @mssql_fetch_array($this->_queryID); 997 } else { 998 if ($this->hasFetchAssoc) // only for PHP 4.2.0 or later 999 $this->fields = @mssql_fetch_assoc($this->_queryID); 1000 else { 1001 $this->fields = @mssql_fetch_array($this->_queryID); 1002 if (@is_array($this->fields)) { 1003 $fassoc = array(); 1004 foreach($this->fields as $k => $v) { 1005 if (is_integer($k)) continue; 1006 $fassoc[$k] = $v; 1007 } 1008 $this->fields = $fassoc; 1009 } 1010 } 1011 } 1012 1013 if (!$this->fields) { 1014 } else if (ADODB_ASSOC_CASE == 0) { 1015 foreach($this->fields as $k=>$v) { 1016 $kn = strtolower($k); 1017 if ($kn <> $k) { 1018 unset($this->fields[$k]); 1019 $this->fields[$kn] = $v; 1020 } 1021 } 1022 } else if (ADODB_ASSOC_CASE == 1) { 1023 foreach($this->fields as $k=>$v) { 1024 $kn = strtoupper($k); 1025 if ($kn <> $k) { 1026 unset($this->fields[$k]); 1027 $this->fields[$kn] = $v; 1028 } 1029 } 1030 } 1031 } else { 1032 $this->fields = @mssql_fetch_row($this->_queryID); 1033 } 1034 return $this->fields; 1035 } 1036 1037 /* close() only needs to be called if you are worried about using too much memory while your script 1038 is running. All associated result memory for the specified result identifier will automatically be freed. */ 1039 1040 function _close() 1041 { 1042 if($this->_queryID) { 1043 $rez = mssql_free_result($this->_queryID); 1044 $this->_queryID = false; 1045 return $rez; 1046 } 1047 return true; 1048 } 1049 1050 /** 1051 * Returns the maximum size of a MetaType C field. Because of the 1052 * database design, SQL Server places no limits on the size of data inserted 1053 * Although the actual limit is 2^31-1 bytes. 1054 * 1055 * @return int 1056 */ 1057 function charMax() 1058 { 1059 return ADODB_STRINGMAX_NOLIMIT; 1060 } 1061 1062 /** 1063 * Returns the maximum size of a MetaType X field. Because of the 1064 * database design, SQL Server places no limits on the size of data inserted 1065 * Although the actual limit is 2^31-1 bytes. 1066 * 1067 * @return int 1068 */ 1069 function textMax() 1070 { 1071 return ADODB_STRINGMAX_NOLIMIT; 1072 } 1073 1074 } 1075 1076 1077 class ADORecordSet_array_mssql extends ADORecordSet_array {} 1078 1079 /* 1080 Code Example 1: 1081 1082 select object_name(constid) as constraint_name, 1083 object_name(fkeyid) as table_name, 1084 col_name(fkeyid, fkey) as column_name, 1085 object_name(rkeyid) as referenced_table_name, 1086 col_name(rkeyid, rkey) as referenced_column_name 1087 from sysforeignkeys 1088 where object_name(fkeyid) = x 1089 order by constraint_name, table_name, referenced_table_name, keyno 1090 1091 Code Example 2: 1092 select constraint_name, 1093 column_name, 1094 ordinal_position 1095 from information_schema.key_column_usage 1096 where constraint_catalog = db_name() 1097 and table_name = x 1098 order by constraint_name, ordinal_position 1099 1100 http://www.databasejournal.com/scripts/article.php/1440551 1101 */
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
