OCI8Statement.php 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510
  1. <?php
  2. namespace Doctrine\DBAL\Driver\OCI8;
  3. use Doctrine\DBAL\Driver\Statement;
  4. use Doctrine\DBAL\Driver\StatementIterator;
  5. use Doctrine\DBAL\FetchMode;
  6. use Doctrine\DBAL\ParameterType;
  7. use InvalidArgumentException;
  8. use IteratorAggregate;
  9. use PDO;
  10. use const OCI_ASSOC;
  11. use const OCI_B_BIN;
  12. use const OCI_B_BLOB;
  13. use const OCI_BOTH;
  14. use const OCI_D_LOB;
  15. use const OCI_FETCHSTATEMENT_BY_COLUMN;
  16. use const OCI_FETCHSTATEMENT_BY_ROW;
  17. use const OCI_NUM;
  18. use const OCI_RETURN_LOBS;
  19. use const OCI_RETURN_NULLS;
  20. use const OCI_TEMP_BLOB;
  21. use const PREG_OFFSET_CAPTURE;
  22. use const SQLT_CHR;
  23. use function array_key_exists;
  24. use function count;
  25. use function implode;
  26. use function is_numeric;
  27. use function oci_bind_by_name;
  28. use function oci_cancel;
  29. use function oci_error;
  30. use function oci_execute;
  31. use function oci_fetch_all;
  32. use function oci_fetch_array;
  33. use function oci_fetch_object;
  34. use function oci_new_descriptor;
  35. use function oci_num_fields;
  36. use function oci_num_rows;
  37. use function oci_parse;
  38. use function preg_match;
  39. use function preg_quote;
  40. use function sprintf;
  41. use function substr;
  42. /**
  43. * The OCI8 implementation of the Statement interface.
  44. */
  45. class OCI8Statement implements IteratorAggregate, Statement
  46. {
  47. /** @var resource */
  48. protected $_dbh;
  49. /** @var resource */
  50. protected $_sth;
  51. /** @var OCI8Connection */
  52. protected $_conn;
  53. /** @var string */
  54. protected static $_PARAM = ':param';
  55. /** @var int[] */
  56. protected static $fetchModeMap = [
  57. FetchMode::MIXED => OCI_BOTH,
  58. FetchMode::ASSOCIATIVE => OCI_ASSOC,
  59. FetchMode::NUMERIC => OCI_NUM,
  60. FetchMode::COLUMN => OCI_NUM,
  61. ];
  62. /** @var int */
  63. protected $_defaultFetchMode = FetchMode::MIXED;
  64. /** @var string[] */
  65. protected $_paramMap = [];
  66. /**
  67. * Holds references to bound parameter values.
  68. *
  69. * This is a new requirement for PHP7's oci8 extension that prevents bound values from being garbage collected.
  70. *
  71. * @var mixed[]
  72. */
  73. private $boundValues = [];
  74. /**
  75. * Indicates whether the statement is in the state when fetching results is possible
  76. *
  77. * @var bool
  78. */
  79. private $result = false;
  80. /**
  81. * Creates a new OCI8Statement that uses the given connection handle and SQL statement.
  82. *
  83. * @param resource $dbh The connection handle.
  84. * @param string $statement The SQL statement.
  85. */
  86. public function __construct($dbh, $statement, OCI8Connection $conn)
  87. {
  88. [$statement, $paramMap] = self::convertPositionalToNamedPlaceholders($statement);
  89. $this->_sth = oci_parse($dbh, $statement);
  90. $this->_dbh = $dbh;
  91. $this->_paramMap = $paramMap;
  92. $this->_conn = $conn;
  93. }
  94. /**
  95. * Converts positional (?) into named placeholders (:param<num>).
  96. *
  97. * Oracle does not support positional parameters, hence this method converts all
  98. * positional parameters into artificially named parameters. Note that this conversion
  99. * is not perfect. All question marks (?) in the original statement are treated as
  100. * placeholders and converted to a named parameter.
  101. *
  102. * The algorithm uses a state machine with two possible states: InLiteral and NotInLiteral.
  103. * Question marks inside literal strings are therefore handled correctly by this method.
  104. * This comes at a cost, the whole sql statement has to be looped over.
  105. *
  106. * @param string $statement The SQL statement to convert.
  107. *
  108. * @return mixed[] [0] => the statement value (string), [1] => the paramMap value (array).
  109. *
  110. * @throws OCI8Exception
  111. *
  112. * @todo extract into utility class in Doctrine\DBAL\Util namespace
  113. * @todo review and test for lost spaces. we experienced missing spaces with oci8 in some sql statements.
  114. */
  115. public static function convertPositionalToNamedPlaceholders($statement)
  116. {
  117. $fragmentOffset = $tokenOffset = 0;
  118. $fragments = $paramMap = [];
  119. $currentLiteralDelimiter = null;
  120. do {
  121. if (! $currentLiteralDelimiter) {
  122. $result = self::findPlaceholderOrOpeningQuote(
  123. $statement,
  124. $tokenOffset,
  125. $fragmentOffset,
  126. $fragments,
  127. $currentLiteralDelimiter,
  128. $paramMap
  129. );
  130. } else {
  131. $result = self::findClosingQuote($statement, $tokenOffset, $currentLiteralDelimiter);
  132. }
  133. } while ($result);
  134. if ($currentLiteralDelimiter) {
  135. throw new OCI8Exception(sprintf(
  136. 'The statement contains non-terminated string literal starting at offset %d',
  137. $tokenOffset - 1
  138. ));
  139. }
  140. $fragments[] = substr($statement, $fragmentOffset);
  141. $statement = implode('', $fragments);
  142. return [$statement, $paramMap];
  143. }
  144. /**
  145. * Finds next placeholder or opening quote.
  146. *
  147. * @param string $statement The SQL statement to parse
  148. * @param string $tokenOffset The offset to start searching from
  149. * @param int $fragmentOffset The offset to build the next fragment from
  150. * @param string[] $fragments Fragments of the original statement not containing placeholders
  151. * @param string|null $currentLiteralDelimiter The delimiter of the current string literal
  152. * or NULL if not currently in a literal
  153. * @param array<int, string> $paramMap Mapping of the original parameter positions to their named replacements
  154. *
  155. * @return bool Whether the token was found
  156. */
  157. private static function findPlaceholderOrOpeningQuote(
  158. $statement,
  159. &$tokenOffset,
  160. &$fragmentOffset,
  161. &$fragments,
  162. &$currentLiteralDelimiter,
  163. &$paramMap
  164. ) {
  165. $token = self::findToken($statement, $tokenOffset, '/[?\'"]/');
  166. if (! $token) {
  167. return false;
  168. }
  169. if ($token === '?') {
  170. $position = count($paramMap) + 1;
  171. $param = ':param' . $position;
  172. $fragments[] = substr($statement, $fragmentOffset, $tokenOffset - $fragmentOffset);
  173. $fragments[] = $param;
  174. $paramMap[$position] = $param;
  175. $tokenOffset += 1;
  176. $fragmentOffset = $tokenOffset;
  177. return true;
  178. }
  179. $currentLiteralDelimiter = $token;
  180. ++$tokenOffset;
  181. return true;
  182. }
  183. /**
  184. * Finds closing quote
  185. *
  186. * @param string $statement The SQL statement to parse
  187. * @param string $tokenOffset The offset to start searching from
  188. * @param string|null $currentLiteralDelimiter The delimiter of the current string literal
  189. * or NULL if not currently in a literal
  190. *
  191. * @return bool Whether the token was found
  192. */
  193. private static function findClosingQuote(
  194. $statement,
  195. &$tokenOffset,
  196. &$currentLiteralDelimiter
  197. ) {
  198. $token = self::findToken(
  199. $statement,
  200. $tokenOffset,
  201. '/' . preg_quote($currentLiteralDelimiter, '/') . '/'
  202. );
  203. if (! $token) {
  204. return false;
  205. }
  206. $currentLiteralDelimiter = false;
  207. ++$tokenOffset;
  208. return true;
  209. }
  210. /**
  211. * Finds the token described by regex starting from the given offset. Updates the offset with the position
  212. * where the token was found.
  213. *
  214. * @param string $statement The SQL statement to parse
  215. * @param string $offset The offset to start searching from
  216. * @param string $regex The regex containing token pattern
  217. *
  218. * @return string|null Token or NULL if not found
  219. */
  220. private static function findToken($statement, &$offset, $regex)
  221. {
  222. if (preg_match($regex, $statement, $matches, PREG_OFFSET_CAPTURE, $offset)) {
  223. $offset = $matches[0][1];
  224. return $matches[0][0];
  225. }
  226. return null;
  227. }
  228. /**
  229. * {@inheritdoc}
  230. */
  231. public function bindValue($param, $value, $type = ParameterType::STRING)
  232. {
  233. return $this->bindParam($param, $value, $type, null);
  234. }
  235. /**
  236. * {@inheritdoc}
  237. */
  238. public function bindParam($column, &$variable, $type = ParameterType::STRING, $length = null)
  239. {
  240. $column = $this->_paramMap[$column] ?? $column;
  241. if ($type === ParameterType::LARGE_OBJECT) {
  242. $lob = oci_new_descriptor($this->_dbh, OCI_D_LOB);
  243. $lob->writeTemporary($variable, OCI_TEMP_BLOB);
  244. $variable =& $lob;
  245. }
  246. $this->boundValues[$column] =& $variable;
  247. return oci_bind_by_name(
  248. $this->_sth,
  249. $column,
  250. $variable,
  251. $length ?? -1,
  252. $this->convertParameterType($type)
  253. );
  254. }
  255. /**
  256. * Converts DBAL parameter type to oci8 parameter type
  257. */
  258. private function convertParameterType(int $type) : int
  259. {
  260. switch ($type) {
  261. case ParameterType::BINARY:
  262. return OCI_B_BIN;
  263. case ParameterType::LARGE_OBJECT:
  264. return OCI_B_BLOB;
  265. default:
  266. return SQLT_CHR;
  267. }
  268. }
  269. /**
  270. * {@inheritdoc}
  271. */
  272. public function closeCursor()
  273. {
  274. // not having the result means there's nothing to close
  275. if (! $this->result) {
  276. return true;
  277. }
  278. oci_cancel($this->_sth);
  279. $this->result = false;
  280. return true;
  281. }
  282. /**
  283. * {@inheritdoc}
  284. */
  285. public function columnCount()
  286. {
  287. return oci_num_fields($this->_sth);
  288. }
  289. /**
  290. * {@inheritdoc}
  291. */
  292. public function errorCode()
  293. {
  294. $error = oci_error($this->_sth);
  295. if ($error !== false) {
  296. $error = $error['code'];
  297. }
  298. return $error;
  299. }
  300. /**
  301. * {@inheritdoc}
  302. */
  303. public function errorInfo()
  304. {
  305. return oci_error($this->_sth);
  306. }
  307. /**
  308. * {@inheritdoc}
  309. */
  310. public function execute($params = null)
  311. {
  312. if ($params) {
  313. $hasZeroIndex = array_key_exists(0, $params);
  314. foreach ($params as $key => $val) {
  315. if ($hasZeroIndex && is_numeric($key)) {
  316. $this->bindValue($key + 1, $val);
  317. } else {
  318. $this->bindValue($key, $val);
  319. }
  320. }
  321. }
  322. $ret = @oci_execute($this->_sth, $this->_conn->getExecuteMode());
  323. if (! $ret) {
  324. throw OCI8Exception::fromErrorInfo($this->errorInfo());
  325. }
  326. $this->result = true;
  327. return $ret;
  328. }
  329. /**
  330. * {@inheritdoc}
  331. */
  332. public function setFetchMode($fetchMode, $arg2 = null, $arg3 = null)
  333. {
  334. $this->_defaultFetchMode = $fetchMode;
  335. return true;
  336. }
  337. /**
  338. * {@inheritdoc}
  339. */
  340. public function getIterator()
  341. {
  342. return new StatementIterator($this);
  343. }
  344. /**
  345. * {@inheritdoc}
  346. */
  347. public function fetch($fetchMode = null, $cursorOrientation = PDO::FETCH_ORI_NEXT, $cursorOffset = 0)
  348. {
  349. // do not try fetching from the statement if it's not expected to contain result
  350. // in order to prevent exceptional situation
  351. if (! $this->result) {
  352. return false;
  353. }
  354. $fetchMode = $fetchMode ?: $this->_defaultFetchMode;
  355. if ($fetchMode === FetchMode::COLUMN) {
  356. return $this->fetchColumn();
  357. }
  358. if ($fetchMode === FetchMode::STANDARD_OBJECT) {
  359. return oci_fetch_object($this->_sth);
  360. }
  361. if (! isset(self::$fetchModeMap[$fetchMode])) {
  362. throw new InvalidArgumentException('Invalid fetch style: ' . $fetchMode);
  363. }
  364. return oci_fetch_array(
  365. $this->_sth,
  366. self::$fetchModeMap[$fetchMode] | OCI_RETURN_NULLS | OCI_RETURN_LOBS
  367. );
  368. }
  369. /**
  370. * {@inheritdoc}
  371. */
  372. public function fetchAll($fetchMode = null, $fetchArgument = null, $ctorArgs = null)
  373. {
  374. $fetchMode = $fetchMode ?: $this->_defaultFetchMode;
  375. $result = [];
  376. if ($fetchMode === FetchMode::STANDARD_OBJECT) {
  377. while ($row = $this->fetch($fetchMode)) {
  378. $result[] = $row;
  379. }
  380. return $result;
  381. }
  382. if (! isset(self::$fetchModeMap[$fetchMode])) {
  383. throw new InvalidArgumentException('Invalid fetch style: ' . $fetchMode);
  384. }
  385. if (self::$fetchModeMap[$fetchMode] === OCI_BOTH) {
  386. while ($row = $this->fetch($fetchMode)) {
  387. $result[] = $row;
  388. }
  389. } else {
  390. $fetchStructure = OCI_FETCHSTATEMENT_BY_ROW;
  391. if ($fetchMode === FetchMode::COLUMN) {
  392. $fetchStructure = OCI_FETCHSTATEMENT_BY_COLUMN;
  393. }
  394. // do not try fetching from the statement if it's not expected to contain result
  395. // in order to prevent exceptional situation
  396. if (! $this->result) {
  397. return [];
  398. }
  399. oci_fetch_all(
  400. $this->_sth,
  401. $result,
  402. 0,
  403. -1,
  404. self::$fetchModeMap[$fetchMode] | OCI_RETURN_NULLS | $fetchStructure | OCI_RETURN_LOBS
  405. );
  406. if ($fetchMode === FetchMode::COLUMN) {
  407. $result = $result[0];
  408. }
  409. }
  410. return $result;
  411. }
  412. /**
  413. * {@inheritdoc}
  414. */
  415. public function fetchColumn($columnIndex = 0)
  416. {
  417. // do not try fetching from the statement if it's not expected to contain result
  418. // in order to prevent exceptional situation
  419. if (! $this->result) {
  420. return false;
  421. }
  422. $row = oci_fetch_array($this->_sth, OCI_NUM | OCI_RETURN_NULLS | OCI_RETURN_LOBS);
  423. if ($row === false) {
  424. return false;
  425. }
  426. return $row[$columnIndex] ?? null;
  427. }
  428. /**
  429. * {@inheritdoc}
  430. */
  431. public function rowCount()
  432. {
  433. return oci_num_rows($this->_sth);
  434. }
  435. }