[Summary view] [Print] [Text view]

   1  <?php
   2  /** 
   3  *
   4  * @package dbal
   5  * @version $Id: mssql_odbc.php,v 1.24 2006/10/14 14:56:44 acydburn Exp $
   6  * @copyright (c) 2005 phpBB Group 
   7  * @license http://opensource.org/licenses/gpl-license.php GNU Public License 
   8  *
   9  */
  10  
  11  /**
  12  * @ignore
  13  */
  14  if (!defined('IN_PHPBB'))
  15  {
  16      exit;
  17  }
  18  
  19  include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
  20  
  21  /**
  22  * Unified ODBC functions
  23  * Unified ODBC functions support any database having ODBC driver, for example Adabas D, IBM DB2, iODBC, Solid, Sybase SQL Anywhere...
  24  * Here we only support MSSQL Server 2000+ because of the provided schema
  25  * @package dbal
  26  */
  27  class dbal_mssql_odbc extends dbal
  28  {
  29      var $last_query_text = '';
  30  
  31      /**
  32      * Connect to server
  33      */
  34  	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
  35      {
  36          $this->persistency = $persistency;
  37          $this->user = $sqluser;
  38          $this->server = $sqlserver . (($port) ? ':' . $port : '');
  39          $this->dbname = $database;
  40  
  41          $this->db_connect_id = ($this->persistency) ? @odbc_pconnect($this->server, $this->user, $sqlpassword) : @odbc_connect($this->server, $this->user, $sqlpassword);
  42  
  43          return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
  44      }
  45  
  46      /**
  47      * Version information about used database
  48      */
  49  	function sql_server_info()
  50      {
  51          $result_id = @odbc_exec($this->db_connect_id, "SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition')");
  52  
  53          $row = false;
  54          if ($result_id)
  55          {
  56              $row = @odbc_fetch_array($result_id);
  57              @odbc_free_result($result_id);
  58          }
  59  
  60          if ($row)
  61          {
  62              return 'MSSQL (ODBC)<br />' . implode(' ', $row);
  63          }
  64  
  65          return 'MSSQL (ODBC)';
  66      }
  67  
  68      /**
  69      * SQL Transaction
  70      * @access private
  71      */
  72  	function _sql_transaction($status = 'begin')
  73      {
  74          switch ($status)
  75          {
  76              case 'begin':
  77                  return @odbc_autocommit($this->db_connect_id, false);
  78              break;
  79  
  80              case 'commit':
  81                  $result = @odbc_commit($this->db_connect_id);
  82                  @odbc_autocommit($this->db_connect_id, true);
  83                  return $result;
  84              break;
  85  
  86              case 'rollback':
  87                  $result = @odbc_rollback($this->db_connect_id);
  88                  @odbc_autocommit($this->db_connect_id, true);
  89                  return $result;
  90              break;
  91          }
  92  
  93          return true;
  94      }
  95  
  96      /**
  97      * Base query method
  98      *
  99      * @param    string    $query        Contains the SQL query which shall be executed
 100      * @param    int        $cache_ttl    Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
 101      * @return    mixed                When casted to bool the returned value returns true on success and false on failure
 102      *
 103      * @access    public
 104      */
 105  	function sql_query($query = '', $cache_ttl = 0)
 106      {
 107          if ($query != '')
 108          {
 109              global $cache;
 110  
 111              // EXPLAIN only in extra debug mode
 112              if (defined('DEBUG_EXTRA'))
 113              {
 114                  $this->sql_report('start', $query);
 115              }
 116  
 117              // For now, MSSQL has no real UTF-8 support
 118              $query = utf8_decode($query);
 119  
 120              $this->last_query_text = $query;
 121              $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
 122              $this->sql_add_num_queries($this->query_result);
 123  
 124              if ($this->query_result === false)
 125              {
 126                  if (($this->query_result = @odbc_exec($this->db_connect_id, $query)) === false)
 127                  {
 128                      $this->sql_error($query);
 129                  }
 130  
 131                  if (defined('DEBUG_EXTRA'))
 132                  {
 133                      $this->sql_report('stop', $query);
 134                  }
 135  
 136                  if ($cache_ttl && method_exists($cache, 'sql_save'))
 137                  {
 138                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 139                      $cache->sql_save($query, $this->query_result, $cache_ttl);
 140                  }
 141                  else if (strpos($query, 'SELECT') === 0 && $this->query_result)
 142                  {
 143                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 144                  }
 145              }
 146              else if (defined('DEBUG_EXTRA'))
 147              {
 148                  $this->sql_report('fromcache', $query);
 149              }
 150          }
 151          else
 152          {
 153              return false;
 154          }
 155  
 156          return ($this->query_result) ? $this->query_result : false;
 157      }
 158  
 159      /**
 160      * Build LIMIT query
 161      */
 162  	function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) 
 163      {
 164          if ($query != '')
 165          {
 166              $this->query_result = false;
 167  
 168              // Since TOP is only returning a set number of rows we won't need it if total is set to 0 (return all rows)
 169              if ($total)
 170              {
 171                  // We need to grab the total number of rows + the offset number of rows to get the correct result
 172                  if (strpos($query, 'SELECT DISTINCT') === 0)
 173                  {
 174                      $query = 'SELECT DISTINCT TOP ' . ($total + $offset) . ' ' . substr($query, 15);
 175                  }
 176                  else
 177                  {
 178                      $query = 'SELECT TOP ' . ($total + $offset) . ' ' . substr($query, 6);
 179                  }
 180              }
 181  
 182              $result = $this->sql_query($query, $cache_ttl);
 183  
 184              // Seek by $offset rows
 185              if ($offset)
 186              {
 187                  $this->sql_rowseek($offset, $result);
 188              }
 189  
 190              return $result;
 191          }
 192          else
 193          {
 194              return false;
 195          }
 196      }
 197  
 198      /**
 199      * Return number of affected rows
 200      */
 201  	function sql_affectedrows()
 202      {
 203          return ($this->db_connect_id) ? @odbc_num_rows($this->query_result) : false;
 204      }
 205  
 206      /**
 207      * Fetch current row
 208      */
 209  	function sql_fetchrow($query_id = false)
 210      {
 211          global $cache;
 212  
 213          if ($query_id === false)
 214          {
 215              $query_id = $this->query_result;
 216          }
 217  
 218          if (isset($cache->sql_rowset[$query_id]))
 219          {
 220              return $cache->sql_fetchrow($query_id);
 221          }
 222  
 223          return ($query_id !== false) ? @odbc_fetch_array($query_id) : false;
 224      }
 225  
 226      /**
 227      * Seek to given row number
 228      * rownum is zero-based
 229      */
 230  	function sql_rowseek($rownum, $query_id = false)
 231      {
 232          global $cache;
 233  
 234          if ($query_id === false)
 235          {
 236              $query_id = $this->query_result;
 237          }
 238  
 239          if (isset($cache->sql_rowset[$query_id]))
 240          {
 241              return $cache->sql_rowseek($rownum, $query_id);
 242          }
 243  
 244          if ($query_id === false)
 245          {
 246              return false;
 247          }
 248  
 249          $this->sql_freeresult($query_id);
 250          $query_id = $this->sql_query($this->last_query_text);
 251  
 252          if ($query_id === false)
 253          {
 254              return false;
 255          }
 256  
 257          // We do not fetch the row for rownum == 0 because then the next resultset would be the second row
 258          for ($i = 0; $i < $rownum; $i++)
 259          {
 260              if (!$this->sql_fetchrow($query_id))
 261              {
 262                  return false;
 263              }
 264          }
 265  
 266          return true;
 267      }
 268  
 269      /**
 270      * Get last inserted id after insert statement
 271      */
 272  	function sql_nextid()
 273      {
 274          $result_id = @odbc_exec($this->db_connect_id, 'SELECT @@IDENTITY');
 275  
 276          if ($result_id)
 277          {
 278              if (@odbc_fetch_array($result_id))
 279              {
 280                  $id = @odbc_result($result_id, 1);    
 281                  @odbc_free_result($result_id);
 282                  return $id;
 283              }
 284              @odbc_free_result($result_id);
 285          }
 286  
 287          return false;
 288      }
 289  
 290      /**
 291      * Free sql result
 292      */
 293  	function sql_freeresult($query_id = false)
 294      {
 295          global $cache;
 296  
 297          if ($query_id === false)
 298          {
 299              $query_id = $this->query_result;
 300          }
 301  
 302          if (isset($cache->sql_rowset[$query_id]))
 303          {
 304              return $cache->sql_freeresult($query_id);
 305          }
 306  
 307          if (isset($this->open_queries[(int) $query_id]))
 308          {
 309              unset($this->open_queries[(int) $query_id]);
 310              return @odbc_free_result($query_id);
 311          }
 312  
 313          return false;
 314      }
 315  
 316      /**
 317      * Escape string used in sql query
 318      */
 319  	function sql_escape($msg)
 320      {
 321          return str_replace("'", "''", $msg);
 322      }
 323  
 324      /**
 325      * Build db-specific query data
 326      * @access private
 327      */
 328  	function _sql_custom_build($stage, $data)
 329      {
 330          return $data;
 331      }
 332  
 333      /**
 334      * return sql error array
 335      * @access private
 336      */
 337  	function _sql_error()
 338      {
 339          return array(
 340              'message'    => @odbc_errormsg(),
 341              'code'        => @odbc_error()
 342          );
 343      }
 344  
 345      /**
 346      * Close sql connection
 347      * @access private
 348      */
 349  	function _sql_close()
 350      {
 351          return @odbc_close($this->db_connect_id);
 352      }
 353  
 354      /**
 355      * Build db-specific report
 356      * @access private
 357      */
 358  	function _sql_report($mode, $query = '')
 359      {
 360          switch ($mode)
 361          {
 362              case 'start':
 363                  $explain_query = $query;
 364                  if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 365                  {
 366                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 367                  }
 368                  else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 369                  {
 370                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 371                  }
 372  
 373                  if (preg_match('/^SELECT/', $explain_query))
 374                  {
 375                      $html_table = false;
 376                      @odbc_exec($this->db_connect_id, "SET SHOWPLAN_TEXT ON;");
 377                      if ($result = @odbc_exec($this->db_connect_id, $explain_query))
 378                      {
 379                          @odbc_next_result($result);
 380                          while ($row = @odbc_fetch_array($result))
 381                          {
 382                              $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 383                          }
 384                      }
 385                      @odbc_exec($this->db_connect_id, "SET SHOWPLAN_TEXT OFF;");
 386                      @odbc_free_result($result);
 387  
 388                      if ($html_table)
 389                      {
 390                          $this->html_hold .= '</table>';
 391                      }
 392                  }
 393              break;
 394  
 395              case 'fromcache':
 396                  $endtime = explode(' ', microtime());
 397                  $endtime = $endtime[0] + $endtime[1];
 398  
 399                  $result = @odbc_exec($this->db_connect_id, $query);
 400                  while ($void = @odbc_fetch_array($result))
 401                  {
 402                      // Take the time spent on parsing rows into account
 403                  }
 404                  @odbc_free_result($result);
 405  
 406                  $splittime = explode(' ', microtime());
 407                  $splittime = $splittime[0] + $splittime[1];
 408  
 409                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 410  
 411              break;
 412          }
 413      }
 414  }
 415  
 416  ?>