CODEWITHSUNDEEP
androidsqlite
Ants
Ants

Reputation: 390

How to fix Android SQLITE_ERROR, error code 1

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

Upvotes: 1

Views: 6095

Answers (2)

MikeT
MikeT

Reputation: 57023

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.

Upvotes: 1

Saloni Shah
Saloni Shah

Reputation: 23

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

Upvotes: 1

Related Questions