Add new Table in sqlite database

Question

I have an question.

I have only one table in my database

@Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL( "CREATE TABLE books (_id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT, author TEXT,isbn TEXT,city TEXT);");
        Log.v("onCreate-------","called onCreate"); 

    }

here:-

sqlite> select * from books;

also working fine.

but i want to add new table so i am trying to add here :-

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        android.util.Log.w("books",
        "Upgrading database, which will destroy all old data");

        //db.execSQL("ALTER TABLE myallbooks ADD COLUMN city2");
        db.execSQL("CREATE TABLE myfields (author TEXT,isbn TEXT,city TEXT);");



        Log.v("onUpgrade-------","called onUpgrade with alter"); 
    }

after changing the version number i run the app but i did not found the table any where.

sqlite> select * from myfields;

Error:-no such table :myfield...................why it is not creating

Why do you want to create table in onUpgrade() ? – Manmeet Singh Batra Jan 18 '13 at 10:57 — Manmeet Singh Batra, Jan 18 '13 at 10:57
tell me where i can add new table.if i needed. – Adb Jan 18 '13 at 11:02 — Adb, Jan 18 '13 at 11:02
I've posted below, please have a look... – Manmeet Singh Batra Jan 18 '13 at 11:07 — Manmeet Singh Batra, Jan 18 '13 at 11:07

score 2 · Answer 1 · answered Jan 18 '13 at 10:55

2

onUpgrade() will be called when you have Database version changes to Higher version. It looks like you are not increasing the version and trying to select the columns of myfields table.

Please check whether you are updated the version number to a higher number compared to earlier one.

answered Jan 18 '13 at 10:55

TNR

5,839
3
33
62

ok i try again.it means my code is fine..i can create table onUpgrade(). – Adb Jan 18 '13 at 10:59
Yes you can create table in onUpgrade() as well. – Manmeet Singh Batra Jan 18 '13 at 11:12

score 1 · Accepted Answer · answered Jan 18 '13 at 11:01

1

@Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL( "CREATE TABLE books (_id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT, author TEXT,isbn TEXT,city TEXT);");
        Log.v("onCreate-------","called onCreate"); 

   db.execSQL("CREATE TABLE myfields (author TEXT,isbn TEXT,city TEXT);");

    }

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        android.util.Log.w("books",
        "Upgrading database, which will destroy all old data");

        //db.execSQL("ALTER TABLE myallbooks ADD COLUMN city2");
       // db.execSQL("CREATE TABLE myfields (author TEXT,isbn TEXT,city TEXT);");

          //delete tables
                db.execSQL("DROP TABLE IF EXISTS books");
          // Create tables again`enter code here`
        onCreate(db);

        Log.v("onUpgrade-------","called onUpgrade with alter"); 
    }

answered Jan 18 '13 at 11:01

Manmeet Singh Batra

467
2
5

onupgrade()...........Caused by: android.database.sqlite.SQLiteException: Can't upgrade read-only database from version 2 to 3: /data/data/com.example.upgrade/databases/upgrade.db – Adb Jan 18 '13 at 11:13
exception --if i write create table query inside the upgrade. – Adb Jan 18 '13 at 11:14
it is working fine..but i do not think so we can write create table query inside upgrade – Adb Jan 18 '13 at 11:17
Have a look on http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int) The SQLite ALTER TABLE documentation can be found here. If you add new columns you can use ALTER TABLE to insert them into a live table. If you rename or remove columns you can use ALTER TABLE to rename the old table, then create the new table and then populate the new table with the contents of the old table. – Manmeet Singh Batra Jan 18 '13 at 11:20

Add new Table in sqlite database

2 Answers2