SQLite table is not properly created

Question

I know there are lots of questions with the same problem. The things I have tried to make this work are the next:

• Uninstall the app and run it again
• Clear data of the app
• Check onUpgrade() and onCreate() methods

I think that my problem is that I'm not creating the two tables at the same time but I can't see why it's not correctly executed.

I'm trying to populate a list view with the data from 'tomasPendientes' table by making a SELECT statement.

Here is the code of the db helper class:

public class AdminSQLiteOpenHelper extends SQLiteOpenHelper {
private ContentValues registro;
private static final String nombre ="dbAlarma";
private static final int versionDB = 1;
private static String table1 = "create table alarma( idal integer primary key autoincrement, nombreMed text, hora time, pastillasTotales integer, pastillasRestantes integer)";
private static String table2 = "create table tomasPendientes( idPend integer, FOREIGN KEY (idPend) REFERENCES alarma (idal))";

public AdminSQLiteOpenHelper(Context context, String nombre, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, nombre, factory, version);
}

@Override
public void onCreate(SQLiteDatabase db) {
    Log.d("DATABASE_CREATION", "Base de datos creandose...");
    db.execSQL(table1);
    db.execSQL(table2);
}

@Override
public void onUpgrade(SQLiteDatabase db, int versionAnte, int versionNue) {
    Log.d("DATABASE_UPDATE", "Base de datos actualizandose...");
    db.execSQL("drop table if exists alarma" );
    db.execSQL("drop table if exists tomasPendientes");
    db.execSQL(table1);
    db.execSQL(table2);
}

public Cursor getTomasPendientes(){
    SQLiteDatabase db = this.getWritableDatabase();
    String query = "SELECT * FROM tomasPendientes NATURAL JOIN alarma";
    Cursor data = db.rawQuery(query, null);
    return data;
}

public void insertarPendiente(String alarma){
    SQLiteDatabase bd = this.getWritableDatabase();
    registro = new ContentValues();
    registro.put("idPendiente",alarma);
    bd.insert("tomasPendientes", null, registro);//nombre de la tabla
    bd.close();
}
}

This is the class that is calling the method that uses the database, which function is to display a list view with the data from the db:

public class VerTomasPendientes extends AppCompatActivity {
private ListView lv;
AdminSQLiteOpenHelper mDataBaseHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_ver_tomas_pendientes);
    lv = findViewById(R.id.lvAlarmas);
    mDataBaseHelper = new AdminSQLiteOpenHelper(this, vars.bd, null, vars.version);
    populateLV();
}

private void populateLV() {
    Cursor data = mDataBaseHelper.getTomasPendientes();
    ArrayList<String> listData = new ArrayList<>();
    while(data.moveToNext()){
        listData.add("Id alarma: " + data.getString(0) + "\n");
    }
    ListAdapter adapter = new ArrayAdapter<>(this, android.R.layout.simple_list_item_1, listData);
    lv.setAdapter(adapter);
}

}

The error in the logcat is the next:

   Caused by: android.database.sqlite.SQLiteException: no such table: tomasPendientes (code 1): , while compiling: SELECT * FROM tomasPendientes NATURAL JOIN alarma
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:893)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:504)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:726)
    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:1426)
    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1365)
    at farmacia.farmacia.AdminSQLiteOpenHelper.getTomasPendientes(AdminSQLiteOpenHelper.java:48)
    at farmacia.farmacia.VerTomasPendientes.populateLV(VerTomasPendientes.java:25)
    at farmacia.farmacia.VerTomasPendientes.onCreate(VerTomasPendientes.java:21)

I don't understand why it isn't created correctly.

Answer 1

I believe that your issue is that you are looking at the wrong part of the logcat. That is that you are looking at a previous failure rather than the current failure.

Running your exact code, doesn't result in the table not found error, but issues a column not found error as per :-

10-23 23:29:36.039 2189-2189/? E/SQLiteLog: (1) table tomasPendientes has no column named idPendiente
10-23 23:29:36.043 2189-2189/? E/SQLiteDatabase: Error inserting idPendiente=bert
    android.database.sqlite.SQLiteException: table tomasPendientes has no column named idPendiente (code 1): , while compiling: INSERT INTO tomasPendientes(idPendiente) VALUES (?)
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
        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.insertWithOnConflict(SQLiteDatabase.java:1467)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
        at axtest.axtest.AdminSQLiteOpenHelper.insertarPendiente(AdminSQLiteOpenHelper.java:48)
        at axtest.axtest.MainActivity.onCreate(MainActivity.java:18)
        at android.app.Activity.performCreate(Activity.java:5008)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
        at android.app.ActivityThread.access$600(ActivityThread.java:130)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:137)
        at android.app.ActivityThread.main(ActivityThread.java:4745)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
        at dalvik.system.NativeStart.main(Native Method)

That is you have used registro.put("idPendiente",alarma); when there is no such column in the tomasPendientes table. the column is idPend.

However, changing to column idPend would then result in a Foreign Key constraint conflict (but these aren't turned on) as there doesn't appear to be any rows inserted into the table on which the tomasPendientes table depends upon.

Working Example

The following code is a modified version of AdminSQLiteOpenhelper.java and an invoking Activity.

AdminSQLiteOpenHelper.java

public class AdminSQLiteOpenHelper extends SQLiteOpenHelper {
    private ContentValues registro;
    private static final String nombre ="dbAlarma";
    private static final int versionDB = 1;


    //<<<<<<<<<< HIGHLY RECOMMEND A SINGLE DEFINITION OF TABLE and COLUMN NAMES
    public static final String TABLE_ALARMA = "alarma";
    public static final String TABLE_TOMASPENDIENTES = "tomasPendientes";
    public static final String ALARMA_COL_IDA1 = "ida1";
    public static final String ALARMA_COL_NOMBREMED = "nombreMed";
    public static final String ALARMA_COl_HORA = "hora";
    public static final String ALARMA_COL_PASTILLASTOTALES = "pastillasTotales";
    public static final String ALARMA_COL_PASTILLASRENTANTES = "pastillasRestantes";
    public static final String TOMASPENDIENTES_COL_IDPEND = "idPend";


    private static String table1 = "create table alarma( idal integer primary key autoincrement, nombreMed text, hora time, pastillasTotales integer, pastillasRestantes integer)";

    private static String crtt1 = "CREATE TABLE IF NOT EXISTS " + TABLE_ALARMA + "(" +
            ALARMA_COL_IDA1 + " INTEGER PRIMARY KEY, " + //<<<<<<<<<< AUTOINCREMENT VERY LIKELY NOT REQUIRED SO OMITTED
            ALARMA_COL_NOMBREMED + " TEXT, " +
            ALARMA_COl_HORA + " TEXT," + //<<<<<<<<<< no column type called time as text (bit of a moot point as it probablu will not matter)
            ALARMA_COL_PASTILLASTOTALES + " INTEGER, " +
            ALARMA_COL_PASTILLASRENTANTES + " INTEGER " +
            ")";
    private static String table2 = "create table tomasPendientes( idPend integer, FOREIGN KEY (idPend) REFERENCES alarma (idal))";

    //<<<<<<<<<< THIS TABLES IS USELESSS AS IT IS >>>>>>>>>>
    private static String crtt2 = "CREATE TABLE IF NOT EXISTS " + TABLE_TOMASPENDIENTES + "(" +
            TOMASPENDIENTES_COL_IDPEND + " INTEGER REFERENCES " +  TABLE_ALARMA +  "(" + ALARMA_COL_IDA1 + ") " +
            ")";

    public AdminSQLiteOpenHelper(Context context, String nombre, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, nombre, factory, version);
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.execSQL("PRAGMA foreign_keys = ON;");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d("DATABASE_CREATION", "Base de datos creandose...");
        db.execSQL(crtt1);
        db.execSQL(crtt2);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int versionAnte, int versionNue) {
        Log.d("DATABASE_UPDATE", "Base de datos actualizandose...");
        db.execSQL("drop table if exists alarma" );
        db.execSQL("drop table if exists tomasPendientes");
        onCreate(db); //<<<<<<<<<< just call onCreate
    }

    public Cursor getTomasPendientes(){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT * FROM tomasPendientes NATURAL JOIN alarma";
        Cursor data = db.rawQuery(query, null);
        return data;
    }


    //<<<<<<<<<< NEW
    public long insertAlarma(String nombreMed, String hora, int pastillastotales, int pastillarentantes) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(ALARMA_COL_NOMBREMED,nombreMed);
        cv.put(ALARMA_COl_HORA,hora);
        cv.put(ALARMA_COL_PASTILLASTOTALES,pastillastotales);
        cv.put(ALARMA_COL_PASTILLASRENTANTES,pastillarentantes);
        return db.insert(TABLE_ALARMA,null,cv);
    }

    //<<<<<<<<<< REPLACEMENT
    public long insertTomaPendientes(long idpend) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(TOMASPENDIENTES_COL_IDPEND,idpend);
        return db.insert(TABLE_TOMASPENDIENTES,null,cv);
    }


    /*<<<<<<<<<< OLD
    public void insertarPendiente(String alarma){
        SQLiteDatabase bd = this.getWritableDatabase();
        registro = new ContentValues();
        registro.put("idPendiente",alarma);
        bd.insert("tomasPendientes", null, registro);//nombre de la tabla
        bd.close();
    }
    */
}

• please refer to the comments.
• Note to utilise Foreign Keys, Foreign Key support has to be turned on for Android. Hence the addition of the overidden onConfigure method that invokes the PRGAMA statement.
• A method has been added to insert rows into the alarma table.
• The method to insert a row into the tomasPendientes has been replaced.

The code used in the Activity was :-

public class MainActivity extends AppCompatActivity {


    AdminSQLiteOpenHelper mASQLiteHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mASQLiteHlpr = new AdminSQLiteOpenHelper(this,"fred",null,1);

        mASQLiteHlpr.insertAlarma("Alarm1","2018-01-11 23:59:59",1234,156);
        mASQLiteHlpr.insertAlarma("Alarm2","2018-01-11 23:59:59",2234,256);
        mASQLiteHlpr.insertAlarma("Alarm3","2018-01-11 23:59:59",3234,356);
        Cursor c1 = mASQLiteHlpr.getTomasPendientes();
        DatabaseUtils.dumpCursor(c1);

        mASQLiteHlpr.insertTomaPendientes(1); // 1 will be OK as there a row in alama table will have 1 in column ida1
        mASQLiteHlpr.insertTomaPendientes(100); // ooops nor row in alarma with 100 in column ida1
        mASQLiteHlpr.insertTomaPendientes(2); // will work
        Cursor c2 = mASQLiteHlpr.getTomasPendientes();
        DatabaseUtils.dumpCursor(c2);
        c1.close();
        c2.close();
    }
}

• mASQLiteHlpr (a AdminSQLiteOpenhelper object) is initially declared as a class variable and then instantiated.
  • Note how database name has been passed as fred (it is ignored) your constructor could be changed to just accept the context
• Three rows are added to the alarma table (note three will be added each run)
• All rows (none when first run) are extracted from the (empty when first run) tomasPendientes table into a Cursor.
• The Cursor is written to the log using the DatabaseUtils dump method.
• An attempt is made to add three rows to the tomasPendientes table (one will not be added due to the Foreign Key constraint).
• The 2nd Cursor is written to the log.
• The 2 Cursors are closed.

Output to the log :-

10-24 00:38:30.423 2694-2694/axtest.axtest D/DATABASE_CREATION: Base de datos creandose...
10-24 00:38:30.431 2694-2694/axtest.axtest I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@53450f08
10-24 00:38:30.431 2694-2694/axtest.axtest I/System.out: <<<<<
10-24 00:38:30.435 2694-2694/axtest.axtest E/SQLiteDatabase: Error inserting idPend=100
    android.database.sqlite.SQLiteConstraintException: foreign key constraint failed (code 19)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:775)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
        at axtest.axtest.AdminSQLiteOpenHelper.insertTomaPendientes(AdminSQLiteOpenHelper.java:92)
        at axtest.axtest.MainActivity.onCreate(MainActivity.java:27)
        at android.app.Activity.performCreate(Activity.java:5008)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
        at android.app.ActivityThread.access$600(ActivityThread.java:130)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:137)
        at android.app.ActivityThread.main(ActivityThread.java:4745)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
        at dalvik.system.NativeStart.main(Native Method)
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@5346544c
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out: 0 {
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    idPend=1
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    ida1=1
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    nombreMed=Alarm1
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    hora=2018-01-11 23:59:59
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    pastillasTotales=1234
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    pastillasRestantes=156
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out: }
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out: 1 {
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    idPend=1
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    ida1=2
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    nombreMed=Alarm2
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    hora=2018-01-11 23:59:59
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    pastillasTotales=2234
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    pastillasRestantes=256
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out: }
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out: 2 {
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    idPend=1
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    ida1=3
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    nombreMed=Alarm3
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    hora=2018-01-11 23:59:59
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    pastillasTotales=3234
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    pastillasRestantes=356
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out: }
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out: 3 {
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    idPend=2
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    ida1=1
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    nombreMed=Alarm1
10-24 00:38:30.439 2694-2694/axtest.axtest I/System.out:    hora=2018-01-11 23:59:59
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    pastillasTotales=1234
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    pastillasRestantes=156
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out: }
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out: 4 {
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    idPend=2
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    ida1=2
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    nombreMed=Alarm2
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    hora=2018-01-11 23:59:59
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    pastillasTotales=2234
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    pastillasRestantes=256
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out: }
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out: 5 {
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    idPend=2
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    ida1=3
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    nombreMed=Alarm3
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    hora=2018-01-11 23:59:59
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    pastillasTotales=3234
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out:    pastillasRestantes=356
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out: }
10-24 00:38:30.443 2694-2694/axtest.axtest I/System.out: <<<<<

• NOTE The error due to the Foreign Key constraint does not result in the App crashing.

• NOTE if adopting the above code, you should do one of the following before running the code :-

  • Delete the App's data.
  • Uninstall the App.
  • Increase the Database Version.

• As it stands the tomasPendientes table serves no useful purpose as it simply has 1 column that has to duplicate a value from the ida1 column of the alarma table.

Answer 2

Did you add table2 after table1 had already been created?

As in, did you previously compile with table1? If so, it could just be that your app isn’t running the onCreate, as it only runs it once. Once the database is created, onCreate no longer runs.

To fix this, you might just need to uninstall the app in your emulator, then run it again.

Hope that helps :)

Edit: I did not see that you already tried this.