Android SQLite doesn't exist error on open and already exist error on create during runtime

Question

I'm new to android programming and I used this tutorial to create a mutliple tables SQLite database. I Created a database with two tables with all the necessary classes as explained. I modified it a little bit to add a try/catch at execution to check if the tables already exist or not. I don't have any errors during the build. At the execution time on my phone (Moto G Play), The DB object gets instantiated but when I try to open a Cursor with a select RawQuery, I get the error:

03-09 07:54:51.823 6235-6235/com.petapp.petfinder D/TReceiver﹕ SELECT PetInfo.RMac, PetInfo.Name FROM TReceiver As ReceiverInfo

03-09 07:54:51.953 6235-6235/com.petapp.petfinder E/SQLiteLog﹕ (1) no such table: TReceiver As

So My code enters the Catch block to create the table before to perform the select query one more time and when it tries to create the table, I get the error:

03-09 07:54:52.378 6235-6235/com.petapp.petfinder E/SQLiteLog﹕ (1) table TReceiver already exists

03-09 07:54:52.603 6235-6235/com.petapp.petfinder E/TReceiver﹕ table TReceiver already exists (code 1): , while compiling: CREATE TABLE TReceiver(RMac PRIMARY_KEY, Name TEXT )

I really don't understand why I'm getting those errors. Does anyone has ever encountered that kind of problem? Here is the code I use. Thank you in advance for your responses.

Here is the database Manager class:

public class DatabaseManager {
private Integer mOpenCounter = 0;

private static DatabaseManager instance;
private static SQLiteOpenHelper mDatabaseHelper;
private SQLiteDatabase mDatabase;

public static synchronized void initializeInstance(SQLiteOpenHelper helper) {
    if (instance == null) {
        instance = new DatabaseManager();
        mDatabaseHelper = helper;
    }
}

public static synchronized DatabaseManager getInstance() {
    if (instance == null) {
        throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
                " is not initialized, call initializeInstance(..) method first.");
    }

    return instance;
}

public synchronized SQLiteDatabase openWriteDatabase() {
    mOpenCounter+=1;
    if(mOpenCounter == 1) {
        // Opening new database
        mDatabase = mDatabaseHelper.getWritableDatabase();
    }
    return mDatabase;
}

public synchronized SQLiteDatabase openReadDatabase() {
    mOpenCounter+=1;
    if(mOpenCounter == 1) {
        // Opening new database
        mDatabase = mDatabaseHelper.getReadableDatabase();
    }
    return mDatabase;
}

public synchronized void closeDatabase() {
    mOpenCounter-=1;
    if(mOpenCounter == 0) {
        // Closing database
        mDatabase.close();

    }
} }

Here is the DBHelper class

public class DBHelper  extends SQLiteOpenHelper {
//version number to upgrade database version
//each time if you Add, Edit table, you need to change the
//version number.
private static final int DATABASE_VERSION =8;
// Database Name
private static final String DATABASE_NAME = "PFDatabase.db";
private static final String TAG = DBHelper.class.getSimpleName().toString();

public DBHelper( ) {
    super(App.getContext() , DATABASE_NAME, null, DATABASE_VERSION);

}

@Override
public void onCreate(SQLiteDatabase db) {
    //All necessary tables you like to create will create here
    db.execSQL(TReceiverRepo.createTable());
    db.execSQL(TPetInfoRepo.createTable());
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.d(TAG, String.format("SQLiteDatabase.onUpgrade(%d -> %d)", oldVersion, newVersion));

    // Drop table if existed, all data will be gone!!!
    db.execSQL("DROP TABLE IF EXISTS " + TReceiver.TABLE);
    db.execSQL("DROP TABLE IF EXISTS " + TPetInfo.TABLE);
    onCreate(db);
}}

Here is the 2 tables object definition classes:

public class TPetInfo {
public static final String TAG = TPetInfo.class.getSimpleName();
public static final String TABLE = "TPetInfo";
// Labels Table Columns names
public static final String KEY_ID_Serial_Transmitter = "ID_Serial_Transmitter";
public static final String KEY_Name = "Name";
public static final String KEY_UUID = "UUID";

public String ID_Serial_Transmitter;
public String name;
public String UUID;

public String getID_Serial_Transmitter() {
    return ID_Serial_Transmitter;
}

public void setID_Serial_Transmitter(String ID_Serial_Transmitter) {
    this.ID_Serial_Transmitter = ID_Serial_Transmitter;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public void setUUID(String UUID) {
    this.UUID = UUID;
}

public String getUUID() {
    return UUID;
}}

public class TReceiver {
public static final String TAG = TReceiver.class.getSimpleName();
public static final String TABLE = "TReceiver";
// Labels Table Columns names
public static final String KEY_RMac = "RMac";
public static final String KEY_Name = "Name";

public String RMac;
public String name;


public String getRMac() {
    return RMac;
}

public void setRMac(String RMac) {
    this.RMac = RMac;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}}

And the two Object Repo classes in which the error occurs in the Try/catch block of the getReceiverInfo() method:

public class TPetInfoRepo {
private static TPetInfo TPetInfo;
private final String TAG = TPetInfo.class.getSimpleName().toString();

public TPetInfoRepo(){
    TPetInfo = new TPetInfo();
}

public static String createTable(){
    return "CREATE TABLE " + TPetInfo.TABLE + "("
    + TPetInfo.KEY_ID_Serial_Transmitter + " PRIMARY_KEY, "
    + TPetInfo.KEY_Name + " TEXT, "
    + TPetInfo.KEY_UUID + " TEXT )";
}
public int insert(TPetInfo tPetInfo) {
    int transmiterID;
    SQLiteDatabase db = DatabaseManager.getInstance().openWriteDatabase();
    ContentValues values = new ContentValues();
    values.put(tPetInfo.KEY_ID_Serial_Transmitter, tPetInfo.getID_Serial_Transmitter());
    values.put(tPetInfo.KEY_Name, tPetInfo.getName());
    values.put(tPetInfo.KEY_UUID, tPetInfo.getUUID());

    // Inserting Row
    transmiterID=(int)db.insert(tPetInfo.TABLE, null, values);
    DatabaseManager.getInstance().closeDatabase();

    return transmiterID;
}

public void delete( ) {
    SQLiteDatabase db = DatabaseManager.getInstance().openWriteDatabase();
    db.delete(TPetInfo.TABLE,null,null);
    DatabaseManager.getInstance().closeDatabase();
}

public void updatePetInfo(TPetInfo petInfos){
    SQLiteDatabase db = DatabaseManager.getInstance().openWriteDatabase();
    ContentValues valeurs = new ContentValues();
    valeurs.put(petInfos.KEY_ID_Serial_Transmitter, TPetInfo.getID_Serial_Transmitter());
    valeurs.put(petInfos.KEY_Name, TPetInfo.getName());
    valeurs.put(petInfos.KEY_UUID, TPetInfo.getUUID());

    db.update(petInfos.TABLE, valeurs, petInfos.KEY_ID_Serial_Transmitter + "= ?", new String[]{String.valueOf(petInfos.ID_Serial_Transmitter)});

    db.close();
}
public List<TPetInfo> getPetsInfo(){
    TPetInfo TPetsInfoList = new TPetInfo();
    List<TPetInfo> PetInfoList = new ArrayList<TPetInfo>();

    SQLiteDatabase db = DatabaseManager.getInstance().openReadDatabase();
    String selectQuery =" SELECT PetInfo." + TPetInfo.KEY_ID_Serial_Transmitter
    + ", PetInfo." + TPetInfo.KEY_Name
    + ", PetInfo." + TPetInfo.KEY_UUID
    + " FROM " + TPetInfo.TABLE + "  As PetInfo "
    ;

    Log.d(TAG, selectQuery);
    try {
        Cursor cursor = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                TPetsInfoList = new TPetInfo();
                TPetsInfoList.setID_Serial_Transmitter(cursor.getString(cursor.getColumnIndex(TPetInfo.KEY_ID_Serial_Transmitter)));
                TPetsInfoList.setName(cursor.getString(cursor.getColumnIndex(TPetInfo.KEY_Name)));
                TPetsInfoList.setUUID(cursor.getString(cursor.getColumnIndex(TPetInfo.KEY_UUID)));

                PetInfoList.add(TPetsInfoList);
            } while (cursor.moveToNext());
        }

        cursor.close();
    }
    catch (SQLiteException e)
    {
        if (e.getMessage().toString().toLowerCase().contains("no such table")){
            Log.e(TAG, "Creating table " + TPetInfo.TABLE + "because it doesn't exist!");
            // create table
            try {
                db.execSQL(createTable().toString());
            }
            catch (SQLiteException exception){
                Log.e(TAG, exception.getMessage().toString());
            }
            // re-run query, etc.
            try {
                Cursor cursor = db.rawQuery(selectQuery, null);
                // looping through all rows and adding to list
                if (cursor.moveToFirst()) {
                    do {
                        TPetsInfoList = new TPetInfo();
                        TPetsInfoList.setID_Serial_Transmitter(cursor.getString(cursor.getColumnIndex(TPetInfo.KEY_ID_Serial_Transmitter)));
                        TPetsInfoList.setName(cursor.getString(cursor.getColumnIndex(TPetInfo.KEY_Name)));
                        TPetsInfoList.setUUID(cursor.getString(cursor.getColumnIndex(TPetInfo.KEY_UUID)));

                        PetInfoList.add(TPetsInfoList);
                    } while (cursor.moveToNext());
                }

                cursor.close();
            }
            catch (SQLiteException exception){
                Log.e(TAG, exception.getMessage().toString());
            }
        }
    }
    DatabaseManager.getInstance().closeDatabase();
    return PetInfoList;
}}

public class TReceiverRepo {
private static TReceiver TReceiver;
private final String TAG = TReceiver.class.getSimpleName().toString();
public TReceiverRepo(){
    TReceiver = new TReceiver();
}

public static String createTable(){
    return "CREATE TABLE " + TReceiver.TABLE + "("
            + TReceiver.KEY_RMac + " PRIMARY_KEY, "
            + TReceiver.KEY_Name + " TEXT ) ";

}
public int insert(TReceiver tReceiver) {
    int ID;
    SQLiteDatabase db = DatabaseManager.getInstance().openWriteDatabase();
    ContentValues values = new ContentValues();
    values.put(TReceiver.KEY_RMac, TReceiver.getRMac());
    values.put(TReceiver.KEY_Name, TReceiver.getName());

    // Inserting Row
    ID=(int)db.insert(TReceiver.TABLE, null, values);
    DatabaseManager.getInstance().closeDatabase();

    return ID;
}

public void delete( ) {
    SQLiteDatabase db = DatabaseManager.getInstance().openWriteDatabase();
    db.delete(TReceiver.TABLE,null,null);
    DatabaseManager.getInstance().closeDatabase();
}
public void updatePetInfo(TReceiver ReceiverInfos){
    SQLiteDatabase db = DatabaseManager.getInstance().openWriteDatabase();
    ContentValues valeurs = new ContentValues();
    valeurs.put(ReceiverInfos.KEY_RMac, TReceiver.getRMac());
    valeurs.put(ReceiverInfos.KEY_Name, TReceiver.getName());


    db.update(ReceiverInfos.TABLE, valeurs, ReceiverInfos.KEY_RMac + "= ?", new String[]{String.valueOf(ReceiverInfos.RMac)});

    db.close();
}
public List<TReceiver> getReceiverInfo(){
    TReceiver TReceiverInfoList = new TReceiver();
    List<TReceiver> PetInfoList = new ArrayList<TReceiver>();

    SQLiteDatabase db = DatabaseManager.getInstance().openReadDatabase();
    String selectQuery =" SELECT PetInfo." + TReceiver.KEY_RMac
            + ", PetInfo." + TReceiver.KEY_Name
            + " FROM " + TReceiver.TABLE + "  As ReceiverInfo "
            ;

    Log.d(TAG, selectQuery);
    try {
        Cursor cursor = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                TReceiverInfoList = new TReceiver();
                TReceiverInfoList.setRMac(cursor.getString(cursor.getColumnIndex(TReceiver.KEY_RMac)));
                TReceiverInfoList.setName(cursor.getString(cursor.getColumnIndex(TReceiver.KEY_Name)));

                PetInfoList.add(TReceiverInfoList);
            } while (cursor.moveToNext());
        }

        cursor.close();
    }
    catch (SQLiteException e) {
        if (e.getMessage().toString().toLowerCase().contains("no such table")) {
            Log.e(TAG, "Creating table " + TReceiver.TABLE + " because it doesn't exist!");
            // create table
            try {
                db.execSQL(createTable().toString());
            } catch (SQLiteException exception) {
                Log.e(TAG, exception.getMessage().toString());
            }
            // re-run query, etc.
            try {
                Cursor cursor = db.rawQuery(selectQuery, null);
                // looping through all rows and adding to list
                if (cursor.moveToFirst()) {
                    do {
                        TReceiverInfoList = new TReceiver();
                        TReceiverInfoList.setRMac(cursor.getString(cursor.getColumnIndex(TReceiver.KEY_RMac)));
                        TReceiverInfoList.setName(cursor.getString(cursor.getColumnIndex(TReceiver.KEY_Name)));

                        PetInfoList.add(TReceiverInfoList);
                    } while (cursor.moveToNext());
                }

                cursor.close();
            } catch (SQLiteException exception) {
                Log.e(TAG, exception.getMessage().toString());
            }
        }
    }
    DatabaseManager.getInstance().closeDatabase();
    return PetInfoList;
}}

Hi and welcome to StackOverflow! You may benefit from reading https://stackoverflow.com/help/mcve Is all of the posted code relevant? — Eel Lee, Mar 13 '17 at 11:54

score 0 · Accepted Answer · edited May 23 '17 at 10:29

There a few things that can cause this kind of error.

The first one, and most common, it's change the application database body, and just run the app. That action doesn't change the current database, every time you change it, you have to uninstall your application, or clear the data before running again.

But in this case I think the error might be related to syntax.

There's a chance that the blank space between your table name and the "As" command isn't been considered a valid blank space, but a valid character, try to use another ASCII value for the blank space, like explained in this answer.

Another thing is that you are using two blank spaces between your table name and the "As" command, I don't believe that's a problem, but worth the shot.

And my last guess is the "As" syntax, try to use SQL commands always capitalized, even if it doesn't solve the issue, it's a good pratice.

Thanks for tips! With your answer, I finally found out that I had multiple SQLite syntax problems. I had to change the "PRIMARY_KEY" keyword to "PRIMARY KEY" in the create table statement, I deleted the second blank space in my select statement as well as changing the "As" for "AS" and finally, I had to remove the table name prefix on the front of my column name in my select statement. It's working perfectly now! — Philippe, Mar 14 '17 at 11:36

Android SQLite doesn't exist error on open and already exist error on create during runtime

1 Answers1