How does one check if a table exists in an Android SQLite database?

Question

I have an android app that needs to check if there's already a record in the database, and if not, process some things and eventually insert it, and simply read the data from the database if the data does exist. I'm using a subclass of SQLiteOpenHelper to create and get a rewritable instance of SQLiteDatabase, which I thought automatically took care of creating the table if it didn't already exist (since the code to do that is in the onCreate(...) method).

However, when the table does NOT yet exist, and the first method ran upon the SQLiteDatabase object I have is a call to query(...), my logcat shows an error of "I/Database(26434): sqlite returned: error code = 1, msg = no such table: appdata", and sure enough, the appdata table isn't being created.

Any ideas on why?

I'm looking for either a method to test if the table exists (because if it doesn't, the data's certainly not in it, and I don't need to read it until I write to it, which seems to create the table properly), or a way to make sure that it gets created, and is just empty, in time for that first call to query(...)

EDIT
This was posted after the two answers below:
I think I may have found the problem. I for some reason decided that a different SQLiteOpenHelper was supposed to be created for each table, even though both access the same database file. I think refactoring that code to only use one OpenHelper, and creating both tables inside it's onCreate may work better...

score 132 · Answer 1 · edited Sep 03 '19 at 12:41

132

Try this one:

public boolean isTableExists(String tableName, boolean openDb) {
    if(openDb) {
        if(mDatabase == null || !mDatabase.isOpen()) {
            mDatabase = getReadableDatabase();
        }

        if(!mDatabase.isReadOnly()) {
            mDatabase.close();
            mDatabase = getReadableDatabase();
        }
    }

    String query = "select DISTINCT tbl_name from sqlite_master where tbl_name = '"+tableName+"'";
    try (Cursor cursor = mDatabase.rawQuery(query, null)) {
        if(cursor!=null) {
            if(cursor.getCount()>0) {
                return true;
            }
        }
        return false;
    }
}

edited Sep 03 '19 at 12:41

Tad

4,668
34
35

answered Oct 22 '11 at 23:52

Nikolay DS

1,367
1
8
8

11

Don't forget `cursor.close();` – Tyler May 07 '12 at 21:31
@Gray I want all the table names which are in database, how its possible? – Pratik Butani May 01 '13 at 06:57
I suspect ""select DISTINCT tbl_name from sqlite_master" without the `WHERE` @PratikButani. I'm not really sure. – Gray May 01 '13 at 18:57
1

For unknown reason, the tablename is case sensitive. I rather use `select * from sqlite_master where UPPER(name) = 'ROUTES'.` – Thupten Jul 07 '13 at 19:06
4

Nice answer but painful grammar! Should surely be called 'isTableExisting()'. – Chris Hatton Mar 08 '14 at 06:42
getCount always return 1 in my case even though I enter some random string as table name. @Nikolay DS – Teja Nandamuri Mar 09 '16 at 17:40
Does `getReadableDatabase()` call `onCreate()`? – Code-Apprentice Oct 12 '16 at 16:29
Overly complicated code (no need boolean for opening, what is this for?). Also closing cursor should be on a `finally` block. – m0skit0 Mar 23 '17 at 11:36
android should put a database.existsTable(string tableName) – 10101010 Oct 25 '17 at 23:35
1

This worked for me but I had to wrap the query in a try/catch or my app would crash when the table didn't exist. – Braden Holt Oct 26 '17 at 05:20
@styler1972 I think this is why `Cursor cursor = mDatabase.rawQuery(query, null)` is put inside the `try` parentheses – YoussefDir May 29 '20 at 15:04
@tyler I think `try`, closes automatically the cursor, since `Cursor` extends `Closable` which extends `AutoCloseable` – Kreshnik Nov 01 '21 at 20:38

score 55 · Answer 2 · answered Jun 17 '10 at 04:47

55

I know nothing about the Android SQLite API, but if you're able to talk to it in SQL directly, you can do this:

create table if not exists mytable (col1 type, col2 type);

Which will ensure that the table is always created and not throw any errors if it already existed.

answered Jun 17 '10 at 04:47

chrisbtoo

1,572
9
13

That's how I'm creating the table in the onCreate method inside the SQLiteOpenHelper class. In android, it's recommended to let that class create the table, as it allows the app to update its database automatically, and just is apparently more efficient in general. Unfortunately, that codeblock that executes code much like the one you wrote isn't getting ran in time :( – camperdave Jun 17 '10 at 05:18
This works great and also works with indexes, ie: "create index if not exists [...]". – Eric Fortier Jul 22 '13 at 18:43
5

This is actually the best answer for the question asked. – The Original Android Apr 05 '16 at 03:00
1

but the question does not ask to create one – 10101010 Oct 25 '17 at 23:34

score 13 · Answer 3 · edited Mar 30 '17 at 08:03

13

Although there are already a lot of good answers to this question, I came up with another solution that I think is more simple. Surround your query with a try block and the following catch:

catch (SQLiteException e){
    if (e.getMessage().contains("no such table")){
            Log.e(TAG, "Creating table " + TABLE_NAME + "because it doesn't exist!" );
            // create table
            // re-run query, etc.
    }
}

It worked for me!

edited Mar 30 '17 at 08:03

Shabbir Dhangot

8,954
10
58
80

answered Aug 15 '12 at 09:07

robguinness

16,266
14
55
65

1

Wouldn't it be better to put the Log statement inside the if() block? It looks like if the SQLiteException is thrown for another reason than "no such table", the log will indicate that you're creating the table, while you're actually not. – Aug 23 '12 at 08:37
2

Using exceptions to control flow is something to be ashamed of, not taught to others. Checking the message in that fashion, doubly so. – Nick Cardoso Nov 22 '18 at 11:58
If used sparingly, I don't think there is anything wrong with using exceptions in use cases such as the one described above. Certainly nothing I am ashamed of. – robguinness Nov 27 '18 at 12:32
I think `e.getMessage().contains("no such table")` is worse than using exceptions for control flow. Both is bad style, but parsing error messages for specific text is even very bad style. – jox Jun 20 '19 at 11:34

score 11 · Answer 4 · edited Aug 28 '12 at 11:20

This is what I did:

/* open database, if doesn't exist, create it */
SQLiteDatabase mDatabase = openOrCreateDatabase("exampleDb.db", SQLiteDatabase.CREATE_IF_NECESSARY,null);

Cursor c = null;
boolean tableExists = false;
/* get cursor on it */
try
{
    c = mDatabase.query("tbl_example", null,
        null, null, null, null, null);
        tableExists = true;
}
catch (Exception e) {
    /* fail */
    Log.d(TAG, tblNameIn+" doesn't exist :(((");
}

return tableExists;

score 8 · Accepted Answer · answered Jun 17 '10 at 18:11

Yep, turns out the theory in my edit was right: the problem that was causing the onCreate method not to run, was the fact that SQLiteOpenHelper objects should refer to databases, and not have a separate one for each table. Packing both tables into one SQLiteOpenHelper solved the problem.

score 3 · Answer 6 · edited Jun 29 '16 at 07:39

 // @param db, readable database from SQLiteOpenHelper

 public boolean doesTableExist(SQLiteDatabase db, String tableName) {
        Cursor cursor = db.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '" + tableName + "'", null);

    if (cursor != null) {
        if (cursor.getCount() > 0) {
            cursor.close();
            return true;
        }
        cursor.close();
    }
    return false;
}

sqlite maintains sqlite_master table containing information of all tables and indexes in database.
So here we are simply running SELECT command on it, we'll get cursor having count 1 if table exists.

score 3 · Answer 7 · answered Jan 14 '21 at 12:27

Kotlin solution, based on what others wrote here:

    fun isTableExists(database: SQLiteDatabase, tableName: String): Boolean {
        database.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '$tableName'", null)?.use {
            return it.count > 0
        } ?: return false
    }

score 2 · Answer 8 · answered Jun 17 '10 at 05:32

You mentioned that you've created an class that extends SQLiteOpenHelper and implemented the onCreate method. Are you making sure that you're performing all your database acquire calls with that class? You should only be getting SQLiteDatabase objects via the SQLiteOpenHelper#getWritableDatabase and getReadableDatabase otherwise the onCreate method will not be called when necessary. If you are doing that already check and see if th SQLiteOpenHelper#onUpgrade method is being called instead. If so, then the database version number was changed at some point in time but the table was never created properly when that happened.

As an aside, you can force the recreation of the database by making sure all connections to it are closed and calling Context#deleteDatabase and then using the SQLiteOpenHelper to give you a new db object.

Well, I am getting my database object through the getWritableDatabase() call, sorry I forgot to specify that. Also, I'm sure onUpgrade() isn't being called, since that method has a Log.d(...) call as its first line that I'm not seeing in the database. I'll try deleting the entire database file, and we'll see if that somehow fixes it... — camperdave, Jun 17 '10 at 14:50
Unfortunately, deleting the entire database (I used root explorer to purge the file) did not work. I use two tables in my app - one of them initialized perfectly, however the other that has been giving me trouble all along did not. — camperdave, Jun 17 '10 at 14:54

score 0 · Answer 9 · answered Sep 03 '16 at 07:22

 public boolean isTableExists(String tableName) {
    boolean isExist = false;
    Cursor cursor = db.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '" + tableName + "'", null);
    if (cursor != null) {
        if (cursor.getCount() > 0) {
            isExist = true;
        }
        cursor.close();
    }
    return isExist;
}

score 0 · Answer 10 · answered Aug 27 '19 at 06:24

Important condition is IF NOT EXISTS to check table is already exist or not in database

like...

String query = "CREATE TABLE IF NOT EXISTS " + TABLE_PLAYER_PHOTO + "("
            + KEY_PLAYER_ID + " TEXT,"
            + KEY_PLAYER_IMAGE + " TEXT)";
db.execSQL(query);

score 0 · Answer 11 · answered Nov 06 '19 at 18:58

i faced that and deal with it by try catch as simple as that i do what i want in table if it not exist will cause error so catch it by exceptions and create it :)

SQLiteDatabase db=this.getWritableDatabase();
        try{
            db.execSQL("INSERT INTO o_vacations SELECT * FROM vacations");
            db.execSQL("DELETE FROM vacations");
        }catch (SQLiteException e){
            db.execSQL("create table o_vacations (id integer primary key ,name text ,vacation text,date text,MONTH text)");
            db.execSQL("INSERT INTO o_vacations SELECT * FROM vacations");
            db.execSQL("DELETE FROM vacations");
        }

score 0 · Answer 12 · edited Mar 29 '17 at 10:48

0

no such table exists: error is coming because once you create database with one table after that whenever you create table in same database it gives this error.

To solve this error you must have to create new database and inside the onCreate() method you can create multiple table in same database.

edited Mar 29 '17 at 10:48

Jéwôm'

3,753
5
40
73

answered Sep 07 '11 at 10:10

Megha

1,581
2
18
33

score -1 · Answer 13 · answered Mar 08 '13 at 16:28

..... Toast t = Toast.makeText(context, "try... " , Toast.LENGTH_SHORT); t.show();

    Cursor callInitCheck = db.rawQuery("select count(*) from call", null);

    Toast t2a = Toast.makeText(context, "count rows " + callInitCheck.getCount() , Toast.LENGTH_SHORT);
    t2a.show();

    callInitCheck.moveToNext();
    if( Integer.parseInt( callInitCheck.getString(0)) == 0) // if no rows then do
    {
        // if empty then insert into call

.....

How does one check if a table exists in an Android SQLite database?

13 Answers13

Linked

Related