Android parameterized sql query not working

Question

If I hardcode a number into the where section I get the rows I'm expecting back

Cursor cursor = db.query( tableName, 
new String[] {colID, colString, colNumber },  
colNumber + "=14", 
null, 
null, null, null, null);

If I instead put the number in as a parameter, I don't get any rows back.

Cursor cursor = db.query( tableName, 
new String[] {colID, colString, colNumber },  
colNumber + "=?", 
new String[] { String.valueOf(14) }, 
null, null, null, null);

What am I doing wrong in the second case?

Create table statements:

db.execSQL("CREATE TABLE "
        + otherTableName + " ("
        + otherTableID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + otherTableString + " TEXT"
        + ");");

db.execSQL("CREATE TABLE "
        + tableName + " ("
        + colID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + colString + " TEXT, "
        + colNumber + " REFERENCES "
        + otherTableName + "(" + otherTableID
            + "));");

Odd. Normally SQLite doesn't care about data types, but how did you define colNumber? — Sam
– Sam, Commented Apr 2, 2013 at 15:59

animuson · Accepted Answer · 2014-01-29 22:36:53Z

1

I think that problem is your second DML statement, more accurate your declaration of foreign key. I think that it should be:

db.execSQL("CREATE TABLE "
        + tableName + " ("
        + colID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + colString + " TEXT, "
        + colNumber + " INTEGER, "
        + " FOREIGN KEY (" + colNumber + ") REFERENCES "
        + otherTableName + "(" + otherTableID
            + "));");

I cannot explain myself other reason why it's not working. Your statements are equal except one uses hardcored value and second placeholder. Probably you tried parametrized statement on tableName table where i think you declared FK incorrectly and this was reason why you got any rows.

Placeholders work based on SQLite's type affinity

If you defined column as TEXT, placeholder is replaced and value is compared as col = 'value'. If you defined column as INTEGER, placeholder is replaced and value is compared as col = value. But in your second DML statement, you didn't specified column's datatype, only that it references to some column somewhere.

edited Jan 29, 2014 at 22:36

animuson♦

54.9k28 gold badges142 silver badges150 bronze badges

answered Apr 2, 2013 at 16:29

Simon Dorociak

33.5k10 gold badges70 silver badges107 bronze badges

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

2 Comments

CL. Over a year ago

Actually, parameters are always replaced as col = 'value' (i.e., as strings; this is a silly restriction in Android's DB interface), but SQLite's type affinity will then compare it correctly for numeric-typed columns.

Simon Dorociak Over a year ago

@CL. thanks for correction. Good to know. i was wrong with that.

Collectives™ on Stack Overflow

Android parameterized sql query not working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related