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

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