How to check if table exists in Android SQLite database?

Question

I have an Android application that should check if there is a record in the database, and if not, process some things and, ultimately, insert them and just read the data from the database if the data really exists. I am using a subclass of SQLiteOpenHelper to create and retrieve a rewritable instance of SQLiteDatabase, which I thought automatically took care of creating the table if it does not already exist (since the code for this is in onCreate (...)).

However, when the table does not exist yet, and the first method runs on the SQLiteDatabase object, I have a query call (...), the error "I / Database (26434) is displayed in my logarithm: sqlite return: error code = 1, msg = no such table: appdata ", and, of course, the appdata table is not created.

Any ideas on why?

I’m either looking for a method to check if a table exists (because if it’s not, the data is certainly not in it, and I don’t need to read it until I write to it, which seems to create the table correctly) or a way to make sure that it is created and just empty by the time the request is first called (...)

EDIT
This was published after two answers below:
I seem to have found a problem. For some reason, I decided that a different SQLiteOpenHelper should be created for each table, although both access the same database file. I think that refactoring this code uses only one OpenHelper, and creating both tables inside it onCreate might work better ...

Answer 1

Yes, it turns out that the theory in my board is correct: the problem that caused the onCreate method to not start was that SQLiteOpenHelper objects should belong to databases and not have a separate one for each table. Packing both tables into one SQLiteOpenHelper solved the problem.

Answer 2

Try it:

 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; } } 

Answer 3

I don't know anything about the Android API for Android, but if you can talk to it directly in SQL, you can do this:

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

which ensures that the table is always created and does not cause any errors if it already exists.

Answer 4

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; 

Answer 5

Although there are already many good answers to this question, I came up with another solution, which, I think, is simpler. Combine 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!

Answer 6

You mentioned that you created a class that extends SQLiteOpenHelper and implements the onCreate method. Are you sure you are running your entire database, getting calls with this class? You should only get SQLiteDatabase objects through SQLiteOpenHelper#getWritableDatabase and getReadableDatabase , otherwise the onCreate method onCreate not be called when necessary. If you do this already check and see, the SQLiteOpenHelper#onUpgrade method is SQLiteOpenHelper#onUpgrade . If so, then the database version number was changed at some point in time, but the table was never created properly when this happened.

Aside, you can force a database restore by making sure all connections to it are closed and call Context#deleteDatabase , and then use SQLiteOpenHelper to give you a new db object.

Answer 7

  // @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 a sqlite_master table containing information about all tables and indexes in the database.
• So, here we just run the SELECT command on it, we will get a cursor having a score of 1 if the table exists.

Answer 8

no such table exists: error occurs because after creating a database with one table after that, whenever you create a table in the same database, it gives this error.

To solve this error, you need to create a new database and inside the onCreate () method you can create several tables in one database.

Answer 9

  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; } 

Answer 10

Important condition: IF DOES NOT EXIST , to verify that the table already exists or is not in the database

as...

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

Answer 11

..... 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 

.....