sqlite3 c++: retrieving a blob

Question

I have a sqlite3 DB with a .png image stored in it as a BLOB

I am trying to retrieve the raw data via the C++ interface like so

RawImageWrapper* obj = 0;
int rc = sqlite3_exec(db, action.c_str(), imageSelectCallback, obj, &errMsg);

Where action is just my SQL statement (it returns the correct record) and RawImageWrapper looks like this

struct RawImageWrapper
{
    int callback(int argc, char **argv, char **azColName) {
        for (int i = 0; i < argc; i++) {
            if (strcmp(azColName[i], "Image") == 0) {
                buff = std::string(argv[i]);
                return 0;
            }
        }
        return 1;
    }

    std::string buff;
};

Using a string to hold the image data probably isn't the best but I just wanted a quick object to wrap the char[]. However this fails with an access violation when it tries to construct buff.

What is the proper way to retrieve this image blob from my database and load it into some container? I am planning on reconstituting the image from it's raw data at a later point

Two bugs: The `std::string` c'tor taking a `const char*` stops when it encounters the first NUL character. For binary data, this could terminate too early. Or it could keep reading for far too long, reaching into invalid memory. You cannot read binary data like that without knowing its length. — IInspectable, Oct 27 '16 at 19:13
I see, do you know of a better object to use rather than string for this purpose? — Madden, Oct 27 '16 at 19:27
There is nothing inherently wrong with a `std::string` (although a `std::vector` would better convey its purpose). Your real issue is, that you do not have access to the data length. Regardless of the container you use, you need to know the length of the data. — IInspectable, Oct 27 '16 at 19:32
Hmm I'll have to work a way to get that from the API then I imagine, thanks for your help — Madden, Oct 27 '16 at 19:44
You have to replace `sqlite3_exec()` with [a cursor](http://stackoverflow.com/documentation/sqlite/5456/sqlite3-stmt-prepared-statement-c-api/19406/reading-data-from-a-cursor). — CL., Oct 27 '16 at 20:39

score 0 · Answer 1 · answered Feb 13 '22 at 06:23

The issue with sqlite3 blob data is if you don't know the size of the blob then you can't construct a string or any other object from char*. Because blob data can have null values and when you generate string it will stop as soon as it gets a null, ignoring rest of the data.

I know two solutions to address this problem:

Add an extra output to your SELECT statement of the form LENGTH(BLOB_COLUMN), which gives you the size of the blob column in bytes.
convert the blob column to hex value using select hex(BLOB_COLUMN) from ... and now you get a normal hex string, which thereafter you've to convert to get a raw byte array.

sqlite3 c++: retrieving a blob

1 Answers1