CODEWITHSUNDEEP
javaandroidsqliteandroid-sqlite
Asaf
Asaf

Reputation: 2035

Android sqlite - "No such table" while table exist

I wrote a SQL query to return items to show to the users.
However, there seems to be a weird problem with it.

Here's the error:

11-17 13:44:08.644: E/AndroidRuntime(3229): Caused by: 
android.database.sqlite.SQLiteException: no such table: events (code 1): , while compiling: 

SELECT eventsmeta.*, events.* FROM eventsmeta JOIN 
(SELECT events.*, classes.* FROM events JOIN classes ON events.idfortype=classes._id)
 ON eventsmeta.eventid=events._id WHERE (eventsmeta.repeat_start = ?) OR 
(((? - eventsmeta.repeat_start) % (eventsmeta.repeat_day * 86400000)) = 0) OR 
(((? - eventsmeta.repeat_start) % (eventsmeta.repeat_week * 7 * 86400000)) = 0) OR 
((((12 - eventsmeta.repeat_start_month + ?) % (eventsmeta.repeat_month)) = 0) AND (eventsmeta.repeat_start_year <= ?)) 
ORDER BY events.startat ASC;

The thing is the table events does exist (I verified it with a database viewer).

If so, I guess there is some syntax problem... but I can't find it.

Other questions about this problem suggested the code refers to a different database, or a not up-to-date one. I do not think that is the problem since I tried to delete and recreate the database from scratch. I've also used the same code pattern before without any problems...

If so, what else could cause this problem?

CREATE TABLE Code

public static final String SQL_CREATE_EVENTS = 
    "CREATE TABLE IF NOT EXISTS " + Events.TABLE_NAME + " (" +
    Events._ID + " INTEGER PRIMARY KEY," +
    Events.COLUMN_NAME_EVENT_PRIORITY + " INTEGER," + //
    Events.COLUMN_NAME_EVENT_TITLE + " Text," +
    Events.COLUMN_NAME_START_AT + " INTEGER," +
    Events.COLUMN_NAME_END_AT + " INTEGER," +
    Events.COLUMN_NAME_EVENT_NOTES + " Text," +
    Events.COLUMN_NAME_EVENT_TYPE + " Text," +
    Events.COLUMN_NAME_ID_FOR_TYPE + " INTEGER);";

Extra Code

SQLiteDatabase db = SchooLauncherDbHelper.getInstance(getActivity()).getReadableDatabase();

String sql = SQL QUERY HERE;

Cursor c = db.rawQuery(sql, new String[] {.............});

Edit

Apparently, SELECT * FROM events works perfectly...

Edit 2

SQLiteOpenHelper: 

public class SchooLauncherDbHelper extends SQLiteOpenHelper {

    static SchooLauncherDbHelper mInstance = null;
    static final int DATABASE_VERSION = 1;
    static final String DATABASE_NAME = "SchooLauncher.db";
    String query;
    String[] iconFileNames = {};

    public static SchooLauncherDbHelper getInstance(Context context) {
        if (mInstance == null) {
            mInstance = new SchooLauncherDbHelper(
                    context.getApplicationContext());
        }

        return mInstance;
    }

    private SchooLauncherDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SchooLauncherContract.SQL_CREATE_SEMESTERS);
        db.execSQL(SchooLauncherContract.SQL_CREATE_STUDENTS);
        db.execSQL(SchooLauncherContract.SQL_CREATE_SUBJECTS);
        db.execSQL(SchooLauncherContract.SQL_CREATE_STUDENTSTUBJECTJOIN);
        db.execSQL(SchooLauncherContract.SQL_CREATE_TEACHERS);
        db.execSQL(SchooLauncherContract.SQL_CREATE_TEACHERSUBJECTJOIN);
        db.execSQL(SchooLauncherContract.SQL_CREATE_CLASSES);
        db.execSQL(SchooLauncherContract.SQL_CREATE_TEACHERCLASSJOIN);
        db.execSQL(SchooLauncherContract.SQL_CREATE_ASSIGNMENTS);
        db.execSQL(SchooLauncherContract.SQL_CREATE_EXAMS);
        db.execSQL(SchooLauncherContract.SQL_CREATE_EVENTS);
        db.execSQL(SchooLauncherContract.SQL_CREATE_EVENTS_META);
        db.execSQL(SchooLauncherContract.SQL_CREATE_SETTINGS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i("onUpgrade()", "Upgrading from " + oldVersion + " to "
                + newVersion);

    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }
}

Upvotes: 2

Views: 2642

Answers (3)

Vijay
Vijay

Reputation: 1388

Solution is to uninstall your application then run again.

Reason - you may have changed the @Override onCreate() method of SQLite class.

we cannot change the tables of SQLite by changing the onCreate code. That's what happened to me.

**TIP - ** We have to delete the already created SQL database to make changes directly or you can use sql code to that.

Upvotes: 0

Asaf
Asaf

Reputation: 2035

Found the problem!

All I had to do is make an alias for the derived table, and use it instead of events: 

SELECT eventsmeta.*, E1.* FROM eventsmeta JOIN 
(SELECT events.*, classes.* FROM events JOIN classes ON events.idfortype=classes._id) AS E1
 ON eventsmeta.eventid=E1._id WHERE (eventsmeta.repeat_start = ?) OR 
(((? - eventsmeta.repeat_start) % (eventsmeta.repeat_day * 86400000)) = 0) OR 
(((? - eventsmeta.repeat_start) % (eventsmeta.repeat_week * 7 * 86400000)) = 0) OR 
((((12 - eventsmeta.repeat_start_month + ?) % (eventsmeta.repeat_month)) = 0) AND (eventsmeta.repeat_start_year <= ?)) 
ORDER BY E1.startat ASC;

I think that the problem was I couldn't possibly select the table events neither from the eventsmeta table and the new derived table because it didn't exist THERE. So I had to create a temporary new "table" - E1, and use it instead.

Upvotes: 1

Muhammad Ali Hassan
Muhammad Ali Hassan

Reputation: 192

this problem occur some times, the solution is to uninstall your application from the device and then install again.

Upvotes: 0

Related Questions