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

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