problem with codeigniter using odbc database driver

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

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 three ways of fixing it.

The easy way:

LIMIT (or TOP in MSSQL) your results in the SQL query.

SELECT TOP(100) * FROM Table

The UPDATED unbuffered way:

Use the unbuffered_row() function that was introduced 2 years after this question was asked.

$query = $this->db->query($sql);// not $query->result() because that loads everything into an internal array.// and not $query->first_row() because it does the same thing (as of 2013-04-02)while ($record = $query->unbuffered_row('array')) {     // code...}

The hard way:

Use a proper result object that uses PHP5 iterators (that the devs don't like because it excludes php4). 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.