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

   1 <?php
   2 /*
   3 V5.14 8 Sept 2011  (c) 2000-2011 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,
  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 MetaIndexes($table,$primary=false, $owner=false)
 378         {
 379                 $table = $this->qstr($table);
 380
 381                 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
 382                         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,
 383                         CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
 384                         FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
 385                         INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
 386                         INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
 387                         WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
 388                         ORDER BY O.name, I.Name, K.keyno";
 389
 390                 global $ADODB_FETCH_MODE;
 391                 $save = $ADODB_FETCH_MODE;
 392         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 393         if ($this->fetchMode !== FALSE) {
 394                 $savem = $this->SetFetchMode(FALSE);
 395         }
 396
 397         $rs = $this->Execute($sql);
 398         if (isset($savem)) {
 399                 $this->SetFetchMode($savem);
 400         }
 401         $ADODB_FETCH_MODE = $save;
 402
 403         if (!is_object($rs)) {
 404                 return FALSE;
 405         }
 406
 407                 $indexes = array();
 408                 while ($row = $rs->FetchRow()) {
 409                         if ($primary && !$row[5]) continue;
 410
 411             $indexes[$row[0]]['unique'] = $row[6];
 412             $indexes[$row[0]]['columns'][] = $row[1];
 413         }
 414         return $indexes;
 415         }
 416
 417         function MetaForeignKeys($table, $owner=false, $upper=false)
 418         {
 419         global $ADODB_FETCH_MODE;
 420
 421                 $save = $ADODB_FETCH_MODE;
 422                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 423                 $table = $this->qstr(strtoupper($table));
 424
 425                 $sql =
 426 "select object_name(constid) as constraint_name,
 427         col_name(fkeyid, fkey) as column_name,
 428         object_name(rkeyid) as referenced_table_name,
 429         col_name(rkeyid, rkey) as referenced_column_name
 430 from sysforeignkeys
 431 where upper(object_name(fkeyid)) = $table
 432 order by constraint_name, referenced_table_name, keyno";
 433
 434                 $constraints = $this->GetArray($sql);
 435
 436                 $ADODB_FETCH_MODE = $save;
 437
 438                 $arr = false;
 439                 foreach($constraints as $constr) {
 440                         //print_r($constr);
 441                         $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
 442                 }
 443                 if (!$arr) return false;
 444
 445                 $arr2 = false;
 446
 447                 foreach($arr as $k => $v) {
 448                         foreach($v as $a => $b) {
 449                                 if ($upper) $a = strtoupper($a);
 450                                 $arr2[$a] = $b;
 451                         }
 452                 }
 453                 return $arr2;
 454         }
 455
 456         //From: Fernando Moreira <FMoreira@imediata.pt>
 457         function MetaDatabases()
 458         {
 459                 if(@mssql_select_db("master")) {
 460                                  $qry=$this->metaDatabasesSQL;
 461                                  if($rs=@mssql_query($qry,$this->_connectionID)){
 462                                                  $tmpAr=$ar=array();
 463                                                  while($tmpAr=@mssql_fetch_row($rs))
 464                                                                  $ar[]=$tmpAr[0];
 465                                                 @mssql_select_db($this->database);
 466                                                  if(sizeof($ar))
 467                                                                  return($ar);
 468                                                  else
 469                                                                  return(false);
 470                                  } else {
 471                                                  @mssql_select_db($this->database);
 472                                                  return(false);
 473                                  }
 474                  }
 475                  return(false);
 476         }
 477
 478         // "Stein-Aksel Basma" <basma@accelero.no>
 479         // tested with MSSQL 2000
 480         function MetaPrimaryKeys($table, $owner=false)
 481         {
 482         global $ADODB_FETCH_MODE;
 483
 484                 $schema = '';
 485                 $this->_findschema($table,$schema);
 486                 if (!$schema) $schema = $this->database;
 487                 if ($schema) $schema = "and k.table_catalog like '$schema%'";
 488
 489                 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
 490                 information_schema.table_constraints tc
 491                 where tc.constraint_name = k.constraint_name and tc.constraint_type =
 492                 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
 493
 494                 $savem = $ADODB_FETCH_MODE;
 495                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 496                 $a = $this->GetCol($sql);
 497                 $ADODB_FETCH_MODE = $savem;
 498
 499                 if ($a && sizeof($a)>0) return $a;
 500                 $false = false;
 501                 return $false;
 502         }
 503
 504
 505         function MetaTables($ttype=false,$showSchema=false,$mask=false)
 506         {
 507                 if ($mask) {
 508                         $save = $this->metaTablesSQL;
 509                         $mask = $this->qstr(($mask));
 510                         $this->metaTablesSQL .= " AND name like $mask";
 511                 }
 512                 $ret = ADOConnection::MetaTables($ttype,$showSchema);
 513
 514                 if ($mask) {
 515                         $this->metaTablesSQL = $save;
 516                 }
 517                 return $ret;
 518         }
 519
 520         function SelectDB($dbName)
 521         {
 522                 $this->database = $dbName;
 523                 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
 524                 if ($this->_connectionID) {
 525                         return @mssql_select_db($dbName);
 526                 }
 527                 else return false;
 528         }
 529
 530         function ErrorMsg()
 531         {
 532                 if (empty($this->_errorMsg)){
 533                         $this->_errorMsg = mssql_get_last_message();
 534                 }
 535                 return $this->_errorMsg;
 536         }
 537
 538         function ErrorNo()
 539         {
 540                 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
 541                 if (empty($this->_errorMsg)) {
 542                         $this->_errorMsg = mssql_get_last_message();
 543                 }
 544                 $id = @mssql_query("select @@ERROR",$this->_connectionID);
 545                 if (!$id) return false;
 546                 $arr = mssql_fetch_array($id);
 547                 @mssql_free_result($id);
 548                 if (is_array($arr)) return $arr[0];
 549            else return -1;
 550         }
 551
 552         // returns true or false, newconnect supported since php 5.1.0.
 553         function _connect($argHostname, $argUsername, $argPassword, $argDatabasename,$newconnect=false)
 554         {
 555                 if (!function_exists('mssql_pconnect')) return null;
 556                 $this->_connectionID = mssql_connect($argHostname,$argUsername,$argPassword,$newconnect);
 557                 if ($this->_connectionID === false) return false;
 558                 if ($argDatabasename) return $this->SelectDB($argDatabasename);
 559                 return true;
 560         }
 561
 562
 563         // returns true or false
 564         function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 565         {
 566                 if (!function_exists('mssql_pconnect')) return null;
 567                 $this->_connectionID = mssql_pconnect($argHostname,$argUsername,$argPassword);
 568                 if ($this->_connectionID === false) return false;
 569
 570                 // persistent connections can forget to rollback on crash, so we do it here.
 571                 if ($this->autoRollback) {
 572                         $cnt = $this->GetOne('select @@TRANCOUNT');
 573                         while (--$cnt >= 0) $this->Execute('ROLLBACK TRAN');
 574                 }
 575                 if ($argDatabasename) return $this->SelectDB($argDatabasename);
 576                 return true;
 577         }
 578
 579         function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 580     {
 581                 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename, true);
 582     }
 583
 584         function Prepare($sql)
 585         {
 586                 $sqlarr = explode('?',$sql);
 587                 if (sizeof($sqlarr) <= 1) return $sql;
 588                 $sql2 = $sqlarr[0];
 589                 for ($i = 1, $max = sizeof($sqlarr); $i < $max; $i++) {
 590                         $sql2 .=  '@P'.($i-1) . $sqlarr[$i];
 591                 }
 592                 return array($sql,$this->qstr($sql2),$max,$sql2);
 593         }
 594
 595         function PrepareSP($sql,$param=true)
 596         {
 597                 if (!$this->_has_mssql_init) {
 598                         ADOConnection::outp( "PrepareSP: mssql_init only available since PHP 4.1.0");
 599                         return $sql;
 600                 }
 601                 $stmt = mssql_init($sql,$this->_connectionID);
 602                 if (!$stmt)  return $sql;
 603                 return array($sql,$stmt);
 604         }
 605
 606         // returns concatenated string
 607     // MSSQL requires integers to be cast as strings
 608     // automatically cast every datatype to VARCHAR(255)
 609     // @author David Rogers (introspectshun)
 610     function Concat()
 611     {
 612             $s = "";
 613             $arr = func_get_args();
 614
 615             // Split single record on commas, if possible
 616             if (sizeof($arr) == 1) {
 617                 foreach ($arr as $arg) {
 618                     $args = explode(',', $arg);
 619                 }
 620                 $arr = $args;
 621             }
 622
 623             array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
 624             $s = implode('+',$arr);
 625             if (sizeof($arr) > 0) return "$s";
 626
 627                         return '';
 628     }
 629
 630         /*
 631         Usage:
 632                 $stmt = $db->PrepareSP('SP_RUNSOMETHING'); -- takes 2 params, @myid and @group
 633
 634                 # note that the parameter does not have @ in front!
 635                 $db->Parameter($stmt,$id,'myid');
 636                 $db->Parameter($stmt,$group,'group',false,64);
 637                 $db->Execute($stmt);
 638
 639                 @param $stmt Statement returned by Prepare() or PrepareSP().
 640                 @param $var PHP variable to bind to. Can set to null (for isNull support).
 641                 @param $name Name of stored procedure variable name to bind to.
 642                 @param [$isOutput] Indicates direction of parameter 0/false=IN  1=OUT  2= IN/OUT. This is ignored in oci8.
 643                 @param [$maxLen] Holds an maximum length of the variable.
 644                 @param [$type] The data type of $var. Legal values depend on driver.
 645
 646                 See mssql_bind documentation at php.net.
 647         */
 648         function Parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false)
 649         {
 650                 if (!$this->_has_mssql_init) {
 651                         ADOConnection::outp( "Parameter: mssql_bind only available since PHP 4.1.0");
 652                         return false;
 653                 }
 654
 655                 $isNull = is_null($var); // php 4.0.4 and above...
 656
 657                 if ($type === false)
 658                         switch(gettype($var)) {
 659                         default:
 660                         case 'string': $type = SQLVARCHAR; break;
 661                         case 'double': $type = SQLFLT8; break;
 662                         case 'integer': $type = SQLINT4; break;
 663                         case 'boolean': $type = SQLINT1; break; # SQLBIT not supported in 4.1.0
 664                         }
 665
 666                 if  ($this->debug) {
 667                         $prefix = ($isOutput) ? 'Out' : 'In';
 668                         $ztype = (empty($type)) ? 'false' : $type;
 669                         ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);");
 670                 }
 671                 /*
 672                         See http://phplens.com/lens/lensforum/msgs.php?id=7231
 673
 674                         RETVAL is HARD CODED into php_mssql extension:
 675                         The return value (a long integer value) is treated like a special OUTPUT parameter,
 676                         called "RETVAL" (without the @). See the example at mssql_execute to
 677                         see how it works. - type: one of this new supported PHP constants.
 678                                 SQLTEXT, SQLVARCHAR,SQLCHAR, SQLINT1,SQLINT2, SQLINT4, SQLBIT,SQLFLT8
 679                 */
 680                 if ($name !== 'RETVAL') $name = '@'.$name;
 681                 return mssql_bind($stmt[1], $name, $var, $type, $isOutput, $isNull, $maxLen);
 682         }
 683
 684         /*
 685                 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
 686                 So all your blobs must be of type "image".
 687
 688                 Remember to set in php.ini the following...
 689
 690                 ; Valid range 0 - 2147483647. Default = 4096.
 691                 mssql.textlimit = 0 ; zero to pass through
 692
 693                 ; Valid range 0 - 2147483647. Default = 4096.
 694                 mssql.textsize = 0 ; zero to pass through
 695         */
 696         function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
 697         {
 698
 699                 if (strtoupper($blobtype) == 'CLOB') {
 700                         $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
 701                         return $this->Execute($sql) != false;
 702                 }
 703                 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
 704                 return $this->Execute($sql) != false;
 705         }
 706
 707         // returns query ID if successful, otherwise false
 708         function _query($sql,$inputarr=false)
 709         {
 710                 $this->_errorMsg = false;
 711                 if (is_array($inputarr)) {
 712
 713                         # bind input params with sp_executesql:
 714                         # see http://www.quest-pipelines.com/newsletter-v3/0402_F.htm
 715                         # works only with sql server 7 and newer
 716             $getIdentity = false;
 717             if (!is_array($sql) && preg_match('/^\\s*insert/i', $sql)) {
 718                 $getIdentity = true;
 719                 $sql .= (preg_match('/;\\s*$/i', $sql) ? ' ' : '; ') . $this->identitySQL;
 720             }
 721                         if (!is_array($sql)) $sql = $this->Prepare($sql);
 722                         $params = '';
 723                         $decl = '';
 724                         $i = 0;
 725                         foreach($inputarr as $v) {
 726                                 if ($decl) {
 727                                         $decl .= ', ';
 728                                         $params .= ', ';
 729                                 }
 730                                 if (is_string($v)) {
 731                                         $len = strlen($v);
 732                                         if ($len == 0) $len = 1;
 733
 734                                         if ($len > 4000 ) {
 735                                                 // NVARCHAR is max 4000 chars. Let's use NTEXT
 736                                                 $decl .= "@P$i NTEXT";
 737                                         } else {
 738                                                 $decl .= "@P$i NVARCHAR($len)";
 739                                         }
 740
 741                                         $params .= "@P$i=N". (strncmp($v,"'",1)==0? $v : $this->qstr($v));
 742                                 } else if (is_integer($v)) {
 743                                         $decl .= "@P$i INT";
 744                                         $params .= "@P$i=".$v;
 745                                 } else if (is_float($v)) {
 746                                         $decl .= "@P$i FLOAT";
 747                                         $params .= "@P$i=".$v;
 748                                 } else if (is_bool($v)) {
 749                                         $decl .= "@P$i INT"; # Used INT just in case BIT in not supported on the user's MSSQL version. It will cast appropriately.
 750                                         $params .= "@P$i=".(($v)?'1':'0'); # True == 1 in MSSQL BIT fields and acceptable for storing logical true in an int field
 751                                 } else {
 752                                         $decl .= "@P$i CHAR"; # Used char because a type is required even when the value is to be NULL.
 753                                         $params .= "@P$i=NULL";
 754                                         }
 755                                 $i += 1;
 756                         }
 757                         $decl = $this->qstr($decl);
 758                         if ($this->debug) ADOConnection::outp("<font size=-1>sp_executesql N{$sql[1]},N$decl,$params</font>");
 759                         $rez = mssql_query("sp_executesql N{$sql[1]},N$decl,$params", $this->_connectionID);
 760             if ($getIdentity) {
 761                 $arr = @mssql_fetch_row($rez);
 762                 $this->lastInsID = isset($arr[0]) ? $arr[0] : false;
 763                 @mssql_data_seek($rez, 0);
 764             }
 765
 766                 } else if (is_array($sql)) {
 767                         # PrepareSP()
 768                         $rez = mssql_execute($sql[1]);
 769             $this->lastInsID = false;
 770
 771                 } else {
 772                         $rez = mssql_query($sql,$this->_connectionID);
 773             $this->lastInsID = false;
 774                 }
 775                 return $rez;
 776         }
 777
 778         // returns true or false
 779         function _close()
 780         {
 781                 if ($this->transCnt) $this->RollbackTrans();
 782                 $rez = @mssql_close($this->_connectionID);
 783                 $this->_connectionID = false;
 784                 return $rez;
 785         }
 786
 787         // mssql uses a default date like Dec 30 2000 12:00AM
 788         static function UnixDate($v)
 789         {
 790                 return ADORecordSet_array_mssql::UnixDate($v);
 791         }
 792
 793         static function UnixTimeStamp($v)
 794         {
 795                 return ADORecordSet_array_mssql::UnixTimeStamp($v);
 796         }
 797 }
 798
 799 /*--------------------------------------------------------------------------------------
 800          Class Name: Recordset
 801 --------------------------------------------------------------------------------------*/
 802
 803 class ADORecordset_mssql extends ADORecordSet {
 804
 805         var $databaseType = "mssql";
 806         var $canSeek = true;
 807         var $hasFetchAssoc; // see http://phplens.com/lens/lensforum/msgs.php?id=6083
 808         // _mths works only in non-localised system
 809
 810         function ADORecordset_mssql($id,$mode=false)
 811         {
 812                 // freedts check...
 813                 $this->hasFetchAssoc = function_exists('mssql_fetch_assoc');
 814
 815                 if ($mode === false) {
 816                         global $ADODB_FETCH_MODE;
 817                         $mode = $ADODB_FETCH_MODE;
 818
 819                 }
 820                 $this->fetchMode = $mode;
 821                 return $this->ADORecordSet($id,$mode);
 822         }
 823
 824
 825         function _initrs()
 826         {
 827         GLOBAL $ADODB_COUNTRECS;
 828                 $this->_numOfRows = ($ADODB_COUNTRECS)? @mssql_num_rows($this->_queryID):-1;
 829                 $this->_numOfFields = @mssql_num_fields($this->_queryID);
 830         }
 831
 832
 833         //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
 834         // get next resultset - requires PHP 4.0.5 or later
 835         function NextRecordSet()
 836         {
 837                 if (!mssql_next_result($this->_queryID)) return false;
 838                 $this->_inited = false;
 839                 $this->bind = false;
 840                 $this->_currentRow = -1;
 841                 $this->Init();
 842                 return true;
 843         }
 844
 845         /* Use associative array to get fields array */
 846         function Fields($colname)
 847         {
 848                 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
 849                 if (!$this->bind) {
 850                         $this->bind = array();
 851                         for ($i=0; $i < $this->_numOfFields; $i++) {
 852                                 $o = $this->FetchField($i);
 853                                 $this->bind[strtoupper($o->name)] = $i;
 854                         }
 855                 }
 856
 857                  return $this->fields[$this->bind[strtoupper($colname)]];
 858         }
 859
 860         /*      Returns: an object containing field information.
 861                 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
 862                 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
 863                 fetchField() is retrieved.      */
 864
 865         function FetchField($fieldOffset = -1)
 866         {
 867                 if ($fieldOffset != -1) {
 868                         $f = @mssql_fetch_field($this->_queryID, $fieldOffset);
 869                 }
 870                 else if ($fieldOffset == -1) {  /*      The $fieldOffset argument is not provided thus its -1   */
 871                         $f = @mssql_fetch_field($this->_queryID);
 872                 }
 873                 $false = false;
 874                 if (empty($f)) return $false;
 875                 return $f;
 876         }
 877
 878         function _seek($row)
 879         {
 880                 return @mssql_data_seek($this->_queryID, $row);
 881         }
 882
 883         // speedup
 884         function MoveNext()
 885         {
 886                 if ($this->EOF) return false;
 887
 888                 $this->_currentRow++;
 889
 890                 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
 891                         if ($this->fetchMode & ADODB_FETCH_NUM) {
 892                                 //ADODB_FETCH_BOTH mode
 893                                 $this->fields = @mssql_fetch_array($this->_queryID);
 894                         }
 895                         else {
 896                                 if ($this->hasFetchAssoc) {// only for PHP 4.2.0 or later
 897                                          $this->fields = @mssql_fetch_assoc($this->_queryID);
 898                                 } else {
 899                                         $flds = @mssql_fetch_array($this->_queryID);
 900                                         if (is_array($flds)) {
 901                                                 $fassoc = array();
 902                                                 foreach($flds as $k => $v) {
 903                                                         if (is_numeric($k)) continue;
 904                                                         $fassoc[$k] = $v;
 905                                                 }
 906                                                 $this->fields = $fassoc;
 907                                         } else
 908                                                 $this->fields = false;
 909                                 }
 910                         }
 911
 912                         if (is_array($this->fields)) {
 913                                 if (ADODB_ASSOC_CASE == 0) {
 914                                         foreach($this->fields as $k=>$v) {
 915                                                 $kn = strtolower($k);
 916                                                 if ($kn <> $k) {
 917                                                         unset($this->fields[$k]);
 918                                                         $this->fields[$kn] = $v;
 919                                                 }
 920                                         }
 921                                 } else if (ADODB_ASSOC_CASE == 1) {
 922                                         foreach($this->fields as $k=>$v) {
 923                                                 $kn = strtoupper($k);
 924                                                 if ($kn <> $k) {
 925                                                         unset($this->fields[$k]);
 926                                                         $this->fields[$kn] = $v;
 927                                                 }
 928                                         }
 929                                 }
 930                         }
 931                 } else {
 932                         $this->fields = @mssql_fetch_row($this->_queryID);
 933                 }
 934                 if ($this->fields) return true;
 935                 $this->EOF = true;
 936
 937                 return false;
 938         }
 939
 940
 941         // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
 942         // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
 943         function _fetch($ignore_fields=false)
 944         {
 945                 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
 946                         if ($this->fetchMode & ADODB_FETCH_NUM) {
 947                                 //ADODB_FETCH_BOTH mode
 948                                 $this->fields = @mssql_fetch_array($this->_queryID);
 949                         } else {
 950                                 if ($this->hasFetchAssoc) // only for PHP 4.2.0 or later
 951                                         $this->fields = @mssql_fetch_assoc($this->_queryID);
 952                                 else {
 953                                         $this->fields = @mssql_fetch_array($this->_queryID);
 954                                         if (@is_array($$this->fields)) {
 955                                                 $fassoc = array();
 956                                                 foreach($$this->fields as $k => $v) {
 957                                                         if (is_integer($k)) continue;
 958                                                         $fassoc[$k] = $v;
 959                                                 }
 960                                                 $this->fields = $fassoc;
 961                                         }
 962                                 }
 963                         }
 964
 965                         if (!$this->fields) {
 966                         } else if (ADODB_ASSOC_CASE == 0) {
 967                                 foreach($this->fields as $k=>$v) {
 968                                         $kn = strtolower($k);
 969                                         if ($kn <> $k) {
 970                                                 unset($this->fields[$k]);
 971                                                 $this->fields[$kn] = $v;
 972                                         }
 973                                 }
 974                         } else if (ADODB_ASSOC_CASE == 1) {
 975                                 foreach($this->fields as $k=>$v) {
 976                                         $kn = strtoupper($k);
 977                                         if ($kn <> $k) {
 978                                                 unset($this->fields[$k]);
 979                                                 $this->fields[$kn] = $v;
 980                                         }
 981                                 }
 982                         }
 983                 } else {
 984                         $this->fields = @mssql_fetch_row($this->_queryID);
 985                 }
 986                 return $this->fields;
 987         }
 988
 989         /*      close() only needs to be called if you are worried about using too much memory while your script
 990                 is running. All associated result memory for the specified result identifier will automatically be freed.       */
 991
 992         function _close()
 993         {
 994                 $rez = mssql_free_result($this->_queryID);
 995                 $this->_queryID = false;
 996                 return $rez;
 997         }
 998         // mssql uses a default date like Dec 30 2000 12:00AM
 999         static function UnixDate($v)
1000         {
1001                 return ADORecordSet_array_mssql::UnixDate($v);
1002         }
1003
1004         static function UnixTimeStamp($v)
1005         {
1006                 return ADORecordSet_array_mssql::UnixTimeStamp($v);
1007         }
1008
1009 }
1010
1011
1012 class ADORecordSet_array_mssql extends ADORecordSet_array {
1013         function ADORecordSet_array_mssql($id=-1,$mode=false)
1014         {
1015                 $this->ADORecordSet_array($id,$mode);
1016         }
1017
1018                 // mssql uses a default date like Dec 30 2000 12:00AM
1019         static function UnixDate($v)
1020         {
1021
1022                 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
1023
1024         global $ADODB_mssql_mths,$ADODB_mssql_date_order;
1025
1026                 //Dec 30 2000 12:00AM
1027                 if ($ADODB_mssql_date_order == 'dmy') {
1028                         if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
1029                                 return parent::UnixDate($v);
1030                         }
1031                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1032
1033                         $theday = $rr[1];
1034                         $themth =  substr(strtoupper($rr[2]),0,3);
1035                 } else {
1036                         if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
1037                                 return parent::UnixDate($v);
1038                         }
1039                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1040
1041                         $theday = $rr[2];
1042                         $themth = substr(strtoupper($rr[1]),0,3);
1043                 }
1044                 $themth = $ADODB_mssql_mths[$themth];
1045                 if ($themth <= 0) return false;
1046                 // h-m-s-MM-DD-YY
1047                 return  mktime(0,0,0,$themth,$theday,$rr[3]);
1048         }
1049
1050         static function UnixTimeStamp($v)
1051         {
1052
1053                 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
1054
1055         global $ADODB_mssql_mths,$ADODB_mssql_date_order;
1056
1057                 //Dec 30 2000 12:00AM
1058                  if ($ADODB_mssql_date_order == 'dmy') {
1059                          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})|"
1060                         ,$v, $rr)) return parent::UnixTimeStamp($v);
1061                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1062
1063                         $theday = $rr[1];
1064                         $themth =  substr(strtoupper($rr[2]),0,3);
1065                 } else {
1066                         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})|"
1067                         ,$v, $rr)) return parent::UnixTimeStamp($v);
1068                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1069
1070                         $theday = $rr[2];
1071                         $themth = substr(strtoupper($rr[1]),0,3);
1072                 }
1073
1074                 $themth = $ADODB_mssql_mths[$themth];
1075                 if ($themth <= 0) return false;
1076
1077                 switch (strtoupper($rr[6])) {
1078                 case 'P':
1079                         if ($rr[4]<12) $rr[4] += 12;
1080                         break;
1081                 case 'A':
1082                         if ($rr[4]==12) $rr[4] = 0;
1083                         break;
1084                 default:
1085                         break;
1086                 }
1087                 // h-m-s-MM-DD-YY
1088                 return  mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
1089         }
1090 }
1091
1092 /*
1093 Code Example 1:
1094
1095 select  object_name(constid) as constraint_name,
1096         object_name(fkeyid) as table_name,
1097         col_name(fkeyid, fkey) as column_name,
1098         object_name(rkeyid) as referenced_table_name,
1099         col_name(rkeyid, rkey) as referenced_column_name
1100 from sysforeignkeys
1101 where object_name(fkeyid) = x
1102 order by constraint_name, table_name, referenced_table_name,  keyno
1103
1104 Code Example 2:
1105 select  constraint_name,
1106         column_name,
1107         ordinal_position
1108 from information_schema.key_column_usage
1109 where constraint_catalog = db_name()
1110 and table_name = x
1111 order by constraint_name, ordinal_position
1112
1113 http://www.databasejournal.com/scripts/article.php/1440551
1114 */
1115
1116 ?>