spyder3anz spyder3anz -4 years ago 83
Android Question

SQLite Exception: no such column Constants.Column_Minute(code 1)

I am getting an error that says my minute column does not exist even though I clearly have initialized it when creating my database table

this is my database activity where the problem is

@Override
public void onCreate(SQLiteDatabase db) {


String CREATE_ALARM_DATABASE = "CREATE TABLE " + Constants.TABLE_NAME +
" ( " + Constants.KEY_ID + " TEXT PRIMARY KEY, " + Constants.COLUMN_NAME +
" TEXT, " + Constants.COLUMN_DESCRIPTION + " TEXT, " + Constants.COLUMN_HOUR +
" INT, " + Constants.COLUMN_MINUTE + " INT, "+ Constants.COLUMN_YEAR +
" INT, " + Constants.COLUMN_MONTH +
" INT, " + Constants.COLUMN_DAY +
" INT, " +Constants.COLUMN_TYPE+ " TEXT);";

db.execSQL(CREATE_ALARM_DATABASE);

}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
db.execSQL("DROP TABLE IF EXIST " + Constants.TABLE_NAME);

onCreate(db);

}


public void addAlarms(MyAlarm alarm) {

SQLiteDatabase db=this.getWritableDatabase();


ContentValues values= new ContentValues();
values.put(Constants.COLUMN_NAME , alarm.getTitle());
values.put(Constants.COLUMN_DESCRIPTION , alarm.getContent());
values.put(Constants.COLUMN_HOUR , alarm.getHours());
values.put(Constants.COLUMN_MINUTE , alarm.getMinutes());
values.put(Constants.COLUMN_YEAR,alarm.getYear());
values.put(Constants.COLUMN_MONTH,alarm.getMonth());
values.put(Constants.COLUMN_DAY,alarm.getDay());
values.put(Constants.COLUMN_TYPE, alarm.getType());

db.insert(Constants.TABLE_NAME, null, values);
db.close();
}

public ArrayList<MyAlarm> getAlarms(){

String selectQuery= "SELECT * FROM " + Constants.TABLE_NAME;
SQLiteDatabase db = this.getReadableDatabase();


Cursor cursor = db.query(Constants.TABLE_NAME, new String[]{Constants.KEY_ID, Constants.COLUMN_NAME
,Constants.COLUMN_DESCRIPTION, Constants.COLUMN_MINUTE, Constants.COLUMN_HOUR, Constants.COLUMN_YEAR
,Constants.COLUMN_MONTH,Constants.COLUMN_DAY,Constants.COLUMN_TYPE},null,null,null,null,Constants.COLUMN_YEAR
+ " DESC");

if(cursor.moveToFirst()){
do{

MyAlarm alarm = new MyAlarm();
alarm.setTitle(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_NAME)));
alarm.setContent(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_DESCRIPTION)));

alarm.setYear(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_YEAR)));
alarm.setMonth(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_MONTH)));
alarm.setDay(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_DAY)));
alarm.setHours(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_HOUR)));
alarm.setMinutes(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_MINUTE)));
alarm.setId(cursor.getString(cursor.getColumnIndex(Constants.KEY_ID)));
alarm.setType(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_TYPE)));

alarmList.add(alarm);
}while(cursor.moveToNext());
}
cursor.close();

return alarmList;
}


the error always points to my cursor...any help would be much appreciated

EDIT:Logcat added

05-20 00:53:45.977 13871-13871/seniorreminder.androidstudioprojects.com.seniorreminder E/AndroidRuntime: FATAL EXCEPTION: main
Process: seniorreminder.androidstudioprojects.com.seniorreminder, PID: 13871
java.lang.RuntimeException: Unable to start activity ComponentInfo{seniorreminder.androidstudioprojects.com.seniorreminder/seniorreminder.androidstudioprojects.com.seniorreminder.MainActivity}: android.database.sqlite.SQLiteException: near "EXIST": syntax error (code 1): , while compiling: DROP TABLE IF EXIST alarms
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2184)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233)
at android.app.ActivityThread.access$800(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5001)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: near "EXIST": syntax error (code 1): , while compiling: DROP TABLE IF EXIST alarms
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
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.executeSql(SQLiteDatabase.java:1672)
at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1603)
at seniorreminder.androidstudioprojects.com.seniorreminder.Database.DatabaseActivity.onUpgrade(DatabaseActivity.java:45)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:257)
at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)
at seniorreminder.androidstudioprojects.com.seniorreminder.Database.DatabaseActivity.getAlarms(DatabaseActivity.java:74)
at seniorreminder.androidstudioprojects.com.seniorreminder.MainActivity.refreshData(MainActivity.java:131)
at seniorreminder.androidstudioprojects.com.seniorreminder.MainActivity.onCreate(MainActivity.java:73)
at android.app.Activity.performCreate(Activity.java:5231)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2148)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233) 
at android.app.ActivityThread.access$800(ActivityThread.java:135) 
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196) 
at android.os.Handler.dispatchMessage(Handler.java:102) 
at android.os.Looper.loop(Looper.java:136) 
at android.app.ActivityThread.main(ActivityThread.java:5001) 
at java.lang.reflect.Method.invokeNative(Native Method) 
at java.lang.reflect.Method.invoke(Method.java:515) 
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785) 
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601) 
at dalvik.system.NativeStart.main(Native Method) 

Answer Source

The error, according to the logcat, is 'SQLiteException: near "EXIST": syntax error (code 1): , while compiling: DROP TABLE IF EXIST alarms'

The cause is that SQLite does not have a keyword called EXIST. The keyword should be EXISTS. So to fix this issue make the following change:-

Replace db.execSQL("DROP TABLE IF EXIST " + Constants.TABLE_NAME); with

db.execSQL("DROP TABLE IF EXISTS " + Constants.TABLE_NAME);

You may also wish to consider simplifying

Cursor cursor = db.query(Constants.TABLE_NAME, new String[]{Constants.KEY_ID, Constants.COLUMN_NAME ,Constants.COLUMN_DESCRIPTION, Constants.COLUMN_MINUTE, Constants.COLUMN_HOUR, Constants.COLUMN_YEAR ,Constants.COLUMN_MONTH,Constants.COLUMN_DAY,Constants.COLUMN_TYPE},null,null,null,null,Constants.COLUMN_YEAR + " DESC");

It could be

`Cursor cursor =  db.query(Constants.TABLE_NAME,null,null,null,null,null,Constants.COLUMN_YEAR
+ " DESC");`

The null instead of the String Array of column names equates to get all columns.

You could simplify the cursor processing loop by using

while (cusror.moveToNext) {

    // do your stuff here
}
cursor.close();

moveToNext() returns false if the cursor is past the last entry.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download