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

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