CODEWITHSUNDEEP
sqlsqliteblob
waldyrious
waldyrious

Reputation: 3844

How to test if a blob in an sqlite table is not null, without reading the blob entirely?

I am trying to query a table that has a blob column, and need to filter the results to only provide rows that have content (any content) in the blob column.

However, doing SELECT column_name FROM table_name WHERE blob_column IS NOT NULL takes a long time, which I assume is due to the fact that some of the blobs are quite heavy. It seems that the WHERE clause is reading the entire content of the blobs and comparing them to null.

Is there a way to test whether a blob column is null or not, without having sqlite read the entire contents of the blob?

Upvotes: 4

Views: 6225

Answers (2)

waldyrious
waldyrious

Reputation: 3844

Since version 3.7.12, using the length() or typeof() functions avoids reading the binary data (the query is much faster). So to avoid the unnecessary overhead, one must change

SELECT column_name FROM table_name WHERE blob_column IS NOT NULL

to

SELECT column_name FROM table_name WHERE LENGTH(blob_column) IS NOT NULL

or

SELECT column_name FROM table_name WHERE TYPEOF(blob_column) != 'null'

Upvotes: 7

Anand
Anand

Reputation: 31

Use sqlite3_blob_read() On success returns SQLITE_OK. Otherwise, an error code

Upvotes: 0

Related Questions