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

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