1 <?php

     2 /**

     3  * Zend Framework

     4  *

     5  * LICENSE

     6  *

     7  * This source file is subject to the new BSD license that is bundled

     8  * with this package in the file LICENSE.txt.

     9  * It is also available through the world-wide-web at this URL:

    10  * http://framework.zend.com/license/new-bsd

    11  * If you did not receive a copy of the license and are unable to

    12  * obtain it through the world-wide-web, please send an email

    13  * to license@zend.com so we can send you a copy immediately.

    14  *

    15  * @category   Zend

    16  * @package    Zend_Db

    17  * @subpackage Statement

    18  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)

    19  * @license    http://framework.zend.com/license/new-bsd     New BSD License

    20  * @version    $Id: Oracle.php 20096 2010-01-06 02:05:09Z bkarwin $

    21  */

    22 

    23 /**

    24  * @see Zend_Db_Statement

    25  */

    26 require_once 'Zend/Db/Statement.php';

    27 

    28 /**

    29  * Extends for Oracle.

    30  *

    31  * @category   Zend

    32  * @package    Zend_Db

    33  * @subpackage Statement

    34  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)

    35  * @license    http://framework.zend.com/license/new-bsd     New BSD License

    36  */

    37 class Zend_Db_Statement_Oracle extends Zend_Db_Statement

    38 {

    39 

    40     /**

    41      * Column names.

    42      */

    43     protected $_keys;

    44 

    45     /**

    46      * Fetched result values.

    47      */

    48     protected $_values;

    49 

    50     /**

    51      * Check if LOB field are returned as string

    52      * instead of OCI-Lob object

    53      *

    54      * @var boolean

    55      */

    56     protected $_lobAsString = false;

    57 

    58     /**

    59      * Activate/deactivate return of LOB as string

    60      *

    61      * @param string $lob_as_string

    62      * @return Zend_Db_Statement_Oracle

    63      */

    64     public function setLobAsString($lob_as_string)

    65     {

    66         $this->_lobAsString = (bool) $lob_as_string;

    67         return $this;

    68     }

    69 

    70     /**

    71      * Return whether or not LOB are returned as string

    72      *

    73      * @return boolean

    74      */

    75     public function getLobAsString()

    76     {

    77         return $this->_lobAsString;

    78     }

    79 

    80     /**

    81      * Prepares statement handle

    82      *

    83      * @param string $sql

    84      * @return void

    85      * @throws Zend_Db_Statement_Oracle_Exception

    86      */

    87     protected function _prepare($sql)

    88     {

    89         $connection = $this->_adapter->getConnection();

    90         $this->_stmt = oci_parse($connection, $sql);

    91         if (!$this->_stmt) {

    92             /**

    93              * @see Zend_Db_Statement_Oracle_Exception

    94              */

    95             require_once 'Zend/Db/Statement/Oracle/Exception.php';

    96             throw new Zend_Db_Statement_Oracle_Exception(oci_error($connection));

    97         }

    98     }

    99 

   100     /**

   101      * Binds a parameter to the specified variable name.

   102      *

   103      * @param mixed $parameter Name the parameter, either integer or string.

   104      * @param mixed $variable  Reference to PHP variable containing the value.

   105      * @param mixed $type      OPTIONAL Datatype of SQL parameter.

   106      * @param mixed $length    OPTIONAL Length of SQL parameter.

   107      * @param mixed $options   OPTIONAL Other options.

   108      * @return bool

   109      * @throws Zend_Db_Statement_Exception

   110      */

   111     protected function _bindParam($parameter, &$variable, $type = null, $length = null, $options = null)

   112     {

   113         // default value

   114         if ($type === NULL) {

   115             $type = SQLT_CHR;

   116         }

   117 

   118         // default value

   119         if ($length === NULL) {

   120             $length = -1;

   121         }

   122 

   123         $retval = @oci_bind_by_name($this->_stmt, $parameter, $variable, $length, $type);

   124         if ($retval === false) {

   125             /**

   126              * @see Zend_Db_Adapter_Oracle_Exception

   127              */

   128             require_once 'Zend/Db/Statement/Oracle/Exception.php';

   129             throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));

   130         }

   131 

   132         return true;

   133     }

   134 

   135     /**

   136      * Closes the cursor, allowing the statement to be executed again.

   137      *

   138      * @return bool

   139      */

   140     public function closeCursor()

   141     {

   142         if (!$this->_stmt) {

   143             return false;

   144         }

   145 

   146         oci_free_statement($this->_stmt);

   147         $this->_stmt = false;

   148         return true;

   149     }

   150 

   151     /**

   152      * Returns the number of columns in the result set.

   153      * Returns null if the statement has no result set metadata.

   154      *

   155      * @return int The number of columns.

   156      */

   157     public function columnCount()

   158     {

   159         if (!$this->_stmt) {

   160             return false;

   161         }

   162 

   163         return oci_num_fields($this->_stmt);

   164     }

   165 

   166 

   167     /**

   168      * Retrieves the error code, if any, associated with the last operation on

   169      * the statement handle.

   170      *

   171      * @return string error code.

   172      */

   173     public function errorCode()

   174     {

   175         if (!$this->_stmt) {

   176             return false;

   177         }

   178 

   179         $error = oci_error($this->_stmt);

   180 

   181         if (!$error) {

   182             return false;

   183         }

   184 

   185         return $error['code'];

   186     }

   187 

   188 

   189     /**

   190      * Retrieves an array of error information, if any, associated with the

   191      * last operation on the statement handle.

   192      *

   193      * @return array

   194      */

   195     public function errorInfo()

   196     {

   197         if (!$this->_stmt) {

   198             return false;

   199         }

   200 

   201         $error = oci_error($this->_stmt);

   202         if (!$error) {

   203             return false;

   204         }

   205 

   206         if (isset($error['sqltext'])) {

   207             return array(

   208                 $error['code'],

   209                 $error['message'],

   210                 $error['offset'],

   211                 $error['sqltext'],

   212             );

   213         } else {

   214             return array(

   215                 $error['code'],

   216                 $error['message'],

   217             );

   218         }

   219     }

   220 

   221 

   222     /**

   223      * Executes a prepared statement.

   224      *

   225      * @param array $params OPTIONAL Values to bind to parameter placeholders.

   226      * @return bool

   227      * @throws Zend_Db_Statement_Exception

   228      */

   229     public function _execute(array $params = null)

   230     {

   231         $connection = $this->_adapter->getConnection();

   232 

   233         if (!$this->_stmt) {

   234             return false;

   235         }

   236 

   237         if ($params !== null) {

   238             if (!is_array($params)) {

   239                 $params = array($params);

   240             }

   241             $error = false;

   242             foreach (array_keys($params) as $name) {

   243                 if (!@oci_bind_by_name($this->_stmt, $name, $params[$name], -1)) {

   244                     $error = true;

   245                     break;

   246                 }

   247             }

   248             if ($error) {

   249                 /**

   250                  * @see Zend_Db_Adapter_Oracle_Exception

   251                  */

   252                 require_once 'Zend/Db/Statement/Oracle/Exception.php';

   253                 throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));

   254             }

   255         }

   256 

   257         $retval = @oci_execute($this->_stmt, $this->_adapter->_getExecuteMode());

   258         if ($retval === false) {

   259             /**

   260              * @see Zend_Db_Adapter_Oracle_Exception

   261              */

   262             require_once 'Zend/Db/Statement/Oracle/Exception.php';

   263             throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));

   264         }

   265 

   266         $this->_keys = Array();

   267         if ($field_num = oci_num_fields($this->_stmt)) {

   268             for ($i = 1; $i <= $field_num; $i++) {

   269                 $name = oci_field_name($this->_stmt, $i);

   270                 $this->_keys[] = $name;

   271             }

   272         }

   273 

   274         $this->_values = Array();

   275         if ($this->_keys) {

   276             $this->_values = array_fill(0, count($this->_keys), null);

   277         }

   278 

   279         return $retval;

   280     }

   281 

   282     /**

   283      * Fetches a row from the result set.

   284      *

   285      * @param int $style  OPTIONAL Fetch mode for this fetch operation.

   286      * @param int $cursor OPTIONAL Absolute, relative, or other.

   287      * @param int $offset OPTIONAL Number for absolute or relative cursors.

   288      * @return mixed Array, object, or scalar depending on fetch mode.

   289      * @throws Zend_Db_Statement_Exception

   290      */

   291     public function fetch($style = null, $cursor = null, $offset = null)

   292     {

   293         if (!$this->_stmt) {

   294             return false;

   295         }

   296 

   297         if ($style === null) {

   298             $style = $this->_fetchMode;

   299         }

   300 

   301         $lob_as_string = $this->getLobAsString() ? OCI_RETURN_LOBS : 0;

   302 

   303         switch ($style) {

   304             case Zend_Db::FETCH_NUM:

   305                 $row = oci_fetch_array($this->_stmt, OCI_NUM | OCI_RETURN_NULLS | $lob_as_string);

   306                 break;

   307             case Zend_Db::FETCH_ASSOC:

   308                 $row = oci_fetch_array($this->_stmt, OCI_ASSOC | OCI_RETURN_NULLS | $lob_as_string);

   309                 break;

   310             case Zend_Db::FETCH_BOTH:

   311                 $row = oci_fetch_array($this->_stmt, OCI_BOTH | OCI_RETURN_NULLS | $lob_as_string);

   312                 break;

   313             case Zend_Db::FETCH_OBJ:

   314                 $row = oci_fetch_object($this->_stmt);

   315                 break;

   316             case Zend_Db::FETCH_BOUND:

   317                 $row = oci_fetch_array($this->_stmt, OCI_BOTH | OCI_RETURN_NULLS | $lob_as_string);

   318                 if ($row !== false) {

   319                     return $this->_fetchBound($row);

   320                 }

   321                 break;

   322             default:

   323                 /**

   324                  * @see Zend_Db_Adapter_Oracle_Exception

   325                  */

   326                 require_once 'Zend/Db/Statement/Oracle/Exception.php';

   327                 throw new Zend_Db_Statement_Oracle_Exception(

   328                     array(

   329                         'code'    => 'HYC00',

   330                         'message' => "Invalid fetch mode '$style' specified"

   331                     )

   332                 );

   333                 break;

   334         }

   335 

   336         if (! $row && $error = oci_error($this->_stmt)) {

   337             /**

   338              * @see Zend_Db_Adapter_Oracle_Exception

   339              */

   340             require_once 'Zend/Db/Statement/Oracle/Exception.php';

   341             throw new Zend_Db_Statement_Oracle_Exception($error);

   342         }

   343 

   344         if (is_array($row) && array_key_exists('zend_db_rownum', $row)) {

   345             unset($row['zend_db_rownum']);

   346         }

   347 

   348         return $row;

   349     }

   350 

   351     /**

   352      * Returns an array containing all of the result set rows.

   353      *

   354      * @param int $style OPTIONAL Fetch mode.

   355      * @param int $col   OPTIONAL Column number, if fetch mode is by column.

   356      * @return array Collection of rows, each in a format by the fetch mode.

   357      * @throws Zend_Db_Statement_Exception

   358      */

   359     public function fetchAll($style = null, $col = 0)

   360     {

   361         if (!$this->_stmt) {

   362             return false;

   363         }

   364 

   365         // make sure we have a fetch mode

   366         if ($style === null) {

   367             $style = $this->_fetchMode;

   368         }

   369 

   370         $flags = OCI_FETCHSTATEMENT_BY_ROW;

   371 

   372         switch ($style) {

   373             case Zend_Db::FETCH_BOTH:

   374                 /**

   375                  * @see Zend_Db_Adapter_Oracle_Exception

   376                  */

   377                 require_once 'Zend/Db/Statement/Oracle/Exception.php';

   378                 throw new Zend_Db_Statement_Oracle_Exception(

   379                     array(

   380                         'code'    => 'HYC00',

   381                         'message' => "OCI8 driver does not support fetchAll(FETCH_BOTH), use fetch() in a loop instead"

   382                     )

   383                 );

   384                 // notreached

   385                 $flags |= OCI_NUM;

   386                 $flags |= OCI_ASSOC;

   387                 break;

   388             case Zend_Db::FETCH_NUM:

   389                 $flags |= OCI_NUM;

   390                 break;

   391             case Zend_Db::FETCH_ASSOC:

   392                 $flags |= OCI_ASSOC;

   393                 break;

   394             case Zend_Db::FETCH_OBJ:

   395                 break;

   396             case Zend_Db::FETCH_COLUMN:

   397                 $flags = $flags &~ OCI_FETCHSTATEMENT_BY_ROW;

   398                 $flags |= OCI_FETCHSTATEMENT_BY_COLUMN;

   399                 $flags |= OCI_NUM;

   400                 break;

   401             default:

   402                 /**

   403                  * @see Zend_Db_Adapter_Oracle_Exception

   404                  */

   405                 require_once 'Zend/Db/Statement/Oracle/Exception.php';

   406                 throw new Zend_Db_Statement_Oracle_Exception(

   407                     array(

   408                         'code'    => 'HYC00',

   409                         'message' => "Invalid fetch mode '$style' specified"

   410                     )

   411                 );

   412                 break;

   413         }

   414 

   415         $result = Array();

   416         if ($flags != OCI_FETCHSTATEMENT_BY_ROW) { /* not Zend_Db::FETCH_OBJ */

   417             if (! ($rows = oci_fetch_all($this->_stmt, $result, 0, -1, $flags) )) {

   418                 if ($error = oci_error($this->_stmt)) {

   419                     /**

   420                      * @see Zend_Db_Adapter_Oracle_Exception

   421                      */

   422                     require_once 'Zend/Db/Statement/Oracle/Exception.php';

   423                     throw new Zend_Db_Statement_Oracle_Exception($error);

   424                 }

   425                 if (!$rows) {

   426                     return array();

   427                 }

   428             }

   429             if ($style == Zend_Db::FETCH_COLUMN) {

   430                 $result = $result[$col];

   431             }

   432             foreach ($result as &$row) {

   433                 if (is_array($row) && array_key_exists('zend_db_rownum', $row)) {

   434                     unset($row['zend_db_rownum']);

   435                 }

   436             }

   437         } else {

   438             while (($row = oci_fetch_object($this->_stmt)) !== false) {

   439                 $result [] = $row;

   440             }

   441             if ($error = oci_error($this->_stmt)) {

   442                 /**

   443                  * @see Zend_Db_Adapter_Oracle_Exception

   444                  */

   445                 require_once 'Zend/Db/Statement/Oracle/Exception.php';

   446                 throw new Zend_Db_Statement_Oracle_Exception($error);

   447             }

   448         }

   449 

   450         return $result;

   451     }

   452 

   453 

   454     /**

   455      * Returns a single column from the next row of a result set.

   456      *

   457      * @param int $col OPTIONAL Position of the column to fetch.

   458      * @return string

   459      * @throws Zend_Db_Statement_Exception

   460      */

   461     public function fetchColumn($col = 0)

   462     {

   463         if (!$this->_stmt) {

   464             return false;

   465         }

   466 

   467         if (!oci_fetch($this->_stmt)) {

   468             // if no error, there is simply no record

   469             if (!$error = oci_error($this->_stmt)) {

   470                 return false;

   471             }

   472             /**

   473              * @see Zend_Db_Adapter_Oracle_Exception

   474              */

   475             require_once 'Zend/Db/Statement/Oracle/Exception.php';

   476             throw new Zend_Db_Statement_Oracle_Exception($error);

   477         }

   478 

   479         $data = oci_result($this->_stmt, $col+1); //1-based

   480         if ($data === false) {

   481             /**

   482              * @see Zend_Db_Adapter_Oracle_Exception

   483              */

   484             require_once 'Zend/Db/Statement/Oracle/Exception.php';

   485             throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));

   486         }

   487 

   488         if ($this->getLobAsString()) {

   489             // instanceof doesn't allow '-', we must use a temporary string

   490             $type = 'OCI-Lob';

   491             if ($data instanceof $type) {

   492                 $data = $data->read($data->size());

   493             }

   494         }

   495 

   496         return $data;

   497     }

   498 

   499     /**

   500      * Fetches the next row and returns it as an object.

   501      *

   502      * @param string $class  OPTIONAL Name of the class to create.

   503      * @param array  $config OPTIONAL Constructor arguments for the class.

   504      * @return mixed One object instance of the specified class.

   505      * @throws Zend_Db_Statement_Exception

   506      */

   507     public function fetchObject($class = 'stdClass', array $config = array())

   508     {

   509         if (!$this->_stmt) {

   510             return false;

   511         }

   512 

   513         $obj = oci_fetch_object($this->_stmt);

   514 

   515         if ($error = oci_error($this->_stmt)) {

   516             /**

   517              * @see Zend_Db_Adapter_Oracle_Exception

   518              */

   519             require_once 'Zend/Db/Statement/Oracle/Exception.php';

   520             throw new Zend_Db_Statement_Oracle_Exception($error);

   521         }

   522 

   523         /* @todo XXX handle parameters */

   524 

   525         return $obj;

   526     }

   527 

   528     /**

   529      * Retrieves the next rowset (result set) for a SQL statement that has

   530      * multiple result sets.  An example is a stored procedure that returns

   531      * the results of multiple queries.

   532      *

   533      * @return bool

   534      * @throws Zend_Db_Statement_Exception

   535      */

   536     public function nextRowset()

   537     {

   538         /**

   539          * @see Zend_Db_Statement_Oracle_Exception

   540          */

   541         require_once 'Zend/Db/Statement/Oracle/Exception.php';

   542         throw new Zend_Db_Statement_Oracle_Exception(

   543             array(

   544                 'code'    => 'HYC00',

   545                 'message' => 'Optional feature not implemented'

   546             )

   547         );

   548     }

   549 

   550     /**

   551      * Returns the number of rows affected by the execution of the

   552      * last INSERT, DELETE, or UPDATE statement executed by this

   553      * statement object.

   554      *

   555      * @return int     The number of rows affected.

   556      * @throws Zend_Db_Statement_Exception

   557      */

   558     public function rowCount()

   559     {

   560         if (!$this->_stmt) {

   561             return false;

   562         }

   563 

   564         $num_rows = oci_num_rows($this->_stmt);

   565 

   566         if ($num_rows === false) {

   567             /**

   568              * @see Zend_Db_Adapter_Oracle_Exception

   569              */

   570             require_once 'Zend/Db/Statement/Oracle/Exception.php';

   571             throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));

   572         }

   573 

   574         return $num_rows;

   575     }

   576 

   577 }