Have successfully inserted blob data to SQLite database but can't get the data inserted

Question

I use BLOB to insert an object to a SQLite databse. After the insertion, I can get the data with "SELECT" sentence and the data is correct, although the row of "TASK_HEAD" is "Empty" when browsing the database with "SQLite Database Browser". However, if I destroy the object which has just been inserted, I can't get the correct data anymore, with the pointer "pHead" points to an address where the content of its "id" member is "铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪UG?" when read in VS2008 in debug mode.

Here is an example:

// user-defined data type
typedef std::string TASK_ID;
struct TASK_HEAD
{
    TASK_ID id; 
    std::string userData;

    int Size()
    {
        return (id.size() + userData.size()) * sizeof(TCHAR);
    }
};

// when TEST_INSIDE is defined, pHead is invalid; but if undef it, I can get the "head" I just inserted
// and if the blob data is a string (when USING_STRING is defined), I can get the string inserted into the db even though the "test" string has been destroyed
void CDBWriter::WriteTestData()
{
    // open db
    sqlite3* db = NULL;
    int nRet = sqlite3_open(DATABASE_NAME.c_str(), &db);
    if (nRet != SQLITE_OK)
    {
        return;
    }

    if (db != NULL)
    {
        // create a table
        std::string cmdCreate("CREATE TABLE IF NOT EXISTS testTable (id TEXT NOT NULL, TASK_HEAD BLOB, PRIMARY KEY(id));");
        char* errMsg = NULL;
        nRet = sqlite3_exec( db , cmdCreate.c_str() , 0 , 0 , &errMsg );
        if( errMsg != NULL )
        {
            sqlite3_free( errMsg );
            errMsg = NULL;
            return;
        }

//#define USING_STRING
#define TEST_INSIDE
#ifndef TEST_INSIDE
        TASK_HEAD head;
#endif // TEST_INSIDE

        // insert blob data
        const TASK_ID newID(NewGUID()); // NewGUID returns string like this: "5811307F-7AA7-4C44-831F-774FC5832627"
        string query = "INSERT OR REPLACE INTO testTable (id, TASK_HEAD) VALUES ('";
        query += newID;
        query += "', ?1);";
        sqlite3_stmt* res = NULL;
        nRet = sqlite3_prepare_v2(db, query.c_str(), query.length(), &res, 0);
        {
#ifdef TEST_INSIDE
            TASK_HEAD head;
#endif // TEST_INSIDE
            head.id = newID;
#ifdef USING_STRING
            std::string test("ewsjoafijdoaijeofsafsd");
            nRet = sqlite3_bind_blob (res, 1, test.c_str(), test.size(), SQLITE_TRANSIENT);
#else
            int nsizeHead = sizeof(head);
            int nSizeHeadSt = sizeof(TASK_HEAD);
            int sizeString = sizeof(std::string);
            size_t nLen = newID.size();
            //nRet = sqlite3_bind_blob (res, 1, &head, sizeof(head), SQLITE_TRANSIENT);
            nRet = sqlite3_bind_blob (res, 1, &head, head.Size(), SQLITE_TRANSIENT);
#endif // USING_STRING

            if (SQLITE_OK == nRet)
            {
                nRet = sqlite3_step(res);
            }
            if (nRet != SQLITE_OK && nRet != SQLITE_DONE)
            {
                return;
            }
        }

        // get all columns in the database
        query = "SELECT * FROM testTable;";
        nRet = sqlite3_prepare_v2 (db, query.c_str(), query.length(), &res, 0);
        if (SQLITE_OK == nRet)
        {
            while (SQLITE_ROW == sqlite3_step(res))
            {
#ifdef USING_STRING
                const char* pHead = (const char*)sqlite3_column_blob(res, 1);
#else
                const TASK_HEAD *pHead = (const TASK_HEAD*)sqlite3_column_blob(res, 1);
#endif // USING_STRING
                continue;
            }
        }
        sqlite3_finalize(res);
        sqlite3_close(db);
    }
}

At first, I thought it might be the problem of bytes passed to sqlite3_bind_blob, so I get the bytes of the object with a stupid method, as you can see here (the size() function of TASK_HEAD), but that doesn't help. Then I tried to use SQLITE_STATIC instead of SQLITE_TRANSIENT, still not working. What's wrong?

Ps: I know it's a bad solution to insert an object to the db, and I just wanna know why I can't read back my data inserted into the db.

Answer 1

The content of userData is likely to be stored on the heap. Even if it's stored inside the std::string (for SSO) it still may use a pointer to itself internally, and so it won't work when you bitwise copy it to another place in memory (what you're doing is equivalent to a memcpy).

However, it doesn't matter why exactly it doesn't work, since it's just undefined behavior. Just don't "insert an object to the db" like this. Either serialize it using some serialization library and then insert it, or use two columns in the table, one for id and one for userData.