CODEWITHSUNDEEP
androidsqlitecursor
Rohan Kandwal
Rohan Kandwal

Reputation: 9336

SQLite Database where clause not working properly

I have a sql database consist of a column Contact_Priority which consist of integers as string entires (i.e. 1,2... but saved as string). Contact_Priority may contain a number more than once. I am using following query to get the number of rows which contain a specific number.

SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.query(CONTACT_TABLE, null, Contact_Priority + " =? ", new String[]{priority} , null, null, null);
        
        int count = cursor.getCount();

but it is returning 0, although there are some rows present in the database.

if i put Cursor cursor1 = sqLiteDatabase.query(CONTACT_TABLE, null, null, null, null, null, null, null);

I am getting correct number of rows. I am so confused why it is not working.

P.S. - Contact_Priority has priority in its row i.e. no typo error. enter image description here

I just want to know how many number of rows has same priority.

Update :-

I don't know if it helps, but the app i am making assign some contact numbers to a given Contact_Priority. What i have done is, when i add a contact to any particular Contact_Priority i.e. 1,2 or so on, on a button click i can add then to all the priority i.e. priority 1 to 8 will have same contacts as you can see in the image above.

This i have implemented successfully, now while showing the contacts for a priority i am getting the problem but strangely it is working one particular priority, through which i set all the similar contacts.

Consider i choose to keep all the contacts of priority "2" then i am getting the correct output for "2" i.e. correct number of rows while for all others i am getting 0. I have checked that the database is updated and rows are update but why i am getting output for only one priority i cannot understand.

Update 2

The value of priority depends on the position of the listview which contains the button on whose onClick the query function is called. eg. Consider, I copied contacts of priority 2 to all others. The database is updated, like in image above. Now when i click on the button of listview child 1 then priority is 1, if i click on 2nd child's button then priority is 2 and so on. For some strange reasons, it is working priority is "2" (Since i copied contacts of "2" to all). for others it is not working.

Now if i had copied contacts of "priority 3" then it would have worked for only when priority is "3" (i.e. i clicked 3rd button on the list) and not working for anyone else.

I think i have made myself clear. Sorry for confusing you all, I myself am confused since i haven't seen any strange problem like this.

Upvotes: 0

Views: 1660

Answers (5)

Boycott A.I.
Boycott A.I.

Reputation: 18901

I had the same problem as the OP, but have discovered what seems to be causing it.

My table using FULLTEXT search - e.g., CREATE VIRTUAL TABLE my_table USING fts3 (...). And when I change it to a normal (non-virtual / non-fts3 / non-fts4) table, the problem goes away.

However, I need to keep it as a virtual/fts3 table, so the workaround I have used is the change my selection String from _id = ? to docid = ?. (The docid column is automatically created by Android/SQLite when you create a virtual/fts3 table.)

Of course, you can instead hardcode your id (e.g., 12345) using _id = 12345 for the selection (with a null selectionArgs).

Upvotes: 0

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

 .query(CONTACT_TABLE, null, Contact_Priority + " =? ", new String[]{priority} , null, null, null);

will return all rows in which Contact_Priority (which I hope is a variable with "Contact_Priority") are exactly equal to priority given.

You don't want exact, you want a match, so:

 .query(CONTACT_TABLE, null, "','||Contact_Priority||',' LIKE '%,'||?||',%'", new String[]{priority} , null, null, null);

will do!

EDIT: forgot wildcard character...

Upvotes: 0

Rohan Kandwal
Rohan Kandwal

Reputation: 9336

I have no idea why this is working but using the following query i get desired result

Cursor cursor = sqLiteDatabase.rawQuery("Select * from Contact where Contact_Priority =" + priority,null);

Since both the queries are same i don't understand why this is working and the previous one not. I am mentioning this answer for all those who face similar problems. If anyone has clear answer what cause this query working and previous query not working please mention in comments.

Upvotes: 1

jagmohan
jagmohan

Reputation: 2052

You can try this as well

String.format(Contact_Property + " = \'%s\'", priority);

and set the selectionArgs argument to null.

Upvotes: 0

ibrahim demir
ibrahim demir

Reputation: 461

Please have a look at here: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html What Contact_Priority variable's intent ? and why you use " =? " this string?

The definition of method which you are using is:

query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

it should be something like this: CONTACT_TABLE, null, Contact_Priority, new String[]{priority} , null, null, null, null

Upvotes: 0

Related Questions