In my android app I want to add a column to existing table of database using following method

Question

public void addcolumn(String year,String period){


    //Create column name here

    Calendar now = Calendar.getInstance();
    int yeara = now.get(Calendar.YEAR);
    int month = now.get(Calendar.MONTH) + 1;
    int day = now.get(Calendar.DAY_OF_MONTH);

    //select period according to current time

    String colnew = ""+yeara+"_"+month+"_"+day+"_"+period;
    SQLiteDatabase db = this.getWritableDatabase();
    String exec;



    //Add column in table according to given year
    //

    if (year.equals("First Year")) {
        exec="ALTER TABLE "+ table_name1+" ADD COLUMN "+colnew+" INTEGER NOT NULL DEFAULT 0";
        db.execSQL(exec);

    }
    else if (year.equals("Second Year")) {
        db.execSQL("ALTER TABLE "+ table_name2+" ADD COLUMN "+colnew+" INTEGER NOT NULL  DEFAULT 0");

    }
    else if (year.equals("Third Year")) {
        db.execSQL("ALTER TABLE "+ table_name3+" ADD COLUMN "+colnew+" INTEGER NOT NULL  DEFAULT 0");

    }
    else if (year.equals("Fourth Year")) {
        db.execSQL("ALTER TABLE "+ table_name4+" ADD COLUMN "+colnew+" INTEGER NOT NULL  DEFAULT 0");

    }


}

But when I try to add columns using the addcolumn() method my app crashes. If there is something wrong with my code, please help me to correct it.

Connect your phone to your pc... Enable usb debugging in your phone. There's a small tab in bottom left corner of android studio... There's be a monitor tab where you'll get log output and all crashes and stuff... Disconnect the phone immediately after the crash and scroll to find the crash details... Post them here please — Kushan, Sep 01 '17 at 21:01
I guess you should start with https://developer.android.com/studio/debug/am-logcat.html — Droidman, Sep 01 '17 at 21:11
The tab is called android-monitor. And yes dealing with sqlite is also tricky and not as straight forward as normal sql. You'll have to read up a lot on it since you don't know how to upgrade your db and stuff. It's a bit tricky but there are plenty of tutorials you'll find online. — Kushan, Sep 01 '17 at 21:14

score 1 · Answer 1 · answered Sep 01 '17 at 21:10

1

One obvious error that i can see is that you are trying to alter the db schema outside onUpgrade() method of your helper class.

Schemas can only be altered inside onUpgrade() method similar to how they can only be created inside onCreate().

You'll have to upgrade your db version and that should trigger your onUpgrade() code. The correct way to handle it depends on perspective. You can either truncate your whole db and call onCreate() which will now have the altered schema or you can have cases which match to the db version and change schemas.

Check out this answer for more details:

How to add new Column to Android SQLite Database?

answered Sep 01 '17 at 21:10

Kushan

5,855
3
31
45

is it necessary to use 'alter table' sqlite command inside onupgrade method of helper class? – Ashwini Kumar Sep 01 '17 at 21:17
can any one give me an example how to invoke onupgrade() method. – Ashwini Kumar Sep 01 '17 at 21:19
I have specified a link in my answer. please look at it for examples of onUpgrade(). And yes it is necessary to have alter statements or any DDL commands in onCreate() or onUpgrade() – Kushan Sep 01 '17 at 22:00
@Kushan Schemas/Database structure can be changed outside of `onUpgrade`, and `onCreate` these are basically convenience methods. – MikeT Sep 01 '17 at 23:02

score 0 · Answer 2 · answered Sep 01 '17 at 23:23

0

It appears that you are attempting to add a column named along the lines of 20170901 e.g. your ALTER statements would be like ALTER TABLE youtablename ADD COLUMN 20170901 INTEGER NOT NULL DEFAULT 0.

This will result in a failure as SQL will treat the number as a number as opposed to a string and complain along the lines of:-

SQLiteManager: Likely SQL syntax error: ALTER TABLE log ADD COLUMN 2020 INTEGER NOT NULL DEFAULT 0  [ near "2020": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]

You could indicate a string by using ALTER TABLE log ADD COLUMN '20170901' INTEGER NOT NULL DEFAULT 0 (i.e. enclosing the number in quotes. single or double).

Alternately you could use a non-numeric prefix e.g. ALTER TABLE youtablename ADD COLUMN Y20170901 INTEGER NOT NULL DEFAULT 0 (i.e.Y prefixes the numeric portion).

answered Sep 01 '17 at 23:23

MikeT

51,415
16
49
68

Can I intentionally call 'onupgrade()' method from other classes? – Ashwini Kumar Sep 02 '17 at 07:21
I mean 'onupgrade()' method of dbhelper class. – Ashwini Kumar Sep 02 '17 at 07:22
Yes, in theory, like `onUpgrade` is often used to call `onCreate`. However I'm not sure that you would want/need to as if you used alternative method/methodology then you'd call that. – MikeT Sep 02 '17 at 07:42
@AshwiniKumar, I don't use `onUpgrade` but rather use my own classes (e.g. DBColumn, DBTable, DBDatabase) to define a ***required/wanted*** schema and have methods `actionBuildSQL` (will add any tables that are not in the actual schema) and `actionAlterSQL` (adds columns) often in an `onExpand` method that I invoke. I have these on github here [Mike-j-t/Modules](https://github.com/Mike-j-t/Modules), noting that these are really on GitHub as a backup for myself (so not really maintained that well). – MikeT Sep 02 '17 at 07:53

In my android app I want to add a column to existing table of database using following method

2 Answers2