SQLite Table not being created: No such table

Question

I have a database created but one of the tables is not being created. Have one table called 'campanha' and work perfectly but when i try to create another one (user) even without columns says 'no such table: user'

Already try:

this.database = this.getWritableDatabase();

Create table without columns to make sure isnt sql problem

Creating a different file to create a different table - same problem

Code:

public class UserBDHelper extends SQLiteOpenHelper {

    private static  final int DB_VERSION = 1;
    private static final String DB_NAME = "tesp-psi-pl2-07-web";
    private static final String TABLE_NAME = "user";
    private static final String USERNAME = "username";
    private static final String EMAIL = "email";
    private static final String ACCESSTOKEN = "accesstoken";
    private static final String NOMEPROPRIO = "userNomeProprio";
    private static final String APELIDO = "userApelido";
    private static final String MORADA = "userMorada";
    private static final String DATANASC = "userDataNasc";
    private final SQLiteDatabase database;

    public UserBDHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        this.database = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createUsertable = "CREATE TABLE " + TABLE_NAME +
                "(id INTEGER, " +
                USERNAME    + " TEXT NOT NULL, " +
                EMAIL     + " TEXT NOT NULL, " +
                ACCESSTOKEN + " TEXT NOT NULL, " +
                NOMEPROPRIO    + " TEXT NOT NULL, " +
                APELIDO     + " TEXT NOT NULL, " +
                DATANASC     + " TEXT NOT NULL, " +
                MORADA       + " TEXT NOT NULL " +
                ")";

        db.execSQL(createUsertable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        this.onCreate(db);
    }

    public void removeAllUsers(){
        this.database.delete(TABLE_NAME,null,null);
    }

}

The erro in the log is :-

android.database.sqlite.SQLiteException: no such table: user (code 1): , while compiling: DELETE FROM user
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    at android.database.sqlite.SQLiteDatabase.delete(SQLiteDatabase.java:1496)
    at pt.ipleiria.estg.dei.amsi.fixbyte.modelo.UserBDHelper.removeAllUsers(UserBDHelper.java:109)

Answer 1

1. "CREATE TABLE " + TABLE_NAME is an invalid table creation statement, because it does not have a single column. you have to add at least one column in order to have the table created.

2. "DROP TABLE IF EXISTS " + TABLE_NAME also seems unfortunate onUpgrade(), because the users table will simply be dropped.

Answer 2

You can't create a table with no columns.
If you tried to execute a statement like

String createUsertable = "CREATE TABLE " + TABLE_NAME;
db.execSQL(createUsertable);

then you would get an error and the app would crash.
So if your app does not crash, this means that the above statements are not executed because oncreate() is not invoked.
It is not invoked because your database already exists.
So either uninstall the app and rerun with a valid create statement for the table,
or change the version of the database to 2 so that onUpgrade() is invoked.

Answer 3

I believe that your issue is that you likely have multiple database helpers one per table for the one/same database.

That is, the one that you have shown appears to be solely for the user table and you probably have another for the campanha table.

A Database Helper typically sub-classes the SQLiteOpenHelper class which is designed to open (connect to) a database. If the database file does not exist it will create the database and then, open the database as it does if the database file exists (note this doesn't happen until an attempt is made to access the database).

If the database file doesn't exist and it creates the file, then it will invoke the onCreate method. It only ever calls onCreate this one time (deleting the file, normally by clearing the App's data or uninstalling the App, would result in the onCreate method running).

So what is happening if you have a database helper for each table, is that the first Database Helper, when an attempt is made to access the database, will see that the database file doesn't exist, create it and then invoke the onCreate method. The second database helper then see that the database file exists and doesn't invoke it's onCreate method.

In terms of your code (assumed), the Database Helper for the campanha table runs onCreate and creates that table but the onCreate for the user table doesn't get run.

The fix is to have a single database helper that creates both tables in the onCreate method, so you need to combine the Database helpers.

You code (noting that the campanha table will very likely not be as per your design) could be something like :-

public class UserBDHelper extends SQLiteOpenHelper {

    private static  final int DB_VERSION = 1;
    private static final String DB_NAME = "tesp-psi-pl2-07-web";
    private static final String TABLE_NAME = "user";
    private static final String USERNAME = "username";
    private static final String EMAIL = "email";
    private static final String ACCESSTOKEN = "accesstoken";
    private static final String NOMEPROPRIO = "userNomeProprio";
    private static final String APELIDO = "userApelido";
    private static final String MORADA = "userMorada";
    private static final String DATANASC = "userDataNasc";

    private static final String CAMPANHA_TABLE_NAME = "campanha"; //<<<<<<<<<< ADDED
    private static final String CAMPANHA_MYCOLUMN = "mycolumn"; //<<<<<<<<< ADDED

    private final SQLiteDatabase database;

    public UserBDHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        this.database = this.getWritableDatabase(); //<<<<<<<<<< Note this will access the database so the database will be created when the Database helper is instantiated
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createUsertable = "CREATE TABLE " + TABLE_NAME +
                "(id INTEGER, " +
                USERNAME    + " TEXT NOT NULL, " +
                EMAIL     + " TEXT NOT NULL, " +
                ACCESSTOKEN + " TEXT NOT NULL, " +
                NOMEPROPRIO    + " TEXT NOT NULL, " +
                APELIDO     + " TEXT NOT NULL, " +
                DATANASC     + " TEXT NOT NULL, " +
                MORADA       + " TEXT NOT NULL " +
                ")";

        db.execSQL(createUsertable);

        //<<<<<<<<<< ADDED this block of code
        String createCampanhaTable = "CREATE TABLE + CAMPANHA_TABLE_NAME +
                "(id INTEGER, " +
                CAMPANHA_MYCOLUMN + " TEXT NOT NULL " +
                ")";
        db.execSQL(createCampanhaTable); //<<<<<<<<< ADDED
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + CAMPANHA_TABLE_NAME); //<<<<<<<<<< ADDED
        this.onCreate(db);
    }

    public void removeAllUsers(){
        this.database.delete(TABLE_NAME,null,null);
    }
}

• NOTE The above is in-principle code it will very likely NOT be the actual code that you want and will therefore very likely need to be adjusted to suit.
• NOTE YOU SHOULD DELETE THE APP'S DATA OR UNINSTALL THE ADD BEFORE RUNNING WITH THE AMENDED CODE
• NOTE YOU WILL NEED TO ENSURE THAT ONLY 1 DATABASE HELPER IS USED