How can Upgrade sqlite database to next version with saving user data

Question

i have an application with version 1.0 and now i hade made newxt version and now i want ot upgrade the database but i can not upgrade the database. the android studio give error from this line:

 db = this.getReadableDatabase();

. here is full code:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db = this.getReadableDatabase();
    Cursor SignalRes = db.rawQuery("select * from signal", null);

    db.execSQL("DROP TABLE IF EXISTS signal");
    onCreate(db);

    SignalRes.moveToFirst();
    while (!SignalRes.isAfterLast()) {
        insertSignal((SignalRes.getInt(0)), SignalRes.getString(1), SignalRes.getString(2)
                , SignalRes.getString(3), SignalRes.getString(4), SignalRes.getString(5)
                , SignalRes.getString(6), SignalRes.getString(7), SignalRes.getString(8));
        SignalRes.moveToNext();
    }
    SignalRes.close();

Charles Li · Accepted Answer · 2016-06-18 22:18:20Z

2

onUpgrade already has a db variable passed in, so you should just use that variable rather than assigning to it again. It will be better if you can post the error message as well, but I'm suspecting you are opening the database recursively.

By the way, check this post out, you also need to clarify the relation between newVersion and oldVersion to indicate when you want the database to be updated.

So your code should look something like this:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < YOUR_NEW_DB_VERSION) {
         db.execSQL(YOUR_QUERY_TO_MOVE_DATA_FROM_TABLE1_TO_TABLE2);
         db.execSQL(YOUR_QUERY_TO_DROP_TABLE1);
    }
}

edited Jun 18, 2016 at 22:18

answered Jun 18, 2016 at 21:20

Charles Li

1,9354 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

moji Over a year ago

yes. you are right. but if i dont write db=this.getReadableDatabase(); it bring for me a null table because it is not open for reading from it. @charles-li

Charles Li Over a year ago

Take a look at the link I posted, if you still have problems let me know then. And can you clarify how you are trying to update your database? ex. Insert data into another table?

moji Over a year ago

yes. first i put the table's data to a cursor and then remove it. then i create it again. after creating the table i will write data to the new table. but i want to know why this line have error?@charles-li

Charles Li Over a year ago

first of all, you're trying to edit the database, so you should call getWritableDatabase() (not in onUpgrade). And if you check the documentation, it says "The first time this is called, the database will be opened and onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int) and/or onOpen(SQLiteDatabase) will be called." So you are in essence making it loop recursively since you are calling the function in onUpgrade.

Charles Li Over a year ago

check my updated post, that might give you a better idea what you should do.

|

Hayrullah Cansu · Accepted Answer · 2016-06-18 21:21:02Z

-1

1) Define a variable like that private static final int DATABASE_VERSION = 5;

2) Your SQLiteOpenHelper Class Constructor and super method will check version

public EzberletDB(Context context) {
    super(context, DATABASE_NAME,null,DATABASE_VERSION);
    this.context=context;
    SQLiteDatabase db = getWritableDatabase();
    db.close();
    try {
        context.openOrCreateDatabase(DATABASE_NAME, context.MODE_PRIVATE, null); 
    } catch (Exception e) {

        context.openOrCreateDatabase(DATABASE_NAME,context.MODE_PRIVATE,null);
    }
}

3) onUpgrade method if version chanced that method will execute

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    try {
        String query="";
        query = "DROP TABLE IF EXISTS signal";
        db.execSQL(query);
        onCreate(db);
    } catch (SQLException e) {
        e.printStackTrace();
        Message.message(context,""+e);
    }
}

Finally: if you increase DATABASE_VERSION to 6, your db will be updated. I hope you can't lose user datas with this way.

answered Jun 18, 2016 at 21:21

Hayrullah Cansu

2625 silver badges14 bronze badges

4 Comments

moji Over a year ago

what did you mean by writing this code: context.openOrCreateDatabase(DATABASE_NAME, context.MODE_PRIVATE, null); @hayrullah-cansu

Hayrullah Cansu Over a year ago

It's not relevant your problem. But that code try open database or else create a database. @moji

moji Over a year ago

I tried the code: context.openOrCreateDatabase(DATABASE_NAME, context.MODE_PRIVATE, null); but i got this error: database is locked (code 5): , while compiling: PRAGMA journal_mode @hayrullah-cansu

Hayrullah Cansu Over a year ago

Can you post your extended SQLiteOpenHelper class and usage it? @moji

Collectives™ on Stack Overflow

How can Upgrade sqlite database to next version with saving user data

2 Answers 2

8 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related