CODEWITHSUNDEEP
androidsqlite
APOSTOLOS
APOSTOLOS

Reputation: 45

Having trouble with not adding already saved records in Android SQLite

I am working on an app that reads the contact names from the mobile and examines whether the name is already saved in a database. If it is already saved to the db, then I just update a saved counter variable but if it isn't then I go on adding it. AT LEAST THIS IS MY INTENTION.

Part of the code is as follows :

 public Integer countRecords(String name) {
       SQLiteDatabase db = events.getReadableDatabase();
       Cursor mCount= db.rawQuery("select count('"+CONTACT_NAME+"') from'"+ TABLE_NAME + "' where '" + CONTACT_NAME + "' = '" + name + "'", null); 
       mCount.moveToFirst();
       Integer count= mCount.getInt(0); 
       startManagingCursor(mCount);
       return count;
   }

and at the main body of the code goes as follows:

 ContentResolver cr= getContentResolver();
        Cursor cu= cr.query(URI, null, null, null, null);
            if(cu.getCount()>0){    
                while(cu.moveToNext()){
                    contactName=cu.getString(cu.getColumnIndex(DNAME));
                    Integer rawValue = countRecords(contactName);
                    if(rawValue==0){
                        addRecord(contactName);
                        addedCounter+=1;
                        recordName=cu.getString(cu.getColumnIndex(DNAME));
                        recordInfo = addedCounter + " " + recordName + "\n";    
                        recordsList+= recordInfo;
                        }
                    else{
                        savedCounter+=1;
                    }
                }

Now, I have tried everything I know. The problem seems to be the returning value of the procedure countRecords. Maybe I don't use the right criteria in the IF clause if(rawValue==0){, because it either adds all contacts, even they are already saved in the db or it doesn't.

Upvotes: 1

Views: 152

Answers (3)

Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

A bit simple form of your desired query:

Cursor mCount= db.rawQuery("select count(1) from "+ TABLE_NAME + 
" where " + CONTACT_NAME + " = '" + name + "'", null);

Upvotes: 0

Alex Lockwood
Alex Lockwood

Reputation: 83303

Your current implementation is not only wrong... it's also incredibly inefficient. Try something like this instead:

// use as the 2nd argument; otherwise, the cursor will return all information
// associated with the contacts. this is inefficient because you only care
// about the column DNAME in the while loop.
final String[] PROJECTION_CONTACTS = new String[] { DNAME };
final String[] PROJECTION_DATABASE = new String[] { CONTACT_NAME };

// you only need to retrieve this once (dont do it inside the loop)
SQLiteDatabase db = events.getReadableDatabase();

Cursor c = getContentResolver().query(URI, PROJECTION_CONTACTS, null, null, null);

if (c.moveToFirst()) { 
    // then the cursor is not empty

    // compute the columnIndex for "DNAME" only once
    final int col = c.getColumnIndex(DNAME);

    while(c.moveToNext()) {
        // iterate each 
        contactName = c.getString(col);
        Cursor exist = db.query(TABLE_NAME, 
                                PROJECTION_DATABASE, 
                                CONTACT_NAME + " = ?",
                                new String[] { contactName }, 
                                null);

        if (exist.moveToFirst()) {
            // the cursor is not empty, so it exists in the database
        } else {
            // the cursor is empty, so it doesn't exist in the database
        }
        exist.close();
    }
}
c.close();

Something like this should work. (I guarantee you I made a typo somewhere, but the general idea of it should get you started). Please please please make sure you do this asynchronously on a separate thread... it could potentially be a very time consuming operation.

Upvotes: 1

azgolfer
azgolfer

Reputation: 15137

Use this query: 

Cursor mCount= db.rawQuery("select count("+CONTACT_NAME+") from "+ TABLE_NAME + " where " + CONTACT_NAME + " = '" + name + "'", null);

You have an additional single quotes between column names.

Upvotes: 0

Related Questions