[Sommaire] [Imprimer]

   1  <?php
   2  /*
   3   *  $Id: StatementCommon.php,v 1.4 2004/06/13 02:31:07 hlellelid Exp $
   4   *
   5   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   6   * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   7   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
   8   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
   9   * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  10   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  11   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  12   * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  13   * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  14   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  15   * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  16   *
  17   * This software consists of voluntary contributions made by many individuals
  18   * and is licensed under the LGPL. For more information please see
  19   * <http://creole.phpdb.org>.
  20   */
  21  
  22  /**
  23   * Class that contains common/shared functionality for Statements.
  24   * 
  25   * @author   Hans Lellelid <hans@xmpl.org>
  26   * @version  $Revision: 1.4 $
  27   * @package  creole.common
  28   */
  29  abstract class StatementCommon {
  30  
  31      /**
  32       * The database connection.
  33       * @var Connection
  34       */ 
  35      protected $conn;
  36      
  37      /**
  38       * Temporarily hold a ResultSet object after an execute() query.
  39       * @var ResultSet
  40       */
  41      protected $resultSet;
  42  
  43      /**
  44       * Temporary hold the affected row cound after an execute() query.
  45       * @var int
  46       */
  47      protected $updateCount;
  48      
  49      /**
  50       * Array of warning objects generated by methods performed on result set.
  51       * @var array SQLWarning[]
  52       */
  53      protected $warnings = array();
  54      
  55      /** 
  56       * The ResultSet class name.
  57       * @var string
  58       */
  59      protected $resultClass;
  60      
  61      /**
  62       * The prepared statement resource id.
  63       * @var resource
  64       */
  65      protected $stmt;
  66      
  67      /**
  68       * Max rows to retrieve from DB.
  69       * @var int
  70       */
  71      protected $limit = 0;
  72      
  73      /**
  74       * Offset at which to start processing DB rows.
  75       * "Skip X rows"
  76       * @var int
  77       */
  78      protected $offset = 0;
  79      
  80      /**
  81       * Create new statement instance.
  82       * 
  83       * @param Connection $conn Connection object
  84       */ 
  85      function __construct(Connection $conn) 
  86      {
  87          $this->conn = $conn;        
  88      }
  89      
  90      /**
  91       * Sets the maximum number of rows to return from db.
  92       * This will affect the SQL if the RDBMS supports native LIMIT; if not,
  93       * it will be emulated.  Limit only applies to queries (not update sql).
  94       * @param int $v Maximum number of rows or 0 for all rows.
  95       * @return void
  96       */
  97      public function setLimit($v)
  98      {
  99          $this->limit = (int) $v;
 100      }
 101      
 102      /**
 103       * Returns the maximum number of rows to return or 0 for all.
 104       * @return int
 105       */
 106      public function getLimit()
 107      {
 108          return $this->limit;
 109      }
 110      
 111      /**
 112       * Sets the start row.
 113       * This will affect the SQL if the RDBMS supports native OFFSET; if not,
 114       * it will be emulated. Offset only applies to queries (not update) and 
 115       * only is evaluated when LIMIT is set!
 116       * @param int $v
 117       * @return void
 118       */ 
 119      public function setOffset($v)
 120      {
 121          $this->offset = (int) $v;
 122      }
 123      
 124      /**
 125       * Returns the start row.
 126       * Offset only applies when Limit is set!
 127       * @return int
 128       */
 129      public function getOffset()
 130      {
 131          return $this->offset;
 132      }
 133      
 134      /**
 135       * Free resources associated with this statement.
 136       * Some drivers will need to implement this method to free
 137       * database result resources. 
 138       * 
 139       * @return void
 140       */
 141      public function close()
 142      {
 143          // do nothing here (subclasses will implement)
 144      }
 145      
 146      /**
 147       * Generic execute() function has to check to see whether SQL is an update or select query.
 148       * 
 149       * If you already know whether it's a SELECT or an update (manipulating) SQL, then use
 150       * the appropriate method, as this one will incurr overhead to check the SQL.
 151       * 
 152       * @param int $fetchmode Fetchmode (only applies to queries).
 153       * @return boolean True if it is a result set, false if not or if no more results (this is identical to JDBC return val).
 154       * @throws SQLException
 155       * @todo -cStatementCommon Update execute() to not use isSelect() method, but rather to determine type based on returned results.
 156       */
 157      public function execute($sql, $fetchmode = null)
 158      {
 159          
 160          if (!$this->isSelect($sql)) {                    
 161              $this->updateCount = $this->executeUpdate($sql);
 162              return false;
 163          } else {
 164              $this->resultSet = $this->executeQuery($sql, $fetchmode);
 165              if ($this->resultSet->getRecordCount() === 0) {
 166                  return false;
 167              }
 168              return true;
 169          }
 170      }
 171  
 172      /**
 173       * Get result set.
 174       * This assumes that the last thing done was an executeQuery() or an execute()
 175       * with SELECT-type query.
 176       *
 177       * @return RestultSet (or null if none)
 178       */
 179      public function getResultSet()
 180      {
 181          return $this->resultSet;
 182      }
 183  
 184      /**
 185       * Get update count.
 186       *
 187       * @return int Number of records affected, or <code>null</code> if not applicable.
 188       */
 189      public function getUpdateCount()
 190      {
 191          return $this->updateCount;
 192      }
 193          
 194      /**
 195       * Returns whether the passed SQL is a SELECT statement.
 196       * 
 197       * Returns true if SQL starts with 'SELECT' but not 'SELECT INTO'.  This exists
 198       * to support the execute() function -- which could either execute an update or
 199       * a query.
 200       * 
 201       * Currently this function does not take into consideration comments, primarily
 202       * because there are a number of different comment options for different drivers:
 203       * <pre>
 204       *     -- SQL-defined comment, but not truly comment in Oracle
 205       *  # comment in mysql
 206       *  /* comment in mssql, others * /
 207       *  // comment sometimes?
 208       *  REM also comment ...
 209       * </pre>
 210       * 
 211       * If you're wondering why we can't just execute the query and look at the return results
 212       * to see whether it was an update or a select, the reason is that for update queries we
 213       * need to do stuff before we execute them -- like start transactions if auto-commit is off.
 214       * 
 215       * @param string $sql
 216       * @return boolean Whether statement is a SELECT SQL statement.
 217       * @see execute()
 218       */
 219      protected function isSelect($sql)
 220      {
 221          // is first word is SELECT, then return true, unless it's SELECT INTO ...
 222          // this doesn't, however, take comments into account ...
 223          $sql = trim($sql);
 224          return (stripos($sql, 'select') === 0 && stripos($sql, 'select into ') !== 0);
 225      }
 226  
 227      /**
 228       * Executes the SQL query in this PreparedStatement object and returns the resultset generated by the query.
 229       * 
 230       * @param string $sql This method may optionally be called with the SQL statement.
 231       * @param int $fetchmode The mode to use when fetching the results (e.g. ResultSet::FETCHMODE_NUM, ResultSet::FETCHMODE_ASSOC).
 232       * @return object Creole::ResultSet
 233       * @throws SQLException If there is an error executing the specified query.
 234       * @todo -cStatementCommon Put native query execution logic in statement subclasses.
 235       */
 236      public function executeQuery($sql, $fetchmode = null)
 237      {
 238          $this->updateCount = null;
 239          if ($this->limit > 0 || $this->offset > 0) {
 240              $this->conn->applyLimit($sql, $this->offset, $this->limit);
 241          }
 242          $this->resultSet = $this->conn->executeQuery($sql, $fetchmode);
 243          return $this->resultSet;
 244      }
 245  
 246      /**
 247       * Executes the SQL INSERT, UPDATE, or DELETE statement in this PreparedStatement object.
 248       * 
 249       * @param string $sql This method may optionally be called with the SQL statement.
 250       * @return int Number of affected rows (or 0 for drivers that return nothing).
 251       * @throws SQLException if a database access error occurs.
 252       */
 253      public function executeUpdate($sql) 
 254      {
 255          if ($this->resultSet) $this->resultSet->close();
 256          $this->resultSet = null;
 257          $this->updateCount = $this->conn->executeUpdate($sql);
 258          return $this->updateCount;
 259      }
 260      
 261      /**
 262       * Gets next result set (if this behavior is supported by driver).
 263       * Some drivers (e.g. MSSQL) support returning multiple result sets -- e.g.
 264       * from stored procedures.
 265       *
 266       * This function also closes any current restult set.
 267       *
 268       * Default behavior is for this function to return false.  Driver-specific
 269       * implementations of this class can override this method if they actually
 270       * support multiple result sets.
 271       * 
 272       * @return boolean True if there is another result set, otherwise false.
 273       */
 274      public function getMoreResults()
 275      {
 276          if ($this->resultSet) $this->resultSet->close();        
 277          $this->resultSet = null;
 278          return false;        
 279      }
 280       
 281      /**
 282       * Gets the db Connection that created this statement.
 283       * @return Connection
 284       */
 285      public function getConnection()
 286      {
 287          return $this->conn;
 288      }
 289  }