Android SQLite table does not exist

Question

I am trying to create and insert rows in an SQLite table. However, I get an error when inserting the rows that the table does not exist. Can anyone tell me what I am doing wrong here? Thanks!

The code in the activity that is calling the class to create the database and insert rows is as follows:

    // Open database
    db = new DBAdapter(this);
    db.open();

    // TODO: insertRecept statements hieronder verwijderen
    // Dienen enkel voor test om inhoud te hebben
    long id = db.insertRecept("aardappelen - bloemkool - kotelet");
    id = db.insertRecept("rijst - wokgroenten - scampi - currysaus");
    id = db.insertRecept("nasi goreng - omelet");
    id = db.insertRecept("ebly - veggieburger - provencaalse saus");
    id = db.insertRecept("rijst - kipfilet - zoetzure saus");
    id = db.insertRecept("ebly - veggieburger - provencaalse saus");
    id = db.insertRecept("puree - fish-sticks - spinazie");
    id = db.insertRecept("tortellini - kaassaus");
    id = db.insertRecept("aardappelen - bloemkool - chippolata");
    id = db.insertRecept("pizza");
    id = db.insertRecept("frietjes");
    id = db.insertRecept("aardappel - zalm - prei - melksaus");
    db.close();

The DBAdapter class is as follows:

package be.bertcarremans.weekmenu;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.sql.SQLException;

public class DBAdapter {
static final String KEY_ROWID = "_id";
static final String KEY_RECEPT_TITEL = "recept_titel";
static final String TAG = "DBAdapter";

static final String DATABASE_NAME = "recepten_db";
static final String DATABASE_TABLE = "recepten";
static final int DATABASE_VERSION = 1;

static final String DATABASE_CREATE =
        "CREATE TABLE recepten (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
        + "recept_titel  TEXT NOT NULL);";

DatabaseHelper DBHelper;
SQLiteDatabase db;

final Context context;

// Constructor
public DBAdapter(Context ctx) {
    this.context = ctx;
    DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper {
    DatabaseHelper(Context context) {
        super(context,DATABASE_NAME,null,DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS recepten");
    }
}

// database openen
public DBAdapter open() {
    db = DBHelper.getWritableDatabase();
    return this;
}

// database sluiten
public void close() {
    DBHelper.close();
}

// recept toevoegen
public long insertRecept(String recept) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_RECEPT_TITEL, recept);
    return db.insert(DATABASE_TABLE, null, initialValues);
}
}

Did you change the table structure, in your experiments? Because your `onUpgrade()` is not appropriate. It should re-create the dropped table. AND you need to define a DATABASE_VERSION constant to be incremented upon updates, in order for that method to fire. — Phantômaxx, Jun 14 '15 at 17:24
Uninstall and reinstall your app, or clear its data in the app manager to get rid of the database file and recreate it with your current code. Incrementing database version won't help since your `onUpgrade()` is buggy. http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run — laalto, Jun 14 '15 at 17:25

Marcin Orlowski · Accepted Answer · 2015-06-14T17:31:41.103

It looks you most likely have changed the schema structure, and (most likely) bumped version number of your database. This, in conjunction with broken onUpgrade() implementation caused your problems.

As a quick solution: uninstall the app and install again - this will completely wipe the database and make your app create it on 1st run. But as a long term solution, you need to fix onUpgrade(). Right now you do this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS recepten");
}

so in human language it reads: "when we need to upgrade, just wipe the table". What about re-creating it after that? If you won't do that, you got no table. If you got no plans to have update code, then I'd move my table removal/creation code to (respectively) i.e. createTables()/removeTables() methods and just make my onUpgrade() use it:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    removeTables(db);
    createTables(db);
}

you can also call onCreate(db) instead of creating new method createTable(). — Hatim, Dec 23 '16 at 06:18
you will end up duplicating the code w/o having separate method as you need it in at least two cases — Marcin Orlowski, Dec 23 '16 at 13:20

Android SQLite table does not exist

1 Answers1