CODEWITHSUNDEEP
sqlitesqlitestudio
framontb
framontb

Reputation: 2077

Update query to append zeroes into blob field with SQLiteStudio

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?

Upvotes: 2

Views: 701

Answers (2)

Googie
Googie

Reputation: 6057

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.

Upvotes: 2

framontb
framontb

Reputation: 2077

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.

Upvotes: 2

Related Questions