CodeIgniter Sqlite not working

Credits for this fix are with S. Stüvel, J. Bransen and S. Timmer. This is a fix for a specific server, so YMMV. It did the trick for me though.

In pdo_driver.php, starting line 81 change:

    empty($this->database) OR $this->hostname .= ';dbname='.$this->database;    $this->trans_enabled = FALSE;    $this->_random_keyword = ' RND('.time().')'; // database specific random keyword}

to

if(strpos($this->database, 'sqlite') !== FALSE) {        $this->hostname = $this->database;        $this->_random_keyword = ' RANDOM()';    }    else {        $this->hostname .= ";dbname=".$this->database;        $this->_random_keyword = ' RND('.time().')'; // database specific random keyword    }    $this->trans_enabled = FALSE;}

On line 189 change the entire function _execute($sql) to

function _execute($sql){    $sql = $this->_prep_query($sql);    $result_id = $this->conn_id->query($sql);    if (is_object($result_id))    {        $this->affect_rows = $result_id->rowCount();    }    else    {        $this->affect_rows = 0;    }    return $result_id;}

Then in pdo_result.php change":On line 29 change

public $num_rows;

to

var $pdo_results = '';var $pdo_index = 0;

on line 36 replace entire function

public function num_rows(){    if (is_int($this->num_rows))    {        return $this->num_rows;    }    elseif (($this->num_rows = $this->result_id->rowCount()) > 0)    {        return $this->num_rows;    }    $this->num_rows = count($this->result_id->fetchAll());    $this->result_id->execute();    return $this->num_rows;}

with:

function num_rows(){    if ( ! $this->pdo_results ) {        $this->pdo_results = $this->result_id->fetchAll(PDO::FETCH_ASSOC);    }    return sizeof($this->pdo_results);

Then on line 60 change

function num_fields(){    return $this->result_id->columnCount();}

to:

function num_fields(){    if ( is_array($this->pdo_results) ) {        return sizeof($this->pdo_results[$this->pdo_index]);    } else {        return $this->result_id->columnCount();    }}

Then on line 94 change:

function field_data(){    $data = array();    try    {        for($i = 0; $i < $this->num_fields(); $i++)        {            $data[] = $this->result_id->getColumnMeta($i);        }        return $data;    }    catch (Exception $e)    {        if ($this->db->db_debug)        {            return $this->db->display_error('db_unsuported_feature');        }        return FALSE;    }}

to:

function field_data(){    if ($this->db->db_debug)    {        return $this->db->display_error('db_unsuported_feature');    }    return FALSE;}

then line 146 change:

return FALSE;

to

$this->pdo_index = $n;

then on line 159 change

function _fetch_assoc(){    return $this->result_id->fetch(PDO::FETCH_ASSOC);}

to

function _fetch_assoc(){    if ( is_array($this->pdo_results) ) {        $i = $this->pdo_index;        $this->pdo_index++;        if ( isset($this->pdo_results[$i]))            return $this->pdo_results[$i];        return null;    }    return $this->result_id->fetch(PDO::FETCH_ASSOC);}

And finally on line 174 change:

function _fetch_object(){       return $this->result_id->fetchObject();

to

function _fetch_object(){    if ( is_array($this->pdo_results) ) {        $i = $this->pdo_index;        $this->pdo_index++;        if ( isset($this->pdo_results[$i])) {            $back = new stdClass();            foreach ( $this->pdo_results[$i] as $key => $val ) {                $back->$key = $val;            }            return $back;        }        return null;    }    return $this->result_id->fetch(PDO::FETCH_OBJ);}

This worked for me. Again, not my work, credit goes out to S. Stüvel, J. Bransen and S. Timmer.Rather long answer, but i hope this helps.

There is a bug in CodeIgniter version 2.1.0 for PDO drivers (They had just added PDO driver in version 2.1.0)

You can see change log for version 2.1.1

Please try upgrading your CodeIgniter.

I have Codeigniter 2.2.1 and when I set application/config/database.php the same as OP I can use sqlite database, sort of. I can create new database/file, create new tables and insert data. The problem is that I can't read any.

$query = $this->db->get('table_name');return $query->result_array();

Returns empty array. The same happens when I do.

$query = $this->db->query("SELECT...");

Apparently there are still some bugs. I'm just starting to explore Codeigniter, but when I switch to mysql the same exact Model works as it should.

For the record, everything on my server is set up OK. I can use my sqlite databases just fine, it is just Codeigniter that has problems.