Click here to Skip to main content
15,881,803 members
Search within:


How to upgrade sqlite database on android app update for existing user without loosing data
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello,
I have a Quiz app where I created a local SQLite database and stored the data in it. with some Tables Like Category, Sub Category, Level, and Questions.
now After some time, I want to add more categories and questions to it but I am not able to do so if I tried on upgrade with version number increase either it gives me an error or not updating the database.

here is the error which I got and my app crash

PowerShell
2021-07-24 15:54:13.977 32604-32604/com.offline.aligator E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.offline.aligator, PID: 32604
    android.database.sqlite.SQLiteException: no such table: tbl_category (code 1): , while compiling: SELECT * FROM  tbl_category
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:890)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:501)
        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:46)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1392)
        at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1331)
        at com.offline.aligator.adapter.DBHelper.getAllCategories(DBHelper.java:155)
        at com.offline.aligator.fragment.FragmentCategory.onCreateView(FragmentCategory.java:64)
        at androidx.fragment.app.Fragment.performCreateView(Fragment.java:2963)
        at androidx.fragment.app.FragmentStateManager.createView(FragmentStateManager.java:518)
        at androidx.fragment.app.FragmentStateManager.moveToExpectedState(FragmentStateManager.java:282)
        at androidx.fragment.app.FragmentManager.executeOpsTogether(FragmentManager.java:2189)
        at androidx.fragment.app.FragmentManager.removeRedundantOperationsAndExecute(FragmentManager.java:2100)
        at androidx.fragment.app.FragmentManager.execPendingActions(FragmentManager.java:2002)
        at androidx.fragment.app.FragmentManager$5.run(FragmentManager.java:524)
        at android.os.Handler.handleCallback(Handler.java:790)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:164)
        at android.app.ActivityThread.main(ActivityThread.java:6494)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807)



Here is my DBhelper Code

package com.offline.aligator.adapter;


import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import com.offline.aligator.Constant;
import com.offline.aligator.activity.MainActivity;
import com.offline.aligator.model.Category;
import com.offline.aligator.model.Quizplay;
import com.offline.aligator.model.SubCategory;

import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import static android.content.ContentValues.TAG;
import static com.offline.aligator.activity.MainActivity.context;

public class DBHelper extends SQLiteOpenHelper {
    private String packageName;
    private SQLiteDatabase db;
    private static final String db_name = "quiz_main_sub_cat.db";
    private static final String db_name_single_cat = "quiz_single_cat.db";

    //table names
    public static final String TBL_CATEGORY = "tbl_category";
    public static final String TBL_SUB_CATEGORY = "tbl_subCategory";
    public static final String TBL_QUESTION = "questions_list";
    //table name
    public static String TBL_LEVEL = "tbl_level";

    //column names
    public static String LEVEL_NO = "level_no";
    public static final String ID = "id";
    public static final String CATE_ID = "cate_id";
    public static final String SUB_CATE_ID = "sub_cate_id";
    public static final String CATEGORY_NAME = "category";
    public static final String SUB_CATEGORY_NAME = "sub_category";
    public static final String QUESTION_SOLUTION = "que_solution";
    public static final String QUESTION = "question";
    public static final String OPTION_A = "option_a";
    public static final String OPTION_B = "option_b";
    public static final String OPTION_C = "option_c";
    public static final String OPTION_D = "option_d";
    public static final String RIGHT_ANSWER = "right_answer";
    public static final String LEVEL = "level";


    private String db_path;
    private static int db_version = 2;
    Context con;


    public DBHelper(Context con) {
        super(con, db_name, null, db_version);
        // TODO Auto-generated constructor stub

        db_path= con.getDatabasePath(db_name).getAbsolutePath();
        //db_path = con.getDatabasePath(db_name).toString().replace(db_name, "");
        this.con = con;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub




    }

    public void createDB() throws IOException {

        if (checkDB()) {
        } else if (!checkDB()) {
            this.getReadableDatabase();
            close();
            copyDB();
        }

    }

    private boolean checkDB() {

        SQLiteDatabase cDB = null;
        try {
            cDB = SQLiteDatabase.openDatabase(db_path, null,
                    SQLiteDatabase.OPEN_READWRITE);
        } catch (SQLiteException e) {
            e.printStackTrace();
        }
        if (cDB != null) {
            cDB.close();
        }
        return cDB != null ? true : false;
    }


    private void copyDB() throws IOException {
        InputStream inputFile = con.getAssets().open(db_name);
      //  String outFileName = db_path + db_name;
        OutputStream outFile = new FileOutputStream(db_path);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = inputFile.read(buffer)) > 0) {
            outFile.write(buffer, 0, length);
        }
        outFile.flush();
        outFile.close();
        inputFile.close();
    }

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

        // TODO Auto-generated method stub
        db.execSQL("drop table " + TBL_CATEGORY);
        onCreate(db);

    }

    private void executeSQLScript(SQLiteDatabase db, BufferedReader reader) throws IOException {
        String line;
        StringBuilder statement = new StringBuilder();
        while ((line = reader.readLine()) != null) {
            statement.append(line);
            statement.append("\n");
            if (line.endsWith(";")) {
                db.execSQL(statement.toString());
                statement = new StringBuilder();
            }
        }
    }

    /*
     *get All category from table
     */
    public ArrayList<Category> getAllCategories() {
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<Category> categoryArrayList = new ArrayList<>();
        Cursor cur = db.rawQuery("SELECT * FROM  " + TBL_CATEGORY, null);
        if (cur.moveToFirst()) {
            do {
                Category category = new Category();
                category.setId(cur.getInt(cur.getColumnIndex(ID)));
                category.setName(cur.getString(cur.getColumnIndex(CATEGORY_NAME)));
                categoryArrayList.add(category);

            } while (cur.moveToNext());
        }
        //}
        return categoryArrayList;
    }

    public int GetMaxLevelSingleCat(int cat_id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cur = db.rawQuery("select max(" + LEVEL + ") from " + TBL_QUESTION + " where (" + CATE_ID + "=" + cat_id + ")", null);
        if (cur.moveToFirst()) {
            do {
                Constant.totalLevel = cur.getInt(cur.getColumnIndex("max(level)"));
            } while (cur.moveToNext());
        }
        //}
        return Constant.totalLevel;
    }

    public int GetMaxLevel(int cat_id, int sub_cate_id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cur = db.rawQuery("select max(" + LEVEL + ") from " + TBL_QUESTION + " where (" + CATE_ID + "=" + cat_id + " and " + SUB_CATE_ID + "=" + sub_cate_id + ")", null);
        if (cur.moveToFirst()) {
            do {
                Constant.totalLevel = cur.getInt(cur.getColumnIndex("max(level)"));
            } while (cur.moveToNext());
        }
        //}
        return Constant.totalLevel;
    }

    public ArrayList<SubCategory> getSubCategoryById(int cate_id) {
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<SubCategory> subCategories = new ArrayList<>();
        Cursor cur = db.rawQuery("SELECT * FROM  " + TBL_SUB_CATEGORY + " where (" + CATE_ID + " = " + cate_id + ")", null);
        if (cur.moveToFirst()) {
            do {
                SubCategory subCategory = new SubCategory();
                subCategory.setId(cur.getInt(cur.getColumnIndex(ID)));
                subCategory.setCategoryId(cur.getString(cur.getColumnIndex(CATE_ID)));
                subCategory.setName(cur.getString(cur.getColumnIndex(SUB_CATEGORY_NAME)));
                subCategories.add(subCategory);

            } while (cur.moveToNext());
        }
        //}
        return subCategories;
    }

    public List<Quizplay> getQuestionGujSingleCat(int cate_id, int noOfQuestion, int level) {

        List<Quizplay> quizplay = new ArrayList<Quizplay>();
        int total = noOfQuestion;
        String sql = "select *  FROM " + TBL_QUESTION + " where (" + CATE_ID + "=" + cate_id + " and "
                + LEVEL + "=" + level + ") ORDER BY RANDOM() LIMIT " + total;
        SQLiteDatabase db = this.getReadableDatabase();
        //SQLiteDatabase db = SQLiteDatabase.openDatabase("/data/data/" + packageName + "/databases/" + DATABASE_NAME, null, 0);
        Cursor cursor = db.rawQuery(sql, null);

        if (cursor.moveToFirst()) {
            do {
                Quizplay question = new Quizplay();
                question.setId(cursor.getInt(cursor.getColumnIndex("id")));
                question.setQuestion(cursor.getString(cursor.getColumnIndex("question")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_a")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_b")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_c")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_d")));
                String rightAns = cursor.getString(cursor.getColumnIndex("right_answer"));
                if (rightAns.equalsIgnoreCase("A")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_a")));
                } else if (rightAns.equalsIgnoreCase("B")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_b")));
                } else if (rightAns.equalsIgnoreCase("C")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_c")));
                } else {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_d")));
                }
                if (question.getOptions().size() == 4) {
                    quizplay.add(question);
                }

            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        Collections.shuffle(quizplay);
        quizplay = quizplay.subList(0, noOfQuestion);
        return quizplay;
    }
    public List<Quizplay> getQuestionGuj(int cate_id, int sub_cate_id, int noOfQuestion, int level) {

        List<Quizplay> quizplay = new ArrayList<Quizplay>();
        int total = noOfQuestion;
        String sql = "select *  FROM " + TBL_QUESTION + " where (" + CATE_ID + "=" + cate_id + " and "
                + SUB_CATE_ID + " =" + sub_cate_id + " and "
                + LEVEL + "=" + level + ") ORDER BY RANDOM() LIMIT " + total;
        SQLiteDatabase db = this.getReadableDatabase();
        //SQLiteDatabase db = SQLiteDatabase.openDatabase("/data/data/" + packageName + "/databases/" + DATABASE_NAME, null, 0);
        Cursor cursor = db.rawQuery(sql, null);

        if (cursor.moveToFirst()) {
            do {
                Quizplay question = new Quizplay();
                question.setId(cursor.getInt(cursor.getColumnIndex("id")));
                question.setQuestion(cursor.getString(cursor.getColumnIndex("question")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_a")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_b")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_c")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_d")));
                String rightAns = cursor.getString(cursor.getColumnIndex("right_answer"));
                if (rightAns.equalsIgnoreCase("A")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_a")));
                } else if (rightAns.equalsIgnoreCase("B")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_b")));
                } else if (rightAns.equalsIgnoreCase("C")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_c")));
                } else {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_d")));
                }
                if (question.getOptions().size() == 4) {
                    quizplay.add(question);
                }

            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        Collections.shuffle(quizplay);
        quizplay = quizplay.subList(0, noOfQuestion);
        return quizplay;
    }


    /*
     * insert level no
     */
    public void insertIntoDBSingleCat(int cat_id,int level_no) {
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "INSERT INTO " + TBL_LEVEL + " (" + CATE_ID + "," + LEVEL_NO + ") VALUES('" + cat_id + "', '" + level_no + "');";
        db.execSQL(query);

    }
    public void insertIntoDB(int cat_id, int sub_cat_id, int level_no) {
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "INSERT INTO " + TBL_LEVEL + " (" + CATE_ID + "," + SUB_CATE_ID + "," + LEVEL_NO + ") VALUES('" + cat_id + "', '" + sub_cat_id + "', '" + level_no + "');";
        db.execSQL(query);

    }

    /*
     *with this method we check if categoryId & subCategoryId is already exist or not in our database
     */
    public boolean isExistSingleCat(int cat_id) {
        db = this.getReadableDatabase();
        Cursor cur = db.rawQuery("SELECT * FROM " + TBL_LEVEL + " WHERE ( " + CATE_ID + " = " + cat_id + ")", null);
        boolean exist = (cur.getCount() > 0);
        cur.close();

        return exist;

    }
    public boolean isExist(int cat_id, int sub_cat_id) {
        db = this.getReadableDatabase();
        Cursor cur = db.rawQuery("SELECT * FROM " + TBL_LEVEL + " WHERE ( " + CATE_ID + " = " + cat_id + " AND " + SUB_CATE_ID + " = " + sub_cat_id + ")", null);
        boolean exist = (cur.getCount() > 0);
        cur.close();

        return exist;

    }

    /*
     * get level
     */
    public int GetLevelByIdUsingSingleCat(int cat_id) {
        int level = 1;
        String selectQuery = "SELECT  * FROM " + TBL_LEVEL + " WHERE  (" + CATE_ID + "=" + cat_id + ")";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                level = c.getInt(c.getColumnIndex(LEVEL_NO));
            } while (c.moveToNext());
        }
        return level;
    }

    public int GetLevelById(int cat_id, int sub_cat_id) {
        int level = 1;
        String selectQuery = "SELECT  * FROM " + TBL_LEVEL + " WHERE  (" + CATE_ID + "=" + cat_id + " AND " + SUB_CATE_ID + "=" + sub_cat_id + ")";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                level = c.getInt(c.getColumnIndex(LEVEL_NO));
            } while (c.moveToNext());
        }
        return level;
    }

    public String getQuestionSolution(int queId) {
        String level = "";
        String selectQuery = "SELECT  * FROM " + TBL_QUESTION + " WHERE  (" + ID + "=" + queId + ")";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
                if (c.moveToFirst()) {
                    do {
                        level = c.getString(c.getColumnIndex(QUESTION_SOLUTION));
                    } while (c.moveToNext());
                }


        return level;
    }

    /*
     * Update lavel
     */
   public void UpdateLevelSingleCat(int cat_id, int level_no) {
       db = this.getReadableDatabase();

       db.execSQL("update " + TBL_LEVEL + " set level_no=" + level_no + " where (" + CATE_ID + "=" + cat_id + ")");    }
    public void UpdateLevel(int cat_id, int sub_cat_id, int level_no) {
        db = this.getReadableDatabase();

        db.execSQL("update " + TBL_LEVEL + " set level_no=" + level_no + " where (" + CATE_ID + "=" + cat_id + "  and  " + SUB_CATE_ID + " = " + sub_cat_id + ")");
    }
}



How to resolve this?? I don't want that's user will loose there progress

What I have tried:

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

       // TODO Auto-generated method stub
       db.execSQL("drop table " + TBL_CATEGORY);
       onCreate(db);

   }
Posted
Add a Solution
Comments
Richard MacCutchan 24-Jul-21 7:01am    
You have just deleted the table, so you have lost all your data.
Feather WoRK 24-Jul-21 8:51am    
how to resolved and upgrade data without losing and deleting data?
Richard MacCutchan 24-Jul-21 8:59am    
For a start do not drop a table if it contains current data. If you need to modify it then use the ALTER TABLE[^] command.
David Crow 24-Jul-21 22:08pm    
"android.database.sqlite.SQLiteException: no such table: tbl_category (code 1): , while compiling: SELECT * FROM tbl_category"

This error should be self explanatory. You are trying to interact with a nonexistent table. Your onCreate() method is empty. That is where the three tables get created.

"After some time, I want to add more categories and questions to it but I am not able to do so if I tried on upgrade with version number increase either it gives me an error or not updating the database."

How did the initial categories and questions get in the tables? Are you reading them from some other source (e.g., text file, string literals in the code)?

If you want more, simply add more "insert" statements in a method such as populateDatabase().

"How to upgrade sqlite database..."

Upgrade implies you want to change the database schema (e.g., add more columns).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
How to update sqlite database in android application?
update data in sqlite database android
update data through sqlite database
How to backup and restore sqlite database java android?
Android SQLite Database
How do I update a sqlite database by adding data from a excel file using fastexcel and C# ?
Problems upgrading to 2.5.1
Android With SQlite database Question?
Downloading remote sqlite DB to android application
Android crash when connecting to sqlite?

Advertise
Privacy
Cookies
Terms of Use
Last Updated 24 Jul 2021
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web02 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900