[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /**
   4   *  $Id: OCI8Connection.php,v 1.18 2005/10/17 19:03:51 dlawson_mi Exp $
   5   *
   6   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   7   * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   8   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
   9   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
  10   * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  11   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES ( INCLUDING, BUT NOT
  12   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  13   * DATA, OR PROFITS; OR BUSINESS INTERRUPTION ) HOWEVER CAUSED AND ON ANY
  14   * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  15   * ( INCLUDING NEGLIGENCE OR OTHERWISE ) ARISING IN ANY WAY OUT OF THE USE
  16   * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  17   *
  18   * This software consists of voluntary contributions made by many individuals
  19   * and is licensed under the LGPL. For more information please see
  20   * <http://creole.phpdb.org>.
  21   */
  22   
  23  require_once 'creole/Connection.php';
  24  require_once 'creole/common/ConnectionCommon.php';
  25  include_once 'creole/drivers/oracle/OCI8ResultSet.php';
  26  
  27  /**
  28   * Oracle implementation of Connection.
  29   * 
  30   * @author    David Giffin <david@giffin.org>
  31   * @author    Hans Lellelid <hans@xmpl.org>
  32   * @author    Stig Bakken <ssb@fast.no> 
  33   * @author    Lukas Smith
  34   * @version   $Revision: 1.18 $
  35   * @package   creole.drivers.oracle
  36   */ 
  37  class OCI8Connection extends ConnectionCommon implements Connection
  38  {        
  39      protected $lastStmt            = null;    
  40  
  41      /**
  42       * Auto commit mode for oci_execute
  43       * @var int
  44       */
  45      protected $execMode            = OCI_COMMIT_ON_SUCCESS;
  46  
  47      /**
  48       * Connect to a database and log in as the specified user.
  49       *
  50       * @param array $dsn The data source hash.
  51       * @param int $flags Any connection flags.
  52       * @access public
  53       * @throws SQLException
  54       * @return void
  55       */
  56      function connect( $dsninfo, $flags = 0 )
  57      {
  58          if ( !extension_loaded( 'oci8' ) )
  59          {
  60              throw new SQLException( 'oci8 extension not loaded' );
  61          }
  62  
  63          $this->dsn                = $dsninfo;
  64          $this->flags            = $flags;
  65          
  66          $persistent                =
  67              ( $flags & Creole::PERSISTENT === Creole::PERSISTENT );
  68          
  69          $user                    = $dsninfo[ 'username' ];
  70          $pw                        = $dsninfo[ 'password' ];
  71          $hostspec                = $dsninfo[ 'hostspec' ];
  72          $port       = $dsninfo[ 'port' ];
  73          $db                    = $dsninfo[ 'database' ];
  74  
  75          $connect_function        = ( $persistent )
  76                                      ? 'oci_pconnect'
  77                                      : 'oci_connect';
  78          $encoding = !empty($dsninfo['encoding']) ? $dsninfo['encoding'] : null;
  79  
  80          @ini_set( 'track_errors', true );
  81          
  82          if ( $hostspec && $port )
  83          {
  84            $hostspec .= ':' . $port;
  85          }
  86  
  87          if ( $db && $hostspec && $user && $pw )
  88      {
  89              $conn                = @$connect_function( $user, $pw, "//$hostspec/$db", $encoding);
  90      }
  91          elseif ( $hostspec && $user && $pw )
  92          {
  93              $conn                = @$connect_function( $user, $pw, $hostspec, $encoding );
  94          }
  95          
  96          elseif ( $user || $pw )
  97          {
  98              $conn                = @$connect_function( $user, $pw, null, $encoding );
  99          }
 100          
 101          else
 102          {
 103              $conn                = false;
 104          }
 105  
 106          @ini_restore( 'track_errors' );
 107          
 108          if ( $conn == false )
 109          {
 110              $error                = oci_error();
 111              $error                = ( is_array( $error ) )
 112                                      ? $error[ 'message' ]
 113                                      : null;
 114  
 115              throw new SQLException( 'connect failed', $error );
 116          }
 117  
 118          $this->dblink            = $conn;
 119  
 120          //connected ok, need to set a few environment settings
 121          //please note, if this is changed, the function setTimestamp and setDate in OCI8PreparedStatement.php
 122          //must be changed to match
 123          $sql = "ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'";
 124          $this->executeQuery($sql);
 125      }
 126  
 127  
 128      /**
 129       * @see Connection::disconnect()
 130       */
 131      function close()
 132      {
 133          $ret = @oci_close( $this->dblink );
 134          $this->dblink = null;
 135          return $ret;
 136      }        
 137      
 138      /**
 139       * @see Connection::executeQuery()
 140       */
 141      function executeQuery( $sql, $fetchmode = null )
 142      {
 143          $this->lastQuery        = $sql;
 144  
 145          // $result = @oci_parse( $this->dblink, $sql );
 146          $result                    = oci_parse( $this->dblink, $sql );
 147  
 148          if ( ! $result )
 149          {
 150              throw new SQLException( 'Unable to prepare query'
 151                  , $this->nativeError()
 152                  , $sql
 153              );
 154          }
 155  
 156          $success                = oci_execute( $result, $this->execMode );
 157  
 158          if ( ! $success )
 159          {
 160              throw new SQLException( 'Unable to execute query'
 161                  , $this->nativeError( $result )
 162                  , $sql
 163              );
 164          }
 165          
 166          return new OCI8ResultSet( $this, $result, $fetchmode );
 167      }
 168  
 169      
 170      /**
 171       * @see Connection::simpleUpdate()
 172       */
 173      
 174      function executeUpdate( $sql )
 175      {    
 176          $this->lastQuery        = $sql;
 177  
 178          $statement                = oci_parse( $this->dblink, $sql );
 179          
 180          if ( ! $statement )
 181          {
 182              throw new SQLException( 'Unable to prepare update'
 183                  , $this->nativeError()
 184                  , $sql
 185              );
 186          }
 187                  
 188          $success                = oci_execute( $statement, $this->execMode );
 189  
 190          if ( ! $success )
 191          {
 192              throw new SQLException( 'Unable to execute update'
 193                  , $this->nativeError( $statement )
 194                  , $sql
 195              );
 196          }
 197  
 198          $this->lastStmt            = $statement;
 199  
 200          return oci_num_rows( $statement );
 201      }
 202  
 203      /**
 204       * Start a database transaction.
 205       * @throws SQLException
 206       * @return void
 207       */
 208      protected function beginTrans()
 209      {
 210          $this->execMode            = OCI_DEFAULT;
 211      }
 212          
 213      /**
 214       * Commit the current transaction.
 215       * @throws SQLException
 216       * @return void
 217       */
 218      protected function commitTrans()
 219      {
 220          $result                    = oci_commit( $this->dblink );
 221  
 222          if ( ! $result )
 223          {
 224              throw new SQLException( 'Unable to commit transaction'
 225                  , $this->nativeError()
 226              );
 227          }
 228  
 229          $this->execMode            = OCI_COMMIT_ON_SUCCESS;
 230      }
 231  
 232      
 233      /**
 234       * Roll back ( undo ) the current transaction.
 235       * @throws SQLException
 236       * @return void
 237       */
 238      protected function rollbackTrans()
 239      {
 240          $result                    = oci_rollback( $this->dblink );
 241  
 242          if ( ! $result )
 243          {
 244              throw new SQLException( 'Unable to rollback transaction'
 245                  , $this->nativeError()
 246              );
 247          }
 248  
 249          $this->execMode            = OCI_COMMIT_ON_SUCCESS;
 250      }
 251  
 252      
 253      /**
 254       * Gets the number of rows affected by the data manipulation
 255       * query.
 256       *
 257       * @return int Number of rows affected by the last query.
 258       * @todo -cOCI8Connection Figure out whether getUpdateCount() should throw exception on error or just return 0.
 259       */
 260      function getUpdateCount()
 261      {
 262          if ( ! $this->lastStmt )
 263          {
 264              return 0;
 265          }
 266  
 267          $result                    = oci_num_rows( $this->lastStmt );
 268  
 269          if ( $result === false )
 270          {
 271              throw new SQLException( 'Update count failed'
 272                  , $this->nativeError( $this->lastStmt )
 273              );
 274          }
 275  
 276          return $result;
 277      }
 278  
 279  
 280     /**
 281      * Build Oracle-style query with limit or offset.
 282      * If the original SQL is in variable: query then the requlting
 283      * SQL looks like this:
 284      * <pre>
 285      * SELECT B.* FROM ( 
 286      *          SELECT A.*, rownum as TORQUE$ROWNUM FROM ( 
 287      *                  query
 288      *           ) A
 289      *      ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM
 290      *     <= offset + limit
 291      * </pre>
 292      *
 293      * @param string &$sql the query
 294      * @param int $offset
 295      * @param int $limit
 296      * @return void ( $sql parameter is currently manipulated directly )
 297      */
 298     public function applyLimit( &$sql, $offset, $limit )
 299     {
 300          $sql                    =
 301              'SELECT B.* FROM (  '
 302              .  'SELECT A.*, rownum AS CREOLE$ROWNUM FROM (  '
 303              . $sql
 304              . '  ) A '
 305              .  ' ) B WHERE ';
 306  
 307          if ( $offset > 0 )
 308          {
 309              $sql                .= ' B.CREOLE$ROWNUM > ' . $offset;            
 310  
 311              if ( $limit > 0 )
 312              {
 313                  $sql            .= ' AND B.CREOLE$ROWNUM <= '
 314                                      . ( $offset + $limit );
 315              }
 316          }
 317  
 318          else
 319          {
 320              $sql                .= ' B.CREOLE$ROWNUM <= ' . $limit;
 321          }
 322     } 
 323  
 324      /**
 325       * Get the native Oracle Error Message as a string.
 326       *
 327       * @param string $msg The Internal Error Message
 328       * @param mixed $errno The Oracle Error resource
 329       */
 330      public function nativeError( $result = null )
 331      {
 332          if ( $result !== null )
 333          {
 334              $error                = oci_error( $result );
 335          }
 336      
 337          else
 338          {
 339              $error                = oci_error( $this->dblink );
 340          }         
 341  
 342          return $error[ 'code' ] . ': ' . $error[ 'message' ];
 343      }
 344      
 345      
 346      /**
 347       * @see Connection::getDatabaseInfo()
 348       */
 349      public function getDatabaseInfo()
 350      {
 351          require_once 'creole/drivers/oracle/metadata/OCI8DatabaseInfo.php';
 352  
 353          return new OCI8DatabaseInfo( $this );
 354      }
 355      
 356      /**
 357       * @see Connection::getIdGenerator()
 358       */
 359      public function getIdGenerator()
 360      {
 361          require_once 'creole/drivers/oracle/OCI8IdGenerator.php';
 362  
 363          return new OCI8IdGenerator( $this );
 364      }
 365      
 366      /**
 367       * Oracle supports native prepared statements, but the oci_parse call
 368       * is actually called by the OCI8PreparedStatement class because
 369       * some additional SQL processing may be necessary ( e.g. to apply limit ).
 370       * @see OCI8PreparedStatement::executeQuery()
 371       * @see OCI8PreparedStatement::executeUpdate()
 372       * @see Connection::prepareStatement()
 373       */
 374      public function prepareStatement( $sql ) 
 375      {
 376          require_once 'creole/drivers/oracle/OCI8PreparedStatement.php';
 377  
 378          return new OCI8PreparedStatement( $this, $sql );
 379      }
 380      
 381      /**
 382       * @see Connection::prepareCall()
 383       */
 384      public function prepareCall( $sql )
 385      {
 386          throw new SQLException( 'Oracle driver does not yet support stored procedures using CallableStatement.' );
 387      }
 388      
 389      /**
 390       * @see Connection::createStatement()
 391       */
 392      public function createStatement()
 393      {
 394          require_once 'creole/drivers/oracle/OCI8Statement.php';
 395  
 396          return new OCI8Statement( $this );
 397      }
 398  }