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

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