PHPOpenChat PHP Cross Reference Customer Relationship Management

Source: /include/adodb/drivers/adodb-mssql.inc.php - 1017 lines - 30895 bytes - Summary - Text - Print

   1  <?php
   2  /* 

   3  V4.61 24 Feb 2005  (c) 2000-2005 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:sA'";
  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      
 107  	function ADODB_mssql() 
 108      {        
 109          $this->_has_mssql_init = (strnatcmp(PHP_VERSION,'4.1.0')>=0);
 110      }
 111  
 112  	function ServerInfo()
 113      {
 114      global $ADODB_FETCH_MODE;
 115      
 116          $stmt = $this->PrepareSP('sp_server_info');
 117          $val = 2;
 118          if ($this->fetchMode === false) {
 119              $savem = $ADODB_FETCH_MODE;
 120              $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 121          } else 
 122              $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
 123          
 124          
 125          $this->Parameter($stmt,$val,'attribute_id');
 126          $row = $this->GetRow($stmt);
 127          
 128          //$row = $this->GetRow("execute sp_server_info 2");

 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          $start -= 1;
 165          $this->Execute("create table $seq (id float(53))");
 166          $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
 167          if (!$ok) {
 168                  $this->Execute('ROLLBACK TRANSACTION adodbseq');
 169                  return false;
 170          }
 171          $this->Execute('COMMIT TRANSACTION adodbseq'); 
 172          return true;
 173      }
 174  
 175  	function GenID($seq='adodbseq',$start=1)
 176      {
 177          //$this->debug=1;

 178          $this->Execute('BEGIN TRANSACTION adodbseq');
 179          $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
 180          if (!$ok) {
 181              $this->Execute("create table $seq (id float(53))");
 182              $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
 183              if (!$ok) {
 184                  $this->Execute('ROLLBACK TRANSACTION adodbseq');
 185                  return false;
 186              }
 187              $this->Execute('COMMIT TRANSACTION adodbseq'); 
 188              return $start;
 189          }
 190          $num = $this->GetOne("select id from $seq");
 191          $this->Execute('COMMIT TRANSACTION adodbseq'); 
 192          return $num;
 193          
 194          // in old implementation, pre 1.90, we returned GUID...

 195          //return $this->GetOne("SELECT CONVERT(varchar(255), NEWID()) AS 'Char'");

 196      }
 197      
 198  
 199      function &SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
 200      {
 201          if ($nrows > 0 && $offset <= 0) {
 202              $sql = preg_replace(
 203                  '/(^\s*select\s+(distinctrow|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql);
 204              $rs =& $this->Execute($sql,$inputarr);
 205          } else
 206              $rs =& ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
 207      
 208          return $rs;
 209      }
 210      
 211      
 212      // Format date column in sql string given an input format that understands Y M D

 213  	function SQLDate($fmt, $col=false)
 214      {    
 215          if (!$col) $col = $this->sysTimeStamp;
 216          $s = '';
 217          
 218          $len = strlen($fmt);
 219          for ($i=0; $i < $len; $i++) {
 220              if ($s) $s .= '+';
 221              $ch = $fmt[$i];
 222              switch($ch) {
 223              case 'Y':
 224              case 'y':
 225                  $s .= "datename(yyyy,$col)";
 226                  break;
 227              case 'M':
 228                  $s .= "convert(char(3),$col,0)";
 229                  break;
 230              case 'm':
 231                  $s .= "replace(str(month($col),2),' ','0')";
 232                  break;
 233              case 'Q':
 234              case 'q':
 235                  $s .= "datename(quarter,$col)";
 236                  break;
 237              case 'D':
 238              case 'd':
 239                  $s .= "replace(str(day($col),2),' ','0')";
 240                  break;
 241              case 'h':
 242                  $s .= "substring(convert(char(14),$col,0),13,2)";
 243                  break;
 244              
 245              case 'H':
 246                  $s .= "replace(str(datepart(hh,$col),2),' ','0')";
 247                  break;
 248                  
 249              case 'i':
 250                  $s .= "replace(str(datepart(mi,$col),2),' ','0')";
 251                  break;
 252              case 's':
 253                  $s .= "replace(str(datepart(ss,$col),2),' ','0')";
 254                  break;
 255              case 'a':
 256              case 'A':
 257                  $s .= "substring(convert(char(19),$col,0),18,2)";
 258                  break;
 259                  
 260              default:
 261                  if ($ch == '\\') {
 262                      $i++;
 263                      $ch = substr($fmt,$i,1);
 264                  }
 265                  $s .= $this->qstr($ch);
 266                  break;
 267              }
 268          }
 269          return $s;
 270      }
 271  
 272      
 273  	function BeginTrans()
 274      {
 275          if ($this->transOff) return true; 
 276          $this->transCnt += 1;
 277             $this->Execute('BEGIN TRAN');
 278             return true;
 279      }
 280          
 281  	function CommitTrans($ok=true) 
 282      { 
 283          if ($this->transOff) return true; 
 284          if (!$ok) return $this->RollbackTrans();
 285          if ($this->transCnt) $this->transCnt -= 1;
 286          $this->Execute('COMMIT TRAN');
 287          return true;
 288      }
 289  	function RollbackTrans()
 290      {
 291          if ($this->transOff) return true; 
 292          if ($this->transCnt) $this->transCnt -= 1;
 293          $this->Execute('ROLLBACK TRAN');
 294          return true;
 295      }
 296      
 297      /*

 298          Usage:

 299          

 300          $this->BeginTrans();

 301          $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables

 302          

 303          # some operation on both tables table1 and table2

 304          

 305          $this->CommitTrans();

 306          

 307          See http://www.swynk.com/friends/achigrik/SQL70Locks.asp

 308      */
 309  	function RowLock($tables,$where) 
 310      {
 311          if (!$this->transCnt) $this->BeginTrans();
 312          return $this->GetOne("select top 1 null as ignore from $tables with (ROWLOCK,HOLDLOCK) where $where");
 313      }
 314      
 315      
 316      function &MetaIndexes($table,$primary=false)
 317      {
 318          $table = $this->qstr($table);
 319  
 320          $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno, 
 321              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,
 322              CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
 323              FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id 
 324              INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid 
 325              INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
 326              WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
 327              ORDER BY O.name, I.Name, K.keyno";
 328  
 329          global $ADODB_FETCH_MODE;
 330          $save = $ADODB_FETCH_MODE;
 331          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 332          if ($this->fetchMode !== FALSE) {
 333              $savem = $this->SetFetchMode(FALSE);
 334          }
 335          
 336          $rs = $this->Execute($sql);
 337          if (isset($savem)) {
 338              $this->SetFetchMode($savem);
 339          }
 340          $ADODB_FETCH_MODE = $save;
 341  
 342          if (!is_object($rs)) {
 343              return FALSE;
 344          }
 345  
 346          $indexes = array();
 347          while ($row = $rs->FetchRow()) {
 348              if (!$primary && $row[5]) continue;
 349              
 350              $indexes[$row[0]]['unique'] = $row[6];
 351              $indexes[$row[0]]['columns'][] = $row[1];
 352          }
 353          return $indexes;
 354      }
 355      
 356  	function MetaForeignKeys($table, $owner=false, $upper=false)
 357      {
 358      global $ADODB_FETCH_MODE;
 359      
 360          $save = $ADODB_FETCH_MODE;
 361          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 362          $table = $this->qstr(strtoupper($table));
 363          
 364          $sql = 
 365  "select object_name(constid) as constraint_name,
 366      col_name(fkeyid, fkey) as column_name,
 367      object_name(rkeyid) as referenced_table_name,
 368         col_name(rkeyid, rkey) as referenced_column_name
 369  from sysforeignkeys
 370  where upper(object_name(fkeyid)) = $table
 371  order by constraint_name, referenced_table_name, keyno";
 372          
 373          $constraints =& $this->GetArray($sql);
 374          
 375          $ADODB_FETCH_MODE = $save;
 376          
 377          $arr = false;
 378          foreach($constraints as $constr) {
 379              //print_r($constr);

 380              $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3]; 
 381          }
 382          if (!$arr) return false;
 383          
 384          $arr2 = false;
 385          
 386          foreach($arr as $k => $v) {
 387              foreach($v as $a => $b) {
 388                  if ($upper) $a = strtoupper($a);
 389                  $arr2[$a] = $b;
 390              }
 391          }
 392          return $arr2;
 393      }
 394  
 395      //From: Fernando Moreira <FMoreira@imediata.pt>

 396  	function MetaDatabases() 
 397      { 
 398          if(@mssql_select_db("master")) { 
 399                   $qry=$this->metaDatabasesSQL; 
 400                   if($rs=@mssql_query($qry)){ 
 401                           $tmpAr=$ar=array(); 
 402                           while($tmpAr=@mssql_fetch_row($rs)) 
 403                                   $ar[]=$tmpAr[0]; 
 404                          @mssql_select_db($this->databaseName); 
 405                           if(sizeof($ar)) 
 406                                   return($ar); 
 407                           else 
 408                                   return(false); 
 409                   } else { 
 410                           @mssql_select_db($this->databaseName); 
 411                           return(false); 
 412                   } 
 413           } 
 414           return(false); 
 415      } 
 416  
 417      // "Stein-Aksel Basma" <basma@accelero.no>

 418      // tested with MSSQL 2000

 419      function &MetaPrimaryKeys($table)
 420      {
 421      global $ADODB_FETCH_MODE;
 422      
 423          $schema = '';
 424          $this->_findschema($table,$schema);
 425          if (!$schema) $schema = $this->database;
 426          if ($schema) $schema = "and k.table_catalog like '$schema%'"; 
 427  
 428          $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
 429          information_schema.table_constraints tc 
 430          where tc.constraint_name = k.constraint_name and tc.constraint_type =
 431          'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
 432          
 433          $savem = $ADODB_FETCH_MODE;
 434          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 435          $a = $this->GetCol($sql);
 436          $ADODB_FETCH_MODE = $savem;
 437          
 438          if ($a && sizeof($a)>0) return $a;
 439          return false;      
 440      }
 441  
 442      
 443      function &MetaTables($ttype=false,$showSchema=false,$mask=false) 
 444      {
 445          if ($mask) {
 446              $save = $this->metaTablesSQL;
 447              $mask = $this->qstr(($mask));
 448              $this->metaTablesSQL .= " AND name like $mask";
 449          }
 450          $ret =& ADOConnection::MetaTables($ttype,$showSchema);
 451          
 452          if ($mask) {
 453              $this->metaTablesSQL = $save;
 454          }
 455          return $ret;
 456      }
 457   
 458  	function SelectDB($dbName) 
 459      {
 460          $this->databaseName = $dbName;
 461          if ($this->_connectionID) {
 462              return @mssql_select_db($dbName);        
 463          }
 464          else return false;    
 465      }
 466      
 467  	function ErrorMsg() 
 468      {
 469          if (empty($this->_errorMsg)){
 470              $this->_errorMsg = mssql_get_last_message();
 471          }
 472          return $this->_errorMsg;
 473      }
 474      
 475  	function ErrorNo() 
 476      {
 477          if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
 478          if (empty($this->_errorMsg)) {
 479              $this->_errorMsg = mssql_get_last_message();
 480          }
 481          $id = @mssql_query("select @@ERROR",$this->_connectionID);
 482          if (!$id) return false;
 483          $arr = mssql_fetch_array($id);
 484          @mssql_free_result($id);
 485          if (is_array($arr)) return $arr[0];
 486         else return -1;
 487      }
 488      
 489      // returns true or false

 490  	function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
 491      {
 492          if (!function_exists('mssql_pconnect')) return null;
 493          $this->_connectionID = mssql_connect($argHostname,$argUsername,$argPassword);
 494          if ($this->_connectionID === false) return false;
 495          if ($argDatabasename) return $this->SelectDB($argDatabasename);
 496          return true;    
 497      }
 498      
 499      
 500      // returns true or false

 501  	function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 502      {
 503          if (!function_exists('mssql_pconnect')) return null;
 504          $this->_connectionID = mssql_pconnect($argHostname,$argUsername,$argPassword);
 505          if ($this->_connectionID === false) return false;
 506          
 507          // persistent connections can forget to rollback on crash, so we do it here.

 508          if ($this->autoRollback) {
 509              $cnt = $this->GetOne('select @@TRANCOUNT');
 510              while (--$cnt >= 0) $this->Execute('ROLLBACK TRAN'); 
 511          }
 512          if ($argDatabasename) return $this->SelectDB($argDatabasename);
 513          return true;    
 514      }
 515      
 516  	function Prepare($sql)
 517      {
 518          $sqlarr = explode('?',$sql);
 519          if (sizeof($sqlarr) <= 1) return $sql;
 520          $sql2 = $sqlarr[0];
 521          for ($i = 1, $max = sizeof($sqlarr); $i < $max; $i++) {
 522              $sql2 .=  '@P'.($i-1) . $sqlarr[$i];
 523          } 
 524          return array($sql,$this->qstr($sql2),$max);
 525      }
 526      
 527  	function PrepareSP($sql)
 528      {
 529          if (!$this->_has_mssql_init) {
 530              ADOConnection::outp( "PrepareSP: mssql_init only available since PHP 4.1.0");
 531              return $sql;
 532          }
 533          $stmt = mssql_init($sql,$this->_connectionID);
 534          if (!$stmt)  return $sql;
 535          return array($sql,$stmt);
 536      }
 537      
 538      // returns concatenated string

 539      // MSSQL requires integers to be cast as strings

 540      // automatically cast every datatype to VARCHAR(255)

 541      // @author David Rogers (introspectshun)

 542      function Concat()
 543      {
 544              $s = "";
 545              $arr = func_get_args();
 546  
 547              // Split single record on commas, if possible

 548              if (sizeof($arr) == 1) {
 549                  foreach ($arr as $arg) {
 550                      $args = explode(',', $arg);
 551                  }
 552                  $arr = $args;
 553              }
 554  
 555              array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
 556              $s = implode('+',$arr);
 557              if (sizeof($arr) > 0) return "$s";
 558              
 559              return '';
 560      }
 561      
 562      /* 

 563      Usage:

 564          $stmt = $db->PrepareSP('SP_RUNSOMETHING'); -- takes 2 params, @myid and @group

 565          

 566          # note that the parameter does not have @ in front!

 567          $db->Parameter($stmt,$id,'myid');

 568          $db->Parameter($stmt,$group,'group',false,64);

 569          $db->Execute($stmt);

 570          

 571          @param $stmt Statement returned by Prepare() or PrepareSP().

 572          @param $var PHP variable to bind to. Can set to null (for isNull support).

 573          @param $name Name of stored procedure variable name to bind to.

 574          @param [$isOutput] Indicates direction of parameter 0/false=IN  1=OUT  2= IN/OUT. This is ignored in oci8.

 575          @param [$maxLen] Holds an maximum length of the variable.

 576          @param [$type] The data type of $var. Legal values depend on driver.

 577          

 578          See mssql_bind documentation at php.net.

 579      */
 580  	function Parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false)
 581      {
 582          if (!$this->_has_mssql_init) {
 583              ADOConnection::outp( "Parameter: mssql_bind only available since PHP 4.1.0");
 584              return $sql;
 585          }
 586  
 587          $isNull = is_null($var); // php 4.0.4 and above...

 588              
 589          if ($type === false) 
 590              switch(gettype($var)) {
 591              default:
 592              case 'string': $type = SQLCHAR; break;
 593              case 'double': $type = SQLFLT8; break;
 594              case 'integer': $type = SQLINT4; break;
 595              case 'boolean': $type = SQLINT1; break; # SQLBIT not supported in 4.1.0

 596              }
 597          
 598          if  ($this->debug) {
 599              $prefix = ($isOutput) ? 'Out' : 'In';
 600              $ztype = (empty($type)) ? 'false' : $type;
 601              ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);");
 602          }
 603          /*

 604              See http://phplens.com/lens/lensforum/msgs.php?id=7231

 605              

 606              RETVAL is HARD CODED into php_mssql extension:

 607              The return value (a long integer value) is treated like a special OUTPUT parameter, 

 608              called "RETVAL" (without the @). See the example at mssql_execute to 

 609              see how it works. - type: one of this new supported PHP constants. 

 610                  SQLTEXT, SQLVARCHAR,SQLCHAR, SQLINT1,SQLINT2, SQLINT4, SQLBIT,SQLFLT8 

 611          */
 612          if ($name !== 'RETVAL') $name = '@'.$name;
 613          return mssql_bind($stmt[1], $name, $var, $type, $isOutput, $isNull, $maxLen);
 614      }
 615      
 616      /* 

 617          Unfortunately, it appears that mssql cannot handle varbinary > 255 chars

 618          So all your blobs must be of type "image".

 619          

 620          Remember to set in php.ini the following...

 621          

 622          ; Valid range 0 - 2147483647. Default = 4096. 

 623          mssql.textlimit = 0 ; zero to pass through 

 624  

 625          ; Valid range 0 - 2147483647. Default = 4096. 

 626          mssql.textsize = 0 ; zero to pass through 

 627      */
 628  	function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
 629      {
 630      
 631          if (strtoupper($blobtype) == 'CLOB') {
 632              $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
 633              return $this->Execute($sql) != false;
 634          }
 635          $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
 636          return $this->Execute($sql) != false;
 637      }
 638      
 639      // returns query ID if successful, otherwise false

 640  	function _query($sql,$inputarr)
 641      {
 642          $this->_errorMsg = false;
 643          if (is_array($inputarr)) {
 644              
 645              # bind input params with sp_executesql: 

 646              # see http://www.quest-pipelines.com/newsletter-v3/0402_F.htm

 647              # works only with sql server 7 and newer

 648              if (!is_array($sql)) $sql = $this->Prepare($sql);
 649              $params = '';
 650              $decl = '';
 651              $i = 0;
 652              foreach($inputarr as $v) {
 653                  if ($decl) {
 654                      $decl .= ', ';
 655                      $params .= ', ';
 656                  }    
 657                  if (is_string($v)) {
 658                      $len = strlen($v);
 659                      if ($len == 0) $len = 1;
 660                      
 661                      if ($len > 4000 ) {
 662                          // NVARCHAR is max 4000 chars. Let's use NTEXT

 663                          $decl .= "@P$i NTEXT";
 664                      } else {
 665                          $decl .= "@P$i NVARCHAR($len)";
 666                      }
 667  
 668                      $params .= "@P$i=N". (strncmp($v,"'",1)==0? $v : $this->qstr($v));
 669                  } else if (is_integer($v)) {
 670                      $decl .= "@P$i INT";
 671                      $params .= "@P$i=".$v;
 672                  } else if (is_float($v)) {
 673                      $decl .= "@P$i FLOAT";
 674                      $params .= "@P$i=".$v;
 675                  } else if (is_bool($v)) {
 676                      $decl .= "@P$i INT"; # Used INT just in case BIT in not supported on the user's MSSQL version. It will cast appropriately.

 677                      $params .= "@P$i=".(($v)?'1':'0'); # True == 1 in MSSQL BIT fields and acceptable for storing logical true in an int field

 678                  } else {
 679                      $decl .= "@P$i CHAR"; # Used char because a type is required even when the value is to be NULL.

 680                      $params .= "@P$i=NULL";
 681                      }
 682                  $i += 1;
 683              }
 684              $decl = $this->qstr($decl);
 685              if ($this->debug) ADOConnection::outp("<font size=-1>sp_executesql N{$sql[1]},N$decl,$params</font>");
 686              $rez = mssql_query("sp_executesql N{$sql[1]},N$decl,$params");
 687              
 688          } else if (is_array($sql)) {
 689              # PrepareSP()

 690              $rez = mssql_execute($sql[1]);
 691              
 692          } else {
 693              $rez = mssql_query($sql,$this->_connectionID);
 694          }
 695          return $rez;
 696      }
 697      
 698      // returns true or false

 699  	function _close()
 700      { 
 701          if ($this->transCnt) $this->RollbackTrans();
 702          $rez = @mssql_close($this->_connectionID);
 703          $this->_connectionID = false;
 704          return $rez;
 705      }
 706      
 707      // mssql uses a default date like Dec 30 2000 12:00AM

 708  	function UnixDate($v)
 709      {
 710          return ADORecordSet_array_mssql::UnixDate($v);
 711      }
 712      
 713  	function UnixTimeStamp($v)
 714      {
 715          return ADORecordSet_array_mssql::UnixTimeStamp($v);
 716      }    
 717  }
 718      
 719  /*--------------------------------------------------------------------------------------

 720       Class Name: Recordset

 721  --------------------------------------------------------------------------------------*/
 722  
 723  class ADORecordset_mssql extends ADORecordSet {    
 724  
 725      var $databaseType = "mssql";
 726      var $canSeek = true;
 727      var $hasFetchAssoc; // see http://phplens.com/lens/lensforum/msgs.php?id=6083

 728      // _mths works only in non-localised system

 729      
 730  	function ADORecordset_mssql($id,$mode=false)
 731      {
 732          // freedts check...

 733          $this->hasFetchAssoc = function_exists('mssql_fetch_assoc');
 734  
 735          if ($mode === false) { 
 736              global $ADODB_FETCH_MODE;
 737              $mode = $ADODB_FETCH_MODE;
 738          }
 739          $this->fetchMode = $mode;
 740          return $this->ADORecordSet($id,$mode);
 741      }
 742      
 743      
 744  	function _initrs()
 745      {
 746      GLOBAL $ADODB_COUNTRECS;    
 747          $this->_numOfRows = ($ADODB_COUNTRECS)? @mssql_num_rows($this->_queryID):-1;
 748          $this->_numOfFields = @mssql_num_fields($this->_queryID);
 749      }
 750      
 751  
 752      //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>

 753      // get next resultset - requires PHP 4.0.5 or later

 754  	function NextRecordSet()
 755      {
 756          if (!mssql_next_result($this->_queryID)) return false;
 757          $this->_inited = false;
 758          $this->bind = false;
 759          $this->_currentRow = -1;
 760          $this->Init();
 761          return true;
 762      }
 763  
 764      /* Use associative array to get fields array */

 765  	function Fields($colname)
 766      {
 767          if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
 768          if (!$this->bind) {
 769              $this->bind = array();
 770              for ($i=0; $i < $this->_numOfFields; $i++) {
 771                  $o = $this->FetchField($i);
 772                  $this->bind[strtoupper($o->name)] = $i;
 773              }
 774          }
 775          
 776           return $this->fields[$this->bind[strtoupper($colname)]];
 777      }
 778      
 779      /*    Returns: an object containing field information. 

 780          Get column information in the Recordset object. fetchField() can be used in order to obtain information about

 781          fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by

 782          fetchField() is retrieved.    */
 783  
 784  	function FetchField($fieldOffset = -1) 
 785      {
 786          if ($fieldOffset != -1) {
 787              return @mssql_fetch_field($this->_queryID, $fieldOffset);
 788          }
 789          else if ($fieldOffset == -1) {    /*    The $fieldOffset argument is not provided thus its -1     */
 790              return @mssql_fetch_field($this->_queryID);
 791          }
 792          return null;
 793      }
 794      
 795  	function _seek($row) 
 796      {
 797          return @mssql_data_seek($this->_queryID, $row);
 798      }
 799  
 800      // speedup

 801  	function MoveNext() 
 802      {
 803          if ($this->EOF) return false;
 804          
 805          $this->_currentRow++;
 806          
 807          if ($this->fetchMode & ADODB_FETCH_ASSOC) {
 808              if ($this->fetchMode & ADODB_FETCH_NUM) {
 809                  //ADODB_FETCH_BOTH mode

 810                  $this->fields = @mssql_fetch_array($this->_queryID);
 811              }
 812              else {
 813                  if ($this->hasFetchAssoc) {// only for PHP 4.2.0 or later
 814                       $this->fields = @mssql_fetch_assoc($this->_queryID);
 815                  } else {
 816                      $flds = @mssql_fetch_array($this->_queryID);
 817                      if (is_array($flds)) {
 818                          $fassoc = array();
 819                          foreach($flds as $k => $v) {
 820                              if (is_numeric($k)) continue;
 821                              $fassoc[$k] = $v;
 822                          }
 823                          $this->fields = $fassoc;
 824                      } else
 825                          $this->fields = false;
 826                  }
 827              }
 828              
 829              if (is_array($this->fields)) {
 830                  if (ADODB_ASSOC_CASE == 0) {
 831                      foreach($this->fields as $k=>$v) {
 832                          $this->fields[strtolower($k)] = $v;
 833                      }
 834                  } else if (ADODB_ASSOC_CASE == 1) {
 835                      foreach($this->fields as $k=>$v) {
 836                          $this->fields[strtoupper($k)] = $v;
 837                      }
 838                  }
 839              }
 840          } else {
 841              $this->fields = @mssql_fetch_row($this->_queryID);
 842          }
 843          if ($this->fields) return true;
 844          $this->EOF = true;
 845          
 846          return false;
 847      }
 848  
 849      
 850      // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4

 851      // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!

 852  	function _fetch($ignore_fields=false) 
 853      {
 854          if ($this->fetchMode & ADODB_FETCH_ASSOC) {
 855              if ($this->fetchMode & ADODB_FETCH_NUM) {
 856                  //ADODB_FETCH_BOTH mode

 857                  $this->fields = @mssql_fetch_array($this->_queryID);
 858              } else {
 859                  if ($this->hasFetchAssoc) // only for PHP 4.2.0 or later
 860                      $this->fields = @mssql_fetch_assoc($this->_queryID);
 861                  else {
 862                      $this->fields = @mssql_fetch_array($this->_queryID);
 863                      if (@is_array($$this->fields)) {
 864                          $fassoc = array();
 865                          foreach($$this->fields as $k => $v) {
 866                              if (is_integer($k)) continue;
 867                              $fassoc[$k] = $v;
 868                          }
 869                          $this->fields = $fassoc;
 870                      }
 871                  }
 872              }
 873              
 874              if (!$this->fields) {
 875              } else if (ADODB_ASSOC_CASE == 0) {
 876                  foreach($this->fields as $k=>$v) {
 877                      $this->fields[strtolower($k)] = $v;
 878                  }
 879              } else if (ADODB_ASSOC_CASE == 1) {
 880                  foreach($this->fields as $k=>$v) {
 881                      $this->fields[strtoupper($k)] = $v;
 882                  }
 883              }
 884          } else {
 885              $this->fields = @mssql_fetch_row($this->_queryID);
 886          }
 887          return $this->fields;
 888      }
 889      
 890      /*    close() only needs to be called if you are worried about using too much memory while your script

 891          is running. All associated result memory for the specified result identifier will automatically be freed.    */
 892  
 893  	function _close() 
 894      {
 895          $rez = mssql_free_result($this->_queryID);    
 896          $this->_queryID = false;
 897          return $rez;
 898      }
 899      // mssql uses a default date like Dec 30 2000 12:00AM

 900  	function UnixDate($v)
 901      {
 902          return ADORecordSet_array_mssql::UnixDate($v);
 903      }
 904      
 905  	function UnixTimeStamp($v)
 906      {
 907          return ADORecordSet_array_mssql::UnixTimeStamp($v);
 908      }
 909      
 910  }
 911  
 912  
 913  class ADORecordSet_array_mssql extends ADORecordSet_array {
 914  	function ADORecordSet_array_mssql($id=-1,$mode=false) 
 915      {
 916          $this->ADORecordSet_array($id,$mode);
 917      }
 918      
 919          // mssql uses a default date like Dec 30 2000 12:00AM

 920  	function UnixDate($v)
 921      {
 922      
 923          if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
 924          
 925      global $ADODB_mssql_mths,$ADODB_mssql_date_order;
 926      
 927          //Dec 30 2000 12:00AM 

 928          if ($ADODB_mssql_date_order == 'dmy') {
 929              if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
 930                  return parent::UnixDate($v);
 931              }
 932              if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 933              
 934              $theday = $rr[1];
 935              $themth =  substr(strtoupper($rr[2]),0,3);
 936          } else {
 937              if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
 938                  return parent::UnixDate($v);
 939              }
 940              if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 941              
 942              $theday = $rr[2];
 943              $themth = substr(strtoupper($rr[1]),0,3);
 944          }
 945          $themth = $ADODB_mssql_mths[$themth];
 946          if ($themth <= 0) return false;
 947          // h-m-s-MM-DD-YY

 948          return  mktime(0,0,0,$themth,$theday,$rr[3]);
 949      }
 950      
 951  	function UnixTimeStamp($v)
 952      {
 953      
 954          if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
 955          
 956      global $ADODB_mssql_mths,$ADODB_mssql_date_order;
 957      
 958          //Dec 30 2000 12:00AM

 959           if ($ADODB_mssql_date_order == 'dmy') {
 960               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})|"
 961              ,$v, $rr)) return parent::UnixTimeStamp($v);
 962              if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 963          
 964              $theday = $rr[1];
 965              $themth =  substr(strtoupper($rr[2]),0,3);
 966          } else {
 967              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})|"
 968              ,$v, $rr)) return parent::UnixTimeStamp($v);
 969              if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 970          
 971              $theday = $rr[2];
 972              $themth = substr(strtoupper($rr[1]),0,3);
 973          }
 974          
 975          $themth = $ADODB_mssql_mths[$themth];
 976          if ($themth <= 0) return false;
 977          
 978          switch (strtoupper($rr[6])) {
 979          case 'P':
 980              if ($rr[4]<12) $rr[4] += 12;
 981              break;
 982          case 'A':
 983              if ($rr[4]==12) $rr[4] = 0;
 984              break;
 985          default:
 986              break;
 987          }
 988          // h-m-s-MM-DD-YY

 989          return  mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
 990      }
 991  }
 992  
 993  /*

 994  Code Example 1:

 995  

 996  select     object_name(constid) as constraint_name,

 997             object_name(fkeyid) as table_name, 

 998          col_name(fkeyid, fkey) as column_name,

 999      object_name(rkeyid) as referenced_table_name,

1000         col_name(rkeyid, rkey) as referenced_column_name

1001  from sysforeignkeys

1002  where object_name(fkeyid) = x

1003  order by constraint_name, table_name, referenced_table_name,  keyno

1004  

1005  Code Example 2:

1006  select     constraint_name,

1007      column_name,

1008      ordinal_position

1009  from information_schema.key_column_usage

1010  where constraint_catalog = db_name()

1011  and table_name = x

1012  order by constraint_name, ordinal_position

1013  

1014  http://www.databasejournal.com/scripts/article.php/1440551

1015  */
1016  
1017  ?>

title

Description

title

Description

title

Description

title

title

Body