CodeIgniter Sqlite not working

Question

Whenever I query my database (sqlite) like this in my model (im using codeigniter, full code below):

$this->db->select('post');
$query = $this->db->get('posts');
return $query->result_array();

I get the following error:

Fatal error: Call to a member function rowCount() on a non-object in /codeigniter/system/database/drivers/pdo/pdo_result.php on line 42

When changing the query to something nonexistent I get a "proper" error, something like:

A Database Error Occurred
Error Number: HY000
no such column: posst
SELECT posst FROM posts
Filename: /codeigniter/models/post.php
Line Number: 8

Which leads me to believe the database is actually working, but there is something I am missing. I have tried recreating the database. It literally has 1 table with 1 column, but I just cannot get any data out. I also tried creating it with different "admin" programs but to no avail. I made sure it is an Sqlite 3 db, which is supported by the webserver according to phpinfo. Does anybody have a clue where I am making a mistake?

-------- full code: my post model in models/post.php

 <?php

class Post extends CI_Model{

    function get_posts(){

         $this->db->select('posst');
         $query = $this->db->get('posts');
         return $query->result_array();

        } 
}

---

My controller in controller/posts.php :

<?php

class Posts extends CI_Controller{

    function index(){

        $this->load->model('post');
        $data['posts']=$this->post->get_posts();
        echo"<pre>";
        print_r($data['posts']);
        echo"</pre>";
    }

}

---

My database config in database.php :

$active_group = 'default';
$active_record = TRUE;

$db['default']['hostname'] = 'sqlite:/home/******/******/www/wtp3/codeigniter/db/wtp35.sqlite';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['database'] = '';
$db['default']['dbdriver'] = 'pdo';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

I applied g_m solution after updating from 2.1.3 to 2.2.6, and as jimmy, I had to remove the first change in pdo_driver.php to make it work.