我使用BLOB将对象插入SQLite数据库。 插入后，我可以使用“SELECT”语句获取数据并且数据正确，但在使用“SQLite数据库浏览器”浏览数据库时，“TASK_HEAD”行为“Empty”。 但是，如果我销毁刚刚插入的对象，我就不能再得到正确的数据了，指针“pHead”指向一个地址，其“id”成员的内容是“一二七二”铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪UG？” 在VS2008中以调试模式读取时。
这是一个例子：
// 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); } }起初，我认为这可能是传递给sqlite3_bind_blob的字节问题，所以我用一个愚蠢的方法获取对象的字节，正如你在这里看到的（TASK_HEAD的size（）函数），但这没有帮助。 然后我尝试使用SQLITE_STATIC而不是SQLITE_TRANSIENT，仍然无法正常工作。 怎么了？
Ps：我知道将对象插入数据库是一个糟糕的解决方案，我只想知道为什么我无法读回插入数据库的数据。
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.
最满意答案
我认为问题在于：
nRet = sqlite3_bind_blob (res, 1, &head, head.Size(), SQLITE_TRANSIENT);您无法获取TASK_HEAD结构的地址并将其传递给sqlite。 要构建blob，您需要平面数据，没有指针和动态缓冲区，如std :: string对象。
您需要在绑定操作之前在缓冲区中序列化TASK_HEAD结构。 例如：
struct TASK_HEAD { TASK_ID id; std::string userData; std::string Data() { return id+userData; } int Size() { return (id.size() + userData.size()) * sizeof(TCHAR); } };和：
nRet = sqlite3_bind_blob (res, 1, head.Data().c_str(), head.Size(), SQLITE_TRANSIENT);请注意，如上所示添加要序列化的字段非常差（因为此格式无法反序列化）。 要处理blob，你需要找到一个好的序列化库或格式（协议缓冲区，消息包，JSON等...）或自己滚动。
您的代码中存在第二个问题：
const TASK_HEAD *pHead = (const TASK_HEAD*)sqlite3_column_blob(res, 1);出于类似的原因，这不起作用。
I think the problem is at:
nRet = sqlite3_bind_blob (res, 1, &head, head.Size(), SQLITE_TRANSIENT);You cannot get the address of the TASK_HEAD structure and pass it to sqlite like this. To build a blob you need flat data, nothing with pointers and dynamic buffers like std::string objects.
You need to serialize the TASK_HEAD structure in a buffer before the binding operation. For instance:
struct TASK_HEAD { TASK_ID id; std::string userData; std::string Data() { return id+userData; } int Size() { return (id.size() + userData.size()) * sizeof(TCHAR); } };and:
nRet = sqlite3_bind_blob (res, 1, head.Data().c_str(), head.Size(), SQLITE_TRANSIENT);Please note adding the fields to serialize as shown above is very poor (since this format cannot be unserialized). To deal with blobs, you need to find a good serialization library or format (protocol buffer, message pack, JSON, etc ...) or roll your own.
There is a second issue in your code at:
const TASK_HEAD *pHead = (const TASK_HEAD*)sqlite3_column_blob(res, 1);This will not work, for a similar reason.
已成功将blob数据插入SQLite数据库但无法插入数据(Have successfully inserted blob data to SQLite database but can't get the data inserted)我使用BLOB将对象插入SQLite数据库。 插入后，我可以使用“SELECT”语句获取数据并且数据正确，但在使用“SQLite数据库浏览器”浏览数据库时，“TASK_HEAD”行为“Empty”。 但是，如果我销毁刚刚插入的对象，我就不能再得到正确的数据了，指针“pHead”指向一个地址，其“id”成员的内容是“一二七二”铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪铪UG？” 在VS2008中以调试模式读取时。
这是一个例子：
// 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); } }起初，我认为这可能是传递给sqlite3_bind_blob的字节问题，所以我用一个愚蠢的方法获取对象的字节，正如你在这里看到的（TASK_HEAD的size（）函数），但这没有帮助。 然后我尝试使用SQLITE_STATIC而不是SQLITE_TRANSIENT，仍然无法正常工作。 怎么了？
Ps：我知道将对象插入数据库是一个糟糕的解决方案，我只想知道为什么我无法读回插入数据库的数据。
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.
最满意答案
我认为问题在于：
nRet = sqlite3_bind_blob (res, 1, &head, head.Size(), SQLITE_TRANSIENT);您无法获取TASK_HEAD结构的地址并将其传递给sqlite。 要构建blob，您需要平面数据，没有指针和动态缓冲区，如std :: string对象。
您需要在绑定操作之前在缓冲区中序列化TASK_HEAD结构。 例如：
struct TASK_HEAD { TASK_ID id; std::string userData; std::string Data() { return id+userData; } int Size() { return (id.size() + userData.size()) * sizeof(TCHAR); } };和：
nRet = sqlite3_bind_blob (res, 1, head.Data().c_str(), head.Size(), SQLITE_TRANSIENT);请注意，如上所示添加要序列化的字段非常差（因为此格式无法反序列化）。 要处理blob，你需要找到一个好的序列化库或格式（协议缓冲区，消息包，JSON等...）或自己滚动。
您的代码中存在第二个问题：
const TASK_HEAD *pHead = (const TASK_HEAD*)sqlite3_column_blob(res, 1);出于类似的原因，这不起作用。
I think the problem is at:
nRet = sqlite3_bind_blob (res, 1, &head, head.Size(), SQLITE_TRANSIENT);You cannot get the address of the TASK_HEAD structure and pass it to sqlite like this. To build a blob you need flat data, nothing with pointers and dynamic buffers like std::string objects.
You need to serialize the TASK_HEAD structure in a buffer before the binding operation. For instance:
struct TASK_HEAD { TASK_ID id; std::string userData; std::string Data() { return id+userData; } int Size() { return (id.size() + userData.size()) * sizeof(TCHAR); } };and:
nRet = sqlite3_bind_blob (res, 1, head.Data().c_str(), head.Size(), SQLITE_TRANSIENT);Please note adding the fields to serialize as shown above is very poor (since this format cannot be unserialized). To deal with blobs, you need to find a good serialization library or format (protocol buffer, message pack, JSON, etc ...) or roll your own.
There is a second issue in your code at:
const TASK_HEAD *pHead = (const TASK_HEAD*)sqlite3_column_blob(res, 1);This will not work, for a similar reason.
发布评论