SQLite query with byte[] WHERE clause

Question

From an Android SQLite database point of view - I have a table which has a field with BLOB type and then I want to query this table contents based on this BLOB field.

I can insert my BLOB field using ContentValues and retrieve it using:

cursor.getBlob(0)// index

I just can't figure out how to query this table contents based on this BLOB field and have found nothing about this problem.

paulsm4 · Accepted Answer · 2011-12-19 07:03:48Z

2

You can't query the (textual? binary? other?) contents of a blob.

If you look, you'll see the contents is in hex:

EXAMPLE: X'53514C697465'.

Suggestions:

Create a new text column, e.g. "blob_index". You can search on the "index" column, then fetch the blob.

Alternatively, just store the data as "text".

answered Dec 19, 2011 at 7:03

paulsm4

123k23 gold badges175 silver badges245 bronze badges

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

Comments

Dobbo · Accepted Answer · 2017-08-13 20:54:34Z

I found that you can query on a blob. One needs to use the hex() function on the query.

For example I'm using UUIDs in my database rows as a unique key that I can generate locally and still be sure of uniqueness on the server.

CREATE TABLE example (_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                      uuid BLOB NON NULL UNIQUE,
                      ...)

When inserting data the following works:

final ContentValues values = new ContentValues(4);
values.put(Contract.Line.COL_UUID,
           UuidFactory.toBlob(uuid));

Given a query URI in the form:

content://package.example.com/example/uuid/11112222-3333-0444-0555-666677778888

the query becomes:

final SQLiteDatabase db = mHelper.getReadableDatabase();
return db.query(table, projection,
                "hex(uuid) = ?",
                new String[] { UuidFactory.toHex(uri.getLastPathSegment()) },
                null, null, null, null);

In UuidFactory (which also contains the code to generate new UUIDs) the follow static functions are defined thus:

@NonNull
public static String toHex(@NonNull final UUID uuid) {
    return String.format("%016X%016X",
                        uuid.getMostSignificantBits(),
                        uuid.getLeastSignificantBits());
}

@NonNull
public static String toHex(@NonNull final String uuid) {
    return toHex(UUID.fromString(uuid));
}

@NonNull
public static byte[] toBlob(@NonNull final UUID uuid) {
    final ByteBuffer buf = ByteBuffer.allocate(16);
    buf.putLong(uuid.getMostSignificantBits());
    buf.putLong(uuid.getLeastSignificantBits());
    return buf.array();
}

And for completeness:

@NonNull
public static UUID fromBlob(@NonNull final byte[] array) {
    final ByteBuffer buf = ByteBuffer.allocate(16);
    buf.mark();
    buf.put(array);
    buf.reset();
    final long msb = buf.getLong();
    final long lsb = buf.getLong();
    return new UUID(msb, lsb);
}

Collectives™ on Stack Overflow

SQLite query with byte[] WHERE clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related