7

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.

Steveo
  • 2,238
  • 1
  • 21
  • 34
  • Querying based on a blob sounds horribly inefficient. Are you sure this is the only way to write your query? – ianhanniballake Jan 03 '14 at 20:25
  • Yeah. It's not necessarily a large blob. – Steveo Jan 03 '14 at 20:32
  • 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 Jan 03 '14 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](http://www.sqlite.org/c3ref/stmt.html) – Steveo Jan 03 '14 at 21:02
  • You'll have to implement your own `SQLiteCursorDriver` if you want to make use of the `bindBlob()` method. – corsair992 Jan 03 '14 at 21:09
  • Answering previous comments: searching by an indexed `TEXT` column is not horribly inefficient, and `BLOB`s are even simpler — no charset, no unicode decomposition, no case sensitivity, etc — so searching by such a column is OK. And SQL supports [blob literals](https://stackoverflow.com/a/1039553/3050249). – Miha_x64 Dec 23 '18 at 19:38

2 Answers2

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);
CL.
  • 173,858
  • 17
  • 217
  • 259
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);
Afrig Aminuddin
  • 772
  • 1
  • 9
  • 22
  • Nice solution. But sending null in the parameters at last line will through an exception. You have to send parameters as Array – Ahmed Hammad Jan 28 '19 at 08:49