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