why do i have to increment my database verison every time i add a new table to my sqlite database?

Question

This is where my database is created. I used a global variable that takes the username upon login, then i create a table based on that username. when i try to enter data into the database it gives me an error that says no such table exists. if i increment the database version manually, it works! do i have to increment the database version every time i create a new table? if so, how can i achieve that?

very time a new user registers a new table is added to hold that users info, in debugging, i have to manually go into my code and increment the database version every time i add a new user, is there a better way of doing this? else, a user will be able to log in but wont be able to enter any information untill i access my code and increment the database version.. thank you

package com.example.victor.kidsrewards;
import android.app.Activity;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.content.Intent;
import android.os.Bundle;

/**
 * Created by Victor on 3/20/2016.
 */
public class TaskSQLiteHelper extends SQLiteOpenHelper {
    String _username = Globals._username;

    public TaskSQLiteHelper(Context context){
        super(context, "tasks_db", null, 8);

    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        //create a table

        db.execSQL("CREATE TABLE tasks_"+_username + " (_id INTEGER PRIMARY KEY , task TEXT NOT NULL, points INTEGER NOT NULL)");
    }

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

}

this is my trace

03-30 14:18:25.217 6450-6450/com.example.victor.kidsrewards E/SQLiteLog: (1) no such table: tasks_JOE
03-30 14:18:25.217 6450-6450/com.example.victor.kidsrewards D/AndroidRuntime: Shutting down VM
03-30 14:18:25.221 6450-6450/com.example.victor.kidsrewards W/dalvikvm: threadid=1: thread exiting with uncaught exception (group=0x94d5eb20)
03-30 14:18:25.221 6450-6450/com.example.victor.kidsrewards E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.victor.kidsrewards, PID: 6450
android.database.sqlite.SQLiteException: no such table: tasks_JOE (code 1): , while compiling: SELECT _id, task, points FROM tasks_JOE
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
at com.example.victor.kidsrewards.TaskDataBase.getTasks(TaskDataBase.java:59)
at com.example.victor.kidsrewards.TaskList.onActivityCreated(TaskList.java:46)
at android.support.v4.app.Fragment.performActivityCreated(Fragment.java:1983)
at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1092)
at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1252)
at android.support.v4.app.BackStackRecord.run(BackStackRecord.java:738)
at android.support.v4.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1617)
at android.support.v4.app.FragmentManagerImpl$1.run(FragmentManager.java:517)
at android.os.Handler.handleCallback(Handler.java:733)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5045)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
at dalvik.system.NativeStart.main(Native Method)

You don't have to increment the version just to change the database schema, and, in fact, I advise against doing that. Version increments should only happen for updates to published apps, not while it's still in development. You can use the "Clear data" option in your app's info page in Settings, or you could uninstall and reinstall your app. — Mike M., Mar 30 '16 at 06:24
Yes Agree with you @MikeM.. But it should consider all case. we must inform him real reason behind the Version concept of DB. — Shabbir Dhangot, Mar 30 '16 at 06:28
Please go through already asked questions or just do a little google search, and you would have got to know that that's the defined way to update DB. That's it. Nothing confusing or nothing to complicated in that. So marked this as duplicate to remove unnecessary duplication. — MKJParekh, Mar 30 '16 at 06:29
@SID You're right. They should know what's going on, for when they do need to upgrade their app. — Mike M., Mar 30 '16 at 06:30
if i clear the data, register a user, then log in as that user, it works perfectly fine, howerver; if a register a second user, then log in as that second user, it crashes and produces the error. how can i register sevral users and have my app create and access each usertable accordingly. (if i increment the database version manually it works fine) — vhdz04, Mar 30 '16 at 06:30

score 0 · Answer 1 · answered Mar 30 '16 at 06:23

0

In order to upgrade the Database in Android you should increment the DATABASE_VERSION by one, in order for the SQLOpenHelper to know that it must called the onUpgrade method.

Otherwise your table wont added and old db will be use.

answered Mar 30 '16 at 06:23

Shabbir Dhangot

8,954
10
58
80

when you upgrade your database version. app will remove your old file of database and create new with new changes therefore whenever you make changes in database file at that time increment in version is must. – Abhishek Mar 30 '16 at 06:31
@Abhishek Yes Agreed – Shabbir Dhangot Mar 30 '16 at 06:35
very time a new user registers a new table is added to hold that users info, in debugging, i have to manually go into my code and increment the database version every time i add a new user, is there a better way of doing this? else, a user will be able to log in but wont be able to enter any information untill i access my code and increment the database version.. thank you. – vhdz04 Mar 30 '16 at 06:38

why do i have to increment my database verison every time i add a new table to my sqlite database?

1 Answers1