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