1

Edit 2, problem solved- The problem was that I had two different helpers, one for each of the tables in a single database. This meant that when the first helper was instantiated, its onCreate method was called, and the database was created along with a single table.

When the second helper was instantiated, the onCreate method was not called, as the database already existed. This meant that the second table was not created, and therefore an error was thrown when an attempt was made to access the second table.

Edit, I think I have realised my problem- The SQLiteHelper refers to a database not a table. I shouldn't have multiple helpers, just one which manages all of the tables. Having two helpers causes the second onCreate method not to be called, as the database already exists.

I have a helper class for a database. An activity creates an instance of it as follows-

storageHelper = new ClassTimeStorageHelper(getApplicationContext());

Next, a method is called to load an ArrayList of a dataholder class-

classes = storageHelper.getAllClasses();

The method being called is as follows-

public ArrayList<ClassTime> getAllClasses() {
    ArrayList<ClassTime> classes = new ArrayList<>();

    String query = "SELECT * FROM " + TABLE_CLASS_TIMES;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);

    ClassTime c;
    if (cursor.moveToFirst()) {
        do {
            c = new ClassTime();
            c.setId(Integer.parseInt(cursor.getString(0)));
            c.setSubjectID(cursor.getInt(1));
            c.setStart(cursor.getInt(2));
            c.setEnd(cursor.getInt(3));
            c.setDay(cursor.getInt(4));
            classes.add(c);
        } while (cursor.moveToNext());
    }
    Log.d("getAllClasses", classes.toString());
    return classes;
}

The onCreate method of the storage helper is -

    @Override
public void onCreate(SQLiteDatabase db) {
    Log.d("OnCreate", "Method called");
    try {
        String CREATE_TABLE_CLASS_TIMES = "CREATE TABLE IF NOT EXISTS " +
                TABLE_CLASS_TIMES +
                "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_LESSON_ID + " INTEGER, " +
                "FOREIGN KEY(" + KEY_LESSON_ID + ") " + "REFERENCES " + TABLE_SUBJECTS + "(id), " +
                KEY_START_TIME + " INTEGER, " +
                KEY_END_TIME + " INTEGER, " +
                KEY_DAY + " INTEGER )";
        db.execSQL(CREATE_TABLE_CLASS_TIMES);
    } catch (Exception e) {
        Log.d("Exception ", ""+e.getStackTrace());
    }
}

From the lack of a log statement I can see that the onCreate method isn't called, even when I clear the apps data, or uninstall and reinstall. Edit- I also tried creating a global SQLiteDatabase variable, and calling db = this.getWriteableDatabase() in the constructor, then replacing the other calls to this method. The same problem still occurred.

I don't understand what is wrong, as I have a very similar helper for another table-

    @Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_TABLE_SUBJECT = "CREATE TABLE IF NOT EXISTS " +
            TABLE_SUBJECT +
            "(" + KEY_ID + " Integer PRIMARY KEY AUTOINCREMENT, " +
            KEY_SUBJECT_NAME + " VARCHAR, " +
            KEY_CLASSROOM + " VARCHAR, " +
            KEY_TEACHER + " VARCHAR, " +
            KEY_COLOR + " Integer )";
    db.execSQL(CREATE_TABLE_SUBJECT);
}

public ArrayList<Subject> getAllSubjects() {
    ArrayList<Subject> subjects = new ArrayList<>();

    String query = "SELECT * FROM " + TABLE_SUBJECT;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);

    Subject l;
    if (cursor.moveToFirst()) {
        do {
            l = new Subject();
            l.setId(Integer.parseInt(cursor.getString(0)));
            l.setName(cursor.getString(1));
            l.setClassroom(cursor.getString(2));
            l.setTeacher(cursor.getString(3));
            l.setColor(cursor.getInt(4));
            subjects.add(l);
        } while (cursor.moveToNext());
    }
    Log.d("getAllSubjects", subjects.toString());
    return subjects;
}

And it works without any problems.

The log is as follows-

02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/SQLiteLog: (1) no 
such table: ClassTimes
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement D/AndroidRuntime: Shutting down VM
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime: FATAL EXCEPTION: main
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime: Process: com.anapp.tpb.replacement, PID: 1238
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime: java.lang.RuntimeException: Unable to start activity ComponentInfo{com.anapp.tpb.replacement/com.anapp.tpb.replacement.Setup.DataPresentation.ClassTimeCollector}: android.database.sqlite.SQLiteException: no such table: ClassTimes (code 1): , while compiling: SELECT  * FROM ClassTimes
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2416)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.ActivityThread.-wrap11(ActivityThread.java)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.os.Handler.dispatchMessage(Handler.java:102)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.os.Looper.loop(Looper.java:148)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.ActivityThread.main(ActivityThread.java:5417)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at java.lang.reflect.Method.invoke(Native Method)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:  Caused by: android.database.sqlite.SQLiteException: no such table: ClassTimes (code 1): , while compiling: SELECT  * FROM ClassTimes
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at com.anapp.tpb.replacement.Storage.StorageHelpers.ClassTimeStorageHelper.getAllClasses(ClassTimeStorageHelper.java:111)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at com.anapp.tpb.replacement.Setup.DataPresentation.ClassTimeCollector.onCreate(ClassTimeCollector.java:87)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.Activity.performCreate(Activity.java:6237)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1107)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2369)
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476) 
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.ActivityThread.-wrap11(ActivityThread.java) 
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344) 
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.os.Handler.dispatchMessage(Handler.java:102) 
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.os.Looper.loop(Looper.java:148) 
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at android.app.ActivityThread.main(ActivityThread.java:5417) 
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at java.lang.reflect.Method.invoke(Native Method) 
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) 
02-20 19:59:58.370 1238-1238/com.anapp.tpb.replacement E/AndroidRuntime:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616) 
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Theo Pearson-Bray
  • 775
  • 1
  • 13
  • 32
  • Maybe you are trying to create the tables in the wrong order? i.e.: you should create the table which contains the referenced field first and then the other one, so it finds the foreign key? Or are you sure you're not using invalid table or column names? – Phantômaxx Feb 20 '16 at 20:04
  • The table that is referenced is definitely there. The database causing problems, and the one being referenced are both being used when setting up the app for the first time. The referenced database comes first, and is used to set up subjects (School subjects) and that bit definitely works (I can save something close the app, and it's there), the problem comes with this database, when setting up a timetable of lessons (Each lesson has a foreign key referring to the subject). – Theo Pearson-Bray Feb 20 '16 at 20:08
  • Why do you say database instead of table? Are the tables created in different databases? – Phantômaxx Feb 20 '16 at 20:10
  • No, sorry that was my mistake. They are both in the same database. – Theo Pearson-Bray Feb 20 '16 at 20:14
  • this might help. http://stackoverflow.com/questions/3058909/how-does-one-check-if-a-table-exists-in-an-android-sqlite-database and http://stackoverflow.com/questions/32544828/unable-to-make-second-table-in-oncreate-android – Sree Reddy Menon Feb 20 '16 at 20:40

2 Answers2

3

  1. SQLiteOpenHelpers are for versioning database files, not tables. If you have more than one table in your database, put them all in the same helper.

  2. You have syntax problems in your CREATE TABLE. Foreign key definitions should be at the end and not mixed with column definitions.

  3. You have a try-catch that hides the syntax problem. Remove it. If there's a problem, onCreate() must not return normally but throw an exception.

  4. After fixing the above, uninstall your app once more to remove the empty database created with your broken onCreate().

laalto
  • 150,114
  • 66
  • 286
  • 303
  • I just fixed one problem, which was having multiple helpers. The syntax error exception is now being thrown. I have moved the Foreign key definition to the end, I'll uninstall and see what happens. – Theo Pearson-Bray Feb 20 '16 at 20:46
  • 1
    Yes using more than one helper with the same database name would contribute to your cascade of issues. – laalto Feb 20 '16 at 20:48
  • 1
    Well, I think you have solved my problem. The tables are working correctly , I can read and write to and from them, and data is saved when the app is closed. Thanks for the help. – Theo Pearson-Bray Feb 20 '16 at 20:52
2

First open database, i.e SQLiteDatabase db = this.getWritableDatabase(); and then perform CRUD operations.

So, change

String query = "SELECT * FROM " + TABLE_CLASS_TIMES;
SQLiteDatabase db = this.getWritableDatabase();

                 to

SQLiteDatabase db = this.getWritableDatabase();
String query = "SELECT * FROM " + TABLE_CLASS_TIMES;
Ashwini
  • 245
  • 1
  • 7