problem with codeigniter using odbc database driver

Question

i’ve run into a curious problem with codeigniter's odbc driver. i’m connecting from a linux machine to an MSSQL 2008 machine using FreeTDS.

while i get that the ‘num_rows’ function always returns -1, and that is fully a database/driver issue - for some reason when i try to create a ->result(), the whole application crashes (error 500, sometimes just a blank page), if i’m lucky, i get an error message informing me that the application died because it tried to allocated 2 terrabytes of memory(!).

This happens irregularly, ie: every few refreshes. sometimes it runs fine, sometimes the page returns error 500 and sometimes it gives the memory allocation error - in any case it’s not really something that can be reproduced with percision, and the queries are SUPER simple.

ideas anyone?

Answer 1

Ha ha, this happend to me too! And I've complained to the devs about it so much, and they ignored me so much.

When you call result(), it will loop through every single possible result, and store the record into a massive internal array. See system/database/DB_result.php while loop at the end of result_object() and result_array()

There are two ways of fixing it.

The easy way: limit (or TOP in MSSQL) your results in the SQL query.

SELECT TOP(100) * FROM Table

The other to write a proper result object that uses PHP5 iterators (that the devs don't like because it excludes php4, whine whine). Slap something like this in your DB_result.php file:

-class CI_DB_result {
+class CI_DB_result implements Iterator {

    var $conn_id        = NULL;
    var $result_id      = NULL;
    var $result_array   = array();
    var $result_object  = array();
-   var $current_row    = 0;
+   var $current_row    = -1;
    var $num_rows       = 0;
    var $row_data       = NULL;
+   var $valid          = FALSE;


    /**
    function _fetch_assoc() { return array(); } 
    function _fetch_object() { return array(); }

+   /**
+    * Iterator implemented functions
+    * http://us2.php.net/manual/en/class.iterator.php
+    */
+   
+   /**
+    * Rewind the database back to the first record
+    *
+    */
+   function rewind()
+   {
+       if ($this->result_id !== FALSE AND $this->num_rows() != 0) {
+           $this->_data_seek(0);
+           $this->valid = TRUE;
+           $this->current_row = -1;
+       }
+   }
+   
+   /**
+    * Return the current row record.
+    *
+    */
+   function current()
+   {
+       if ($this->current_row == -1) {
+           $this->next();
+       }
+       return $this->row_data;
+   }
+   
+   /**
+    * The current row number from the result
+    *
+    */
+   function key()
+   {
+       return $this->current_row;
+   }
+   
+   /**
+    * Go to the next result.
+    *
+    */
+   function next()
+   {
+       $this->row_data = $this->_fetch_object();
+       if ($this->row_data) {
+           $this->current_row++;
+           if (!$this->valid)
+               $this->valid = TRUE;
+           return TRUE;
+       } else {
+           $this->valid = FALSE;
+           return FALSE;
+       }
+   }
+   
+   /**
+    * Is the current_row really a record?
+    *
+    */
+   function valid()
+   {
+       return $this->valid;
+   }
+   
 }
 // END DB_result class

Then to use it, instead of calling $query->result() you use just the object without the ->result() on the end like $query. And all the internal CI stuff still works with result().

$query = $this->db->query($sql);
foreach ($query as $record) { // not $query->result() because that loads everything into an internal array.
    // code...
}

By the way, my Iterator code working with their code has some logic problems with the whole -1 thing, so don't use both $query->result() and $query on the same object. If someone wants to fix that, you are awesome.