Gotchas INSERTing into SQLite on Android?

Question

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();
    }
}

Answer 1

It looks OK enough to me.

To troubleshoot I would suggest adding at least one column to your queryArgs. I'm not sure of the effect of sending in an empty list. It's possible this causes no rows to be returned even if there are results.

It's not necessary to convert your Integer values to strings in ContentValues. Try replacing values.put("row", Integer.toString(row)); with just values.put("row", row); Probably not the cause of your problem though as it would have thrown an exception if it objected to the strings.

I've never used SQLiteDatabase.rawQuery(), maybe try the more commonly-used .query()?

I can't see what your code does with the 'cursor' returned by fetchLevelDynamics() so I can't rule out logic errors there.

Answer 2

Try using null for the second parameter in

db.insertOrThrow(DYNAMICS_TABLE, "col", values);

Answer 3

Figured it out, it was a few different things. First and primarily, the query code: a Cursor can only navigate what is an open database. So the query method above will return a Cursor, then close the database in the finally block.

While it won't function properly, the Cursor won't throw an error if operating under a closed database. It will just say it returned 0 rows (which is why I thought the error was in the INSERT code).

Also, I forgot a few SQL things, namely that row and col are not great names for table fields, and I was able to get rid of a few errors by renaming them to row_num and col_num