koneks koneks - 4 months ago 11
Android Question

Android SQLite "no such table" exception

Im starting into SQLite and databases in Android and I just found this issue. Have red a lot of similar questions but I have not found a solution yet.

This is the error code 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 apears there as normal.
Thanks in advice.

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

screen capture

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

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

Answer

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.