How Does One Check If a Table Exists in an Android SQLite Database

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

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.

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

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

How do I check in SQLite whether a table exists?

I missed that FAQ entry.

Anyway, for future reference, the complete query is:

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

Where {table_name} is the name of the table to check.

Documentation section for reference: Database File Format. 2.6. Storage Of The SQL Database Schema

  • This will return a list of tables with the name specified; that is, the cursor will have a count of 0 (does not exist) or a count of 1 (does exist)

how to check if a table exists in sqlite

To start, and know the structure of the SQLite_Master table, getting the schema as shown below.

sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);

What I did next was to create a class structure to match

    private class CSQLite_Master
{
public string type { get; set; }
public string name {get; set;}
public string tbl_name { get; set; }
public int rootpage { get; set; }
public string sql { get; set; }
}

From that, I used the connection .Query() feature like below

using (var db = new SQLiteConnection(YourPathToDatabase) )
{
var tmp1 = db.Query<CSQLite_Master>(
@"select * from sqlite_master where type = 'table' and name = ?",
new object[] { logname });
if( tmp1 is null || tmp1.Count == 0 )
return;

// else we DO have an entry, and you can see all the parts of it
// by doing basic debug inspection in the watch window
}

The .Query executes whatever command available, and based on the class/structure <CSQLite_Master> in this case, the data is retrieved into a list of this object type. Now for the parameter. The "?" is a place-holder for the parameters to be provided.

The last parameter to the Query() call is an array of values added in the same sequence as the "?" place-holders. In this case, a list of objects new object[] { logname } and that list is created with the logname you prepared.

You should get either the record, or records if you did not include the AND name= portion of the query, it would return all table names, for you.

Is it possible to check if an SQLite table exists.

This query will return the list of tables in the database

SELECT * FROM sqlite_master WHERE type = 'table';

You can filter it down to a single row for an "exists" check.

SELECT * FROM sqlite_master WHERE type = 'table' AND tbl_name = 'xyz';

sqlite - how to check if table exists before completing inserts?

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

SELECT * FROM sqlite_master WHERE name ='myTable' and type='table'; 


Related Topics



Leave a reply



Submit