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