Selecting NULL values in Android SQLite

Question

I'm executing the following method with no success beacause of the selectArgs being incorrect (at least this is what I believe.

findAll:

public Collection<Object> findAllByCodigoSetorOrderByStatusWhereDataAgendamentoIsNull(Integer vendedor) {
    Collection<Object> objects = null;
    String selection = Object.FIELDS[20] + "=?" + " OR " + Object.FIELDS[20] + "=?" + " OR " + Object.FIELDS[20] + "=?" + " AND " + Object.FIELDS[6] + "=?";
    String[] selectionArgs = new String[] { "''", "'null'", "NULL", String.valueOf(vendedor) };
    Collection<ContentValues> results = findAllObjects(Object.TABLE_NAME, selection, selectionArgs, Object.FIELDS, null, null, Object.FIELDS[4]);
    objects = new ArrayList<Object>();
    for (ContentValues result : results) {
        objects.add(new Object(result));
    }
    return objects;
}

findAllObjects:

protected Collection<ContentValues> findAllObjects(String table, String selection, String[] selectionArgs, String[] columns, String groupBy, String having, String orderBy) {
        Cursor cursor = null;
        ContentValues contentValue = null;
        Collection<ContentValues> contentValues = null;
        try {
            db = openRead(this.helper);
            if (db != null) {
                cursor = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
                contentValues = new ArrayList<ContentValues>();
                for (int i = 0; i < cursor.getCount(); i++) {
                    cursor.moveToPosition(i);
                    contentValue = new ContentValues();
                    for (int c = 0; c < cursor.getColumnCount(); c++) {
                        contentValue.put(cursor.getColumnName(c), cursor.getString(c));
                    }
                    contentValues.add(contentValue);
                    cursor.moveToNext();
                }
            }
            return contentValues;
        } finally {
            close(db);
        }
    }

How can I correctly select and compare a column to - null, 'null' and '' using the db.query?

Edited to make que question clearer... thanks for the question Cornholio. — BBacon
– BBacon, Commented Apr 11, 2013 at 15:53
The easiest way to just check a row for a null value is this - if (cursor.isNull(cursor.getColumnIndex("my_column")) doStuff();. Is there any reason you can't use that? — Cornholio
– Cornholio, Commented Apr 11, 2013 at 15:55
1 - The findAllObjects is generic... I would have to create a whole new method to manipulate the cursor. 2 - The reading speed would get extremely downsized. — BBacon
– BBacon, Commented Apr 11, 2013 at 15:57
I hear your concerns - what are you checking the value for? Do you want to exclude those rows with null values there, or do you want to do something else with them? — Cornholio
– Cornholio, Commented Apr 11, 2013 at 16:03

CL. · Accepted Answer · 2013-04-11 17:02:47Z

16

Android's database API does not allow to pass NULL values as parameters; it allows only strings. (This is a horrible design bug. Even worse, SQLiteStatement does allow all types for parameters, but works only for queries that return a single value.)

You have no choice but to change the query string to blah IS NULL.

answered Apr 11, 2013 at 17:02

CL.

182k18 gold badges241 silver badges282 bronze badges

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

4 Comments

BBacon Over a year ago

This should (I hope already is) marked as a bug/request on Android lists.

Wolfram Rittmeyer Over a year ago

The is null necessity is traditional SQL syntax. = null doesn't return results in any SQL database management system. And it also doesn't make sense to use an argument for it. First of all because the syntax is different and secondly because you always use the same string anyway. In your case I would use argument substitution only for the variable vendedor. For those other - fixed - values it's simply not needed and thus a waste of CPU cycles and battery life (even if only slightly). They are useful only if the arguments can contain variable values as for your last argument.

Wolfram Rittmeyer Over a year ago

@CL. Indeed, SQLiteStatement is a horrible class. Borders on uselessness.

Chaitanya Over a year ago

This answer and discussion helped me today. Thanks!

Joe Maher · Accepted Answer · 2016-12-20 04:47:33Z

1

Old question but i was still stuck on this for a few hours until i found this answer. For whatever reason this strange behaviour (or bug) still exists within the android sdk, if you want to query against null values simply do

SQLiteDatabase db = getReadableDatabase();
ContentValues contentValues = new ContentValues();

contentValues.put("columnName", newValue);

String nullSelection = "columnName" + " IS NULL";

db.update("tableName", contentValues, nullSelection, null);
db.close();

In this example i am updating values, but it is a similar concept when just selecting values

answered Dec 20, 2016 at 4:47

Joe Maher

5,4805 gold badges30 silver badges44 bronze badges

Comments

chef · Accepted Answer · 2017-05-11 15:44:25Z

As mentioned in other answers, for null "IS NULL" need to be used. Here is some convenience code for having both null and strings (I'm using delete in the example but the same can be done for other methods, e.g. query):

public void deleteSomething(String param1, String param2, String param3) {
    ArrayList<String> queryParams = new ArrayList<>();

    mDb.delete(TABLE_NAME,
            COLUMN_A + getNullSafeComparison(param1, queryParams) + "AND " +
                    COLUMN_B + getNullSafeComparison(param2, queryParams) + "AND " +
                    COLUMN_C + getNullSafeComparison(param3, queryParams),
            queryParams.toArray(new String[0]));
}

private String getNullSafeComparison(String param, List<String> queryParams) {
    if (param == null) {
        return " IS NULL ";
    } else {
        queryParams.add(param);
        return " = ? ";
    }
}

Borzh · Accepted Answer · 2018-12-10 20:55:45Z

0

You can bind NULL values to SQLiteStatement:

    SQLiteDatabase db = getWritableDatabase();
    SQLiteStatement stmt = db.compileStatement("UPDATE table SET " +
            "parameter=? WHERE id=?");
    if (param == null)
        stmt.bindNull(1);
    else
        stmt.bindString(1, param);
    stmt.execute();
    stmt.close();
    db.close();

answered Dec 10, 2018 at 20:55

Borzh

5,2552 gold badges53 silver badges68 bronze badges

1 Comment

Jefferson Quesado Over a year ago

SQLiteStatement can only be called if the result set is 1 column x 1 line (see doc)

Collectives™ on Stack Overflow

Selecting NULL values in Android SQLite

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related