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-2015 Zend Technologies USA Inc. (http://www.zend.com)
19 * @license    http://framework.zend.com/license/new-bsd     New BSD License
20 * @version    $Id$
21 */
22
23/**
24 * @see Zend_Db_Statement
25 */
26
27/**
28 * Extends for Oracle.
29 *
30 * @category   Zend
31 * @package    Zend_Db
32 * @subpackage Statement
33 * @copyright  Copyright (c) 2005-2015 Zend Technologies USA Inc. (http://www.zend.com)
34 * @license    http://framework.zend.com/license/new-bsd     New BSD License
35 */
36class Zend_Db_Statement_Oracle extends Zend_Db_Statement
37{
38
39    /**
40     * Column names.
41     */
42    protected $_keys;
43
44    /**
45     * Fetched result values.
46     */
47    protected $_values;
48
49    /**
50     * Check if LOB field are returned as string
51     * instead of OCI-Lob object
52     *
53     * @var boolean
54     */
55    protected $_lobAsString = false;
56
57    /**
58     * Activate/deactivate return of LOB as string
59     *
60     * @param string $lob_as_string
61     * @return Zend_Db_Statement_Oracle
62     */
63    public function setLobAsString($lob_as_string)
64    {
65        $this->_lobAsString = (bool) $lob_as_string;
66        return $this;
67    }
68
69    /**
70     * Return whether or not LOB are returned as string
71     *
72     * @return boolean
73     */
74    public function getLobAsString()
75    {
76        return $this->_lobAsString;
77    }
78
79    /**
80     * Prepares statement handle
81     *
82     * @param string $sql
83     * @return void
84     * @throws Zend_Db_Statement_Oracle_Exception
85     */
86    protected function _prepare($sql)
87    {
88        $connection = $this->_adapter->getConnection();
89        $this->_stmt = @oci_parse($connection, $sql);
90        if (!$this->_stmt) {
91            /**
92             * @see Zend_Db_Statement_Oracle_Exception
93             */
94            throw new Zend_Db_Statement_Oracle_Exception(oci_error($connection));
95        }
96    }
97
98    /**
99     * Binds a parameter to the specified variable name.
100     *
101     * @param mixed $parameter Name the parameter, either integer or string.
102     * @param mixed $variable  Reference to PHP variable containing the value.
103     * @param mixed $type      OPTIONAL Datatype of SQL parameter.
104     * @param mixed $length    OPTIONAL Length of SQL parameter.
105     * @param mixed $options   OPTIONAL Other options.
106     * @return bool
107     * @throws Zend_Db_Statement_Exception
108     */
109    protected function _bindParam($parameter, &$variable, $type = null, $length = null, $options = null)
110    {
111        // default value
112        if ($type === NULL) {
113            $type = SQLT_CHR;
114        }
115
116        // default value
117        if ($length === NULL) {
118            $length = -1;
119        }
120
121        $retval = @oci_bind_by_name($this->_stmt, $parameter, $variable, $length, $type);
122        if ($retval === false) {
123            /**
124             * @see Zend_Db_Adapter_Oracle_Exception
125             */
126            throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));
127        }
128
129        return true;
130    }
131
132    /**
133     * Closes the cursor, allowing the statement to be executed again.
134     *
135     * @return bool
136     */
137    public function closeCursor()
138    {
139        if (!$this->_stmt) {
140            return false;
141        }
142
143        oci_free_statement($this->_stmt);
144        $this->_stmt = false;
145        return true;
146    }
147
148    /**
149     * Returns the number of columns in the result set.
150     * Returns null if the statement has no result set metadata.
151     *
152     * @return int The number of columns.
153     */
154    public function columnCount()
155    {
156        if (!$this->_stmt) {
157            return false;
158        }
159
160        return oci_num_fields($this->_stmt);
161    }
162
163
164    /**
165     * Retrieves the error code, if any, associated with the last operation on
166     * the statement handle.
167     *
168     * @return string error code.
169     */
170    public function errorCode()
171    {
172        if (!$this->_stmt) {
173            return false;
174        }
175
176        $error = oci_error($this->_stmt);
177
178        if (!$error) {
179            return false;
180        }
181
182        return $error['code'];
183    }
184
185
186    /**
187     * Retrieves an array of error information, if any, associated with the
188     * last operation on the statement handle.
189     *
190     * @return array
191     */
192    public function errorInfo()
193    {
194        if (!$this->_stmt) {
195            return false;
196        }
197
198        $error = oci_error($this->_stmt);
199        if (!$error) {
200            return false;
201        }
202
203        if (isset($error['sqltext'])) {
204            return array(
205                $error['code'],
206                $error['message'],
207                $error['offset'],
208                $error['sqltext'],
209            );
210        } else {
211            return array(
212                $error['code'],
213                $error['message'],
214            );
215        }
216    }
217
218
219    /**
220     * Executes a prepared statement.
221     *
222     * @param array $params OPTIONAL Values to bind to parameter placeholders.
223     * @return bool
224     * @throws Zend_Db_Statement_Exception
225     */
226    public function _execute(array $params = null)
227    {
228        $connection = $this->_adapter->getConnection();
229
230        if (!$this->_stmt) {
231            return false;
232        }
233
234        if ($params !== null) {
235            if (!is_array($params)) {
236                $params = array($params);
237            }
238            $error = false;
239            foreach (array_keys($params) as $name) {
240                if (!$this->bindParam($name, $params[$name], null, -1)) {
241                    $error = true;
242                    break;
243                }
244            }
245            if ($error) {
246                /**
247                 * @see Zend_Db_Adapter_Oracle_Exception
248                 */
249                throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));
250            }
251        }
252
253        $retval = @oci_execute($this->_stmt, $this->_adapter->_getExecuteMode());
254        if ($retval === false) {
255            /**
256             * @see Zend_Db_Adapter_Oracle_Exception
257             */
258            throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));
259        }
260
261        $this->_keys = Array();
262        if ($field_num = oci_num_fields($this->_stmt)) {
263            for ($i = 1; $i <= $field_num; $i++) {
264                $name = oci_field_name($this->_stmt, $i);
265                $this->_keys[] = $name;
266            }
267        }
268
269        $this->_values = Array();
270        if ($this->_keys) {
271            $this->_values = array_fill(0, count($this->_keys), null);
272        }
273
274        return $retval;
275    }
276
277    /**
278     * Fetches a row from the result set.
279     *
280     * @param int $style  OPTIONAL Fetch mode for this fetch operation.
281     * @param int $cursor OPTIONAL Absolute, relative, or other.
282     * @param int $offset OPTIONAL Number for absolute or relative cursors.
283     * @return mixed Array, object, or scalar depending on fetch mode.
284     * @throws Zend_Db_Statement_Exception
285     */
286    public function fetch($style = null, $cursor = null, $offset = null)
287    {
288        if (!$this->_stmt) {
289            return false;
290        }
291
292        if ($style === null) {
293            $style = $this->_fetchMode;
294        }
295
296        $lob_as_string = $this->getLobAsString() ? OCI_RETURN_LOBS : 0;
297
298        switch ($style) {
299            case Zend_Db::FETCH_NUM:
300                $row = oci_fetch_array($this->_stmt, OCI_NUM | OCI_RETURN_NULLS | $lob_as_string);
301                break;
302            case Zend_Db::FETCH_ASSOC:
303                $row = oci_fetch_array($this->_stmt, OCI_ASSOC | OCI_RETURN_NULLS | $lob_as_string);
304                break;
305            case Zend_Db::FETCH_BOTH:
306                $row = oci_fetch_array($this->_stmt, OCI_BOTH | OCI_RETURN_NULLS | $lob_as_string);
307                break;
308            case Zend_Db::FETCH_OBJ:
309                $row = oci_fetch_object($this->_stmt);
310                break;
311            case Zend_Db::FETCH_BOUND:
312                $row = oci_fetch_array($this->_stmt, OCI_BOTH | OCI_RETURN_NULLS | $lob_as_string);
313                if ($row !== false) {
314                    return $this->_fetchBound($row);
315                }
316                break;
317            default:
318                /**
319                 * @see Zend_Db_Adapter_Oracle_Exception
320                 */
321                throw new Zend_Db_Statement_Oracle_Exception(
322                    array(
323                        'code'    => 'HYC00',
324                        'message' => "Invalid fetch mode '$style' specified"
325                    )
326                );
327                break;
328        }
329
330        if (! $row && $error = oci_error($this->_stmt)) {
331            /**
332             * @see Zend_Db_Adapter_Oracle_Exception
333             */
334            throw new Zend_Db_Statement_Oracle_Exception($error);
335        }
336
337        if (is_array($row) && array_key_exists('zend_db_rownum', $row)) {
338            unset($row['zend_db_rownum']);
339        }
340
341        return $row;
342    }
343
344    /**
345     * Returns an array containing all of the result set rows.
346     *
347     * @param int $style OPTIONAL Fetch mode.
348     * @param int $col   OPTIONAL Column number, if fetch mode is by column.
349     * @return array Collection of rows, each in a format by the fetch mode.
350     * @throws Zend_Db_Statement_Exception
351     */
352    public function fetchAll($style = null, $col = 0)
353    {
354        if (!$this->_stmt) {
355            return false;
356        }
357
358        // make sure we have a fetch mode
359        if ($style === null) {
360            $style = $this->_fetchMode;
361        }
362
363        $flags = OCI_FETCHSTATEMENT_BY_ROW;
364
365        switch ($style) {
366            case Zend_Db::FETCH_BOTH:
367                /**
368                 * @see Zend_Db_Adapter_Oracle_Exception
369                 */
370                throw new Zend_Db_Statement_Oracle_Exception(
371                    array(
372                        'code'    => 'HYC00',
373                        'message' => "OCI8 driver does not support fetchAll(FETCH_BOTH), use fetch() in a loop instead"
374                    )
375                );
376                // notreached
377                $flags |= OCI_NUM;
378                $flags |= OCI_ASSOC;
379                break;
380            case Zend_Db::FETCH_NUM:
381                $flags |= OCI_NUM;
382                break;
383            case Zend_Db::FETCH_ASSOC:
384                $flags |= OCI_ASSOC;
385                break;
386            case Zend_Db::FETCH_OBJ:
387                break;
388            case Zend_Db::FETCH_COLUMN:
389                $flags = $flags &~ OCI_FETCHSTATEMENT_BY_ROW;
390                $flags |= OCI_FETCHSTATEMENT_BY_COLUMN;
391                $flags |= OCI_NUM;
392                break;
393            default:
394                /**
395                 * @see Zend_Db_Adapter_Oracle_Exception
396                 */
397                throw new Zend_Db_Statement_Oracle_Exception(
398                    array(
399                        'code'    => 'HYC00',
400                        'message' => "Invalid fetch mode '$style' specified"
401                    )
402                );
403                break;
404        }
405
406        $result = Array();
407        if ($flags != OCI_FETCHSTATEMENT_BY_ROW) { /* not Zend_Db::FETCH_OBJ */
408            if (! ($rows = oci_fetch_all($this->_stmt, $result, 0, -1, $flags) )) {
409                if ($error = oci_error($this->_stmt)) {
410                    /**
411                     * @see Zend_Db_Adapter_Oracle_Exception
412                     */
413                    throw new Zend_Db_Statement_Oracle_Exception($error);
414                }
415                if (!$rows) {
416                    return array();
417                }
418            }
419            if ($style == Zend_Db::FETCH_COLUMN) {
420                $result = $result[$col];
421            }
422            foreach ($result as &$row) {
423                if (is_array($row) && array_key_exists('zend_db_rownum', $row)) {
424                    unset($row['zend_db_rownum']);
425                }
426            }
427        } else {
428            while (($row = oci_fetch_object($this->_stmt)) !== false) {
429                $result [] = $row;
430            }
431            if ($error = oci_error($this->_stmt)) {
432                /**
433                 * @see Zend_Db_Adapter_Oracle_Exception
434                 */
435                throw new Zend_Db_Statement_Oracle_Exception($error);
436            }
437        }
438
439        return $result;
440    }
441
442
443    /**
444     * Returns a single column from the next row of a result set.
445     *
446     * @param int $col OPTIONAL Position of the column to fetch.
447     * @return string
448     * @throws Zend_Db_Statement_Exception
449     */
450    public function fetchColumn($col = 0)
451    {
452        if (!$this->_stmt) {
453            return false;
454        }
455
456        if (!oci_fetch($this->_stmt)) {
457            // if no error, there is simply no record
458            if (!$error = oci_error($this->_stmt)) {
459                return false;
460            }
461            /**
462             * @see Zend_Db_Adapter_Oracle_Exception
463             */
464            throw new Zend_Db_Statement_Oracle_Exception($error);
465        }
466
467        $data = oci_result($this->_stmt, $col+1); //1-based
468        if ($data === false) {
469            /**
470             * @see Zend_Db_Adapter_Oracle_Exception
471             */
472            throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));
473        }
474
475        if ($this->getLobAsString()) {
476            // instanceof doesn't allow '-', we must use a temporary string
477            $type = 'OCI-Lob';
478            if ($data instanceof $type) {
479                $data = $data->read($data->size());
480            }
481        }
482
483        return $data;
484    }
485
486    /**
487     * Fetches the next row and returns it as an object.
488     *
489     * @param string $class  OPTIONAL Name of the class to create.
490     * @param array  $config OPTIONAL Constructor arguments for the class.
491     * @return mixed One object instance of the specified class.
492     * @throws Zend_Db_Statement_Exception
493     */
494    public function fetchObject($class = 'stdClass', array $config = array())
495    {
496        if (!$this->_stmt) {
497            return false;
498        }
499
500        $obj = oci_fetch_object($this->_stmt);
501
502        if ($error = oci_error($this->_stmt)) {
503            /**
504             * @see Zend_Db_Adapter_Oracle_Exception
505             */
506            throw new Zend_Db_Statement_Oracle_Exception($error);
507        }
508
509        /* @todo XXX handle parameters */
510
511        return $obj;
512    }
513
514    /**
515     * Retrieves the next rowset (result set) for a SQL statement that has
516     * multiple result sets.  An example is a stored procedure that returns
517     * the results of multiple queries.
518     *
519     * @return bool
520     * @throws Zend_Db_Statement_Exception
521     */
522    public function nextRowset()
523    {
524        /**
525         * @see Zend_Db_Statement_Oracle_Exception
526         */
527        throw new Zend_Db_Statement_Oracle_Exception(
528            array(
529                'code'    => 'HYC00',
530                'message' => 'Optional feature not implemented'
531            )
532        );
533    }
534
535    /**
536     * Returns the number of rows affected by the execution of the
537     * last INSERT, DELETE, or UPDATE statement executed by this
538     * statement object.
539     *
540     * @return int     The number of rows affected.
541     * @throws Zend_Db_Statement_Exception
542     */
543    public function rowCount()
544    {
545        if (!$this->_stmt) {
546            return false;
547        }
548
549        $num_rows = oci_num_rows($this->_stmt);
550
551        if ($num_rows === false) {
552            /**
553             * @see Zend_Db_Adapter_Oracle_Exception
554             */
555            throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));
556        }
557
558        return $num_rows;
559    }
560
561}
562