Codeigniter issue using odbc database driver
ive a curious problem with the odbc codeigniter driver starts up. im connecting Linux machine to MSSQL 2008 machine using FreeTDS.
while I get that the num_rows function always returns -1 and this is completely a database / driver problem - for some reason when I try to create a result -> result () the whole application crashes (500 error, sometimes just blank page), if they're lucky, I get an error telling me that the application died because it tried to allocate 2 terabytes of memory (!).
This happens irregularly, i.e. every few updates. sometimes it works fine, sometimes the page returns a 500 error and sometimes it gives a memory allocation error - anyway, this is not something that can be reproduced with percision, but SUPER queries are simple.
anybody ideas?
a source to share
Haha, that's with me too! And I have complained to my developers and they are ignored by me .
When you call result (), it will loop through every possible result and store the entry in 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 to fix it.
Simple way:
LIMIT
(or TOP
in MSSQL ) your results in a SQL query.
SELECT TOP(100) * FROM Table
UPDATED unbuffered way:
Use the function unbuffered_row()
that was presented in 2 years after the 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 the correct result object that uses PHP5 iterators (which the developers don't like as it excludes php4). In that DB_result.php file, say something like this:
-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 only use the object without ->result()
at the end, eg $query
. And all internal CI files still work 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 code is an Iterator, running their code, has some logical problems with a -1, so do not use both $query->result()
and $query
at the same facility. If anyone wants to fix this, you are awesome.
a source to share