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