Query is not returning any data SQLite

Question

I have database with 4 columns int id | String data | String date | int boot and i have some data in it. I have method getRow(String s) when i call it with string for id or data and change query to that option it works but when i´m trying to get row with equal date it won´t pass cursor.moveToFirst condition.

Here is my code:

String CREATE_TABLE = "CREATE TABLE "
            + TABLE_NAME + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_DATA
            + " TEXT," + COLUMN_DATE + " TEXT," + COLUMN_BOOT + " Integer" + ")";


 public String getRowID(String id){
     SQLiteDatabase db = this.getReadableDatabase();
     Cursor c = db.rawQuery("select * from " + TABLE_NAME + " where " + COLUMN_ID + " = " + id, null);
     if (c != null && c.moveToFirst()) {
         //loggin succes
         return "string";
     }else return null;
 }

 public String getRowDate(String date){
     SQLiteDatabase db = this.getReadableDatabase();
     Cursor c = db.rawQuery("select * from " + TABLE_NAME + " where " + COLUMN_DATE + " = " + date, null);
     if (c != null && c.moveToFirst()) {
         //loggin succes
         return "string";
     }else return null;
 }

myDb.getRowID("1"); returning something
myDb.getRowDate("02122016"); returning null

I have two rows in my database.

1 | 0.19 | 01122016 | 0
2 | 0.19 | 02122016 | 0

Community · Accepted Answer · 2017-05-23 11:45:55Z

2

Be wary when comparing integers and strings. You may wonder why SQLite would be comparing integers at all since your arguments are strings, until you consider that your raw query looks like this:

select * from TABLE where DATE = 02122016

That value is interpreted as an integer and converted to text, but it loses the leading zero in the process. You can verify this with a sqlite3 shell:

sqlite> select 02122016;
2122016
sqlite> select '02122016' = 02122016;
0 -- false
sqlite> select cast(02122016 as text);
2122016

The simplest fix is to quote the value using a method from DatabaseUtils:

String escaped = DatabaseUtils.sqlEscapeString(date);
String query = "select * from " + TABLE_NAME + " where " + COLUMN_DATE + " = " + escaped;

A better fix would be to use a placeholder argument instead. Note that Android binds all arguments as strings:

String query = "select * from " + TABLE_NAME + " where " + COLUMN_DATE + " = ?";
db.rawQuery(query, new String[]{date});

However, my advice would be to not use rawQuery() and instead use one of the real query() methods. Here's a good example.

Lastly, perhaps you should consider a different format for storing dates. In practice I usually either store an INTEGER column with a unix timestamp (seconds or milliseconds since epoch), or I use a TEXT column with values in the yyyy-MM-dd format since this is implicitly supported by numerous datetime functions in SQLite.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Dec 2, 2016 at 1:57

Karakuri

38.6k12 gold badges91 silver badges106 bronze badges

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

1 Comment

Blackess Over a year ago

Great answer with a lot of examples and ways to solve my problem. Thanks

Collectives™ on Stack Overflow

Query is not returning any data SQLite

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related