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