Gotchas INSERTing into SQLite on Android?
- by paul.meier
Hi friends,
I'm trying to set up a simple SQLite database in Android, handling the schema via a subclass of SQLiteOpenHelper. However, when I query my tables, the columns I think I've inserted are never present.
Namely, in SQLiteOpenHelper's onCreate(SQLiteDatabase db) method, I use db.execSQL() to run CREATE TABLE commands, then have tried both db.execSQL and db.insert() to run INSERT commands on the tables I've just created. This appears to run fine, but when I try to query them I always get 0 rows returned (for debugging, the queries I'm running are simple SELECT * FROM table and checking the Cursor's getCount()).
Anybody run into anything like this before? These commands seem to run on command-line sqlite3. Are they're gotchas that I'm missing (e.g. INSERTS must/must not be semicolon terminated, or some issue involving multiple tables)? I've attached some of the code below.
Thanks for your time, and let me know if I can clarify further.
@Override
public void onCreate(SQLiteDatabase db)
{
db.execSQL("CREATE TABLE "+ LEVEL_TABLE +" (" +
" "+ _ID +" INTEGER PRIMARY KEY AUTOINCREMENT," +
" level TEXT NOT NULL,"+
" rows INTEGER NOT NULL,"+
" cols INTEGER NOT NULL);");
db.execSQL("CREATE TABLE "+ DYNAMICS_TABLE +" (" +
" level_id INTEGER NOT NULL," +
" row INTEGER NOT NULL,"+
" col INTEGER NOT NULL,"+
" type INTEGER NOT NULL);");
db.execSQL("CREATE TABLE "+ SCORE_TABLE +" (" +
" level_id INTEGER NOT NULL," +
" score INTEGER NOT NULL,"+
" date_achieved DATE NOT NULL,"+
" name TEXT NOT NULL);");
this.enterFirstLevel(db);
}
And a sample of the insert code I'm currently using, which gets called in enterFirstLevel() (some values hard-coded just to get it running...):
private void insertDynamic(SQLiteDatabase db, int row, int col, int type)
{
ContentValues values = new ContentValues();
values.put("level_id", "1");
values.put("row", Integer.toString(row));
values.put("col", Integer.toString(col));
values.put("type", Integer.toString(type));
db.insertOrThrow(DYNAMICS_TABLE, "col", values);
}
Finally, query code looks like this:
private Cursor fetchLevelDynamics(int id)
{
SQLiteDatabase db = this.leveldata.getReadableDatabase();
try {
String fetchQuery = "SELECT * FROM " + DYNAMICS_TABLE;
String[] queryArgs = new String[0];
Cursor cursor = db.rawQuery(fetchQuery, queryArgs);
Activity activity = (Activity) this.context;
activity.startManagingCursor(cursor);
return cursor;
}
finally {
db.close();
}
}
