How to check whether a table exist in android sqlite

Question

I have used sqlite to insert the data s and fetch back the result but what i did is first to display the list which contains the fetched values if there is no values then the list shows the empty view indicating the user to add new items to the sqlite database. But when i try this it shows me an error no such table exist in the database. I would like to know how to check whether the table has been created nor it has values .If not how to throw the exception to the user to insert new items to the table.

Database:

public class BuisnessDatabaseHandler extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "receiptExample";

// Labels table name
private static final String TABLE_LABELS = "buisnesslabels";
private static final String KEY_ID = "id";
private static final String KEY_IMAGE = "image";
private static final String KEY_STATUS="status";
private static final String KEY_UPLOADSTATUS = "uplaodstatus";
private static final String KEY_USERID="userid";
SQLiteDatabase db;
public BuisnessDatabaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
    // Category table create query
    String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + TABLE_LABELS + "("
            + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_IMAGE + " BLOB,"
            + KEY_STATUS + " TEXT,"
            + KEY_UPLOADSTATUS + " TEXT,"
            + KEY_USERID + " TEXT" + ");";

    db.execSQL(CREATE_CATEGORIES_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_LABELS);

    // Create tables again
    onCreate(db);
}
public void insertLabel(byte[] imag,String status,String uploadstatus,String userid){
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    //values.put(KEY_ID,id);
    values.put(KEY_IMAGE,imag);
    values.put(KEY_STATUS,status);
    values.put(KEY_UPLOADSTATUS,uploadstatus);
    values.put(KEY_USERID,userid);
    // Inserting Row
    db.insert(TABLE_LABELS, null, values);
    db.close(); // Closing database connection
}
public ArrayList<BuisnesslistItems> getAllLabels(){
    //  List<String> labels = new ArrayList<String>();
    ArrayList<BuisnesslistItems>labels=new ArrayList<BuisnesslistItems>();

    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_LABELS;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {

            BuisnesslistItems buisnesslistItems=new BuisnesslistItems();
            buisnesslistItems.setImage(cursor.getBlob(1));
            buisnesslistItems.setStatus("Status:"+cursor.getString(2));


            labels.add(buisnesslistItems);
        } while (cursor.moveToNext());
    }

    // closing connection
    cursor.close();
    db.close();

    // returning lables
    return labels;
}

public String composeJSONfromSQLite(){
    ArrayList<HashMap<String, String>> label;
    label = new ArrayList<HashMap<String, String>>();
    String selectQuery = "SELECT  * FROM labels where " + KEY_STATUS + " = '"+"no"+"'";
    SQLiteDatabase database = this.getWritableDatabase();
    Cursor cursor = database.rawQuery(selectQuery, null);
    if (cursor.moveToFirst()) {
        String encodedImage = Base64.encodeToString(cursor.getBlob(1), Base64.DEFAULT);
        do {
            HashMap<String, String> map = new HashMap<String, String>();
            map.put(KEY_ID, cursor.getString(0));
            map.put(KEY_IMAGE, encodedImage);
            map.put(KEY_USERID,cursor.getString(4));
            map.put(KEY_STATUS,"yes");
            label.add(map);
        } while (cursor.moveToNext());
    }
    database.close();
    Gson gson = new GsonBuilder().create();
    //Use GSON to serialize Array List to JSON
    return gson.toJson(label);
}

/**
 * Get Sync status of SQLite
 * @return
 */
public String getSyncStatus(){
    String msg = null;
    if(this.dbSyncCount() == 0){
        msg = "Wallet is Sync with cloud!";
    }else{
        msg = "Wallet needs to be Sync\n";
    }
    return msg;
}

/**
 * Get SQLite records that are yet to be Synced
 * @return
 */
public int dbSyncCount(){
    int count = 0;
    String selectQuery = "SELECT  * FROM labels where " +KEY_STATUS+" = '"+"no"+"'";
    SQLiteDatabase database = this.getWritableDatabase();
    Cursor cursor = database.rawQuery(selectQuery, null);
    count = cursor.getCount();
    database.close();
    return count;
}

/**
 * Update Sync status against each User ID
 * @param id
 * @param status
 */
public void updateSyncStatus(String id, String status){
    SQLiteDatabase database = this.getWritableDatabase();
    String updateQuery = "Update labels set " + KEY_STATUS + " = '"+ status +"'," + KEY_UPLOADSTATUS + " = 'Uploaded' where " + KEY_ID + "="+"'"+ id +"'";
    Log.d("query", updateQuery);
    database.execSQL(updateQuery);
    database.close();
}
public void deleteUploaded(String status){
    SQLiteDatabase database=this.getWritableDatabase();
    String deleteQuery="Delete From labels where " + KEY_STATUS+ " = 'yes' ";
    Log.d("dquery",deleteQuery);
    database.execSQL(deleteQuery);
    database.close();
}

Listpage:

BuisnessDatabaseHandler db = new BuisnessDatabaseHandler(getApplicationContext());

    // Spinner Drop down elements
    //  List<String> lables = db.getAllLabels();
    ArrayList<BuisnesslistItems>list=new ArrayList<BuisnesslistItems>();
    list=db.getAllLabels();
    buisnessListAdapter = new BuisnessListAdapter(
            BuisnessList.this, list);
    if (loginSession.isLoggedIn()) {
        loginSession.checkLogin();
        listView.setAdapter(buisnessListAdapter);
        listView.setEmptyView(findViewById(R.id.empty));

        Toast.makeText(getApplicationContext(), db.getSyncStatus(), Toast.LENGTH_LONG).show();
    }else{
        Intent i=new Intent(BuisnessList.this,LoginPAge.class);
        startActivity(i);
        BuisnessList.this.finish();
    }

Error:

 Caused by: android.database.sqlite.SQLiteException: no such table: buisnesslabels (code 1): , while compiling: SELECT  * FROM buisnesslabels
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:898)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:509)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1346)
        at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1285)
        at nidhinkumar.reccs.buisnesscarddetails.BuisnessDatabaseHandler.getAllLabels(BuisnessDatabaseHandler.java:81)
        at nidhinkumar.reccs.buisnesscarddetails.BuisnessList.syncSQLiteMySQLDB(BuisnessList.java:364)
        at nidhinkumar.reccs.buisnesscarddetails.BuisnessList.init(BuisnessList.java:216)
        at nidhinkumar.reccs.buisnesscarddetails.BuisnessList.onCreate(BuisnessList.java:143)

I would like to add a condition in the list page to check whether the table exist or not and whether it has values or not

Answer 1

You can use the following statement to check if the table exists:

Cursor cursor = db.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '"
    + TABLE_NAME + "'", null);

SQLite maintains a table called sqlite_master containing the information of all the tables in the database. So if the resulting cursor returns a count of 1, you know the table exists. After you check that, query the database again with your desired query:

cursor = your-query-here; 

And use cursor.getCount(); again to determine if you have data. If cursor.getCount(); returns more than 0 then you have the data you want.

Answer 2

SQLiteStatement:

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

Call

db.rawQuery() 

and read the Cursor or

SQLiteStatement s = db.compileStatement(SQLiteStatement);
long count = s.simpleQueryForLong();

if(count > 0) -> Table Exists

After you checked if it exists you can query a count on rows or so on. maybe you have to check if specific columns do exist for the table

Answer 3

Manual way of doing it

Visit following link and download DBrowser : Sqlite DBBrowser

Now, go to Android Device Monitor

• expand data
• again expand data inside it
• then search for folder with your app package name
• Then open database folder inside it
• expand and pull that database file into local, while pulling save it with .sqlite extension
• Open DBBrowser and open the database which you have in local.
• you can now check wether the table has created or it has rows in it.

EDIT

change the DATABASE_VERSION = 1 to DATABASE_VERSION = 2 So whenever you change anything within the onCreate() and onUpgrade() increase the Database version.

To check whether your table has rows,

if(list.size() == 0){
   // empty
}