How to search for a specific BLOB in SQLite?

Question

I write binary data from pictures into my SQLite database into a BLOB field called "icondata".

CREATE TABLE pictures(id INTEGER PRIMARY KEY AUTOINCREMENT, icondata BLOB)

The code snippet to write the binary data is:

SQLcommand.CommandText = "INSERT INTO pictures (id, icondata) VALUES (" & MyInteger & "," & "@img)"
SQLcommand.Prepare()
SQLcommand.Parameters.Add("@img", DbType.Binary, PicData.Length)
SQLcommand.Parameters("@img").Value = PicData

This works fine and I can find the BLOB values in the database using a tool like SQlite Spy.

But how can I search for a specific "icondata" BLOB?

If I try it with:

SQLcommand.CommandText = "SELECT id FROM pictures WHERE icondata=@img"

and with the same Parameters like above:

SQLcommand.Prepare()
SQLcommand.Parameters.Add("@img", DbType.Binary, PicData.Length)
SQLcommand.Parameters("@img").Value = PicData

Dim SQLreader As SQLite.SQLiteDataReader = SQLcommand.ExecuteReader()
While SQLreader.Read()
    ... Process found database entries....
End While

I don't get any result.

If I change the SELECT query to 'LIKE' instead of '=' I get all entries with BLOBs, not only the matching one.

How do I have to write the SELECT query to find the 1 exactly matching entry for a specific BLOB?

Partan

Do you really want to search for the data? I would think you want to search for an image given its id?! – Broider

What's your table definition? – Saucedo

It's crazy to be retrieving data based on the actual binary data of an image. If you're not going to retrieve by ID or tags or the like then at least store a hash of the binary data and then retrieval would be a case of hashing the binary data you have and then retrieving the record with the same hash. Retrieving by the binary data itself is just madness. – Harem

@corion @ jmcihinney I'm aware that a search for binary data is not the best way. Do you know a solution anyway? – Partan

@shawn The table definition command is written on the 2nd line in my post. Do you need some more informations? – Partan

Answer 1

You can search on BLOBs here's some examples :-

DROP TABLE IF EXISTS pictures;
CREATE TABLE IF NOT EXISTS pictures (id INTEGER PRIMARY KEY, icondata columntypedoesnotmatter);
INSERT INTO pictures (icondata) VALUES
    (x'fff1f2f3f4f5f6f7f8f9f0fff1f2f3f4f5f6f7f8f9f0'),
    (x'ffffffffffffffffffff'),
    (x'010203040506070809'),
    (x'010203040506070809010203040506070809')
    ;
SELECT id, hex(icondata) FROM pictures WHERE icondata = x'010203040506070809' OR icondata = x'FFFFFFFFFFFFFFFFFFFF';
SELECT id, hex(icondata) FROM pictures WHERE icondata LIKE '%'||x'F0'||'%';
SELECT id, hex(icondata) FROM pictures WHERE hex(icondata) LIKE '%F0%';

This results in :-

First query as expected finds the 2 rows (2nd and 3rd)

i.e. SELECT id, hex(icondata) FROM pictures WHERE icondata = x'010203040506070809' OR icondata = x'FFFFFFFFFFFFFFFFFFFF'; results in :-

The Second query does not work as expected and is an example of how not to use LIKE :- i.e. SELECT id, hex(icondata) FROM pictures WHERE icondata LIKE '%'||x'F0'||'%'; results in the unexpected two rows :-

• I believe that this behaviour is due to only checking the higher/lower order bits but I can't find the relevant documentation.

The third query, however converts the stored blob into it's hexadecimal string representation using the hex function and compares that against the string representation of F0

i.e. SELECT id, hex(icondata) FROM pictures WHERE hex(icondata) LIKE '%F0%'; results in the expected single row :-

I've never used vb.net so I'm not sure how you'd code the above as passed parameters.

Slovenia