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

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