SQLite Forum

Confused about blobs
The difference between "text" and "blob" is that "text" is a bag-of-bytes that is encoded using UTF-8 and terminated with a NULL (0) byte -- and that rather than explicitly specifying the length of the storage area, you can use "-1" which means "go look for the NULL (0) terminator and figure it out for yourself, Mr. SQLite" -- but a "blob" is just a bag-of-bytes with no null (0) terminator and you must explicitly pass the size-of-the-bag when binding a blob.

Similarly, sqlite3\_column\_text returns a pointer to a properly encoded NULL (0) terminated UTF-8 text string.  sqlite3\_column\_blob returns a pointer to the "bag-o-bytes".  A bag-o-bytes that is "text" will always have a NULL (0) terminator appended (it is not part of the text).  A bag-o-bytes that is a "blob" will not have a NULL (0) terminator appended.  In both cases sqlite3\_column\_bytes will tell you the size of the bag EXCLUDING the NULL (0) terminator which is appended to "text".

If "text" contains embedded nulls (0) bytes or otherwise contains invalid UTF-8 data hell might freeze over or you might cause your computer to explode since the definition of a "C UTF-8 Encoded Text String" is a sequence of valid UTF-8 encoded bytes followed by a NULL (0) terminator and a NULL (0) byte is not allowed to be embedded because that character "terminates" the string.

A "blob" is just a bag-of-bytes.  SQLite3 imposes no structure on a blob, and makes no assumptions or reliance on the "properness" of the bytes -- it is merely a bag-o-bytes of a specified size.

The sqlite3\_blob* interfaces are conceptually similar to the open/read/write/close calls for files because the bag-o-bytes that comprises a blob may contain more bytes than your computer contains memory.  Also, the blob may be, for example, 1 TB is size but you only need to read the 47 bytes at offset 483754943 in the blob and not what comes before or after that, so there is no point in allocating a whole 1 TB memory block to read the whole thing when you only want a wee bit.  Also, sqlite3\_blob\_write cannot "append" to a blob.  It can only write to an already allocated blob.