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