| [ Index ] |
PHP Cross Reference of MantisBT |
[Summary view] [Print] [Text view]
1 <?php 2 /* 3 V5.11 5 May 2010 (c) 2000-2010 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 H: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, 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 MetaIndexes($table,$primary=false, $owner=false) 378 { 379 $table = $this->qstr($table); 380 381 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno, 382 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, 383 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique 384 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id 385 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid 386 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid 387 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table 388 ORDER BY O.name, I.Name, K.keyno"; 389 390 global $ADODB_FETCH_MODE; 391 $save = $ADODB_FETCH_MODE; 392 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 393 if ($this->fetchMode !== FALSE) { 394 $savem = $this->SetFetchMode(FALSE); 395 } 396 397 $rs = $this->Execute($sql); 398 if (isset($savem)) { 399 $this->SetFetchMode($savem); 400 } 401 $ADODB_FETCH_MODE = $save; 402 403 if (!is_object($rs)) { 404 return FALSE; 405 } 406 407 $indexes = array(); 408 while ($row = $rs->FetchRow()) { 409 if ($primary && !$row[5]) continue; 410 411 $indexes[$row[0]]['unique'] = $row[6]; 412 $indexes[$row[0]]['columns'][] = $row[1]; 413 } 414 return $indexes; 415 } 416 417 function MetaForeignKeys($table, $owner=false, $upper=false) 418 { 419 global $ADODB_FETCH_MODE; 420 421 $save = $ADODB_FETCH_MODE; 422 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 423 $table = $this->qstr(strtoupper($table)); 424 425 $sql = 426 "select object_name(constid) as constraint_name, 427 col_name(fkeyid, fkey) as column_name, 428 object_name(rkeyid) as referenced_table_name, 429 col_name(rkeyid, rkey) as referenced_column_name 430 from sysforeignkeys 431 where upper(object_name(fkeyid)) = $table 432 order by constraint_name, referenced_table_name, keyno"; 433 434 $constraints = $this->GetArray($sql); 435 436 $ADODB_FETCH_MODE = $save; 437 438 $arr = false; 439 foreach($constraints as $constr) { 440 //print_r($constr); 441 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3]; 442 } 443 if (!$arr) return false; 444 445 $arr2 = false; 446 447 foreach($arr as $k => $v) { 448 foreach($v as $a => $b) { 449 if ($upper) $a = strtoupper($a); 450 $arr2[$a] = $b; 451 } 452 } 453 return $arr2; 454 } 455 456 //From: Fernando Moreira <FMoreira@imediata.pt> 457 function MetaDatabases() 458 { 459 if(@mssql_select_db("master")) { 460 $qry=$this->metaDatabasesSQL; 461 if($rs=@mssql_query($qry,$this->_connectionID)){ 462 $tmpAr=$ar=array(); 463 while($tmpAr=@mssql_fetch_row($rs)) 464 $ar[]=$tmpAr[0]; 465 @mssql_select_db($this->database); 466 if(sizeof($ar)) 467 return($ar); 468 else 469 return(false); 470 } else { 471 @mssql_select_db($this->database); 472 return(false); 473 } 474 } 475 return(false); 476 } 477 478 // "Stein-Aksel Basma" <basma@accelero.no> 479 // tested with MSSQL 2000 480 function MetaPrimaryKeys($table, $owner=false) 481 { 482 global $ADODB_FETCH_MODE; 483 484 $schema = ''; 485 $this->_findschema($table,$schema); 486 if (!$schema) $schema = $this->database; 487 if ($schema) $schema = "and k.table_catalog like '$schema%'"; 488 489 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k, 490 information_schema.table_constraints tc 491 where tc.constraint_name = k.constraint_name and tc.constraint_type = 492 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position "; 493 494 $savem = $ADODB_FETCH_MODE; 495 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 496 $a = $this->GetCol($sql); 497 $ADODB_FETCH_MODE = $savem; 498 499 if ($a && sizeof($a)>0) return $a; 500 $false = false; 501 return $false; 502 } 503 504 505 function MetaTables($ttype=false,$showSchema=false,$mask=false) 506 { 507 if ($mask) { 508 $save = $this->metaTablesSQL; 509 $mask = $this->qstr(($mask)); 510 $this->metaTablesSQL .= " AND name like $mask"; 511 } 512 $ret = ADOConnection::MetaTables($ttype,$showSchema); 513 514 if ($mask) { 515 $this->metaTablesSQL = $save; 516 } 517 return $ret; 518 } 519 520 function SelectDB($dbName) 521 { 522 $this->database = $dbName; 523 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions 524 if ($this->_connectionID) { 525 return @mssql_select_db($dbName); 526 } 527 else return false; 528 } 529 530 function ErrorMsg() 531 { 532 if (empty($this->_errorMsg)){ 533 $this->_errorMsg = mssql_get_last_message(); 534 } 535 return $this->_errorMsg; 536 } 537 538 function ErrorNo() 539 { 540 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode; 541 if (empty($this->_errorMsg)) { 542 $this->_errorMsg = mssql_get_last_message(); 543 } 544 $id = @mssql_query("select @@ERROR",$this->_connectionID); 545 if (!$id) return false; 546 $arr = mssql_fetch_array($id); 547 @mssql_free_result($id); 548 if (is_array($arr)) return $arr[0]; 549 else return -1; 550 } 551 552 // returns true or false, newconnect supported since php 5.1.0. 553 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename,$newconnect=false) 554 { 555 if (!function_exists('mssql_pconnect')) return null; 556 $this->_connectionID = mssql_connect($argHostname,$argUsername,$argPassword,$newconnect); 557 if ($this->_connectionID === false) return false; 558 if ($argDatabasename) return $this->SelectDB($argDatabasename); 559 return true; 560 } 561 562 563 // returns true or false 564 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 565 { 566 if (!function_exists('mssql_pconnect')) return null; 567 $this->_connectionID = mssql_pconnect($argHostname,$argUsername,$argPassword); 568 if ($this->_connectionID === false) return false; 569 570 // persistent connections can forget to rollback on crash, so we do it here. 571 if ($this->autoRollback) { 572 $cnt = $this->GetOne('select @@TRANCOUNT'); 573 while (--$cnt >= 0) $this->Execute('ROLLBACK TRAN'); 574 } 575 if ($argDatabasename) return $this->SelectDB($argDatabasename); 576 return true; 577 } 578 579 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 580 { 581 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename, true); 582 } 583 584 function Prepare($sql) 585 { 586 $sqlarr = explode('?',$sql); 587 if (sizeof($sqlarr) <= 1) return $sql; 588 $sql2 = $sqlarr[0]; 589 for ($i = 1, $max = sizeof($sqlarr); $i < $max; $i++) { 590 $sql2 .= '@P'.($i-1) . $sqlarr[$i]; 591 } 592 return array($sql,$this->qstr($sql2),$max,$sql2); 593 } 594 595 function PrepareSP($sql,$param=true) 596 { 597 if (!$this->_has_mssql_init) { 598 ADOConnection::outp( "PrepareSP: mssql_init only available since PHP 4.1.0"); 599 return $sql; 600 } 601 $stmt = mssql_init($sql,$this->_connectionID); 602 if (!$stmt) return $sql; 603 return array($sql,$stmt); 604 } 605 606 // returns concatenated string 607 // MSSQL requires integers to be cast as strings 608 // automatically cast every datatype to VARCHAR(255) 609 // @author David Rogers (introspectshun) 610 function Concat() 611 { 612 $s = ""; 613 $arr = func_get_args(); 614 615 // Split single record on commas, if possible 616 if (sizeof($arr) == 1) { 617 foreach ($arr as $arg) { 618 $args = explode(',', $arg); 619 } 620 $arr = $args; 621 } 622 623 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";')); 624 $s = implode('+',$arr); 625 if (sizeof($arr) > 0) return "$s"; 626 627 return ''; 628 } 629 630 /* 631 Usage: 632 $stmt = $db->PrepareSP('SP_RUNSOMETHING'); -- takes 2 params, @myid and @group 633 634 # note that the parameter does not have @ in front! 635 $db->Parameter($stmt,$id,'myid'); 636 $db->Parameter($stmt,$group,'group',false,64); 637 $db->Execute($stmt); 638 639 @param $stmt Statement returned by Prepare() or PrepareSP(). 640 @param $var PHP variable to bind to. Can set to null (for isNull support). 641 @param $name Name of stored procedure variable name to bind to. 642 @param [$isOutput] Indicates direction of parameter 0/false=IN 1=OUT 2= IN/OUT. This is ignored in oci8. 643 @param [$maxLen] Holds an maximum length of the variable. 644 @param [$type] The data type of $var. Legal values depend on driver. 645 646 See mssql_bind documentation at php.net. 647 */ 648 function Parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false) 649 { 650 if (!$this->_has_mssql_init) { 651 ADOConnection::outp( "Parameter: mssql_bind only available since PHP 4.1.0"); 652 return false; 653 } 654 655 $isNull = is_null($var); // php 4.0.4 and above... 656 657 if ($type === false) 658 switch(gettype($var)) { 659 default: 660 case 'string': $type = SQLVARCHAR; break; 661 case 'double': $type = SQLFLT8; break; 662 case 'integer': $type = SQLINT4; break; 663 case 'boolean': $type = SQLINT1; break; # SQLBIT not supported in 4.1.0 664 } 665 666 if ($this->debug) { 667 $prefix = ($isOutput) ? 'Out' : 'In'; 668 $ztype = (empty($type)) ? 'false' : $type; 669 ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);"); 670 } 671 /* 672 See http://phplens.com/lens/lensforum/msgs.php?id=7231 673 674 RETVAL is HARD CODED into php_mssql extension: 675 The return value (a long integer value) is treated like a special OUTPUT parameter, 676 called "RETVAL" (without the @). See the example at mssql_execute to 677 see how it works. - type: one of this new supported PHP constants. 678 SQLTEXT, SQLVARCHAR,SQLCHAR, SQLINT1,SQLINT2, SQLINT4, SQLBIT,SQLFLT8 679 */ 680 if ($name !== 'RETVAL') $name = '@'.$name; 681 return mssql_bind($stmt[1], $name, $var, $type, $isOutput, $isNull, $maxLen); 682 } 683 684 /* 685 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars 686 So all your blobs must be of type "image". 687 688 Remember to set in php.ini the following... 689 690 ; Valid range 0 - 2147483647. Default = 4096. 691 mssql.textlimit = 0 ; zero to pass through 692 693 ; Valid range 0 - 2147483647. Default = 4096. 694 mssql.textsize = 0 ; zero to pass through 695 */ 696 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 697 { 698 699 if (strtoupper($blobtype) == 'CLOB') { 700 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where"; 701 return $this->Execute($sql) != false; 702 } 703 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where"; 704 return $this->Execute($sql) != false; 705 } 706 707 // returns query ID if successful, otherwise false 708 function _query($sql,$inputarr=false) 709 { 710 $this->_errorMsg = false; 711 if (is_array($inputarr)) { 712 713 # bind input params with sp_executesql: 714 # see http://www.quest-pipelines.com/newsletter-v3/0402_F.htm 715 # works only with sql server 7 and newer 716 $getIdentity = false; 717 if (!is_array($sql) && preg_match('/^\\s*insert/i', $sql)) { 718 $getIdentity = true; 719 $sql .= (preg_match('/;\\s*$/i', $sql) ? ' ' : '; ') . $this->identitySQL; 720 } 721 if (!is_array($sql)) $sql = $this->Prepare($sql); 722 $params = ''; 723 $decl = ''; 724 $i = 0; 725 foreach($inputarr as $v) { 726 if ($decl) { 727 $decl .= ', '; 728 $params .= ', '; 729 } 730 if (is_string($v)) { 731 $len = strlen($v); 732 if ($len == 0) $len = 1; 733 734 if ($len > 4000 ) { 735 // NVARCHAR is max 4000 chars. Let's use NTEXT 736 $decl .= "@P$i NTEXT"; 737 } else { 738 $decl .= "@P$i NVARCHAR($len)"; 739 } 740 741 $params .= "@P$i=N". (strncmp($v,"'",1)==0? $v : $this->qstr($v)); 742 } else if (is_integer($v)) { 743 $decl .= "@P$i INT"; 744 $params .= "@P$i=".$v; 745 } else if (is_float($v)) { 746 $decl .= "@P$i FLOAT"; 747 $params .= "@P$i=".$v; 748 } else if (is_bool($v)) { 749 $decl .= "@P$i INT"; # Used INT just in case BIT in not supported on the user's MSSQL version. It will cast appropriately. 750 $params .= "@P$i=".(($v)?'1':'0'); # True == 1 in MSSQL BIT fields and acceptable for storing logical true in an int field 751 } else { 752 $decl .= "@P$i CHAR"; # Used char because a type is required even when the value is to be NULL. 753 $params .= "@P$i=NULL"; 754 } 755 $i += 1; 756 } 757 $decl = $this->qstr($decl); 758 if ($this->debug) ADOConnection::outp("<font size=-1>sp_executesql N{$sql[1]},N$decl,$params</font>"); 759 $rez = mssql_query("sp_executesql N{$sql[1]},N$decl,$params", $this->_connectionID); 760 if ($getIdentity) { 761 $arr = @mssql_fetch_row($rez); 762 $this->lastInsID = isset($arr[0]) ? $arr[0] : false; 763 @mssql_data_seek($rez, 0); 764 } 765 766 } else if (is_array($sql)) { 767 # PrepareSP() 768 $rez = mssql_execute($sql[1]); 769 $this->lastInsID = false; 770 771 } else { 772 $rez = mssql_query($sql,$this->_connectionID); 773 $this->lastInsID = false; 774 } 775 return $rez; 776 } 777 778 // returns true or false 779 function _close() 780 { 781 if ($this->transCnt) $this->RollbackTrans(); 782 $rez = @mssql_close($this->_connectionID); 783 $this->_connectionID = false; 784 return $rez; 785 } 786 787 // mssql uses a default date like Dec 30 2000 12:00AM 788 static function UnixDate($v) 789 { 790 return ADORecordSet_array_mssql::UnixDate($v); 791 } 792 793 static function UnixTimeStamp($v) 794 { 795 return ADORecordSet_array_mssql::UnixTimeStamp($v); 796 } 797 } 798 799 /*-------------------------------------------------------------------------------------- 800 Class Name: Recordset 801 --------------------------------------------------------------------------------------*/ 802 803 class ADORecordset_mssql extends ADORecordSet { 804 805 var $databaseType = "mssql"; 806 var $canSeek = true; 807 var $hasFetchAssoc; // see http://phplens.com/lens/lensforum/msgs.php?id=6083 808 // _mths works only in non-localised system 809 810 function ADORecordset_mssql($id,$mode=false) 811 { 812 // freedts check... 813 $this->hasFetchAssoc = function_exists('mssql_fetch_assoc'); 814 815 if ($mode === false) { 816 global $ADODB_FETCH_MODE; 817 $mode = $ADODB_FETCH_MODE; 818 819 } 820 $this->fetchMode = $mode; 821 return $this->ADORecordSet($id,$mode); 822 } 823 824 825 function _initrs() 826 { 827 GLOBAL $ADODB_COUNTRECS; 828 $this->_numOfRows = ($ADODB_COUNTRECS)? @mssql_num_rows($this->_queryID):-1; 829 $this->_numOfFields = @mssql_num_fields($this->_queryID); 830 } 831 832 833 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se> 834 // get next resultset - requires PHP 4.0.5 or later 835 function NextRecordSet() 836 { 837 if (!mssql_next_result($this->_queryID)) return false; 838 $this->_inited = false; 839 $this->bind = false; 840 $this->_currentRow = -1; 841 $this->Init(); 842 return true; 843 } 844 845 /* Use associative array to get fields array */ 846 function Fields($colname) 847 { 848 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname]; 849 if (!$this->bind) { 850 $this->bind = array(); 851 for ($i=0; $i < $this->_numOfFields; $i++) { 852 $o = $this->FetchField($i); 853 $this->bind[strtoupper($o->name)] = $i; 854 } 855 } 856 857 return $this->fields[$this->bind[strtoupper($colname)]]; 858 } 859 860 /* Returns: an object containing field information. 861 Get column information in the Recordset object. fetchField() can be used in order to obtain information about 862 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by 863 fetchField() is retrieved. */ 864 865 function FetchField($fieldOffset = -1) 866 { 867 if ($fieldOffset != -1) { 868 $f = @mssql_fetch_field($this->_queryID, $fieldOffset); 869 } 870 else if ($fieldOffset == -1) { /* The $fieldOffset argument is not provided thus its -1 */ 871 $f = @mssql_fetch_field($this->_queryID); 872 } 873 $false = false; 874 if (empty($f)) return $false; 875 return $f; 876 } 877 878 function _seek($row) 879 { 880 return @mssql_data_seek($this->_queryID, $row); 881 } 882 883 // speedup 884 function MoveNext() 885 { 886 if ($this->EOF) return false; 887 888 $this->_currentRow++; 889 890 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 891 if ($this->fetchMode & ADODB_FETCH_NUM) { 892 //ADODB_FETCH_BOTH mode 893 $this->fields = @mssql_fetch_array($this->_queryID); 894 } 895 else { 896 if ($this->hasFetchAssoc) {// only for PHP 4.2.0 or later 897 $this->fields = @mssql_fetch_assoc($this->_queryID); 898 } else { 899 $flds = @mssql_fetch_array($this->_queryID); 900 if (is_array($flds)) { 901 $fassoc = array(); 902 foreach($flds as $k => $v) { 903 if (is_numeric($k)) continue; 904 $fassoc[$k] = $v; 905 } 906 $this->fields = $fassoc; 907 } else 908 $this->fields = false; 909 } 910 } 911 912 if (is_array($this->fields)) { 913 if (ADODB_ASSOC_CASE == 0) { 914 foreach($this->fields as $k=>$v) { 915 $kn = strtolower($k); 916 if ($kn <> $k) { 917 unset($this->fields[$k]); 918 $this->fields[$kn] = $v; 919 } 920 } 921 } else if (ADODB_ASSOC_CASE == 1) { 922 foreach($this->fields as $k=>$v) { 923 $kn = strtoupper($k); 924 if ($kn <> $k) { 925 unset($this->fields[$k]); 926 $this->fields[$kn] = $v; 927 } 928 } 929 } 930 } 931 } else { 932 $this->fields = @mssql_fetch_row($this->_queryID); 933 } 934 if ($this->fields) return true; 935 $this->EOF = true; 936 937 return false; 938 } 939 940 941 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4 942 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot! 943 function _fetch($ignore_fields=false) 944 { 945 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 946 if ($this->fetchMode & ADODB_FETCH_NUM) { 947 //ADODB_FETCH_BOTH mode 948 $this->fields = @mssql_fetch_array($this->_queryID); 949 } else { 950 if ($this->hasFetchAssoc) // only for PHP 4.2.0 or later 951 $this->fields = @mssql_fetch_assoc($this->_queryID); 952 else { 953 $this->fields = @mssql_fetch_array($this->_queryID); 954 if (@is_array($$this->fields)) { 955 $fassoc = array(); 956 foreach($$this->fields as $k => $v) { 957 if (is_integer($k)) continue; 958 $fassoc[$k] = $v; 959 } 960 $this->fields = $fassoc; 961 } 962 } 963 } 964 965 if (!$this->fields) { 966 } else if (ADODB_ASSOC_CASE == 0) { 967 foreach($this->fields as $k=>$v) { 968 $kn = strtolower($k); 969 if ($kn <> $k) { 970 unset($this->fields[$k]); 971 $this->fields[$kn] = $v; 972 } 973 } 974 } else if (ADODB_ASSOC_CASE == 1) { 975 foreach($this->fields as $k=>$v) { 976 $kn = strtoupper($k); 977 if ($kn <> $k) { 978 unset($this->fields[$k]); 979 $this->fields[$kn] = $v; 980 } 981 } 982 } 983 } else { 984 $this->fields = @mssql_fetch_row($this->_queryID); 985 } 986 return $this->fields; 987 } 988 989 /* close() only needs to be called if you are worried about using too much memory while your script 990 is running. All associated result memory for the specified result identifier will automatically be freed. */ 991 992 function _close() 993 { 994 $rez = mssql_free_result($this->_queryID); 995 $this->_queryID = false; 996 return $rez; 997 } 998 // mssql uses a default date like Dec 30 2000 12:00AM 999 static function UnixDate($v) 1000 { 1001 return ADORecordSet_array_mssql::UnixDate($v); 1002 } 1003 1004 static function UnixTimeStamp($v) 1005 { 1006 return ADORecordSet_array_mssql::UnixTimeStamp($v); 1007 } 1008 1009 } 1010 1011 1012 class ADORecordSet_array_mssql extends ADORecordSet_array { 1013 function ADORecordSet_array_mssql($id=-1,$mode=false) 1014 { 1015 $this->ADORecordSet_array($id,$mode); 1016 } 1017 1018 // mssql uses a default date like Dec 30 2000 12:00AM 1019 static function UnixDate($v) 1020 { 1021 1022 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v); 1023 1024 global $ADODB_mssql_mths,$ADODB_mssql_date_order; 1025 1026 //Dec 30 2000 12:00AM 1027 if ($ADODB_mssql_date_order == 'dmy') { 1028 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) { 1029 return parent::UnixDate($v); 1030 } 1031 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1032 1033 $theday = $rr[1]; 1034 $themth = substr(strtoupper($rr[2]),0,3); 1035 } else { 1036 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) { 1037 return parent::UnixDate($v); 1038 } 1039 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1040 1041 $theday = $rr[2]; 1042 $themth = substr(strtoupper($rr[1]),0,3); 1043 } 1044 $themth = $ADODB_mssql_mths[$themth]; 1045 if ($themth <= 0) return false; 1046 // h-m-s-MM-DD-YY 1047 return mktime(0,0,0,$themth,$theday,$rr[3]); 1048 } 1049 1050 static function UnixTimeStamp($v) 1051 { 1052 1053 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v); 1054 1055 global $ADODB_mssql_mths,$ADODB_mssql_date_order; 1056 1057 //Dec 30 2000 12:00AM 1058 if ($ADODB_mssql_date_order == 'dmy') { 1059 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})|" 1060 ,$v, $rr)) return parent::UnixTimeStamp($v); 1061 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1062 1063 $theday = $rr[1]; 1064 $themth = substr(strtoupper($rr[2]),0,3); 1065 } else { 1066 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})|" 1067 ,$v, $rr)) return parent::UnixTimeStamp($v); 1068 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 1069 1070 $theday = $rr[2]; 1071 $themth = substr(strtoupper($rr[1]),0,3); 1072 } 1073 1074 $themth = $ADODB_mssql_mths[$themth]; 1075 if ($themth <= 0) return false; 1076 1077 switch (strtoupper($rr[6])) { 1078 case 'P': 1079 if ($rr[4]<12) $rr[4] += 12; 1080 break; 1081 case 'A': 1082 if ($rr[4]==12) $rr[4] = 0; 1083 break; 1084 default: 1085 break; 1086 } 1087 // h-m-s-MM-DD-YY 1088 return mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]); 1089 } 1090 } 1091 1092 /* 1093 Code Example 1: 1094 1095 select object_name(constid) as constraint_name, 1096 object_name(fkeyid) as table_name, 1097 col_name(fkeyid, fkey) as column_name, 1098 object_name(rkeyid) as referenced_table_name, 1099 col_name(rkeyid, rkey) as referenced_column_name 1100 from sysforeignkeys 1101 where object_name(fkeyid) = x 1102 order by constraint_name, table_name, referenced_table_name, keyno 1103 1104 Code Example 2: 1105 select constraint_name, 1106 column_name, 1107 ordinal_position 1108 from information_schema.key_column_usage 1109 where constraint_catalog = db_name() 1110 and table_name = x 1111 order by constraint_name, ordinal_position 1112 1113 http://www.databasejournal.com/scripts/article.php/1440551 1114 */ 1115 1116 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Jul 28 15:48:31 2011 | Cross-referenced by PHPXref 0.7 |