EllisLab/CodeIgniter — Issue #696: oci8 num_rows breaks transactions
Author: a-krebs
Created: 2011-11-23 02:18:13+00:00
State: open

This could be related to #182 and #682:

in oci8_result, the function num_rows runs queries again before returning (note #682 is still open, so I have changed the return call to be return $this->num_rows)

```php
public function num_rows()
{
if ($this->num_rows === 0 && count($this->result_array()) > 0)
{
$this->num_rows = count($this->result_array());
@oci_execute($this->stmt_id);

if ($this->curs_id)
{
@oci_execute($this->curs_id);
}
}

return $this->num_rows(); // is currently "return $rowcount" which is a bug as noted above
}
```

When these queries are run, they are committed (and not treated as part of the transaction). This issue is present even if num_rows() is never called directly, so I have a feeling num_rows() is called from somewhere else in the database library (either somewhere in $this->db->query(), or in $resultSet->result()).

Thus, num_rows() will break a transaction lock. I noticed this when trying to do update BLOB columns in my database (which require row lock).

The code I was trying to run is:

```php
$empty_blob_set = $this->db->query('SELECT photo FROM images WHERE photo_id = ?', $photoId);
foreach($empty_blob_set->result_array() as $row) {
$blob = $row['PHOTO'];
$blob->write($imageAsString);
}
```

What is the purpose of running the

```php
@oci_execute($this->stmt_id);
if ($this->curs_id)
{
@oci_execute($this->curs_id);
}
```
block? If I change the @oci_execute to use OCI_DEFAULT (and thus not commit), the issue appears to go away (but removing the @oci_execute entirely means empty results are returned by $this->db->query() calls). I don't know what side-effects OCI_DEFAULT has, as I'm not sure why the @oci_execute calls are there, so this isn't really a fix.