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