user908759 user908759 - 4 months ago 21
SQL Question

Android Database - No such table error

I am trying to use and a few http requests to populate a few tables in a database for and android application. When those tables are populated the part of the data from the tables will populate and expanded view. The problem is I am getting an error telling me that the table I am trying to create is not created.

Error Log:

01-05 13:33:29.811: E/SQLiteLog(1823): (1) no such table: lvl2List

01-05 13:33:29.831: E/AndroidRuntime(1823): android.database.sqlite.SQLiteException: no such table: lvl2List (code 1): , while compiling: SELECT id, Title, Description FROM lvl2List


Here is my code:

MainActivity.java

public class MainActivity extends Activity
{
private List<lvItem> myLVItems = new ArrayList<lvItem>();
private List<Parent> myParent = new ArrayList<Parent>();
ExpandableListView exv;
final DBHelper db2 = new DBHelper(this);
@Override
protected void onCreate(Bundle savedInstanceState) {
exv=(ExpandableListView)findViewById(R.id.expandableListView1);
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

db2.open();

lvl3 lvl3Item = new lvl3();
lvl3Item.popDB(db2, myParent);
}
}


DBHelper.java

public class DBHelper
{
private static final String Database_Name = "MyDBs";
private static final int Database_Version = 2;

public static final String Key_ID = "id";
public static final String Key_Title = "Title";
public static final String Key_Summary = "Summary";
public static final String Key_Book = "Book";
public static final String Key_Chapter = "Chapter";
public static final String Key_Description = "Description";
public static final String Key_SourceType = "SourceType";

private static final String Lvl3_Table_Name = "lvl3";
private static final String Lvl2_Table_Name = "lvl2List";
private static final String Lvl1_Table_Name = "lvl1List";

private static final String Lvl3_Create = "CREATE TABLE IF NOT EXISTS lvl3 (id integer primary key autoincrement, "
+"Summary VARCHAR, Book VARCHAR, Chapter VARCHAR, Description VARCHAR, SourceType VARCHAR);";
private static final String Lvl2_Create = "CREATE TABLE IF NOT EXISTS lvl2List (id integer primary key autoincrement, "
+"Title VARCHAR, Description VARCHAR);";
private static final String Lvl1_Create = "CREATE TABLE IF NOT EXISTS lvl1List (id integer primary key autoincrement, "
+"Title VARCHAR, Description VARCHAR);";

public DBHelper(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)
{
try
{
db.execSQL(Lvl3_Create);
db.execSQL(Lvl2_Create);
db.execSQL(Lvl1_Create);
}catch(SQLException e){

}

}

public long insertLvl2Record(String Title, String Description)
{
ContentValues initialValues = new ContentValues();
initialValues.put(Key_Title, Title);
initialValues.put(Key_Description, Description);

return db.insert(Lvl2_Table_Name, null, initialValues);
}

public long insertLvl3Record(String Summary, String Book, String Chapter, String Description, String SourceType)
{
ContentValues initialValues = new ContentValues();
initialValues.put(Key_Summary, Summary);
initialValues.put(Key_Book, Book);
initialValues.put(Key_Chapter, Chapter);
initialValues.put(Key_Description, Description);
initialValues.put(Key_SourceType, SourceType);

return db.insert(Lvl3_Table_Name, null, initialValues);
}

public int getLvl2Count()
{
Cursor c = db.query(Lvl2_Table_Name, new String[] {Key_ID, Key_Title,Key_Description},
null, null, null, null, null);

return c.getCount();
}

public int getLvl3Count()
{
Cursor c = db.query(Lvl3_Table_Name, new String[] {Key_ID, Key_Summary,Key_Book,Key_Verse,Key_Scripts,Key_Description,Key_SourceType}, null, null, null, null, null);

return c.getCount();
}


lvl3.java

public class lvl3
{
DBHelper db2;
List<Parent> parent;
public void popDB(DBHelper db, List<Parent> myParent)
{
this.db2 = db;
this.parent = myParent;
Lvl3Thread lvl3theard = new Lvl3Thread();
lvl3theard.execute();
}
public class Lvl3Thread extends AsyncTask<String, Integer, String>{
@Override
protected String doInBackground(String... params)
{
//Httprequest that get the data for the database… this works
}
@Override
protected void onPostExecute(String result)
{
lvl2 lvl2Item = new lvl2();
lvl2Item.popDB(db2, parent);
}
}
}


lvl2.java

public class lvl3
{
DBHelper db2;
List<Parent> parent;
public void popDB(DBHelper db, List<Parent> myParent)
{
this.db2 = db;
this.parent = myParent;
Lvl3Thread lvl3theard = new Lvl3Thread();
lvl3theard.execute();
}
public class Lvl3Thread extends AsyncTask<String, Integer, String>{
@Override
protected String doInBackground(String... params)
{
//Httprequest that get the data for the database… this works
}
@Override
protected void onPostExecute(String result)
{
lvl2 lvl2Item = new lvl2();
lvl2Item.popDB(db2, parent);
}
}
public void popLvl2List(DBHelper db)
{
List<lvItem> childlvItems = new ArrayList<lvItem>();
Cursor cLvl3 = db.getAllLvl3Records();
db.getLvl2Count(); //This is where the error is coming from stating that there is no lvl2List table
}
}


I have tried using
db2.detLvl2Count();
but that does not work. If I comment that line
out the program will run. I have tried reordering the
db.execSQL
so that db.execSQL(Lvl2_Create);
is first, but I still get the error that the table does not exist.

Answer

onCreate of your DBHelper class will only be called the first time you open the app and the class is called.

Did you by any chance add the lvl2List table later on?

If so, then try to re-install the app and try again.

When adding/changing SQL tables, use the onUpgrade method. See the official documentation

Also in your onCreate method of DBHelper you're "eating" the SQLException - don't do this - it might have interesting info for you ;-)

By eating the Exception I mean, that you "silently" handle the exception and move on.

This is your onCreate method of the DatabaseHelper:

@Override
public void onCreate(SQLiteDatabase db) {
    try {
        db.execSQL(Lvl3_Create);
        db.execSQL(Lvl2_Create);
        db.execSQL(Lvl1_Create);
    } catch (SQLException e) {
        // Handle a possible exception here.
    }
}

Notice the catch-clause. Here you should at least do e.printStacktrace(); or Log.e(e);.

In your case you might not have gotten anything out this logging, because the onCreate method was only called the first time you installed and started the app.

See this SO post about the issue or just search on Google - this is a known "issue" / "behaviour" we as programmers tend to do ;-)

A good trail about how to handle exceptions, can be found here.