CODEWITHSUNDEEP
androidsqlite
John Smith
John Smith

Reputation: 752

Android: SQLite where clause won't return anything

I am working with SQLite for the past few days and every now and then I get an error I cannot resolve. Sometimes my query works, sometimes it does not, eventhough when I dump the tables, it just should work. Check this dump from Logcat (debugging on device, haven't found a good way to access the db yet...):

This is from a table called ct_data

01-05 20:13:16.619: I/DataDAO(12453): >===>ID  100
01-05 20:13:16.619: I/DataDAO(12453): >===>cablefinderID   4 
01-05 20:13:16.619: I/DataDAO(12453): >===>latitude  51.5077559
01-05 20:13:16.619: I/DataDAO(12453): >===>longitude  7.5959846
01-05 20:13:16.629: I/DataDAO(12453): >===>data  DV200SN041385SV4.01TM19:24DT12/04/09CM33ST0BT9MD0SS28UMMDP---
01-05 20:13:16.629: I/DataDAO(12453): >===>time  1357416761579
01-05 20:13:16.629: I/DataDAO(12453): >===>ID  101
01-05 20:13:16.629: I/DataDAO(12453): >===>cablefinderID   4 
01-05 20:13:16.629: I/DataDAO(12453): >===>latitude  51.5077559
01-05 20:13:16.629: I/DataDAO(12453): >===>longitude  7.5959846
01-05 20:13:16.629: I/DataDAO(12453): >===>data  DV200SN041385SV4.01TM19:24DT12/04/09CM33ST0BT9MD0SS27UMMDP---DV200SN041385SV4.01TM19:24DT12/04/09CM33ST0BT9MD0SS28UMMDP---
01-05 20:13:16.629: I/DataDAO(12453): >===>time  1357416771625

And this is the sql statement I also print out (without quotes):

"SELECT * from ct_data WHERE cablefinderID='4'"

I tried the where clause ='4' with and without quotes (''), however cablefinderID is of type Integer in SQL and long in Java. Here is my Code in Java:

    String query = "SELECT * from  ct_data WHERE cablefinderID='" + registration.getId()+"'"; 
    cursor = database.rawQuery( query, null );
    // check if cursor is empty
    if(!cursor.moveToFirst()) {
        Log.i(TAG, "WTF!!!");
    }

I also already tried to use the 2nd argument of rawQuery, String[] selectionArgs namely.

String query = "SELECT * from  ct_cablefinder_data WHERE cablefinderID=?";
String[] args = {"4"};
cursor = database.rawQuery( query, null );

But still, sometimes I reach "WTF". This occured two days ago and I ended up comparing values in Java later, but that was just a hotfix and not a good thing to go with when there there are hundereds of thousands of rows in the DB lateron. I figured that based on my debugging, the index might have got broken, deleted all App data and then it worked. Now I deleted all App data again and the same stuff happens. I start to doupt on myself here, this makes me crazy...

Please tell me there is just a stupid mistake i have overseen!

Regards, Simon.

---------------------------------EDIT----------------------------------

First off, big thanks for the help! But this is crazy. It just won't work. Again, removed all App Data before to get a fresh and clean DB. Check this:

01-06 05:54:38.969: I/DataDAO(19394): ====================ct_cablefinder_data====================
01-06 05:54:39.039: I/DataDAO(19394): >===>ID  1
01-06 05:54:39.039: I/DataDAO(19394): >===>cablefinderID   1 
01-06 05:54:39.039: I/DataDAO(19394): >===>latitude  51.5077559
01-06 05:54:39.039: I/DataDAO(19394): >===>longitude  7.5959846
01-06 05:54:39.039: I/DataDAO(19394): >===>data  DV200SN041385SV4.01TM05:06DT13/04/09CM33ST0BT9MD0SS00UMMDP---
01-06 05:54:39.039: I/DataDAO(19394): >===>time  1357451653224
01-06 05:54:39.039: I/DataDAO(19394): >===>ID  2
01-06 05:54:39.039: I/DataDAO(19394): >===>cablefinderID   1 
01-06 05:54:39.039: I/DataDAO(19394): >===>latitude  51.5077559
01-06 05:54:39.049: I/DataDAO(19394): >===>longitude  7.5959846
01-06 05:54:39.049: I/DataDAO(19394): >===>data  DV200SN041385SV4.01TM05:06DT13/04/09CM33ST0BT9MD0SS00UMMDP---
DV200SN041385SV4.01TM05:06DT13/04/09CM33ST0BT9MD0SS00UMMDP---
DV200SN041385SV4.01TM05:06DT13/04/09CM33ST0BT9MD0SS00UMMDP---
DV200SN041385SV4.01TM05:06DT13/04/09CM33ST0BT9MD0SS00UMMDP---
01-06 05:54:39.049: I/DataDAO(19394): >===>time  1357451663259
01-06 05:54:39.049: I/DataDAO(19394): ==============================================

01-06 05:54:51.889: I/DataDAO(19394): ====================ct_cableregistration====================
01-06 05:54:51.939: I/DataDAO(19394): >===>ID  1
01-06 05:54:51.939: I/DataDAO(19394): >===>projectID  1
01-06 05:54:51.939: I/DataDAO(19394): >===>startTime  2013-01-06 05:54:12
01-06 05:54:51.939: I/DataDAO(19394): >===>endTime  null
01-06 05:54:51.939: I/DataDAO(19394): >===>deviceType  DC200-041385
01-06 05:54:51.939: I/DataDAO(19394): >===>deviceKey  00:01:95:08:2F:3B
01-06 05:54:51.939: I/DataDAO(19394): >===>registrationServerID  0
01-06 05:54:51.949: I/DataDAO(19394): ==============================================

And the SQL statement:
01-05 20:14:20.859: E/DataDAO(12453): SELECT * from  ct_cablefinder_data WHERE cablefinderID=1

This is my adjusted code, followed the suggestion by Meghal Shah:

public List<DataVO> getDataByRegistration( CableRegistrationVO registration ) {
    synchronized ( lock ) {

        openReadable();
        Cursor cursor = null;
        List<DataVO> result = new ArrayList<DataVO>();

        try {       
            String query = "SELECT * from  ct_cablefinder_data WHERE cablefinderID="
                + registration.getId();

            cursor = database.rawQuery( query, null );
            dumpTableI( "ct_cablefinder_data" );        // see tabledump above
            dumpTableI( "ct_cableregistration" );       // again, dump above
            Log.e( TAG, query );

            while ( cursor.moveToNext() ) { 
                result.add( this.bindSQLite( cursor ) );
            }

        } catch ( Throwable t ) {
            Log.e( TAG, t.toString() );
        } finally {
            if ( cursor != null ) {
                cursor.close();
            }
        }
        close();
        return result;
    }
}

I mean, I am doing step by step debugging and everytime the program reaches the while condition, it just jumps straight down to the finally block and closes the cursor, retrieving no data whatsoever. the result ArrayList remains empty. But it is there: cablefinderID = 1, I can see it, you can see it, can't you?

I now will try to cast everything to Strings and try to compare those in the WHERE clause and if that won't work, well I dont know but heavy alcohol abuse seems considerable.

Upvotes: 1

Views: 2387

Answers (2)

John Smith
John Smith

Reputation: 752

Ok. So somewhere down the line there was a problem with the datatype. I specifically declare cablefinderID as Integer. However, Sqlite may have thought that wasn't just enough. So what I did was to explicitly cast to Integer again before comparing:

String query = "SELECT * from  ct_cablefinder_data WHERE CAST(cablefinderID as integer)=" + registration.getId();

That just did the trick.

However, thanks for helping me out, now I can keep coding and won't fall for alcoholism!

Upvotes: 1

Meghal Shah
Meghal Shah

Reputation: 405

In first Query if cablefinderID is an integer you do not need '' or "" . you should try the following snippet it should work

        String query = "SELECT * from  ct_data WHERE cablefinderID=" + registration.getId();
        cursor = database.rawQuery( query, null );
        while(cursor.moveToNext()){
          //your code here
        }
        cursor.close();

Second Query is using ct_cablefinder_data instead of ct_data, incase that might be the problem. Also it would be help how you are creating your table.

Upvotes: 0

Related Questions