Android Sqlite selection args[] with int values

Question

Have this line of code in the getEvents method of my DBHelper.

    int year, month, day;
    String[] columns = new String[] { KEY_EVENTNAME, KEY_DESCRIPTION,
            KEY_HOUR, KEY_MINUTE, KEY_DAY, KEY_MONTH, KEY_YEAR,
            KEY_REMINDER };
    Cursor c = database.query(DATABASE_TABLE, columns, 
            KEY_YEAR + "=?"+ " AND " + KEY_MONTH + "=?" + " AND "+ KEY_DAY + "=?",
            new String[] {String.valueOf(year), String.valueOf(month), String.valueOf(day)}, 
            null, null, KEY_MONTH + " AND "
            + KEY_DAY);

It always returns nothing. When I remove the following

    int year, month, day;
    String[] columns = new String[] { KEY_EVENTNAME, KEY_DESCRIPTION,
            KEY_HOUR, KEY_MINUTE, KEY_DAY, KEY_MONTH, KEY_YEAR,
            KEY_REMINDER };
    Cursor c = database.query(DATABASE_TABLE, columns, 
            KEY_YEAR + "=?",
            new String[] {String.valueOf(year)}, 
            null, null, KEY_MONTH + " AND "
            + KEY_DAY);

it runs correctly. What's the problem with my code? It seems like it doesn't accept multiple values as where clause. Can any one help me with this? Thanks.

Do you get an error or an empty result set? did you check the values of month and day? — shem
– shem, Commented Sep 11, 2013 at 16:19
I did. year, month and day values are correct. That's why I don't see why this returns nothing. — TheGPWorx
– TheGPWorx, Commented Sep 11, 2013 at 16:25
So there is no error? cause the query looks really ok. please put a sample results from your second query, maybe there is a problem with the params. — shem
– shem, Commented Sep 11, 2013 at 16:27

CL. · Accepted Answer · 2013-09-11 19:18:19Z

15

The answer is in the title: you are trying to pass integer values into the query, but what you actually end up with are string values.

This is a horrible design bug in the Android database API; you can use parameters only for strings.

Integer numbers do not have the formatting and SQL injection problems that string values would have, so you can just insert the numbers directly into the SQL expression:

Cursor c = database.query(
    DATABASE_TABLE, columns, 
    KEY_YEAR  + "=" + year  + " AND " +
    KEY_MONTH + "=" + month + " AND " +
    KEY_DAY   + "=" + day,
    null, null, null,
    KEY_MONTH + "," + KEY_DAY);

(And the orderBy syntax was wrong.)

answered Sep 11, 2013 at 19:18

CL.

182k18 gold badges241 silver badges282 bronze badges

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

3 Comments

josephus Over a year ago

Never encountered this before, and I have been using the api since 2.2! That being said, I never tried storing Y/M/D values individually for Date..

Jon Over a year ago

Does this happen in all APIs? Did sqlite get smarter and just start handling int = '4'? I just found that a query that worked on a newer device wasn't working on an android kitkat device

CL. Over a year ago

This has nothing to do with SQLite itself; the Android framework uses a string array here. (Except for execSQL(), which cannot return result rows.)

josephus · Accepted Answer · 2013-09-11 16:20:49Z

1

I don't see anything wrong with the syntax or logic. Try checking if the values of year, month, and day are correct.

answered Sep 11, 2013 at 16:20

josephus

8,3041 gold badge40 silver badges58 bronze badges

1 Comment

TheGPWorx Over a year ago

I did. year, month and day values are correct. That's why I don't see why this returns nothing.

TheGPWorx · Accepted Answer · 2013-09-12 01:22:49Z

Thank you for all your help. After considering everything you suggested, I have finally found out the cause why it always returns nothing. The month value that I passed to this method was incorrect. It was a month ahead that's why it returns 0 for the current month. I'm sorry for the trouble. But all your answers are correct. It was a fault that I believe most or some have experienced. As a learning, I'd give extra care in looking into the values that I pass. Carefully looking into the flow of the values that are passed between methods saves time. I spent the whole night looking for the problem and just this morning after waking up I remembered the value that was passed into the method. Thanks again to everyone who gave the answers.

Collectives™ on Stack Overflow

Android Sqlite selection args[] with int values

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related