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

柔情只为你懂 2022-01-31 03:41 1593阅读 0赞

【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;
    }

运行结果：

![20190515155029308.png][]

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

[20190515155029308.png]: /images/20220131/ad822bf717424368ab05fc4a34bc9ea8.png
[https_download.csdn.net_download_hellokandy_11180068]: https://download.csdn.net/download/hellokandy/11180068