How to fix Android SQLITE_ERROR, error code 1

Question

trying to fetch a table that will contain dates with associated notes. I'm sending a request to fetch a table and data with date = whatever I decide.

I get the following error:

E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.ar.calendar13, PID: 22214
android.database.sqlite.SQLiteException: near "Feb": syntax error (code 1): , while compiling: SELECT CAL_EVENT_ID, CAL_EVENT_TTTLE, CAL_EVENT_NOTES, CAL_REMINDER_DATE, CAL_EVENT_REPEAT FROM CAL_EVENT_TABLE WHERE CAL_REMINDER_DATE = Tue Feb 12 00:00:00 EST 2019
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
    (near "Feb": syntax error (code 1): , while compiling: SELECT CAL_EVENT_ID, CAL_EVENT_TTTLE, CAL_EVENT_NOTES, CAL_REMINDER_DATE, CAL_EVENT_REPEAT FROM CAL_EVENT_TABLE WHERE CAL_REMINDER_DATE = Tue Feb 12 00:00:00 EST 2019)
#################################################################
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1096)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:661)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1746)
    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1593)
    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1464)
    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1632)
    at com.example.ar.calendar13.Cal_DBManager.fetchDayRecords(Cal_DBManager.java:85)

DB-MANAGER:

 public Cursor fetchDayRecords(String strCalDate) {
    String[] columns = new String[]{
            Cal_DBHelper.CAL_EVENT_ID, Cal_DBHelper.CAL_EVENT_TTTLE,
            Cal_DBHelper.CAL_EVENT_NOTES, Cal_DBHelper.CAL_REMINDER_DATE,
            Cal_DBHelper.CAL_EVENT_REPEAT
    };

    Cursor cursor = database.query(Cal_DBHelper.TABLE_NAME1, columns,
            Cal_DBHelper.CAL_REMINDER_DATE + " = " + strCalDate,
            null, null, null, null);

    if (cursor != null) {
        cursor.moveToFirst();
    }//if

    return cursor;
}//displayRecordTable1

DBHELPER:

 private static final String CREATE_TABLE1 = "create table " + TABLE_NAME1 + "(" + CAL_EVENT_ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + CAL_EVENT_TTTLE + " TEXT NOT NULL, " +
        CAL_EVENT_NOTES + " TEXT NOT NULL, " + CAL_REMINDER_DATE + " TEXT NOT NULL, " +
        CAL_EVENT_REPEAT + " INTEGER);";

MY CODE THAT CALLS "FETCHDAYRECORDS":

final Cursor cursor = dbManager.fetchDayRecords(dateClicked.toString());
    dbNumItems = cursor.getCount();

dateCLicked is a string value that looks like this (for example): Tue Feb 12 00:00:00 EST 2019

Why am I getting an error??? it says my error is near "Feb" ???

Is it because the table is empty? I doubt it, i'm checking if there are any records...

Any help would be helpful!

Thank you in advance!

Tony

MikeT · Accepted Answer · 2019-02-13 13:49:26Z

1

Your issue is that the date in the WHERE clause ( i.e.Tue Feb 12 00:00:00 EST 2019) is not enclosed in quotation marks.

If you use the 4th parameter of the query method, i.e. specify the selection arguments (string[]) and include the place-holder (?) in the WHERE clause (the 3rd parameter), then in addition to the literals being correctly enclosed in quotations marks, you are also protecting against SQL injection.

e.g. you could use

public Cursor fetchDayRecords(String strCalDate) {
    String[] columns = new String[]{
            Cal_DBHelper.CAL_EVENT_ID, Cal_DBHelper.CAL_EVENT_TTTLE,
            Cal_DBHelper.CAL_EVENT_NOTES, Cal_DBHelper.CAL_REMINDER_DATE,
            Cal_DBHelper.CAL_EVENT_REPEAT
    };

    Cursor cursor = database.query(Cal_DBHelper.TABLE_NAME1, columns,
            Cal_DBHelper.CAL_REMINDER_DATE + " =?", new String[]{strCalDate},
            null, null, null);
    cursor.moveToFirst();
    return cursor;

}//displayRecordTable1

Note a Cursor returned from the query method will never be null so if (cursor !- null) has no effect and has been removed.

answered Feb 13, 2019 at 13:49

MikeT

57.9k16 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ants Over a year ago

I had a feeling that, that was the issue! But I was like nahhh it's already a string! Thanks @MikeT for your help!

Saloni Shah · Accepted Answer · 2019-02-13 05:12:02Z

1

just put quotation marks

Cursor cursor = database.query(Cal_DBHelper.TABLE_NAME1, columns,
            Cal_DBHelper.CAL_REMINDER_DATE + " = "+" " " + strCalDate +" " ",
            null, null, null, null);

SELECT
  CAL_EVENT_ID,
  CAL_EVENT_TTTLE,
  CAL_EVENT_NOTES,
  CAL_REMINDER_DATE,
  CAL_EVENT_REPEAT
FROM
  CAL_EVENT_TABLE
WHERE
  CAL_REMINDER_DATE = "Tue  Feb 12 00 :00 :00 EST 2019"

your query should be like this

answered Feb 13, 2019 at 5:12

Saloni Shah

237 bronze badges

1 Comment

Philippe Banwarth Over a year ago

although it works (with SQLite), it is better to quote string literals with ', instead of ",

Collectives™ on Stack Overflow

How to fix Android SQLITE_ERROR, error code 1

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related