2

How to add column in an existing table? I used the following code.

SQLiteDatabase db = this.getWritableDatabase();
 db.rawQuery("ALTER TABLE " + MNEMONICTABLE +" ADD COLUMN " + F_STATUS +" int DEFAULT 0", null);

This code runs without error. But the new column was not added to the table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Govin
  • 108
  • 7

3 Answers3

4

You are using rawQuery instead of execSQL. Try this:

SQLiteDatabase db = this.getWritableDatabase();
 db.execSQL("ALTER TABLE " + MNEMONICTABLE +" ADD COLUMN " + F_STATUS +" int DEFAULT 0");
Rafael T
  • 15,401
  • 15
  • 83
  • 144
  • I had following error. Caused by: java.lang.IllegalArgumentException: Empty bindArgs atandroid.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1642) – Govin Nov 25 '13 at 12:22
  • 1
    @Govin just use the parameterless overload of `execSQL()` by removing the `, null` – laalto Nov 25 '13 at 12:28
  • you can do it with rawQuery also, you just need to commit the transaction. Usually `cursor.moveToFirst()` or some of these – mihail Nov 25 '13 at 13:07
  • An `ALTER TABLE` SQL Statement is NO query! `rawQuery` is for SELECT statements ONLY, where execSQL is for anything EXCEPT `SELECT` statements! See http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#execSQL%28java.lang.String%29 – Rafael T Nov 25 '13 at 13:17
  • @mihail Nothing to do with transactions. `rawQuery()` just compiles the SQL but does not run it. It needs one of the `moveTo...()` methods on the cursor to run it. `execSQL()` both compiles and runs. – laalto Nov 25 '13 at 13:19
  • @RafaelT The docs are bad. Some clarification e.g. in http://stackoverflow.com/questions/20110274/what-is-the-correct-way-to-do-inserts-updates-deletes-in-android-sqlitedatabase/20118910#20118910 – laalto Nov 25 '13 at 13:20
  • @laalto, you are right, anyway, my point was that you can use rawQuery, you just have to call some of the moveTo... methods to complete it. – mihail Nov 25 '13 at 13:29
  • And WHY should one use the somehow wrong method and call another method just to make the 'hack' working, when there is a one-liner that will work? – Rafael T Nov 25 '13 at 14:17
0

you can use ALTER TABLE function on your onUpgrade() method, like this :

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    // If you need to add a column
    if (newVersion > oldVersion) {
        db.execSQL("ALTER TABLE " + MNEMONICTABLE +" ADD COLUMN " + F_STATUS +" int DEFAULT 0", null);
    }
}

Obviously, the SQLite will differ depending on the column definition. Also you need to upgrade your database version.

GrIsHu
  • 29,068
  • 10
  • 64
  • 102
-1

you should COMMIT changes also

MikroDel
  • 6,705
  • 7
  • 39
  • 74