Android: how to query SQLiteDatabase with non-string selection args?

Question

Is there a straightforward way to query an SQLiteDatabase with selection args that are not String types?

Particularly: if the arg is a byte[] type?

The closest thing I can find is SQLiteDatabase.compileStatement(), which returns an SQLiteStatement you can call bindBlob etc. on. Unfortunately SQLiteStatement isn't good for querying because it doesn't return a cursor.

SQLiteCursor looks promising but I can't figure out how to use it.

Querying based on a blob sounds horribly inefficient. Are you sure this is the only way to write your query? — ianhanniballake
– ianhanniballake, Commented Jan 3, 2014 at 20:25
Is there a way to even express a query that has a BLOB in its WHERE clause? Forget Android for the moment: how would you do this in ordinary SQL? — CommonsWare
– CommonsWare, Commented Jan 3, 2014 at 20:45
Good question. Could a byte literal somehow be expressed in an sql statement? While I can't verify right now, it seems that the SQLite C api does allow querying via prepared statements, which you can bind blobs to via sqlite3_bind_blob. link — Steveo
– Steveo, Commented Jan 3, 2014 at 21:02
You'll have to implement your own SQLiteCursorDriver if you want to make use of the bindBlob() method. — corsair992
– corsair992, Commented Jan 3, 2014 at 21:09

CL. · Accepted Answer · 2014-01-04 09:35:11Z

10

This is a design bug in the Android database API.

query and rawQuery accept only string parameters. execSQL accepts any Object parameters, but does not return results. SQLiteStatement accepts parameters of any type, but allows only queries that return a single value.

What you can do is to ignore parameters, and format the blobs as blob literals by hand:

cursor = db.rawQuery("SELECT a FROM b WHERE c = x'112233'", null);

answered Jan 4, 2014 at 9:35

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Comments

Afrig Aminuddin · Accepted Answer · 2014-04-18 18:08:45Z

2

also you may try

Cursor cs = db.rawQueryWithFactory(new CursorFactory() {
            @SuppressLint("NewApi")
            @SuppressWarnings("deprecation")
            @Override
            public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery,
                    String editTable, SQLiteQuery query) {

                // bind it here!...
                query.bindBlob(1, mac);



                if (Build.VERSION.SDK_INT < 11) {
                    return new SQLiteCursor(db, masterQuery, editTable, query);
                } else {
                    return new SQLiteCursor(masterQuery, editTable, query);
                }
            }
        }, query, null, DB_TABLE);

answered Apr 18, 2014 at 18:08

Afrig Aminuddin

7821 gold badge9 silver badges22 bronze badges

1 Comment

Ahmed Hammad Over a year ago

Nice solution. But sending null in the parameters at last line will through an exception. You have to send parameters as Array<String>

Collectives™ on Stack Overflow

Android: how to query SQLiteDatabase with non-string selection args?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related