SQLite3 插入BLOB类型的数据（C++实现）

【BLOB是什么】

BLOB (binary large object)即二进制大对象，是一种可以存储二进制文件的容器。在计算机中，BLOB常常是数据库中用来存储二进制文件的字段类型。常见的BLOB文件有图片、声音和自定义对象等。

 

示例代码：

#include "stdafx.h"
#include <Windows.h>
#include <string>
#include <fstream>
#include <iostream>
#include "cppsqlite3u.h"
using namespace std;


/**
 * @brief 使用cppsqlite3u封装的方法
 */
void insert_bold_demo(wstring szFile)
{
	std::fstream fs;
	fs.open(szFile.c_str(), std::ios::in | std::ios::binary);
	if (fs)
	{
		//计算图片文件长度
		fs.seekg(0, std::ios::end);
		int filesize = fs.tellg();
		fs.seekg(0, std::ios::beg);

		//读取内容
		char *filebuff = new char[filesize + 1];
		fs.read(filebuff, filesize);

		//打开数据库
		CppSQLite3DB db;
		db.open(L"demo.db");

		if (!db.tableExists(L"USER"))
		{
			db.execDML(L"CREATE TABLE USER (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, szName VARCHAR(64), Image BLOB);");

			//
			CppSQLite3Statement dbment;
			wchar_t bufsql[255] = {0};
			wsprintf(bufsql, L"INSERT INTO USER VALUES (NULL, ?, ?);");
			dbment = db.compileStatement(bufsql);
			dbment.bind(1, L"Kandy");//写到szName字段
			dbment.bind(2, (unsigned char *)filebuff, filesize);
			int ret = dbment.execDML();
			dbment.finalize();
		}
		else
		{
			//
			CppSQLite3Statement dbment;
			wchar_t bufsql[255] = {0};
			wsprintf(bufsql, L"INSERT INTO USER VALUES (NULL, NULL, ?);");
			dbment = db.compileStatement(bufsql);
			dbment.bind(1, (unsigned char *)filebuff, filesize);
			int ret = dbment.execDML();
			dbment.finalize();
		}
		//
		delete filebuff;
		fs.close();
	}
}

/**
 * @brief sqlite3原始写法
 */
int insert_blob_raw(string szFile)
{
	//二进制方式打开
	ifstream file(szFile.c_str(), ios::in | ios::binary);
	if (!file)
	{
		cerr << "opening file failed.\n";
		return -1;
	}

	//获取文件长度
	file.seekg(0, ifstream::end);
	streampos size = file.tellg();
	file.seekg(0);

	//读取文件内容
	char* buffer = new char[size];
	file.read(buffer, size);

	sqlite3 *db = NULL;
	
	//打开数据库
	int rc = sqlite3_open_v2("demo.db", &db, SQLITE_OPEN_READWRITE, NULL);
	//int rc = sqlite3_open("demo.db", &db);
	if (rc != SQLITE_OK)
	{
		cerr << "db open failed: " << sqlite3_errmsg(db) << endl;
	}
	else 
	{
		sqlite3_stmt *stmt = NULL;
		//插入的sql语句，这里用?占位，代表BLOB数据
		rc = sqlite3_prepare_v2(db,	"INSERT INTO USER(ID, szName, Image) VALUES (NULL, NULL, ?)",
			-1, &stmt, NULL);

		if (rc != SQLITE_OK) 
		{
			cerr << "prepare failed: " << sqlite3_errmsg(db) << endl;
		} 
		else
		{
			// SQLITE_STATIC: 因为语句是在释放缓冲区之前完成的
			// 绑定BLOB数据(参数从1开始，不是0)
			rc = sqlite3_bind_blob(stmt, 1, buffer, size, SQLITE_STATIC);
			if (rc != SQLITE_OK)
			{
				cerr << "bind failed: " << sqlite3_errmsg(db) << endl;
			} 
			else
			{
				rc = sqlite3_step(stmt);
				if (rc != SQLITE_DONE)
					cerr << "execution failed: " << sqlite3_errmsg(db) << endl;
			}
		}
		sqlite3_finalize(stmt);
	}
	sqlite3_close(db);

	delete[] buffer;

	return 0;
}

/**
 * @brief _tmain
 */
int _tmain(int argc, _TCHAR* argv[])
{
	insert_bold_demo(L"1.jpg");

	insert_blob_raw("1.jpg");

	system("pause");

	return 0;
}

运行结果：

 

源代码（包含sqlite库）下载地址：https://download.csdn.net/download/hellokandy/11180068