Android SQLite issue "Cannot perform this operation because the connection pool has been closed"

Question

I'm facing sometimes a SQLite issue, some times my app crashes, and the error is

 Caused by: java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.

this is my function where the error comes from

public List<contacts> getAllcontacts() {
List<contacts> contactsl = new LinkedList<contacts>();

// 1. build the query
String query = "SELECT  * FROM contacts";

// 2. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);

// 3. go over each row, build a contact and add it to list
contacts contact = null;
if (cursor.moveToFirst()) {
    do {
        contact = new contacts();

        contact.setName(cursor.getString(1));
        contact.setNumero(cursor.getString(3));
        contact.setProfil(cursor.getString(2));
        contact.setShow(cursor.getString(5));
        contact.setBlocked(cursor.getString(4));
        contact.setObjectid(cursor.getString(6));
        contactsl.add(contact);
    } while (cursor.moveToNext());
}

return contactsl;
}

The error is showing when I execute the function when I'm testing my app and trying to launch the function more than once maybe.

Here is the creation of the database :

private static sql sInstance;

private static final int DATABASE_VERSION = 1;

private static final String DATABASE_NAME = "Contact";

public static sql getInstance(Context context) {

// Use the application context, which will ensure that you
// don't accidentally leak an Activity's context.
if (sInstance == null) {
    sInstance = new sql(context.getApplicationContext());
}
return sInstance;
}

public sql(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {

    String CREATETABLE = "CREATE TABLE contacts ( " +
            "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "name TEXT, "+
            "profil TEXT, "+
            "phone TEXT UNIQUE ,"+
            "blocked TEXT, "+
            "show TEXT , "+
            "objectid TEXT )";

    db.execSQL(CREATETABLE);
}




@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int `newVersion) {`
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS contacts");

    this.onCreate(db);
}

Answer 1

If you are closing your DBHelper prematurely you will likely get this error. Search you code if you close your Helper and turn that off and see if you still encounter that error.

Else maybe try this Template for instantiating your DBHelper.

public class MyDBHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "myDatabase.db";
    private static final int SCHEMA = 1;
    private static volatile MyDBHelper sInstance;


    private MyDBHelper(Context context) {
        super(context, DATABASE_NAME, null, SCHEMA);
    }

    public static MyDBHelper getInstance(Context context) {
        if (sInstance == null) {
            synchronized (MyDBHelper.class) {
                if (sInstance == null) {
                    sInstance = new MyDBHelper(context.getApplicationContext());
                }
            }
        }
        return sInstance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // create your tables here
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // handle db upgrade or leave blank
    }
}

As answer on your comment, you can still close the database when your instance gets finalized

@Override
public void finalize() throws Throwable {
    sInstance.close();
    super.finalize();
}