2

I'm trying to append zeroes to a blob field in a sqlite3 db. What I tried is this:

UPDATE Logs
SET msg_buffer =  msg_buffer || zeroblob(1)
WHERE msg_id = 'F0'

As you can see, the field name is "msg_buffer", and what I want is to append byte zero. It seems that the concat operator || doesn't work. How could I achieve this?

framontb
  • 1,817
  • 1
  • 15
  • 33

2 Answers2

2

SQLite3 does support datatypes. See https://www.sqlite.org/datatype3.html

They are not strictly linked with declared type of a column, but rather individual per each cell value. The type is determined by how it was created/modified. For example if you insert 5, it will be INTEGER. If you insert 5.5, it will be REAL. If you insert 'test' it will be TEXT, if you insert zeroblob(1), it will be BLOB and if you insert null, it will be NULL.

Now, what you are doing is that you're trying to concatenate current value with a BLOB type. If current value is TEXT (or basically if you use || operator, as you do, you are converting any type into a TEXT), it will be concatenated with byte \x00, which actually determines the end of a string. In other words, you are adding yet another string terminator, to an already existing one, that the TEXT type has.

There will be no change on output of this operation. TEXT always ends with byte zero and it is always excluded from the result, as it's a meta character, not the value itself.

Additional information from http://sqlite.1065341.n5.nabble.com/Append-data-to-a-BLOB-field-td46003.html - appending binary data to BLOB field is not possible. You can modify prealocated blob:

Append is not possible. But if you preallocate space using zeroblob() or similar, you can write to it using the incremental blob API:

http://www.sqlite.org/c3ref/blob_open.html

Finally, please see accepted answer, as author of the question found an interesting solution.

Googie
  • 5,742
  • 2
  • 19
  • 31
  • msg_buffer field is declared as BLOB Data type, and data is inserted with a Python script as sqlite3.Binary(bytearray(...)) – framontb Mar 25 '19 at 08:32
  • 1
    Yes, but the concatenation operator works on strings. See https://sqlite.org/lang_expr.html - it says `The || operator is "concatenate" - it joins together the two strings of its operands.` and then also `(...) the || concatenation operator which always evaluates to either NULL or a text value`. SQLite takes your two binary values, converts them to TEXT, then concatenates and returns TEXT. You can also have a look at this explanation: http://sqlite.1065341.n5.nabble.com/Append-data-to-a-BLOB-field-td46003.html – Googie Mar 26 '19 at 09:31
2

Reading the doc link posted by Googie (3.2. Affinity Of Expressions), I managed to find the way:

UPDATE Logs
SET msg_buffer =  CAST(msg_buffer || zeroblob(1) AS blob)
WHERE msg_id = 'F0'

The CAST operator can take the expression with the concatenate operator and force the blob affinity.

framontb
  • 1,817
  • 1
  • 15
  • 33