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