Check if database exists in sqlite

Question

I have an android app with a sqllite database. This database has various tables. So far I have two tables and I am having an error: when I create the table A first, when I run the code for the table B my app crashes; when I create the table B first and then the A, the app also crashes. I have to clear my app cache so I can "change" the acess, so I'm never able to access both in the same app "run". I think its because Im recreating the database when I add the new table. To avoid that, I tried to check if the database already exists and, if does, not recreate but I'm checking it in the constructor and android doesn't allow it.

log:

12-25 00:30:28.024 32031-32031/com.support.android.iplfit E/AndroidRuntime: FATAL EXCEPTION: main Process: com.support.android.iplfit, PID: 32031 android.database.sqlite.SQLiteException: no such table: Dica (code 1): , while compiling: DELETE FROM Dica 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.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) at android.database.sqlite.SQLiteDatabase.delete(SQLiteDatabase.java:1499) at com.support.android.iplfit.BDHelpers.DicaBDHelper.removerAllDicasBD(DicaBDHelper.java:73) at com.support.android.iplfit.Singletons.SingletonDicas.adicionarDicasBD(SingletonDicas.java:65) at com.support.android.iplfit.Singletons.SingletonDicas$1.onResponse(SingletonDicas.java:83) at com.support.android.iplfit.Singletons.SingletonDicas$1.onResponse(SingletonDicas.java:77) at com.android.volley.toolbox.JsonRequest.deliverResponse(JsonRequest.java:65) at com.android.volley.ExecutorDelivery$ResponseDeliveryRunnable.run(ExecutorDelivery.java:99) at android.os.Handler.handleCallback(Handler.java:751) at android.os.Handler.dispatchMessage(Handler.java:95) at android.os.Looper.loop(Looper.java:154) at android.app.ActivityThread.main(ActivityThread.java:6077) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:756)

And one of the classes where Im creating one table:

public class DicaBDHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "db_iplfit";
    private static final int DB_VERSION = 1;
    private static final String TABLE_NAME = "Dica";

    private static final String ID_DICA = "id";
    private static final String CHANNEL_DICA = "channel";
    private static final String TITULO_DICA = "titulo";
    private static final String CONTEUDO_DICA = "conteudo";
    private final SQLiteDatabase database;

    public DicaBDHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);

            this.database = getWritableDatabase();
    }

    private static boolean haveDB(Context context, String dbName) {
        File dbFile = context.getDatabasePath(dbName);
        return dbFile.exists();
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        String createDicaTable = "CREATE TABLE " + TABLE_NAME + "( " +
                ID_DICA + " INTEGER UNSIGNED PRIMARY KEY," +
                CHANNEL_DICA + " TEXT NOT NULL," +
                TITULO_DICA + " TEXT NOT NULL," +
                CONTEUDO_DICA + " TEXT NOT NULL" + ")";
        database.execSQL(createDicaTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase database, int i, int j) {
        String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
        database.execSQL(sql);
        //this.onCreate(database);
    }
}

if both databases have the same name, either create the tables in the same helper, or use different database names — njzk2
– njzk2, Commented Dec 25, 2017 at 2:41
You should create all your tables at the same time, whenever onCreate() is called. (You can optionally populate the tables with initial/default values at that time.) An activity will interact with your entire database, not just one table, so the order of activities should never make a difference in how the db is created, just the order in which the tables are used. — Ted Hopp
– Ted Hopp, Commented Dec 25, 2017 at 3:22

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Option 1

Use onCreate to create multiple tables :-

@Override
public void onCreate(SQLiteDatabase database) {
    String createDicaTable = "CREATE TABLE " + TABLE_NAME + "( " +
            ID_DICA + " INTEGER UNSIGNED PRIMARY KEY," +
            CHANNEL_DICA + " TEXT NOT NULL," +
            TITULO_DICA + " TEXT NOT NULL," +
            CONTEUDO_DICA + " TEXT NOT NULL" + ")";
    database.execSQL(createDicaTable);

    // Next table
    String createanothertable = "CREATE TABLE anothertable (column TEXT)";
    database.execSQK(createanothertable);
    // etc
}

Note! onCreate only runs automatically when Database doesn't exist

Option 2

Create subsequent(missing) tables (assumes independence from other other tables) without having to remove existing tables.

Run as required or everytime App is run (hence code IF NOT EXISTS).

public void addMissingTables() {
    String createanothertable = "CREATE TABLE IF NOT EXISTS anothertable (column TEXT)";
    database.execSQL(createanothertable);
    // etc
}

Note! assumes method is added to Databasehelper (so db)
Note! Shouldn't be a problem is used after instance of DatabaseHelper is obtained, as this.database = getWritableDatabase(); will force DatabaseHelper to create database if it doesn't exist.
You could also code IF NOT EXISTS for tables in onCreate and call it in a similar fashion.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 25, 2017 at 7:09

MikeT

57.9k16 gold badges61 silver badges78 bronze badges

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

2 Comments

Nelson Silva Over a year ago

I tried the 1st option. So, on the onCreate of each DB class, I reated the others tables along with the present classe table. Is that right ?

MikeT Over a year ago

@NelsonSilva Yep, option 2 and the independence is cautioned because the table(s) wouldn't exist until added so extra code would be needed in conjunction with adding the table. The former is the norm.

Devyani Kotadiya · Accepted Answer · 2017-12-25 05:07:01Z

0

private boolean checkDataBase() {
    SQLiteDatabase checkDB = null;
    try {
        checkDB = SQLiteDatabase.openDatabase(DB_FULL_PATH(DB_FULL_PATH is the path to your database file), null,
                SQLiteDatabase.OPEN_READONLY);
        checkDB.close();
    } catch (SQLiteException e) {
        // database doesn't exist yet.
    }
    return checkDB != null;
}

answered Dec 25, 2017 at 5:07

Devyani Kotadiya

4505 silver badges19 bronze badges

1 Comment

Nelson Silva Over a year ago

that looks like what I have in my haveDB() method, the issues is where to use the verification. Cant do it in the constructor.

Collectives™ on Stack Overflow

Check if database exists in sqlite

2 Answers 2

Option 1

Option 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Option 1

Option 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related