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