Use Blob field as query parameter in SQLite

Question

I'm working on Android and i'm studing use of SQLite database. I already known how to do operation like create query insert etc.. for the database.

Just for example, suppose to have the following table definition:

CREATE TABLE bean84_b (id INTEGER PRIMARY KEY AUTOINCREMENT, column_bean BLOB);

Then SQL query to execute is:

SELECT id, column_bean FROM bean84_b WHERE column_bean=?

The java code to execute above query is:

byte[] param1=...
String[] args={String.valueOf(param1)};
Cursor cursor = database(). rawQuery("SELECT id, column_bean FROM bean84_b WHERE column_bean=?", args);

Is it possible to use a BLOB column like SELECT parameter?

Surya Prakash Kushawah · Accepted Answer · 2016-12-12T04:05:32.457

1

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.

Another type you can bind blob with another fields http://www.programcreek.com/java-api-examples/index.php?api=android.database.sqlite.SQLiteStatement

In the Android database API, execSQL() is the only function where the parameters are not String[] but Object[]:

  byte[] blob = ...; 
  db.execSQL("DELETE FROM t WHERE my_blob = ?", new Object[]{ blob });

edited Dec 12 '16 at 04:05

answered Dec 12 '16 at 02:35

Surya Prakash Kushawah

3,185
1
22
42

3

Hi, it appears that you copied this answer from http://stackoverflow.com/a/20919258 and https://marc.info/?l=sqlite-users&m=141597860701828&w=4. You're using someone else's work without giving the author credit. This amounts to plagiarism, and is not welcome on Stack Overflow. Remember to **always** add prominent attribution when using other sources. Thanks! – CL. Dec 12 '16 at 08:44

score 0 · Answer 2 · answered Dec 20 '17 at 14:31

Yes, it is possible. You have to simply extend CursorFactory, bind values to its SQLiteQuery that will be available in NewCursor method.

Xamarin code snippet:

internal sealed class SQLiteCursorFactory : Java.Lang.Object, SQLiteDatabase.ICursorFactory
{
    private Dictionary<int, object> _selectionArgs;

    internal SQLiteCursorFactory(Dictionary<int, object> selectionArgs)
    {
        _selectionArgs = selectionArgs;
    }

    ICursor SQLiteDatabase.ICursorFactory.NewCursor(SQLiteDatabase db, ISQLiteCursorDriver masterQuery, string editTable, SQLiteQuery query)
    {
        foreach(var key in _selectionArgs.Keys)
        {
            var val = _selectionArgs[key];
            if(val == null)
            {
                query.BindNull(key);
            }
            else if(val is int)
            {
                query.BindLong(key, (int)val);
            }
            else if (val is long)
            {
                query.BindLong(key, (long)val);
            }
            else if (val is double)
            {
                query.BindDouble(key, (double)val);
            }
            else if (val is string)
            {
                query.BindString(key, (string)val);
            }
            else if (val is byte[])
            {
                query.BindBlob(key, (byte[]) val);
            }
        }
        return new SQLiteCursor(masterQuery, editTable, query);
    }
}

Usage:

dbInstance.RawQueryWithFactory(new SQLiteCursorFactory(selectionArgs), query, null, null);

Use Blob field as query parameter in SQLite

2 Answers2

Linked