How to update piecewise blob in SQLite?

Question

I have big drops in a SQLite application and you need to update the small segments of these blocks in a piece. Something like the word "update bytes X to Y of blob B with data D", it can be done in other databases using the manipulation functions with BLOB, but I can not find anything like this for SQLite, am I stuck? Or does SQLite have ways to manipulate blobs?

Thanks.

Answer 1

SQLite 3.x supports this through the sqlite3_blob_write function.

int sqlite3_blob_write(sqlite3_blob *, const void *z, int n, int iOffset) 

Please note that this feature is provided as part of the SQLite 3 C / C ++ API. You will need to program against this directly in order to use it.

If you use another shell of a higher level, such as System.Data.SQLite, the last time I looked, you will not have access to this function.

Answer 2

This is not a direct answer to your question, but I have experience using random access for (large) blocks in SQLite, and I advise you not to use it if you can. That's why:

Blobs completely break the SQL query format. If your blob data needs some kind of processing, you will definitely need to filter at some point. Whatever mechanism you have to deal with filtering in SQL is useless in this regard.

Working with binary blocks wrapped in databases that are opposite to binary data in raw files limits your options. You will not be able to randomly read and write data simultaneously with several processes, which is possible with files. You cannot use any tool that deals with this data, and provides only a file I / O interface. You cannot truncate or resize a drop. Files are simply a lot more versatile.

It may seem convenient to have everything contained in a single file, as it simplifies backup and transfer, but the pain of working with blobs is simply not worth it.

So, as your lawyer, I advise you to write your drops as raw files to the file system and just store the link to the file name in your database. If your drops are quite small and guaranteed not to grow, forget my advice.

Answer 3

I totally agree with everything paniq said. Using BLOB greatly limits your options.

If you use System.Data.SQLite, you will not have real BLOB support. This is why I wrote my own class to handle them. The code can be found here: Example Blob Code

Please note that SQLite has several potential problems for those who dare to work with BLOB. One of the main problems is that SQLite loads the entire BLOB field into memory before it even lets you check their length. So expect a lot of memory span if you have large BLOB fields ...