Android SQLite "no such table" exception

Question

I'm starting into SQLite and databases in Android and I just found this issue. I have read a lot of similar questions but I have not found a solution yet.

This is the error I'm getting:

E/AndroidRuntime(529): Caused by: android.database.sqlite.SQLiteException: no such table: ligas_bd: , while compiling: SELECT * FROM ligas_bd

This is my code.

DBHelper myDbHelper = new DBHelper(this);
myDbHelper = new DBHelper(this);
try {
    myDbHelper.createDataBase();
} catch (IOException ioe) {
    throw new Error("Unable to create database");
}
try {
    myDbHelper.open();
    SQLiteDatabase db = null;
    db = myDbHelper.getWritableDatabase();
    String[] args = new String[] {""};
    Cursor c = db.rawQuery(" SELECT * FROM ligas_bd", args); <---- Error in this line
    if (c.moveToFirst()) {
        do {
            String cod = c.getString(0);
            String nombre = c.getString(1);
            String flag = c.getString(0);
            String cod_url = c.getString(0);
            ligas.add(new Item_Liga(nombre, flag, cod, cod_url));
        } while(c.moveToNext());
    }
}catch(SQLException sqle){
    throw sqle;
}

DBHelper.java

public class DBHelper extends SQLiteOpenHelper{
private static String DB_NAME = "ligas_bd";
private SQLiteDatabase myDataBase;
private final Context myContext;

public DBHelper(Context context) {
    super(context, DB_NAME, null, 1);
    this.myContext = context;
}

public void createDataBase() throws IOException{
    boolean dbExist = checkDataBase();
    if(dbExist){ }
    else {
        this.getReadableDatabase();
        try {
            copyDataBase();
        } catch (IOException e) {
            throw new Error("Error copiando Base de Datos");
        }
    }
}
private boolean checkDataBase(){
    SQLiteDatabase checkDB = null;
    try {
        String myPath = DB_PATH + DB_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    } catch(SQLiteException e) { }
    if(checkDB != null) {
        checkDB.close();
    }
    return checkDB != null ? true : false;
}

private void copyDataBase() throws IOException{
    InputStream myInput = myContext.getAssets().open(DB_NAME);
    String outFileName = DB_PATH + DB_NAME;
    OutputStream myOutput = new FileOutputStream(outFileName);
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0) {
        myOutput.write(buffer, 0, length);
    }
    myOutput.flush();
    myOutput.close();
    myInput.close();
}

public void open() throws SQLException{
    try {
        createDataBase();
    } catch (IOException e) {
        throw new Error("Ha sido imposible crear la Base de Datos");
    }
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}

@Override
public synchronized void close() {
    if(myDataBase != null)
    myDataBase.close();
    super.close();
}

@Override
public void onCreate(SQLiteDatabase db) { }

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

I also checked in my phone with root explorer data/data/mypackagename/databases and the database appears there as normal.

My database and the main table have the same name, as you can see in this pic of SQLite database browser:

I am not creating the table, I created it before using SQLite Database Browser and I am just trying to read from it

---

I don't know how, but I finally fixed it. I just started again, this time following this tutorial. Hope it helps!

Answer 1

This is only a guess but I can see three possible things are happening here...

1. Calling this.getReadableDatabase() in your createDatabase() method is automatically creating an empty database.
2. Your attempt to copy your pre-created database from your assets folder is failing or the database is being copied to the wrong place.
3. The empty database created in step 1 (above) is the one that is being queried when you call db.rawQuery(...) from your main code.

To explain further it is usefult to look at the source for SQLiteOpenHelper

When you create an instance of SQLiteOpenHelper it sets things like the database name and version but it does not create the database. Instead, the first call to either getReadableDatabase() or getWritableDatabase() checks to see if the database exists and if it doesn't it creates it. After creating the empty database, the onCreate(...) method is called which, in your case, does nothing.

On to point 2 (above) - you say that DB_PATH is /data/data/mypackagename/databases. If that is really the case and you have no trailing / then the line...

String myPath = DB_PATH + DB_NAME;

...will set myPath to /data/data/mypackagename/databasesligas_db. In that case, even if the copy from assets succeeds, the copied database isn't going to be where you expect it to be.

Finally on point 3 (above) when you call db.rawQuery(...) the instance that db points to is returned by the previous call to getWritableDatabase(). Assuming the copy from assets has failed or that it has been copied to the incorrect path, db will be pointing at the empty database created in step 1.

The error you get is that the table doesn't exist and it isn't an error indicating the database doesn't exist - the only logical explanation is that it's performing a query on an empty database and not the one that has been copied from assets.

EDIT: One other thing I meant to mention. It isn't a good idea to use hard-coded paths to any of the Android directories. For example - although on most devices, the directory where databases are created will be /data/data/<package-name>/databases this is not a guarantee. It is much safer if you call the getDatabasePath(...) method as it will return a File object which can be used to get the path to the databases directory used by the Android database classes.

Answer 2

I had the same problem and fixed it by cleaning the project and deleting the app off my testing device and reinstalling it. I believe it occurred because I followed another tutorial that created an empty database by mistake. The corrected code was retrieving the old empty database.