Android; SQLiteConnection object for database was leaked even closed

Question

In my database helper I have both closed the cursor and database connection, but still the logcat says there's a leak!

I have this code in my SQLiteOpenHelper

// Getting all entries from database table wp_posts
public ArrayList<String> getAllPosts() {
    // Open the database
    String path_to_db = context.getDatabasePath(db_name).getPath();
    db = SQLiteDatabase.openDatabase(path_to_db, null, SQLiteDatabase.OPEN_READONLY);

    // Prepare the container array
    ArrayList<String> postsArrayList = new ArrayList<String>();

    // select all posts
    String selectQuery = "SELECT post_title FROM " + wp_posts + " ORDER BY post_title COLLATE NOCASE";
    Cursor c = db.rawQuery(selectQuery, null);

    //Log.e("DataBaseHelper", "getAllPosts => line 205");

    // looping through all rows and adding to list
    while (c.moveToNext()) {
        postsArrayList.add(c.getString(0));
    }

    // when you're done close the cursor
    c.close();

    // ..then close the database
    db.close();

    //Log.e("DataBaseHelper", "getAllPosts => line 218");
    return postsArrayList;
}

The Logcat log

09-22 21:29:24.617: E/DataBaseHelper(17090): DataBaseHelper => 84
09-22 21:29:24.807: D/dalvikvm(17090): GC_FOR_ALLOC freed 78K, 5% free 2895K/3024K, paused 26ms, total 28ms
09-22 21:29:24.807: W/SQLiteConnectionPool(17090): A SQLiteConnection object for database 
                    '/data/data/net.example/databases/atest.db' was leaked!  
                    Please fix your application to end transactions in progress properly and to close 
                    the database when it is no longer needed.
09-22 21:29:24.947: D/dalvikvm(17090): GC_FOR_ALLOC freed 65K, 4% free 3343K/3460K, paused 1ms, total 2ms
09-22 21:29:25.027: E/DataBaseHelper(17090): DataBaseHelper => 88

In the Main activity I call the helper using:

    public void fillListView(ListView listview, DataBaseHelper db) {
        Log.e("DataBaseHelper", "DataBaseHelper => 84");

        // Get the entries from db table; wp_posts
        ArrayList<String> entries = db.getAllPosts();
        Log.e("DataBaseHelper", "DataBaseHelper => 88");

        // ..then close the database
        db.close();

        // ArrayAdapter points to list_view.xml; which doesn't contain
        // LinearLayout
        // but only a TextView. This will be used as adapter
        adapter = new ArrayAdapter<String>(this, R.layout.db_row_textview, entries);

        // Binds the Adapter to the ListView
        listview.setAdapter(adapter);

        // Close DB
        db.close();

    }

What am I doing wrong? Thanks.

Answer 1

After many frustrations I decided to change the whole code inside the db helper:

package net.amawal.android;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import net.amawal.android.models.Post;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class AmawalDB extends SQLiteOpenHelper {

    // DATABASE
    private static SQLiteDatabase mSqliteDb;
    private static AmawalDB mInstance;
    public static final int DATABASE_VERSION = 1;
    private static final String DB_PATH_SUFFIX = "/databases/";
    private static final String DATABASE_NAME = "amawal.sqlite";
    private static Context mContext;

    // Awals table post_title
    private static final String wp_posts = "wp_posts";

    public AmawalDB(Context context, CursorFactory factory, int version) {
        super(context, DATABASE_NAME, factory, version);
        mContext = context;
    }

    public void initialise() {
        if (mInstance == null) {
            if (!checkDatabase()) {
                copyDataBase();
            }
            mInstance = new AmawalDB(mContext, null, DATABASE_VERSION);
            mSqliteDb = mInstance.getWritableDatabase();
        }
    }

    public AmawalDB getInstance(){
        return mInstance;
    }

    public SQLiteDatabase getDatabase() {
        return mSqliteDb;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    private static void copyDataBase() {

        try {
            // Open your local db as the input stream
            InputStream myInput = mContext.getAssets().open(DATABASE_NAME);

            // Path to the just created empty db
            String outFileName = getDatabasePath();

            // if the path doesn't exist first, create it
            File f = new File(mContext.getApplicationInfo().dataDir + DB_PATH_SUFFIX);
            if (!f.exists())
                f.mkdir();

            // Open the empty db as the output stream
            OutputStream myOutput = new FileOutputStream(outFileName);

            // transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[1024];
            int length;
            while ((length = myInput.read(buffer)) > 0) {
                myOutput.write(buffer, 0, length);
            }

            // Close the streams
            myOutput.flush();
            myOutput.close();
            myInput.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static boolean checkDatabase() {
        SQLiteDatabase checkDB = null;

        try {
            try {
                String myPath = getDatabasePath();
                checkDB = SQLiteDatabase.openDatabase(myPath, null,
                        SQLiteDatabase.OPEN_READONLY);
                checkDB.close();
            } catch (Exception e) { }
        } catch (Throwable ex) {
        }
        return checkDB != null ? true : false;
    }

    private static String getDatabasePath() {
        return mContext.getApplicationInfo().dataDir + DB_PATH_SUFFIX
                + DATABASE_NAME;
    }

    // Getting All Contacts
    public List<Post> getAllPosts() {

        List<Post> posts_list = new ArrayList<Post>();
        // Select All Query
        String selectQuery = "SELECT * FROM " + wp_posts + " ORDER BY post_title COLLATE NOCASE";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Post post = new Post();
                post.setID(Integer.parseInt(cursor.getString(1)));
                post.setPostContent(cursor.getString(2));
                post.setpostTitle(cursor.getString(3));

                // Adding post to list
                posts_list.add(post);
            } while (cursor.moveToNext());
        }

        // return posts list
        return posts_list;
    }

}