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

   1 <?php
   2 /*
   3 V5.14 8 Sept 2011  (c) 2000-2011 John Lim (jlim#natsoft.com). All rights reserved.
   4   Released under both BSD license and Lesser GPL library license.
   5   Whenever there is any discrepancy between the two licenses,
   6   the BSD license will take precedence.
   7 Set tabs to 4 for best viewing.
   8
   9   Latest version is available at http://adodb.sourceforge.net
  10
  11   Native mssql driver. Requires mssql client. Works on Windows.
  12     http://www.microsoft.com/sql/technologies/php/default.mspx
  13   To configure for Unix, see
  14         http://phpbuilder.com/columns/alberto20000919.php3
  15
  16     $stream = sqlsrv_get_field($stmt, $index, SQLSRV_SQLTYPE_STREAM(SQLSRV_ENC_BINARY));
  17     stream_filter_append($stream, "convert.iconv.ucs-2/utf-8"); // Voila, UTF-8 can be read directly from $stream
  18
  19 */
  20
  21 // security - hide paths
  22 if (!defined('ADODB_DIR')) die();
  23
  24 if (!function_exists('sqlsrv_configure')) {
  25         die("mssqlnative extension not installed");
  26 }
  27
  28 if (!function_exists('sqlsrv_set_error_handling')) {
  29         function sqlsrv_set_error_handling($constant) {
  30                 sqlsrv_configure("WarningsReturnAsErrors", $constant);
  31         }
  32 }
  33 if (!function_exists('sqlsrv_log_set_severity')) {
  34         function sqlsrv_log_set_severity($constant) {
  35                 sqlsrv_configure("LogSeverity", $constant);
  36         }
  37 }
  38 if (!function_exists('sqlsrv_log_set_subsystems')) {
  39         function sqlsrv_log_set_subsystems($constant) {
  40                 sqlsrv_configure("LogSubsystems", $constant);
  41         }
  42 }
  43
  44
  45 //----------------------------------------------------------------
  46 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002
  47 // and this causes tons of problems because localized versions of
  48 // MSSQL will return the dates in dmy or  mdy order; and also the
  49 // month strings depends on what language has been configured. The
  50 // following two variables allow you to control the localization
  51 // settings - Ugh.
  52 //
  53 // MORE LOCALIZATION INFO
  54 // ----------------------
  55 // To configure datetime, look for and modify sqlcommn.loc,
  56 //      typically found in c:\mssql\install
  57 // Also read :
  58 //       http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918
  59 // Alternatively use:
  60 //         CONVERT(char(12),datecol,120)
  61 //
  62 // Also if your month is showing as month-1,
  63 //   e.g. Jan 13, 2002 is showing as 13/0/2002, then see
  64 //     http://phplens.com/lens/lensforum/msgs.php?id=7048&x=1
  65 //   it's a localisation problem.
  66 //----------------------------------------------------------------
  67
  68
  69 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc
  70 if (ADODB_PHPVER >= 0x4300) {
  71 // docs say 4.2.0, but testing shows only since 4.3.0 does it work!
  72         ini_set('mssql.datetimeconvert',0);
  73 } else {
  74     global $ADODB_mssql_mths;           // array, months must be upper-case
  75         $ADODB_mssql_date_order = 'mdy';
  76         $ADODB_mssql_mths = array(
  77                 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
  78                 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
  79 }
  80
  81 //---------------------------------------------------------------------------
  82 // Call this to autoset $ADODB_mssql_date_order at the beginning of your code,
  83 // just after you connect to the database. Supports mdy and dmy only.
  84 // Not required for PHP 4.2.0 and above.
  85 function AutoDetect_MSSQL_Date_Order($conn)
  86 {
  87     global $ADODB_mssql_date_order;
  88         $adate = $conn->GetOne('select getdate()');
  89         if ($adate) {
  90                 $anum = (int) $adate;
  91                 if ($anum > 0) {
  92                         if ($anum > 31) {
  93                                 //ADOConnection::outp( "MSSQL: YYYY-MM-DD date format not supported currently");
  94                         } else
  95                                 $ADODB_mssql_date_order = 'dmy';
  96                 } else
  97                         $ADODB_mssql_date_order = 'mdy';
  98         }
  99 }
 100
 101 class ADODB_mssqlnative extends ADOConnection {
 102         var $databaseType = "mssqlnative";
 103         var $dataProvider = "mssqlnative";
 104         var $replaceQuote = "''"; // string to use to replace quotes
 105         var $fmtDate = "'Y-m-d'";
 106         var $fmtTimeStamp = "'Y-m-d H:i:s'";
 107         var $hasInsertID = true;
 108         var $substr = "substring";
 109         var $length = 'len';
 110         var $hasAffectedRows = true;
 111         var $poorAffectedRows = false;
 112         var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'";
 113         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'))";
 114         var $metaColumnsSQL = # xtype==61 is datetime
 115         "select c.name,t.name,c.length,
 116             (case when c.xusertype=61 then 0 else c.xprec end),
 117             (case when c.xusertype=61 then 0 else c.xscale end)
 118             from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
 119         var $hasTop = 'top';            // support mssql SELECT TOP 10 * FROM TABLE
 120         var $hasGenID = true;
 121         var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
 122         var $sysTimeStamp = 'GetDate()';
 123         var $maxParameterLen = 4000;
 124         var $arrayClass = 'ADORecordSet_array_mssqlnative';
 125         var $uniqueSort = true;
 126         var $leftOuter = '*=';
 127         var $rightOuter = '=*';
 128         var $ansiOuter = true; // for mssql7 or later
 129         var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
 130         var $uniqueOrderBy = true;
 131         var $_bindInputArray = true;
 132         var $_dropSeqSQL = "drop table %s";
 133
 134         function ADODB_mssqlnative()
 135         {
 136         if ($this->debug) {
 137             error_log("<pre>");
 138             sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL );
 139             sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL );
 140             sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
 141             sqlsrv_configure('warnings_return_as_errors', 0);
 142         } else {
 143             sqlsrv_set_error_handling(0);
 144             sqlsrv_log_set_severity(0);
 145             sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
 146             sqlsrv_configure('warnings_return_as_errors', 0);
 147         }
 148         }
 149
 150         function ServerInfo()
 151         {
 152         global $ADODB_FETCH_MODE;
 153                 if ($this->fetchMode === false) {
 154                         $savem = $ADODB_FETCH_MODE;
 155                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 156                 } else
 157                         $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
 158                 $arrServerInfo = sqlsrv_server_info($this->_connectionID);
 159                 $ADODB_FETCH_MODE = $savem;
 160                 $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase'];
 161                 $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']);
 162                 return $arr;
 163         }
 164
 165         function IfNull( $field, $ifNull )
 166         {
 167                 return " ISNULL($field, $ifNull) "; // if MS SQL Server
 168         }
 169
 170         function _insertid()
 171         {
 172         // SCOPE_IDENTITY()
 173         // Returns the last IDENTITY value inserted into an IDENTITY column in
 174         // the same scope. A scope is a module -- a stored procedure, trigger,
 175         // function, or batch. Thus, two statements are in the same scope if
 176         // they are in the same stored procedure, function, or batch.
 177                 return $this->GetOne($this->identitySQL);
 178         }
 179
 180         function _affectedrows()
 181         {
 182         return sqlsrv_rows_affected($this->_queryID);
 183         }
 184
 185         function CreateSequence($seq='adodbseq',$start=1)
 186         {
 187                 if($this->debug) error_log("<hr>CreateSequence($seq,$start)");
 188         sqlsrv_begin_transaction($this->_connectionID);
 189                 $start -= 1;
 190                 $this->Execute("create table $seq (id int)");//was float(53)
 191                 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
 192                 if (!$ok) {
 193             if($this->debug) error_log("<hr>Error: ROLLBACK");
 194             sqlsrv_rollback($this->_connectionID);
 195                         return false;
 196                 }
 197         sqlsrv_commit($this->_connectionID);
 198                 return true;
 199         }
 200
 201         function GenID($seq='adodbseq',$start=1)
 202         {
 203         if($this->debug) error_log("<hr>GenID($seq,$start)");
 204         sqlsrv_begin_transaction($this->_connectionID);
 205                 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
 206                 if (!$ok) {
 207                         $this->Execute("create table $seq (id int)");
 208                         $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
 209                         if (!$ok) {
 210                 if($this->debug) error_log("<hr>Error: ROLLBACK");
 211                 sqlsrv_rollback($this->_connectionID);
 212                                 return false;
 213                         }
 214                         sqlsrv_commit($this->_connectionID);
 215                         return $start;
 216                 }
 217                 $num = $this->GetOne("select id from $seq");
 218         sqlsrv_commit($this->_connectionID);
 219         if($this->debug) error_log(" Returning: $num");
 220                 return $num;
 221         }
 222
 223         // Format date column in sql string given an input format that understands Y M D
 224         function SQLDate($fmt, $col=false)
 225         {
 226                 if (!$col) $col = $this->sysTimeStamp;
 227                 $s = '';
 228
 229                 $len = strlen($fmt);
 230                 for ($i=0; $i < $len; $i++) {
 231                         if ($s) $s .= '+';
 232                         $ch = $fmt[$i];
 233                         switch($ch) {
 234                         case 'Y':
 235                         case 'y':
 236                                 $s .= "datename(yyyy,$col)";
 237                                 break;
 238                         case 'M':
 239                                 $s .= "convert(char(3),$col,0)";
 240                                 break;
 241                         case 'm':
 242                                 $s .= "replace(str(month($col),2),' ','0')";
 243                                 break;
 244                         case 'Q':
 245                         case 'q':
 246                                 $s .= "datename(quarter,$col)";
 247                                 break;
 248                         case 'D':
 249                         case 'd':
 250                                 $s .= "replace(str(day($col),2),' ','0')";
 251                                 break;
 252                         case 'h':
 253                                 $s .= "substring(convert(char(14),$col,0),13,2)";
 254                                 break;
 255
 256                         case 'H':
 257                                 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
 258                                 break;
 259
 260                         case 'i':
 261                                 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
 262                                 break;
 263                         case 's':
 264                                 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
 265                                 break;
 266                         case 'a':
 267                         case 'A':
 268                                 $s .= "substring(convert(char(19),$col,0),18,2)";
 269                                 break;
 270
 271                         default:
 272                                 if ($ch == '\\') {
 273                                         $i++;
 274                                         $ch = substr($fmt,$i,1);
 275                                 }
 276                                 $s .= $this->qstr($ch);
 277                                 break;
 278                         }
 279                 }
 280                 return $s;
 281         }
 282
 283
 284         function BeginTrans()
 285         {
 286                 if ($this->transOff) return true;
 287                 $this->transCnt += 1;
 288         if ($this->debug) error_log('<hr>begin transaction');
 289                 sqlsrv_begin_transaction($this->_connectionID);
 290                 return true;
 291         }
 292
 293         function CommitTrans($ok=true)
 294         {
 295                 if ($this->transOff) return true;
 296         if ($this->debug) error_log('<hr>commit transaction');
 297                 if (!$ok) return $this->RollbackTrans();
 298                 if ($this->transCnt) $this->transCnt -= 1;
 299                 sqlsrv_commit($this->_connectionID);
 300                 return true;
 301         }
 302         function RollbackTrans()
 303         {
 304                 if ($this->transOff) return true;
 305         if ($this->debug) error_log('<hr>rollback transaction');
 306                 if ($this->transCnt) $this->transCnt -= 1;
 307                 sqlsrv_rollback($this->_connectionID);
 308                 return true;
 309         }
 310
 311         function SetTransactionMode( $transaction_mode )
 312         {
 313                 $this->_transmode  = $transaction_mode;
 314                 if (empty($transaction_mode)) {
 315                         $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
 316                         return;
 317                 }
 318                 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
 319                 $this->Execute("SET TRANSACTION ".$transaction_mode);
 320         }
 321
 322         /*
 323                 Usage:
 324
 325                 $this->BeginTrans();
 326                 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
 327
 328                 # some operation on both tables table1 and table2
 329
 330                 $this->CommitTrans();
 331
 332                 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
 333         */
 334         function RowLock($tables,$where,$col='1 as adodbignore')
 335         {
 336                 if ($col == '1 as adodbignore') $col = 'top 1 null as ignore';
 337                 if (!$this->transCnt) $this->BeginTrans();
 338                 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where");
 339         }
 340
 341         function SelectDB($dbName)
 342         {
 343                 $this->database = $dbName;
 344                 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
 345                 if ($this->_connectionID) {
 346             $rs = $this->Execute('USE '.$dbName);
 347             if($rs) {
 348                 return true;
 349             } else return false;
 350                 }
 351                 else return false;
 352         }
 353
 354         function ErrorMsg()
 355         {
 356                 $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
 357                 if($retErrors != null) {
 358                         foreach($retErrors as $arrError) {
 359                                 $this->_errorMsg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n";
 360                                 $this->_errorMsg .= "Error Code: ".$arrError[ 'code']."\n";
 361                                 $this->_errorMsg .= "Message: ".$arrError[ 'message']."\n";
 362                         }
 363                 } else {
 364                         $this->_errorMsg = "No errors found";
 365                 }
 366                 return $this->_errorMsg;
 367         }
 368
 369         function ErrorNo()
 370         {
 371                 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
 372                 $err = sqlsrv_errors(SQLSRV_ERR_ALL);
 373         if($err[0]) return $err[0]['code'];
 374         else return -1;
 375         }
 376
 377         // returns true or false
 378         function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
 379         {
 380                 if (!function_exists('sqlsrv_connect')) return null;
 381         $connectionInfo = array("Database"=>$argDatabasename,'UID'=>$argUsername,'PWD'=>$argPassword);
 382         if ($this->debug) error_log("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true));
 383         //if ($this->debug) error_log("<hr>_connectionID before: ".serialize($this->_connectionID));
 384         if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) {
 385             if ($this->debug) error_log( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true));
 386             return false;
 387         }
 388         //if ($this->debug) error_log(" _connectionID after: ".serialize($this->_connectionID));
 389         //if ($this->debug) error_log("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>");
 390                 return true;
 391         }
 392
 393         // returns true or false
 394         function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 395         {
 396                 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
 397         return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
 398         }
 399
 400         function Prepare($sql)
 401         {
 402                 $stmt = sqlsrv_prepare( $this->_connectionID, $sql);
 403                 if (!$stmt)  return $sql;
 404                 return array($sql,$stmt);
 405         }
 406
 407         // returns concatenated string
 408     // MSSQL requires integers to be cast as strings
 409     // automatically cast every datatype to VARCHAR(255)
 410     // @author David Rogers (introspectshun)
 411     function Concat()
 412     {
 413         $s = "";
 414         $arr = func_get_args();
 415
 416         // Split single record on commas, if possible
 417         if (sizeof($arr) == 1) {
 418             foreach ($arr as $arg) {
 419                 $args = explode(',', $arg);
 420             }
 421             $arr = $args;
 422         }
 423
 424         array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
 425         $s = implode('+',$arr);
 426         if (sizeof($arr) > 0) return "$s";
 427
 428                 return '';
 429     }
 430
 431         /*
 432                 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
 433                 So all your blobs must be of type "image".
 434
 435                 Remember to set in php.ini the following...
 436
 437                 ; Valid range 0 - 2147483647. Default = 4096.
 438                 mssql.textlimit = 0 ; zero to pass through
 439
 440                 ; Valid range 0 - 2147483647. Default = 4096.
 441                 mssql.textsize = 0 ; zero to pass through
 442         */
 443         function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
 444         {
 445
 446                 if (strtoupper($blobtype) == 'CLOB') {
 447                         $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
 448                         return $this->Execute($sql) != false;
 449                 }
 450                 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
 451                 return $this->Execute($sql) != false;
 452         }
 453
 454         // returns query ID if successful, otherwise false
 455         function _query($sql,$inputarr=false)
 456         {
 457                 $this->_errorMsg = false;
 458                 if (is_array($inputarr)) {
 459             $rez = sqlsrv_query($this->_connectionID,$sql,$inputarr);
 460                 } else if (is_array($sql)) {
 461             $rez = sqlsrv_query($this->_connectionID,$sql[1],$inputarr);
 462                 } else {
 463                         $rez = sqlsrv_query($this->_connectionID,$sql);
 464                 }
 465         if ($this->debug) error_log("<hr>running query: ".var_export($sql,true)."<hr>input array: ".var_export($inputarr,true)."<hr>result: ".var_export($rez,true));
 466         if(!$rez) $rez = false;
 467                 return $rez;
 468         }
 469
 470         // returns true or false
 471         function _close()
 472         {
 473                 if ($this->transCnt) $this->RollbackTrans();
 474                 $rez = @sqlsrv_close($this->_connectionID);
 475                 $this->_connectionID = false;
 476                 return $rez;
 477         }
 478
 479         // mssql uses a default date like Dec 30 2000 12:00AM
 480         static function UnixDate($v)
 481         {
 482                 return ADORecordSet_array_mssqlnative::UnixDate($v);
 483         }
 484
 485         static function UnixTimeStamp($v)
 486         {
 487                 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
 488         }
 489
 490         function &MetaIndexes($table,$primary=false, $owner = false)
 491         {
 492                 $table = $this->qstr($table);
 493
 494                 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
 495                         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,
 496                         CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
 497                         FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
 498                         INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
 499                         INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
 500                         WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
 501                         ORDER BY O.name, I.Name, K.keyno";
 502
 503                 global $ADODB_FETCH_MODE;
 504                 $save = $ADODB_FETCH_MODE;
 505         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 506         if ($this->fetchMode !== FALSE) {
 507                 $savem = $this->SetFetchMode(FALSE);
 508         }
 509
 510         $rs = $this->Execute($sql);
 511         if (isset($savem)) {
 512                 $this->SetFetchMode($savem);
 513         }
 514         $ADODB_FETCH_MODE = $save;
 515
 516         if (!is_object($rs)) {
 517                 return FALSE;
 518         }
 519
 520                 $indexes = array();
 521                 while ($row = $rs->FetchRow()) {
 522                         if (!$primary && $row[5]) continue;
 523
 524             $indexes[$row[0]]['unique'] = $row[6];
 525             $indexes[$row[0]]['columns'][] = $row[1];
 526         }
 527         return $indexes;
 528         }
 529
 530         function MetaForeignKeys($table, $owner=false, $upper=false)
 531         {
 532         global $ADODB_FETCH_MODE;
 533
 534                 $save = $ADODB_FETCH_MODE;
 535                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 536                 $table = $this->qstr(strtoupper($table));
 537
 538                 $sql =
 539             "select object_name(constid) as constraint_name,
 540                     col_name(fkeyid, fkey) as column_name,
 541                     object_name(rkeyid) as referenced_table_name,
 542                     col_name(rkeyid, rkey) as referenced_column_name
 543             from sysforeignkeys
 544             where upper(object_name(fkeyid)) = $table
 545             order by constraint_name, referenced_table_name, keyno";
 546
 547                 $constraints =& $this->GetArray($sql);
 548
 549                 $ADODB_FETCH_MODE = $save;
 550
 551                 $arr = false;
 552                 foreach($constraints as $constr) {
 553                         //print_r($constr);
 554                         $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
 555                 }
 556                 if (!$arr) return false;
 557
 558                 $arr2 = false;
 559
 560                 foreach($arr as $k => $v) {
 561                         foreach($v as $a => $b) {
 562                                 if ($upper) $a = strtoupper($a);
 563                                 $arr2[$a] = $b;
 564                         }
 565                 }
 566                 return $arr2;
 567         }
 568
 569         //From: Fernando Moreira <FMoreira@imediata.pt>
 570         function MetaDatabases()
 571         {
 572             $this->SelectDB("master");
 573         $rs =& $this->Execute($this->metaDatabasesSQL);
 574         $rows = $rs->GetRows();
 575         $ret = array();
 576         for($i=0;$i<count($rows);$i++) {
 577             $ret[] = $rows[$i][0];
 578         }
 579         $this->SelectDB($this->database);
 580         if($ret)
 581             return $ret;
 582         else
 583             return false;
 584         }
 585
 586         // "Stein-Aksel Basma" <basma@accelero.no>
 587         // tested with MSSQL 2000
 588         function &MetaPrimaryKeys($table)
 589         {
 590         global $ADODB_FETCH_MODE;
 591
 592                 $schema = '';
 593                 $this->_findschema($table,$schema);
 594                 if (!$schema) $schema = $this->database;
 595                 if ($schema) $schema = "and k.table_catalog like '$schema%'";
 596
 597                 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
 598                 information_schema.table_constraints tc
 599                 where tc.constraint_name = k.constraint_name and tc.constraint_type =
 600                 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
 601
 602                 $savem = $ADODB_FETCH_MODE;
 603                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 604                 $a = $this->GetCol($sql);
 605                 $ADODB_FETCH_MODE = $savem;
 606
 607                 if ($a && sizeof($a)>0) return $a;
 608                 $false = false;
 609                 return $false;
 610         }
 611
 612
 613         function &MetaTables($ttype=false,$showSchema=false,$mask=false)
 614         {
 615             if ($mask) {
 616                         $save = $this->metaTablesSQL;
 617                         $mask = $this->qstr(($mask));
 618                         $this->metaTablesSQL .= " AND name like $mask";
 619                 }
 620                 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
 621
 622                 if ($mask) {
 623                         $this->metaTablesSQL = $save;
 624                 }
 625                 return $ret;
 626         }
 627 }
 628
 629 /*--------------------------------------------------------------------------------------
 630          Class Name: Recordset
 631 --------------------------------------------------------------------------------------*/
 632
 633 class ADORecordset_mssqlnative extends ADORecordSet {
 634
 635         var $databaseType = "mssqlnative";
 636         var $canSeek = false;
 637         var $fieldOffset = 0;
 638         // _mths works only in non-localised system
 639
 640         function ADORecordset_mssqlnative($id,$mode=false)
 641         {
 642                 if ($mode === false) {
 643                         global $ADODB_FETCH_MODE;
 644                         $mode = $ADODB_FETCH_MODE;
 645
 646                 }
 647                 $this->fetchMode = $mode;
 648                 return $this->ADORecordSet($id,$mode);
 649         }
 650
 651
 652         function _initrs()
 653         {
 654             global $ADODB_COUNTRECS;
 655         if ($this->connection->debug) error_log("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
 656         /*$retRowsAff = sqlsrv_rows_affected($this->_queryID);//"If you need to determine the number of rows a query will return before retrieving the actual results, appending a SELECT COUNT ... query would let you get that information, and then a call to next_result would move you to the "real" results."
 657         error_log("rowsaff: ".serialize($retRowsAff));
 658                 $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/
 659         $this->_numOfRows = -1;//not supported
 660         $fieldmeta = sqlsrv_field_metadata($this->_queryID);
 661         $this->_numOfFields = ($fieldmeta)? count($fieldmeta):-1;
 662         if ($this->connection->debug) error_log("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
 663         }
 664
 665
 666         //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
 667         // get next resultset - requires PHP 4.0.5 or later
 668         function NextRecordSet()
 669         {
 670                 if (!sqlsrv_next_result($this->_queryID)) return false;
 671                 $this->_inited = false;
 672                 $this->bind = false;
 673                 $this->_currentRow = -1;
 674                 $this->Init();
 675                 return true;
 676         }
 677
 678         /* Use associative array to get fields array */
 679         function Fields($colname)
 680         {
 681                 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
 682                 if (!$this->bind) {
 683                         $this->bind = array();
 684                         for ($i=0; $i < $this->_numOfFields; $i++) {
 685                                 $o = $this->FetchField($i);
 686                                 $this->bind[strtoupper($o->name)] = $i;
 687                         }
 688                 }
 689
 690                 return $this->fields[$this->bind[strtoupper($colname)]];
 691         }
 692
 693         /*      Returns: an object containing field information.
 694                 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
 695                 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
 696                 fetchField() is retrieved.      */
 697
 698         function FetchField($fieldOffset = -1)
 699         {
 700         if ($this->connection->debug) error_log("<hr>fetchfield: $fieldOffset, fetch array: <pre>".print_r($this->fields,true)."</pre> backtrace: ".adodb_backtrace(false));
 701                 if ($fieldOffset != -1) $this->fieldOffset = $fieldOffset;
 702                 $arrKeys = array_keys($this->fields);
 703                 if(array_key_exists($this->fieldOffset,$arrKeys) && !array_key_exists($arrKeys[$this->fieldOffset],$this->fields)) {
 704                         $f = false;
 705                 } else {
 706                         $f = new ADOFetchObj();
 707                         $f->name = $arrKeys[$this->fieldOffset];
 708                         if($fieldOffset == -1) $this->fieldOffset++;
 709                 }
 710
 711         if (empty($f)) {
 712             $f = false;//PHP Notice: Only variable references should be returned by reference
 713         }
 714                 return $f;
 715         }
 716
 717         function _seek($row)
 718         {
 719                 return false;//There is no support for cursors in the driver at this time.  All data is returned via forward-only streams.
 720         }
 721
 722         // speedup
 723         function MoveNext()
 724         {
 725         if ($this->connection->debug) error_log("movenext()");
 726         //if ($this->connection->debug) error_log("eof (beginning): ".$this->EOF);
 727                 if ($this->EOF) return false;
 728
 729                 $this->_currentRow++;
 730         if ($this->connection->debug) error_log("_currentRow: ".$this->_currentRow);
 731
 732                 if ($this->_fetch()) return true;
 733                 $this->EOF = true;
 734         //if ($this->connection->debug) error_log("eof (end): ".$this->EOF);
 735
 736                 return false;
 737         }
 738
 739
 740         // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
 741         // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
 742         function _fetch($ignore_fields=false)
 743         {
 744         if ($this->connection->debug) error_log("_fetch()");
 745                 if ($this->fetchMode & ADODB_FETCH_BOTH) {
 746                         if ($this->fetchMode & ADODB_FETCH_NUM) {
 747                 if ($this->connection->debug) error_log("fetch mode: both");
 748                                 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH);
 749                         } else {
 750                 if ($this->connection->debug) error_log("fetch mode: assoc");
 751                                 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC);
 752                         }
 753
 754                         if (ADODB_ASSOC_CASE == 0) {
 755                                 foreach($this->fields as $k=>$v) {
 756                                         $this->fields[strtolower($k)] = $v;
 757                                 }
 758                         } else if (ADODB_ASSOC_CASE == 1) {
 759                                 foreach($this->fields as $k=>$v) {
 760                                         $this->fields[strtoupper($k)] = $v;
 761                                 }
 762                         }
 763                 } else {
 764             if ($this->connection->debug) error_log("fetch mode: num");
 765                         $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC);
 766                 }
 767         if(is_array($this->fields) && array_key_exists(1,$this->fields) && !array_key_exists(0,$this->fields)) {//fix fetch numeric keys since they're not 0 based
 768             $arrFixed = array();
 769             foreach($this->fields as $key=>$value) {
 770                 if(is_numeric($key)) {
 771                     $arrFixed[$key-1] = $value;
 772                 } else {
 773                     $arrFixed[$key] = $value;
 774                 }
 775             }
 776             //if($this->connection->debug) error_log("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true));
 777             $this->fields = $arrFixed;
 778         }
 779                 if(is_array($this->fields)) {
 780                         foreach($this->fields as $key=>$value) {
 781                                 if (is_object($value) && method_exists($value, 'format')) {//is DateTime object
 782                                         $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");
 783                                 }
 784                         }
 785                 }
 786         if($this->fields === null) $this->fields = false;
 787         if ($this->connection->debug) error_log("<hr>after _fetch, fields: <pre>".print_r($this->fields,true)." backtrace: ".adodb_backtrace(false));
 788                 return $this->fields;
 789         }
 790
 791     /*  close() only needs to be called if you are worried about using too much memory while your script
 792                 is running. All associated result memory for the specified result identifier will automatically be freed.       */
 793         function _close()
 794         {
 795                 $rez = sqlsrv_free_stmt($this->_queryID);
 796                 $this->_queryID = false;
 797                 return $rez;
 798         }
 799
 800         // mssql uses a default date like Dec 30 2000 12:00AM
 801         static function UnixDate($v)
 802         {
 803                 return ADORecordSet_array_mssqlnative::UnixDate($v);
 804         }
 805
 806         static function UnixTimeStamp($v)
 807         {
 808                 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
 809         }
 810 }
 811
 812
 813 class ADORecordSet_array_mssqlnative extends ADORecordSet_array {
 814         function ADORecordSet_array_mssqlnative($id=-1,$mode=false)
 815         {
 816                 $this->ADORecordSet_array($id,$mode);
 817         }
 818
 819                 // mssql uses a default date like Dec 30 2000 12:00AM
 820         static function UnixDate($v)
 821         {
 822
 823                 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
 824
 825         global $ADODB_mssql_mths,$ADODB_mssql_date_order;
 826
 827                 //Dec 30 2000 12:00AM
 828                 if ($ADODB_mssql_date_order == 'dmy') {
 829                         if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
 830                                 return parent::UnixDate($v);
 831                         }
 832                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 833
 834                         $theday = $rr[1];
 835                         $themth =  substr(strtoupper($rr[2]),0,3);
 836                 } else {
 837                         if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
 838                                 return parent::UnixDate($v);
 839                         }
 840                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 841
 842                         $theday = $rr[2];
 843                         $themth = substr(strtoupper($rr[1]),0,3);
 844                 }
 845                 $themth = $ADODB_mssql_mths[$themth];
 846                 if ($themth <= 0) return false;
 847                 // h-m-s-MM-DD-YY
 848                 return  mktime(0,0,0,$themth,$theday,$rr[3]);
 849         }
 850
 851         static function UnixTimeStamp($v)
 852         {
 853
 854                 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
 855
 856             global $ADODB_mssql_mths,$ADODB_mssql_date_order;
 857
 858                 //Dec 30 2000 12:00AM
 859                  if ($ADODB_mssql_date_order == 'dmy') {
 860                          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})|"
 861                         ,$v, $rr)) return parent::UnixTimeStamp($v);
 862                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 863
 864                         $theday = $rr[1];
 865                         $themth =  substr(strtoupper($rr[2]),0,3);
 866                 } else {
 867                         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})|"
 868                         ,$v, $rr)) return parent::UnixTimeStamp($v);
 869                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 870
 871                         $theday = $rr[2];
 872                         $themth = substr(strtoupper($rr[1]),0,3);
 873                 }
 874
 875                 $themth = $ADODB_mssql_mths[$themth];
 876                 if ($themth <= 0) return false;
 877
 878                 switch (strtoupper($rr[6])) {
 879                 case 'P':
 880                         if ($rr[4]<12) $rr[4] += 12;
 881                         break;
 882                 case 'A':
 883                         if ($rr[4]==12) $rr[4] = 0;
 884                         break;
 885                 default:
 886                         break;
 887                 }
 888                 // h-m-s-MM-DD-YY
 889                 return  mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
 890         }
 891 }
 892
 893 /*
 894 Code Example 1:
 895
 896 select  object_name(constid) as constraint_name,
 897         object_name(fkeyid) as table_name,
 898         col_name(fkeyid, fkey) as column_name,
 899         object_name(rkeyid) as referenced_table_name,
 900         col_name(rkeyid, rkey) as referenced_column_name
 901 from sysforeignkeys
 902 where object_name(fkeyid) = x
 903 order by constraint_name, table_name, referenced_table_name,  keyno
 904
 905 Code Example 2:
 906 select  constraint_name,
 907         column_name,
 908         ordinal_position
 909 from information_schema.key_column_usage
 910 where constraint_catalog = db_name()
 911 and table_name = x
 912 order by constraint_name, ordinal_position
 913
 914 http://www.databasejournal.com/scripts/article.php/1440551
 915 */
 916
 917 ?>