Android with Sqlite - How to check if table exist and if empty

Question

I want to show an alertbox if the table does not exist or the table content is empty.

To check if the table is empty I'm using:

        cCheckDB = db.rawQuery("SELECT COUNT(*) FROM " + MainActivity.TABLE_NAME +"", null);

        if (cCheckDB != null) {
            cCheckDB.moveToFirst();

            if (cCheckDB.getInt (0) == 0) {
                // EMPTY
            }
        }

But how to wrap this in an "if table exist"? What is the best way?

I know both ways to check if a table exist or to check if there is content in it. But I want to use the checks in a meaningful and way and not in single query if this is possible - that's my question.

Please check this flow http://stackoverflow.com/questions/3058909/how-does-one-check-if-a-table-exists-in-an-android-sqlite-database — Volodymyr, May 17 '16 at 06:29
Sure I can catch the Exception or create if not exist but this is not helpfull in my case. I thought there is another way — Oli, May 17 '16 at 06:31

score 5 · Accepted Answer · answered May 17 '16 at 06:52

5

Hope,This will be helpful to you. It's working well!!!

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

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

    Cursor cursor = mDatabase.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;
}

answered May 17 '16 at 06:52

Akash Patel

218
2
10

Its working. I can use this to check if the table is empty after that – Oli May 17 '16 at 07:02
it only check table existance. – Pramod mishra May 17 '16 at 07:06
I wanted to combine both checks but it looks like this is not possible. So Ill use this function with a if clause to check the content after that. This is for me the best solution – Oli May 17 '16 at 07:11

Amit Vaghela · Answer 2 · 2016-05-17T06:37:05.690

2

To check that your table exists or not, you can use:

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';

here, replace table_name with your table name you want to change

EX:

SELECT name FROM sqlite_master WHERE type='table' AND name='Employee_Details';

edited May 17 '16 at 06:37

answered May 17 '16 at 06:31

Amit Vaghela

22,772
22
86
142

check my edited answer. it will give you result if that table exists – Amit Vaghela May 17 '16 at 06:37
So ill have to seperate one table check and one check if table is empty. Is there no other way to combine these two checks? – Oli May 17 '16 at 06:41
1

if table doest exist than there is no need to check whether it is empty or not than check http://stackoverflow.com/questions/22630307/sqlite-check-if-table-is-empty – Amit Vaghela May 17 '16 at 06:45
If Im using a select query on an non existing table ill get a null pointer – Oli May 17 '16 at 06:50
what you need to do is, first check if table exists or not than you can check for data in that table.+ – Amit Vaghela May 17 '16 at 06:55
Thank you. Yes this is most helpfull – Oli May 17 '16 at 07:12
Akash Patel was faster – Oli May 17 '16 at 08:01
okay but he has copied complete answer from above link. it can be marked as duplicate. – Amit Vaghela May 17 '16 at 08:03

Pramod mishra · Answer 3 · 2016-05-17T07:04:09.230

0

the more optimal way is to check if table exist then check if record exist or not

 private void clearTable(String tableName) {
        int count;
        Cursor cursor = db.rawQuery("SELECT count(*) FROM sqlite_master WHERE type='table' AND " +
                "name= " + tableName, null);
        cursor.moveToFirst();
        count = cursor.getInt(0);
        if (!cursor.isClosed())
            cursor.close();

        if (count > 0)//table exist now delete record if it exist
        {
            cursor=db.rawQuery("select exists(select 1 FROM " + tableName + ")", null);
            cursor.moveToFirst();
            count = cursor.getInt(0);
            if (!cursor.isClosed())
                cursor.close();
            if (count > 0)
                db.execSQL("delete from " + tableName);
        }
    }

edited May 17 '16 at 07:04

answered May 17 '16 at 06:34

Pramod mishra

615
6
16

Any clue to combine theres two meaningful? Thats what I would like to know :) – Oli May 17 '16 at 06:37
check my edited answer i tested it in sqlite tool in firefox – Pramod mishra May 17 '16 at 06:50
you need to check if table exists first than check for record existence because there is no combine query for checking table existance and record together. till i searched. – Pramod mishra May 17 '16 at 07:05

Android with Sqlite - How to check if table exist and if empty

3 Answers3