lib/adodb/drivers/adodb-mssql.inc.php

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