SQLite not storing blobs containing \0 bytes

Question

Inserting binary data into an sqlite database using C/C++ interface:

  // Previously: "create table tt(i integer primary key, b blob)"
  sqlite3 *sqliteDB;
  if(sqlite3_open("example.db", &sqliteDB) == 0)
    {

    // "insert" statement
    const char *sqlText = "insert into tt (i,b) values (?,?)";
    sqlite3_stmt *preparedStatement;
    sqlite3_prepare_v2(sqliteDB, sqlText, (int)strlen(sqlText), &preparedStatement, 0);

    // add the number
    sqlite3_bind_int(preparedStatement, 1, 1);

    // add the data
    char myBlobData[] = "He\0llo world"; // sizeof(myBlobData) is 13 bytes
    sqlite3_bind_blob(preparedStatement, 2, myBlobData, sizeof(myBlobData), 0); // returns SQLITE_OK

    // store in database
    sqlite3_step(preparedStatement); // returns SQLITE_DONE

    sqlite3_close(sqliteDB);
    }

This stores a 2-byte blob containing "He", instead of the 13-byte blob that it was asked to store. Why?

It seems to be ending at the first 0 byte, but blobs aren't strings (hence the need for a size parameter to sqlite3_bind_blob) so why would it treat arbitrary binary data as a c-style string?

Works for me^{tm}. Are you sure it's not just the printing routine that's not displaying the full blob? Try `sqlite3 example.db "select length(b) from tt;"` on the command line. — user786653, Dec 11 '12 at 17:39
Don't forget to accept an answer, even if you have to accept your own. — Robᵩ, Dec 11 '12 at 17:46

score 2 · Accepted Answer · answered Dec 11 '12 at 17:39

How are you getting the BLOB out of the DB? It may be getting truncated at that point; perhaps your code is treating it as a string after extraction.

You can check the actual BLOB size via the BLOB APIs. sqlite3_blob_open() followed by a call to sqlite3_blob_bytes().

SQLite not storing blobs containing \0 bytes

1 Answers1

Linked