SQLiteException: no such column on old devices

Question

LogCat throws me the following exception:

android.database.sqlite.SQLiteException: no such column: tblAlarmSetup.ASID (code 1): , while compiling: SELECT tblAlarmSetup.ASID AS A, tblContact.CID AS B, tblContact.Contact AS C, tblSubject.SubID AS D, tblSubject.Subject AS E, tblAlarmSetup.IsActive AS F FROM tblSubject INNER JOIN (tblContact INNER JOIN tblAlarmSetup ON tblContact.CID = tblAlarmSetup.CID) ON tblSubject.SubID = tblAlarmSetup.SubID WHERE tblContact.CID = 1 AND tblSubject.SubID = 1

However I only get this exception when I debug the application on my old samsung S2 phone. on my newer Xperia Z3 everything works just fine.

What could be wrong with this SQL statement that it doesn't work on older devices?

I got the following 3 tables:

tblContact: CID, Contact
tblSubject: SubID, Subject

Both are linked with :

tblAlarmSetup: ASID, CID, SubId, IsActive

Edit: samsung s2 runs android 4.1.2; xperia z3 runs android 6.0.1

Edit 2: here is some of the relevant code

public class DbHandler extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "ReminderDB.db";
private static final int DATABASE_VERSION = 1;  

private static final String TABLE_CONTACT = "tblContact";
private static final String TABLE_CONTACT_CID_ATTR = "CID";
private static final String TABLE_CONTACT_CID = TABLE_CONTACT + "." + TABLE_CONTACT_CID_ATTR;
private static final String TABLE_CONTACT_CONTACT_ATTR = "Contact";
private static final String TABLE_CONTACT_CONTACT = TABLE_CONTACT + "." + TABLE_CONTACT_CONTACT_ATTR;

private static final String TABLE_SUBJECT = "tblSubject";
private static final String TABLE_SUBJECT_SUBID_ATTR = "SubID";
private static final String TABLE_SUBJECT_SUBID = TABLE_SUBJECT + "." + TABLE_SUBJECT_SUBID_ATTR;
private static final String TABLE_SUBJECT_SUBJECT_ATTR = "Subject";
private static final String TABLE_SUBJECT_SUBJECT = TABLE_SUBJECT + "." + TABLE_SUBJECT_SUBJECT_ATTR;

private static final String TABLE_ALARMSETUP = "tblAlarmSetup";
private static final String TABLE_ALARMSETUP_ASID_ATTR = "ASID";
private static final String TABLE_ALARMSETUP_ASID = TABLE_ALARMSETUP + "." + TABLE_ALARMSETUP_ASID_ATTR;
private static final String TABLE_ALARMSETUP_CID_ATTR = "CID";
private static final String TABLE_ALARMSETUP_CID = TABLE_ALARMSETUP + "." + TABLE_ALARMSETUP_CID_ATTR;
private static final String TABLE_ALARMSETUP_SUBID_ATTR = "SubID";
private static final String TABLE_ALARMSETUP_SUBID = TABLE_ALARMSETUP + "." + TABLE_ALARMSETUP_SUBID_ATTR;
private static final String TABLE_ALARMSETUP_ISACTIVE_ATTR = "IsActive";
private static final String TABLE_ALARMSETUP_ISACTIVE = TABLE_ALARMSETUP + "." + TABLE_ALARMSETUP_ISACTIVE_ATTR;`

Create Database:

    @Override
    public void onCreate(SQLiteDatabase db) {
        final String CREATE_CONTACT_TABLE = 
                "CREATE TABLE IF NOT EXISTS " + TABLE_CONTACT + " ("
                + TABLE_CONTACT_CID_ATTR + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + TABLE_CONTACT_CONTACT_ATTR + " STRING NOT NULL UNIQUE)";
        //CID (PK, FK) | Contact (unique)

        final String CREATE_SUBJECT_TABLE = 
                "CREATE TABLE IF NOT EXISTS " + TABLE_SUBJECT + " ("
                + TABLE_SUBJECT_SUBID_ATTR + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + TABLE_SUBJECT_SUBJECT_ATTR + " STRING NOT NULL UNIQUE)";
        //SubID (PK, FK) | Subject (unique)

        final String CREATE_ALARMSETUP_TABLE = 
                "CREATE TABLE IF NOT EXISTS " + TABLE_ALARMSETUP + " ("
                + TABLE_ALARMSETUP_ASID_ATTR + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + TABLE_ALARMSETUP_CID_ATTR + " INTEGER NOT NULL, "
                + TABLE_ALARMSETUP_SUBID_ATTR + " INTEGER NOT NULL, "
                + TABLE_ALARMSETUP_ISACTIVE_ATTR + " INTEGER NOT NULL DEFAULT 0, "
                + "UNIQUE (" + TABLE_ALARMSETUP_CID_ATTR + ", " + TABLE_ALARMSETUP_SUBID_ATTR + "), "
                + "FOREIGN KEY (" + TABLE_ALARMSETUP_CID_ATTR + ") REFERENCES " + TABLE_CONTACT + " (" + TABLE_CONTACT_CID_ATTR + "), "
                + "FOREIGN KEY (" + TABLE_ALARMSETUP_SUBID_ATTR + ") REFERENCES " + TABLE_SUBJECT + " (" + TABLE_SUBJECT_SUBID_ATTR + "))";
        //ASID (PK) | CID (Unique in combination with SubID) | SubID

        db.execSQL(CREATE_CONTACT_TABLE); 
        db.execSQL(CREATE_SUBJECT_TABLE);
        db.execSQL(CREATE_ALARMSETUP_TABLE);
}

the query:

    String query = "SELECT "
                    + TABLE_ALARMSETUP_ASID + " AS A, "
                    + TABLE_CONTACT_CID + " AS B, "
                    + TABLE_CONTACT_CONTACT + " AS C, "
                    + TABLE_SUBJECT_SUBID + " AS D, "
                    + TABLE_SUBJECT_SUBJECT + " AS E, "
                    + TABLE_ALARMSETUP_ISACTIVE + " AS F"
                    + " FROM " + TABLE_SUBJECT
                    + " INNER JOIN (" + TABLE_CONTACT
                        + " INNER JOIN " + TABLE_ALARMSETUP
                        + " ON " + TABLE_CONTACT_CID + " = " + TABLE_ALARMSETUP_CID + ")"
                    + " ON " + TABLE_SUBJECT_SUBID + " = " + TABLE_ALARMSETUP_SUBID
                    + " WHERE " + TABLE_CONTACT_CID + " = " + cid
                    + " AND " + TABLE_SUBJECT_SUBID + " = " + subid;

    Cursor cursor = db.rawQuery(query, null);

Edit 3: please don't close this thread. The suggestion you gave me does not answer my question. OnCreate was triggered and the tables exist. I can do select queries of single tables like SELECT * FROM tblAlarmSetup but it doesn't work on my samsung device if I include joins into my queries.

Your old device has a different database schema that doesn't have those columns. Are `tblSubject` and so on part of your app? Did you forget to [upgrade](http://stackoverflow.com/questions/8133597/android-upgrading-db-version-and-adding-new-table) the database? — zapl, May 20 '16 at 12:57
no such column suggests to me that... drumroll... the column doesn't exist in the schema. I'm guessing you've applied some schema changes (sql scripts) to the new phone but forgot to apply them to the old phone. — lance-java, May 20 '16 at 13:01
i deleted all app data on both devices and reinstalled the app. those tables are part of my app yes. so what my app does it creates those 3 tables with SQLiteOpenHelper OnCreate() and after that i run a method with this sql query. i dont make any schema changes — Elias Suess, May 20 '16 at 13:23
btw other sql queries with those tables work it only seems to have problems when i join multiple tables on my samsung device. when i change the from clause to "FROM tblSubject, tblContact, tblAlarmSetup" it works on my samsung device for some reason but then i get an error on my xperia.... — Elias Suess, May 20 '16 at 13:27
[Someone's mom wants to have a word with you](https://xkcd.com/327/) — Antti Haapala -- Слава Україні, May 21 '16 at 06:20

SQLiteException: no such column on old devices

0 Answers0