SQLite学习系列：掌握二进制数据的插入与读取技巧

源码下载：
https://download.csdn.net/download/no2101/12428235。

1. 插入

void DB_Insert_table_student(sqlite3 *db){int result = -1;sqlite3_stmt *stat = 0;const char *pzTail = NULL;const char *sqls[] = {"insert into STUDENT(ID, name, picture) values(0, '张三', ?);","insert into STUDENT(ID, name, picture) values(1, '李四', ?);"};const char *names[] = {"pic\1.jpg","pic\2.jpg"};for (int j = 0; j < sizeof(sqls) / sizeof(char*); j++){//准备result = sqlite3_prepare(db, sqls[j], -1, &stat, &pzTail);if (!result && stat){FILE *file = NULL;fopen_s(&file, names[j], "rb+");char* data = NULL;long l_file_size = 0;if (file){fseek(file, 0, SEEK_END);l_file_size = ftell(file);fseek(file, 0, SEEK_SET);data = new char[l_file_size];fread(data, 1, l_file_size, file);//和sql的第一个？绑定（如果有多个问号，那就要分开绑定）result = sqlite3_bind_blob(stat, 1, data, l_file_size, NULL);//将数据输入数据库if (sqlite3_step(stat) != SQLITE_DONE){sqlite3_finalize(stat);sqlite3_close(db);return;}fclose(file);delete[] data;printf("Insert STUDENT Succeed.\n");}}//释放statsqlite3_finalize(stat);}}

2. 读取

void DB_Select_table_student(sqlite3 *db){char *sql = "SELECT * from STUDENT;";sqlite3_stmt * stmt3 = NULL;if (sqlite3_prepare_v2(db, sql, strlen(sql), &stmt3, NULL) != SQLITE_OK){if (stmt3)sqlite3_finalize(stmt3);sqlite3_close(db);return;}int fieldCount = sqlite3_column_count(stmt3);//stmt3返回的是查询结果集char picname[100];do{int r = sqlite3_step(stmt3);if (r == SQLITE_ROW) {for (int i = 0; i < fieldCount; ++i) {int vtype = sqlite3_column_type(stmt3, i);//字段类型获取//const char* dtype = sqlite3_column_decltype(stmt3, i);//INT REAL之类const char* otype = sqlite3_column_origin_name(stmt3, i);//申明printf("%s ", otype);if (strcmp(otype, "name") == 0){memset(picname, 0, 100);}//判断当前记录当前字段的类型，根据返回的类型使用不同的API函数//获取实际的数据值if (vtype == SQLITE_INTEGER) {int v = sqlite3_column_int(stmt3, i);printf("is %d.\n", v);}else if (vtype == SQLITE_FLOAT) {double v = sqlite3_column_double(stmt3, i);printf("is %f.\n", v);}else if (vtype == SQLITE_TEXT) {const char* v = (const char*)sqlite3_column_text(stmt3, i);strcpy(picname, v);printf("is %s.\n", v);}else if (vtype == SQLITE_BLOB){const void* piblob = sqlite3_column_blob(stmt3, i);int size = sqlite3_column_bytes(stmt3, i);char total_name[256];memset(total_name, 0, 256);sprintf(total_name, "out_pic\%s.jpg", picname);FILE *f = fopen(total_name, "wb+");fwrite(piblob, 1, size, f);fclose(f);}else if (vtype == SQLITE_NULL) {printf("is NULL.\n");}}}else if (r == SQLITE_DONE) {printf("finish successfully\n");break;}else {printf("Failed to SELECT.\n");sqlite3_finalize(stmt3);sqlite3_close(db);return;}printf("\n");} while (true);printf("sqlite3_prepare_v2 student select done successfully\n");}