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
– user786653, Commented Dec 11, 2012 at 17:39
Don't forget to accept an answer, even if you have to accept your own. — Robᵩ
– Robᵩ, Commented Dec 11, 2012 at 17:46

Graham Perks · Accepted Answer · 2012-12-11 17:39:26Z

2

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().

answered Dec 11, 2012 at 17:39

Graham Perks

23.5k8 gold badges65 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite not storing blobs containing \0 bytes

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related