CODEWITHSUNDEEP
javaandroidsqlite
Djole Pi
Djole Pi

Reputation: 137

Android SQLite select statement strange behaviour

I'm learning android, so I decided to make a simple SMS app. It goes pretty well, but I encountered a problem which I think I am able to bypass, but still, I want to know why it happens.

I'll try not to bother you with redundant code, but I'll edit the question and post more if need be.

THE STRUCTURE:

I have a class:

public class MyDatabaseHelper extends SQLiteOpenHelper

which handles database operations.

In it, I have the following method:

public Contact getContactFromPhoneNumber(String pn) {
    SQLiteDatabase db = this.getReadableDatabase();
    String selectQuery = "SELECT * FROM "+CONTACT_TABLE_NAME+" WHERE "+CONTACT_PHONE_NUMBER+"="+pn+";";
    Cursor cursor = db.rawQuery(selectQuery, null);
    if (cursor.moveToFirst()) {
        Contact contact = new Contact();
        contact.setId(cursor.getInt(cursor.getColumnIndex(CONTACT_PRIMARY_KEY)));
        contact.setFirstName(cursor.getString(cursor.getColumnIndex(CONTACT_FIRST_NAME)));
        contact.setLastName(cursor.getString(cursor.getColumnIndex(CONTACT_LAST_NAME)));
        contact.setPhoneNumber(cursor.getString(cursor.getColumnIndex(CONTACT_PHONE_NUMBER)));
        db.close();
        return contact;
    }
    db.close();
    return null;
}

which returns a Contact object based on the phone number.

Contact class is a simple class with getter and setter methods for attributes fistName, lastName,phoneNumber,and id.

ANOTHER THING I NEED TO MENTION:

I am not passing some arbitrary String value to the method. The value that I pass is previously inserted in the database like this:

public void insertContact(String fn, String ln, String pn) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(CONTACT_FIRST_NAME, fn);
    cv.put(CONTACT_LAST_NAME, ln);
    cv.put(CONTACT_PHONE_NUMBER, pn);
    db.insert(CONTACT_TABLE_NAME, null, cv);
    db.close();
}

So the parameter that I'm passing to the first method is already in the database.

The type of the field in the Contacts table where the phone numbers are stored is TEXT.

THE TROUBLE:

The trouble is that when the first method is called with some String value as parameter which starts with 0 or contains + character (I need + character for countries' calling codes) to the method above, it returns null.

If the pn parameter doesn't start with 0 or contain + it works well.

EXAMPLE: (results are in comments)

Contact c = getContactFromPhoneNumber("123456")  // c != NULL

Contact c = getContactFromPhoneNumber("1023")  // c != NULL

Contact c = getContactFromPhoneNumber("0123")   // c = NULL

Contact c = getContactFromPhoneNumber("+3816035")   // c = NULL

Contact c = getContactFromPhoneNumber("123+123")   // c = NULL

Any idea why this happens?

Upvotes: 0

Views: 75

Answers (1)

laalto
laalto

Reputation: 152807

Your data is stored as strings but this select query here...

String selectQuery = "SELECT * FROM "+CONTACT_TABLE_NAME+" WHERE "+CONTACT_PHONE_NUMBER+"="+pn+";";

... does not quote pn as a string literal. Since it can be interpreted as a number, there's no syntax error but it doesn't match any of the strings with + or 0 in front.

To quote string literals in SQL, use '' single quotes. However, it's better to use ? placeholders and bind values there, like

String selectQuery = "SELECT * FROM "+CONTACT_TABLE_NAME+" WHERE "+CONTACT_PHONE_NUMBER+"=?;";
Cursor cursor = db.rawQuery(selectQuery, new String[] { pn });

Upvotes: 1

Related Questions