SQLite query with multiple arguments

Question

I am trying to query an SQLite table for a row which has the same day of the week between a start time and end time as follows:

    String whereClause = String.format("%s = '?' AND %s <= '?' %s > '?'",
            Shift.PROP_DAY_OF_THE_WEEK, Shift.PROP_START_TIME,
            Shift.PROP_END_TIME);
    String[] whereargs = new String[] { String.valueOf(dow), time, time };
    Cursor cursor = db.rawQuery(whereClause, whereargs);

However I am getting the following Logcat error.

03-30 22:46:02.827: E/AndroidRuntime(11507): android.database.sqlite.SQLiteException: near "dayOfTheWeek": syntax error (code 1): , while compiling: dayOfTheWeek = '?' AND startTime <= '?' endTime > '?'

I have added the single quote(') to surround the question mark(?). It is not working without the single quote also. I have added them since sql statement can be sensitive to having the quote or not. If I am on the wrong track, please let me know.

CL. · Accepted Answer · 2013-03-31 20:12:05Z

2

Besides the missing AND, you have put string delimiters around the parameter markers, which prevents them from being recognized as parameters. '?' is just a string containing a question mark; for a parameter, use a plain ?:

String.format("%s = ? AND %s <= ? AND %s > ?", ...);

Furthermore, when using rawQuery, you have to write out the entire SQL query. To use a where clause like you're trying to do, you'd need query:

Cursor cursor = db.query("MyTableName",
                         null,  // or column list
                         whereClause, whereargs,
                         null, null, null);

edited Mar 31, 2013 at 20:12

answered Mar 31, 2013 at 10:51

CL.

182k18 gold badges241 silver badges282 bronze badges

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

2 Comments

Win Myo Htet Over a year ago

03-31 12:24:35.797: E/AndroidRuntime(18720): android.database.sqlite.SQLiteException: near "dayOfTheWeek": syntax error (code 1): , while compiling: dayOfTheWeek = ? AND startTime <= ? AND endTime > ? still not working

Win Myo Htet Over a year ago

of course ... how am I going to query without a tablename?! I still get Cannot bind argument at index 3 because the index is out of range. The statement has 0 parameters. error because I have left over code using "'" from "%s = '?' AND %s <= '?' %s > '?'" to give feedback to Patashu. All fix now. Thanks a lot.

Patashu · Accepted Answer · 2013-03-31 06:07:38Z

0

"%s = '?' AND %s <= '?' %s > '?'"

forgot an AND between the second and third conditions

"%s = '?' AND %s <= '?' AND %s > '?'"

then test to see if it needs '' or not

answered Mar 31, 2013 at 6:07

Patashu

21.8k4 gold badges49 silver badges53 bronze badges

1 Comment

Win Myo Htet Over a year ago

it is still not working 03-31 12:29:38.487: E/AndroidRuntime(19174): android.database.sqlite.SQLiteException: near "dayOfTheWeek": syntax error (code 1): , while compiling: dayOfTheWeek = '?' AND startTime <= '?' AND endTime > '?'

Collectives™ on Stack Overflow

SQLite query with multiple arguments

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related