sqlite3数据库之blob数据类型读写

sqlite3数据库之blob数据类型读写

#include "Wdebug.h" #include "sqlite/Wsqlite.h" #include 
  
    #define TABLE_NAME "TEST_BLOB" typedef struct { TUint16 len; void* pdata; } TBlobData; void PrintList (TBlobData data) { if (data.pdata) { TUint8* pvalue = (TUint8*)data.pdata; printf ("len(%d):", data.len); for (int i = 0; i < data.len; i++) { printf ("%d ", pvalue[i]); } printf ("\n"); } else { printf ("data is NULL!\n"); } } int main (int argc, char* argv[]) { TBlobData data; struct TsqlCtrl* This = CreateSqlCtrl("database.db"); if (This) { TsqlCtrlDelTable (This, TABLE_NAME); if (TsqlCtrlExec(This, "CREATE TABLE "TABLE_NAME" (" "id INTEGER PRIMARY KEY" ",NAME TEXT NOT NULL" ",age INTEGER" ",address TEXT" ",Data1 BLOB" ");" , NULL, NULL) != WSQLITE_TRUE) { Werr ("创建表失败！"); } TsqlCtrlExec (This, "INSERT INTO "TABLE_NAME" (id, NAME, age, address) VALUES (NULL, '张三', 19, '北京')", NULL, NULL); TsqlCtrlExec (This, "INSERT INTO "TABLE_NAME" (id, NAME, age, address) VALUES (NULL, '李四', 7, '上海')" , NULL, NULL); TsqlCtrlExec (This, "INSERT INTO "TABLE_NAME" (id, NAME, age, address) VALUES (NULL, '刘五', 39, '广州')", NULL, NULL); TUint8 value[10]; for (int i = 0; i < sizeof (value); i++) { value[i] = i + 1; } TsqlCtrlWriteBlob (This, "UPDATE "TABLE_NAME" SET Data1=? WHERE NAME='张三'", value, sizeof (value), 1); for (int i = 0; i < sizeof (value); i++) { value[i] = i + 5; } TsqlCtrlWriteBlob (This, "UPDATE "TABLE_NAME" SET Data1=? WHERE NAME='李四'", value, sizeof (value) - 3, 1); for (int i = 0; i < sizeof (value); i++) { value[i] = i + 20; } TsqlCtrlWriteBlob (This, "INSERT INTO "TABLE_NAME" (id, NAME, age, address, Data1) VALUES (NULL, '老六', 24, '深圳', ?)", value, sizeof (value), 1); data = TsqlCtrlReadBlob (This, "SELECT Data1 FROM "TABLE_NAME" WHERE NAME='张三'", 0, value, sizeof (value)); PrintList (data); data = TsqlCtrlReadBlob (This, "SELECT Data1 FROM "TABLE_NAME" WHERE NAME='李四'", 0, value, sizeof (value)); PrintList (data); data = TsqlCtrlReadBlob (This, "SELECT id FROM "TABLE_NAME" WHERE NAME='李四'", 0, value, sizeof (value)); PrintList (data); data = TsqlCtrlReadBlob (This, "SELECT Data1 FROM "TABLE_NAME" WHERE NAME='老六'", 0, value, sizeof (value)); PrintList (data); TsqlCtrlDestroy(This); } } 
  

sql::Ret sql::sqlite::WriteBlob (const char* const sqlCmd, void* pdata, const TUint32 length, const TUint8 column_index) { sql::Ret ret = sql::False; sqlite3_stmt* stmt = NULL; if (!pdata || length == 0) { return sql::False; } std::lock_guard 
  
    guard(this->mtx); if(sqlite3_prepare(this->db, sqlCmd, -1, &stmt, NULL)==SQLITE_OK) { sqlite3_bind_blob(stmt, column_index, pdata, length, NULL); if (sqlite3_step(stmt)==SQLITE_DONE) { ret = sql::True; } sqlite3_finalize(stmt); } return ret; } TBlobData sql::sqlite::ReadBlob (const char* const sqlCmd, const TUint8 column_index, void* pdata, const TUint32 MaxSize) { TBlobData Data; sqlite3_stmt* stmt = NULL; Data.len = 0; Data.pdata = nullptr; if (pdata&&MaxSize==0) { return Data; } std::lock_guard 
   
     guard(this->mtx); if(sqlite3_prepare(this->db, sqlCmd, -1, &stmt, NULL) == SQLITE_OK) { if (sqlite3_step(stmt) == SQLITE_ROW) { const void* val = sqlite3_column_blob(stmt, column_index); Data.len = sqlite3_column_bytes(stmt, column_index); if (pdata) { Data.pdata = pdata; if (Data.len > MaxSize) Data.len = MaxSize; } else { Data.pdata = malloc (Data.len); } if (Data.pdata) { memcpy (Data.pdata, val, Data.len); } else { Data.len = 0; } } sqlite3_finalize(stmt); } return Data; } 
    
  

sql::Ret sql::sqlite::WriteBlob (const char* const sqlCmd, TBlobData data) { sql::Ret ret = sql::False; sqlite3_stmt* stmt = NULL; std::lock_guard 
  
    guard(this->mtx); if(sqlite3_prepare(this->db, sqlCmd, -1, &stmt, NULL)==SQLITE_OK) { // 第二个参数data.GetData(i)->index代表sql语句中?的位置（相对于VALUES中的位置从0开始，如：VALUES(1, ?)，则问号的位置为1） sqlite3_bind_blob(stmt, data.column[0], data.pdata[0], data.len[0], NULL); sqlite3_bind_blob(stmt, data.column[1], data.pdata[1], data.len[1], NULL); if (sqlite3_step(stmt)==SQLITE_DONE) { ret = sql::True; } sqlite3_finalize(stmt); } return ret; } TBlobData sql::sqlite::ReadBlob (const char* const sqlCmd, TBlobData data) { TBlobData errdata; sqlite3_stmt* stmt = NULL; std::lock_guard 
   
     guard(this->mtx); if(sqlite3_prepare(this->db, sqlCmd, -1, &stmt, NULL) == SQLITE_OK) { if (sqlite3_step(stmt) == SQLITE_ROW) { TUint32 slen; const void* val; val = sqlite3_column_blob(stmt, data.column[0]); slen = sqlite3_column_bytes(stmt, data.column[0]); if (data.len[0] > slen) data.len[0] = slen; memcpy (data.pdata[0], val, data.len[0]); val = sqlite3_column_blob(stmt, data.column[1]); slen = sqlite3_column_bytes(stmt, data.column[1]); if (data.len[1] > slen) data.len[1] = slen; memcpy (data.pdata[1], val, data.len[1]); } else { sqlite3_finalize(stmt); return errdata; } sqlite3_finalize(stmt); } else { return errdata; } return data; } 
    
  

void main(int argc, char* argv[]) { TBlobData data; TBlobData data2; struct TsqlCtrl* This = CreateSqlCtrl("database.db"); if (This) { TsqlCtrlDelTable (This, TABLE_NAME); if (TsqlCtrlExec(This, "CREATE TABLE "TABLE_NAME" (" "id INTEGER PRIMARY KEY" ",NAME TEXT NOT NULL" ",age INTEGER" ",address TEXT" ",Data1 BLOB" ",Data2 BLOB" ");" , NULL, NULL) != WSQLITE_TRUE) { Werr ("创建表失败！"); } TsqlCtrlExec (This, "INSERT INTO "TABLE_NAME" (id, NAME, age, address) VALUES (NULL, '张三', 19, '北京')", NULL, NULL); TsqlCtrlExec (This, "INSERT INTO "TABLE_NAME" (id, NAME, age, address) VALUES (NULL, '李四', 7, '上海')" , NULL, NULL); TsqlCtrlExec (This, "INSERT INTO "TABLE_NAME" (id, NAME, age, address) VALUES (NULL, '刘五', 39, '广州')", NULL, NULL); TUint8 value[10]; TUint8 value2[10]; data2.len[0] = sizeof (value); data2.len[1] = sizeof (value2); data2.column[0] = 1; data2.column[1] = 2; data2.pdata[0] = value; data2.pdata[1] = value2; for (int i = 0; i < sizeof (value); i++) { value[i] = i + 1; value2[i] = i + 3; } TsqlCtrlWriteBlob (This, "UPDATE "TABLE_NAME" SET Data1=?,Data2=? WHERE NAME='张三'", data2); for (int i = 0; i < sizeof (value); i++) { value[i] = i + 5; value2[i] = i + 9; } TsqlCtrlWriteBlob (This, "UPDATE "TABLE_NAME" SET Data1=?,Data2=? WHERE NAME='李四'", data2); for (int i = 0; i < sizeof (value); i++) { value[i] = i + 20; value2[i] = i + 23; } TsqlCtrlWriteBlob (This, "INSERT INTO "TABLE_NAME" (id, NAME, age, address, Data1, Data2) VALUES (NULL, '老六', 24, '深圳', ?, ?)", data2); data2.column[0] = 0; data2.column[1] = 1; data = TsqlCtrlReadBlob (This, "SELECT Data1,Data2 FROM "TABLE_NAME" WHERE NAME='张三'", data2); PrintList (data); data = TsqlCtrlReadBlob (This, "SELECT Data1,Data2 FROM "TABLE_NAME" WHERE NAME='李四'", data2); PrintList (data); data = TsqlCtrlReadBlob (This, "SELECT id,Data1 FROM "TABLE_NAME" WHERE NAME='李四'", data2); PrintList (data); data = TsqlCtrlReadBlob (This, "SELECT Data1,Data2 FROM "TABLE_NAME" WHERE NAME='老六'", data2); PrintList (data); TsqlCtrlDestroy(This); } } 

各API的使用说明将在后续陆陆续续发表博客进行帮助说明，源代码附带的注释也很详尽，通过注释也可轻松理解代码及框架思路等