[Sommaire] [Imprimer]

   1  <?php
   2  /*
   3   *  $Id: OCI8PreparedStatement.php,v 1.26 2006/01/30 21:32:05 sethr 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  require_once 'creole/PreparedStatement.php';
  23  require_once 'creole/common/PreparedStatementCommon.php';
  24  
  25  /**
  26   * Oracle (OCI8) implementation of PreparedStatement.
  27   * 
  28   * @author    David Giffin <david@giffin.org>
  29   * @author    Hans Lellelid <hans@xmpl.org>
  30   * @version   $Revision: 1.26 $
  31   * @package   creole.drivers.oracle
  32   */
  33  class OCI8PreparedStatement extends PreparedStatementCommon implements PreparedStatement {
  34  
  35      /**
  36       * Descriptor holders for LOB values.
  37       * There are other types of descriptors, but we need to keep
  38       * them separate, because we need to execute the save()/savefile() method
  39       * on lob descriptors.
  40       * @var array object from oci_new_descriptor
  41       */
  42      private $lobDescriptors = array();
  43      
  44      /**
  45       * Hold any Blob/Clob data.
  46       * These can be matched (by key) to descriptors in $lobDescriptors.
  47       * @var array Lob[]
  48       */
  49      private $lobs = array();        
  50  
  51      /**
  52       * Array to store the columns in an insert or update statement.
  53       * This is necessary for the proper handling of lob variables
  54       * @var arrary columns[]
  55       */
  56      private $columns = array();
  57      
  58      /**
  59       * If the statement is set, free it.
  60       * @see PreparedStatement::close()
  61       */
  62      function close()
  63      {
  64           if (isset($this->stmt))
  65                @oci_free_statement($this->stmt);
  66      }
  67      
  68      /**
  69       * Nothing to do - since oci_bind is used to insert data, no escaping is needed
  70       * @param string $str
  71       * @return string
  72       */
  73      protected function escape($str)
  74      {
  75          return $str;
  76      }
  77  
  78      /**
  79       * Executes the SQL query in this PreparedStatement object and returns the resultset generated by the query.
  80       * @param mixed $p1 Either (array) Parameters that will be set using PreparedStatement::set() before query is executed or (int) fetchmode.
  81       * @param int $fetchmode The mode to use when fetching the results (e.g. ResultSet::FETCHMODE_NUM, ResultSet::FETCHMODE_ASSOC).
  82       * @return ResultSet
  83       * @throws SQLException if a database access error occurs.
  84       */
  85      public function executeQuery($p1 = null, $fetchmode = null)
  86      {
  87          $params = null;
  88          if ($fetchmode !== null) {
  89              $params = $p1;
  90          } elseif ($p1 !== null) {
  91              if (is_array($p1)) $params = $p1;
  92              else $fetchmode = $p1;
  93          }
  94          
  95          if ($params) {
  96              for($i=0,$cnt=count($params); $i < $cnt; $i++) {
  97                  $this->set($i+1, $params[$i]);
  98              }
  99          }
 100  
 101          $this->updateCount = null; // reset        
 102                  
 103          $sql = $this->sqlToOracleBindVars($this->sql);
 104          
 105          if ($this->limit > 0 || $this->offset > 0) {
 106              $this->conn->applyLimit($sql, $this->offset, $this->limit);
 107          }
 108          
 109          $result = oci_parse($this->conn->getResource(), $sql);
 110          if (!$result) {
 111              throw new SQLException("Unable to prepare query", $this->conn->nativeError(), $this->sqlToOracleBindVars($this->sql));
 112          }
 113          
 114          // bind all variables
 115          $this->bindVars($result);
 116          
 117          $success = oci_execute($result, OCI_DEFAULT);
 118          if (!$success) {
 119              throw new SQLException("Unable to execute query", $this->conn->nativeError($result), $this->sqlToOracleBindVars($this->sql));
 120          }
 121          
 122          $this->resultSet = new OCI8ResultSet($this->conn, $result, $fetchmode);
 123          
 124          return $this->resultSet;
 125      }
 126      
 127      /**
 128       * Executes the SQL INSERT, UPDATE, or DELETE statement in this PreparedStatement object.
 129       * 
 130       * @param array $params Parameters that will be set using PreparedStatement::set() before query is executed.
 131       * @return int Number of affected rows (or 0 for drivers that return nothing).
 132       * @throws SQLException if a database access error occurs.
 133       */
 134      public function executeUpdate($params = null) 
 135      {
 136          if ($params) {
 137              for($i=0,$cnt=count($params); $i < $cnt; $i++) {
 138                  $this->set($i+1, $params[$i]);
 139              }
 140          }
 141  
 142          if($this->resultSet) $this->resultSet->close();
 143          $this->resultSet = null; // reset
 144  
 145          $stmt = oci_parse($this->conn->getResource(), $this->sqlToOracleBindVars($this->sql));
 146  
 147          if (!$stmt) {
 148              throw new SQLException("Unable to prepare update", $this->conn->nativeError(), $this->sqlToOracleBindVars($this->sql));
 149          }
 150          
 151          // bind all variables
 152          $this->bindVars($stmt); 
 153  
 154          // Even if autocommit is on, delay commit until after LOBS have been saved
 155          $success = oci_execute($stmt, OCI_DEFAULT);
 156          if (!$success) {
 157              throw new SQLException("Unable to execute update", $this->conn->nativeError($stmt), $this->sqlToOracleBindVars($this->sql));
 158          }        
 159  
 160          // save data in any LOB descriptors, then free them
 161          foreach($this->lobDescriptors as $paramIndex => $lobster) {
 162              $lob = $this->lobs[$paramIndex]; // corresponding Blob/Clob
 163              if ($lob->isFromFile()) {
 164                  $success = $lobster->savefile($lob->getInputFile());
 165              } else {
 166                  $success = $lobster->save($lob->getContents());
 167              }
 168              if (!$success) {                
 169                  $lobster->free();
 170                  throw new SQLException("Error saving lob bound to " . $paramIndex);
 171              }
 172              $lobster->free();
 173          }
 174  
 175          if ($this->conn->getAutoCommit()) {
 176              oci_commit($this->conn->getResource()); // perform deferred commit
 177          }
 178          
 179          $this->updateCount = @oci_num_rows($stmt);
 180  
 181          return $this->updateCount;
 182      }
 183  
 184      /**
 185       * Performs the actual binding of variables using oci_bind_by_name().
 186       * 
 187       * This may seem like useless overhead, but the reason why calls to oci_bind_by_name() 
 188       * are not performed in the set*() methods is that it is possible that the SQL will
 189       * need to be modified -- e.g. by a setLimit() call -- and re-prepared.  We cannot assume
 190       * that the statement has been prepared when the set*() calls are invoked.  This also means,
 191       * therefore, that the set*() calls will not throw exceptions; all exceptions will be thrown
 192       * when the statement is prepared.
 193       * 
 194       * @param resource $stmt The statement result of oci_parse to use for binding.
 195       * @return void
 196       */
 197      private function bindVars($stmt)
 198      {
 199          foreach ($this->boundInVars as $idx => $val) {
 200              $idxName = ":var" . $idx;
 201              if (!oci_bind_by_name($stmt, $idxName, $this->boundInVars[$idx], -1)) {
 202                  throw new SQLException("Erorr binding value to placeholder " . $idx);
 203              } 
 204          } // foreach
 205  
 206          foreach ($this->lobs as $idx => $val) {
 207              $idxName = ":var" . $idx;
 208              if (class_exists('Blob') && $val instanceof Blob){
 209                  if (!oci_bind_by_name($stmt, $idxName, $this->lobDescriptors[$idx], -1, OCI_B_BLOB))
 210                      throw new SQLException("Erorr binding blob to placeholder " . $idx);
 211              } elseif (class_exists('Clob') && $val instanceof Clob){
 212                  if (!oci_bind_by_name($stmt, $idxName, $this->lobDescriptors[$idx], -1, OCI_B_CLOB))
 213                      throw new SQLException("Erorr binding clob to placeholder " . $idx);
 214              }
 215          } // foreach
 216      }
 217  
 218  
 219      /**
 220       * Convert a Propel SQL into Oracle SQL
 221       *
 222       * Look for all of the '?' and replace with ":varX"
 223       *
 224       * @param string $sql SQL in Propel native format
 225       * @return string SQL in Oracle Bind Var format
 226       * @todo -cOCI8PreparedStatement Consider changing this implementation to use the fact that we
 227       *                               already know where all the '?' chars are (in $positions array).
 228       */
 229      private function sqlToOracleBindVars($sql)
 230      {
 231          $out = "";
 232          $in_literal = 0;
 233          $idxNum = 1;
 234          for ($i = 0; $i < strlen($sql); $i++) {
 235              $char = $sql[$i];
 236              if (strcmp($char,"'")==0) {
 237                  $in_literal = ~$in_literal;
 238              }
 239              if (strcmp($char,"?")==0 && !$in_literal) {
 240                  if (array_key_exists($idxNum, $this->lobs)){
 241                      if (class_exists('Blob') && ($this->lobs[$idxNum] instanceof Blob))
 242                          $out .= "empty_blob()";
 243                      if (class_exists('Clob') && ($this->lobs[$idxNum] instanceof Clob))
 244                          $out .= "empty_clob()";
 245                  } else
 246                      $out .= ":var" . $idxNum;
 247                  $idxNum++;
 248              } else {
 249                  $out .= $char;
 250              }
 251          }
 252  
 253          if (isset($this->lobs) && !empty($this->lobs)) {
 254              $this->setColumnArray();
 255  
 256              $retstmt = " Returning ";
 257              $collist = "";
 258              $bindlist = "";
 259              foreach ($this->lobs as $idx=>$val) {
 260                  $idxName = ":var" . $idx;
 261                  if ((class_exists('Blob') && $val instanceof Blob) || (class_exists('Clob') && $val instanceof Clob)) {
 262                      //the columns array starts at zero instead of 1 like the lobs array
 263                      $collist .= $this->columns[$idx-1] . ",";
 264                      $bindlist .= $idxName . ",";
 265                  }
 266              }
 267  
 268              if (!empty($collist))
 269                  $out .= $retstmt . rtrim($collist, ",") . " into " . rtrim($bindlist, ",");
 270          }
 271  
 272          return $out;
 273      }
 274  
 275      /**
 276       * @param string $paramIndex
 277       * @param mixed $blob Blob object or string containing data.
 278       * @return void
 279       */
 280      function setBlob($paramIndex, $blob) 
 281      {
 282          require_once 'creole/util/Blob.php';
 283          if (!($blob instanceof Blob)) {
 284              $b = new Blob();
 285              $b->setContents($blob);
 286              $blob = $b;
 287          }
 288          $this->lobDescriptors[$paramIndex] = oci_new_descriptor($this->conn->getResource(), OCI_D_LOB);
 289          $this->lobs[$paramIndex] = $blob;        
 290      }
 291  
 292      /**
 293       * @param string $paramIndex
 294       * @param mixed $clob Clob object or string containing data.
 295       * @return void
 296       */
 297      function setClob($paramIndex, $clob) 
 298      {
 299          require_once 'creole/util/Clob.php';
 300          if (!($clob instanceof Clob)) {
 301              $c = new Clob();
 302              $c->setContents($clob);
 303              $clob = $c;
 304          }
 305          $this->lobDescriptors[$paramIndex] = oci_new_descriptor($this->conn->getResource(), OCI_D_LOB);
 306          $this->lobs[$paramIndex] = $clob;        
 307      }
 308  
 309      /**
 310       * Since bind variables in oracle have no special characters, this setString method differs from the
 311       * common one in that it does not single quote strings.
 312       *
 313       * @param int $paramIndex
 314       * @param string $value
 315       * @return void
 316       */
 317      function setString($paramIndex, $value)
 318      {
 319          if ($value === null) {
 320              $this->setNull($paramIndex);
 321          } else {
 322              // it's ok to have a fatal error here, IMO, if object doesn't have
 323              // __toString() and is being passed to this method.
 324              if ( is_object ( $value ) ) {
 325                  $this->boundInVars[$paramIndex] = $value->__toString();
 326              } else {
 327                  $this->boundInVars[$paramIndex] = (string)$value;
 328              }
 329          }
 330      }
 331  
 332      /**
 333       * Copied this function from common/PreparedStatement.php and modified to work with Oracle
 334       * Please note the format used with date() matches that of NLS_DATE_FORMAT set in
 335       * OCI8Connection.php
 336       *
 337       * @param int $paramIndex
 338       * @param string $value
 339       * @return void
 340       */
 341      function setTimestamp($paramIndex, $value)
 342      {
 343          if ($value === null) {
 344              $this->setNull($paramIndex);
 345          } else {
 346              if (is_numeric($value)) $value = date('Y-m-d H:i:s', $value);
 347              elseif (is_object($value)) $value = date('Y-m-d H:i:s', $value->getTime());
 348              $this->boundInVars[$paramIndex] = $value;
 349          }
 350      }
 351  
 352      /**
 353       * Please note the format used with date() matches that of NLS_DATE_FORMAT set in
 354       * OCI8Connection.php
 355       *
 356       * @param int $paramIndex
 357       * @param string $value
 358       * @return void
 359       */
 360      function setDate($paramIndex, $value)
 361      {
 362          if ($value === null) {
 363              $this->setNull($paramIndex);
 364          } else {
 365              if (is_numeric($value)) $value = date("Y-m-d", $value);
 366              elseif (is_object($value)) $value = date("Y-m-d", $value->getTime());
 367              $this->boundInVars[$paramIndex] = $value;
 368          }
 369      }
 370  
 371      /**
 372       * In order to send lob data (clob/blob) to the Oracle data base, the 
 373       * sqlToOracleBindVars function needs to have an ordered list of the
 374       * columns being addressed in the sql statement.
 375       * Since only insert and update statements require special handling,
 376       * there are two ways to find the columns:
 377       *  1) find the first set of () and parse out the columns names based on
 378       *     the token ','
 379       *  2) find all the text strings to the left of the equal signs.
 380       *
 381       * @param void
 382       * @return void
 383       */
 384      private function setColumnArray()
 385      {
 386          $this->columns = array();
 387  
 388          //handle the simple insert case first
 389          if(strtoupper(substr($this->sql, 0, 6)) == 'INSERT') {
 390              $firstPos = strpos($this->sql, '(');
 391              $secPos = strpos($this->sql, ')');
 392              $collist = substr($this->sql, $firstPos + 1, $secPos - $firstPos - 1);
 393              $this->columns = explode(',', $collist);
 394          }
 395          if (strtoupper(substr($this->sql, 0, 6)) == 'UPDATE') {
 396              //handle more complex update case
 397              //first get the string setup so we can explode based on '=?'
 398              //second split results from previous action based on ' '
 399              // the last token from this should be a column name
 400              $tmp = $this->sql;
 401              $tmp = str_replace(" =", "=", $this->sql);
 402              $tmp = str_replace("= ", "=", $tmp);
 403              $tmp = str_replace(",", " ", $tmp);
 404              $stage1 = explode("=?",$tmp);
 405              
 406              foreach($stage1 as $chunk) {
 407                  $stage2 = explode(' ', $chunk);
 408                  $this->columns[count($this->columns)] = $stage2[count($stage2) - 1];
 409              }
 410          }
 411     }
 412  
 413     /**
 414       * @param int $paramIndex
 415       * @return void
 416       */
 417      function setNull($paramIndex)
 418      {
 419          $this->boundInVars[$paramIndex] = '';
 420      }
 421  
 422  
 423  }
 424