lundi 28 mars 2016

SQLite Exception from DELETE *

I have this SQL statement that I want to execute:

db.execSQL("DELETE * FROM " + TABLE_NAME + " WHERE " + DATE_TO_REMIND + "='" + date + "'");

Where TABLE_NAME and DATE_TO_REMIND are column names and date is a string passed into a method.

If I were running it today, it should translate to:

"DELETE * FROM Vocabulary WHERE dateToRemind ='2016/03/28'"

This is throwing errors in the logcat that point to the *, but I can't understand what is faulty about this statement. Here is the error:

java.lang.RuntimeException: Unable to start activity ComponentInfo{nuffsaidm8.me.activeenglish/nuffsaidm8.me.activeenglish.CheckVocabActivity}: android.database.sqlite.SQLiteException: near "*": syntax error (code 1): , while compiling: DELETE * FROM Vocabulary WHERE Date_To_Remind='2016/03/28' at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2416) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476) at android.app.ActivityThread.-wrap11(ActivityThread.java) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344) at android.os.Handler.dispatchMessage(Handler.java:102) at android.os.Looper.loop(Looper.java:148) at android.app.ActivityThread.main(ActivityThread.java:5417) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616) Caused by: android.database.sqlite.SQLiteException: near "*": syntax error (code 1): , while compiling: DELETE * FROM Vocabulary WHERE Date_To_Remind='2016/03/28' at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1674) at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1605) at nuffsaidm8.me.activeenglish.DBHelper.getWordsToReview(DBHelper.java:77) at nuffsaidm8.me.activeenglish.CheckVocabActivity.onCreate(CheckVocabActivity.java:55) at android.app.Activity.performCreate(Activity.java:6251) at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1107) at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2369) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476)  at android.app.ActivityThread.-wrap11(ActivityThread.java)  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344)  at android.os.Handler.dispatchMessage(Handler.java:102)  at android.os.Looper.loop(Looper.java:148)  at android.app.ActivityThread.main(ActivityThread.java:5417)  at java.lang.reflect.Method.invoke(Native Method)  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616) 

The strange thing about this statement is that I used a very similar statement (I just copied the initial statement and changed select to delete) to select all rows that fit these qualifications:

Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + DATE_TO_REMIND + "='" + date + "'", null);

Just in case, I will post the rest of the class (Though I doubt it will be of much use):

public class DBHelper extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "vocab.db";
public static final String VOCAB_INPUT = "Input_Word";
public static final String VOCAB_TRANSLATION = "Translation";
public static final String DATE_TO_REMIND = "Date_To_Remind";
public static final String TABLE_NAME = "Vocabulary";
public static final String CURRENT_DAY_WAIT = "Current_Day_Wait";

public DBHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String query = "CREATE TABLE " + TABLE_NAME + "("
            + VOCAB_INPUT + " TEXT, "
            + VOCAB_TRANSLATION + " TEXT, "
            + DATE_TO_REMIND + " TEXT, "
            + CURRENT_DAY_WAIT + " TEXT"
            + ");";
    db.execSQL(query);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}

public void addWord(Word word) {
    ContentValues values = new ContentValues();
    values.put(VOCAB_INPUT, word.getWord());
    values.put(VOCAB_TRANSLATION, word.getTranslation());
    values.put(DATE_TO_REMIND, word.getDate());
    values.put(CURRENT_DAY_WAIT, word.getDayWait());
    SQLiteDatabase db = getWritableDatabase();
    db.insert(TABLE_NAME, null, values);
    db.close();
}

public void deleteWord(Word word) {
    SQLiteDatabase db = getWritableDatabase();
    db.execSQL("DELETE FROM " + TABLE_NAME + " WHERE " + VOCAB_INPUT + "=\"" + word.getWord() + "\"");
}

public List<Word> getWordsToReview(String date) {
    List<Word> returnWords = new ArrayList<>();
    SQLiteDatabase db = getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + DATE_TO_REMIND + "='" + date + "'", null);
    while (cursor.moveToNext()) {
        String retrievedName = cursor.getString(cursor.getColumnIndex(VOCAB_INPUT));
        String retrievedTranslation = cursor.getString(cursor.getColumnIndex(VOCAB_TRANSLATION));
        String retrievedDate = cursor.getString(cursor.getColumnIndex(DATE_TO_REMIND));
        String currentDayWait = cursor.getString(cursor.getColumnIndex(CURRENT_DAY_WAIT));
        Word tempWord = new Word(retrievedName, retrievedTranslation, retrievedDate, currentDayWait);
        returnWords.add(tempWord);
    }
    cursor.close();
    db.execSQL("DELETE * FROM " + TABLE_NAME + " WHERE " + DATE_TO_REMIND + "='" + date + "'");
    return returnWords;
}

public boolean doesWordExist(Word word) {
    SQLiteDatabase db = getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + VOCAB_INPUT + "='" + word.getWord() + "'", null);
    boolean exists = false;
    while (cursor.moveToNext()) {
        String retrievedName = cursor.getString(cursor.getColumnIndex(VOCAB_INPUT));
        if (retrievedName.equalsIgnoreCase(word.getWord())) {
            exists = true;
            break;
        }
    }
    cursor.close();
    return exists;
}
}

Aucun commentaire:

Enregistrer un commentaire