lib/WikiDB/adodb/drivers/adodb-mssqlnative.inc.php

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