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