0

I wrote this simple Activity to test writing and reading from an SQLite database on Android.

public class MainActivity extends Activity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);     
        DatabaseHelper db = new DatabaseHelper(this);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

    private class DatabaseHelper extends SQLiteOpenHelper
    {

        public DatabaseHelper(Context context) {
            super(context, "TestDatabase", null, 3);
            getWritableDatabase().rawQuery("INSERT INTO TestTable VALUES ('Apple')", null);
            Cursor cursor = getReadableDatabase().rawQuery("SELECT * FROM TestTable", null);
            Log.d("trace", String.valueOf(cursor.moveToFirst()));
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE TestTable (value text)");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS TestTable");
            onCreate(db);
        }

    }

}

The meat of it is in the DatabaseHelper constructor, where I write a value to TestTable, try to get it back in a cursor, and then log the value of cursor.moveToFirst() (which should only be false if the cursor is empty). It's false. What's going on?

Jack M
  • 4,769
  • 6
  • 43
  • 67
  • Don't do stuff like that in constructor. Declare one method to write data and another to read data. – ramaral Dec 28 '13 at 21:17
  • @ramaral It's just a test program. Or is there another reason? – Jack M Dec 28 '13 at 21:18
  • I was able to get it working by writing with .insert rather than .rawQuery, but I have no idea why .rawQuery doesnt' work. – Jack M Dec 28 '13 at 21:31
  • **"but I have no idea why .rawQuery doesnt' work."** Because it's a method to perform queries and not to do other SQL stuff (such as insert, delete etc etc) - the name kind of gives it away. – Squonk Dec 28 '13 at 21:39
  • You can use [`execSQL()`](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#execSQL(java.lang.String)) to execute non-query statements. – Ben Dec 28 '13 at 21:40
  • I think rawQuery should be used only with sql statemens that returns data. You should call execSQL instead. – ramaral Dec 28 '13 at 21:41

2 Answers2

1

The documentation says

rawQuery runs the provided SQL and returns a Cursor over the result set.

Since then, you should not use it for INSERT. Try execSQL or insert for getting the row id of the inserted row.

Luke
  • 2,539
  • 2
  • 23
  • 40
  • Could the documentation possibly be any more vague? Forgive me for assuming "raw" meant *raw*. Thank you. – Jack M Dec 28 '13 at 21:50
  • You are almost right, but consider query (in `rawQuery`) as a **query**. :) – Luke Dec 28 '13 at 21:57
  • Well, I've always thought of "query" as the general term for any SQL command. – Jack M Dec 28 '13 at 21:58
  • 1
    @JackM For an answer involving query/exec distinction and Android sqlite documentation quirks, see http://stackoverflow.com/questions/20110274/what-is-the-correct-way-to-do-inserts-updates-deletes-in-android-sqlitedatabase/20118910#20118910 – laalto Dec 29 '13 at 07:34
0

1) rawQuery() should be changed to execSQL like following line:

    public DatabaseHelper(Context context) {
        super(context, "TestDatabase", null, 3);

        getWritableDatabase().execSQL("INSERT INTO TestTable VALUES ('Apple')");
        Cursor cursor = getReadableDatabase().rawQuery("SELECT * FROM TestTable", null);
        Log.d("trace:", String.valueOf(cursor.moveToFirst()));
    }

2) in order to make sure call onCreate(), override onDowngrade() in your DatabaseHelper class:

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS TestTable");
        onCreate(db);
    }
Chaojun
  • 1