sqlite3 批量插入图片 c语言

本博客参考了以下博客
https://www.cnblogs.com/HPAHPA/articles/7662268.html
https://blog.csdn.net/hik_zxw/article/details/82501606

测试向 sqlite3 数据库中批量插入图片的 c 语言实现方法

/*insert_batch.c -- test insert a large number of data; joden 20190809 */

#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <stdlib.h>
#include <fcntl.h>
#include <sys/stat.h>
#include <unistd.h>

#ifndef NULL
#define NULL ((void *)0)
#endif


int handle_insert_batch(sqlite3 *db);
/*
static int callback(void *data, int column_cnt, char **column_value, char **column_name)
{
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   for(i = 0; i < column_cnt; i++)
   {
      printf("%s = %s\n", column_name[i], column_value[i]);
   }
   printf("\n");
   return 0;
}
*/


int main(int argc, char *argv[])
{
    sqlite3 *db;
    char *pcErrMsg = NULL;
    int  iRet;
    char *pcCreateSql;
    //int iKey;

    /* Open database */
    iRet = sqlite3_open("test.db", &db);
    if(iRet != SQLITE_OK)
    {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
    }
    else
    {
      fprintf(stdout, "Opened database successfully\n");
    }

    /* Create SQL statement */
    pcCreateSql = "CREATE TABLE IF NOT EXISTS test_tb(" \
          "ID INT PRIMARY KEY     NOT NULL," \
          "GENDER         TEXT    NOT NULL," \
          "NAME           TEXT," \
          "AGE            INT," \
          "DATA           BLOB    NOT NULL);";

    /* Execute SQL statement */
    iRet = sqlite3_exec(db, pcCreateSql, NULL, NULL, &pcErrMsg);
    if( iRet != SQLITE_OK )
    {
        fprintf(stderr, "SQL error: %s\n", pcErrMsg);
        sqlite3_free(pcErrMsg);
    }
    else
    {
        fprintf(stdout, "Table created successfully\n");
    }
    
    handle_insert_batch(db);  

    sqlite3_close(db);
    return 0;

}


int handle_insert_batch(sqlite3 *db)
{   
    int iRet = -1;    // return value
    char arrcPicPath[50] = "./Michael.jpg";    // buf to keep the pathname of a pic file
    char *pcPicInfo;
    int i = 1; // count in for();
    char *pcErrMsg = NULL;

     //打开图片文件，读取文件大小信息
	int iFd = open(arrcPicPath, O_RDONLY);
	if(iFd < 0)
	{
		perror("open error:");
		return 2;
	}
    printf("open pic success.\n");
	//读取文件信息
	struct stat st;
	fstat(iFd, &st);
	int iImgSize = st.st_size;
	pcPicInfo = malloc(iImgSize);
 
	if(!pcPicInfo)
	{
		perror("malloc error:");
		return 2;
	}
	
	read(iFd, pcPicInfo, iImgSize);    //将fd中的二进制内容读取到addr中，大小为imgSize
	close(iFd);//关闭文件指针
    printf("read pic success.\n");
 
        
	//数据库操作
 	//进行插入数据操
	// 这里的 “？” 是占位符，说明这一位有数据。
	char *pcInsertSql = "INSERT INTO test_tb(ID, GENDER, NAME, AGE, DATA) VALUES (?,?,?,?,?);";
	sqlite3_stmt *stmt;
	
	iRet = sqlite3_prepare_v2(db, pcInsertSql, strlen(pcInsertSql), &stmt, NULL);
	if(iRet != SQLITE_OK)
	{
		fprintf(stderr,"prepare db error:%s\n",sqlite3_errmsg(db));
		sqlite3_close(db);
	}
    printf("sqlite3_prepare_v2 success.\n");
    
    /* use transaction to improve the efficiency; if transaction is not used every time the func
	   sqlite3_step() is executed, the IO operation will be done once. while, if the transaction is used
	   IO operation will be done only when the transaction is committed.
	*/ 
    iRet = sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, &pcErrMsg);
    if( iRet != SQLITE_OK )
    {
        fprintf(stderr, "SQL error: %s\n", pcErrMsg);
        sqlite3_free(pcErrMsg);
    }
    else
    {
        fprintf(stdout, "Begin transaction successfully\n");
    }
    
    for(i =1; i <= 50000; i++)
    {
        sqlite3_bind_int(stmt, 1, i);   //bind ID 
        sqlite3_bind_text(stmt, 2, "male", strlen("male"), NULL);   //bind gender
        sqlite3_bind_text(stmt, 3, "Tom", strlen("Tom"), NULL);   //bind name
        sqlite3_bind_int(stmt, 4, 29);   //bind age
        sqlite3_bind_blob(stmt, 5, pcPicInfo, iImgSize, NULL);    // bind data 

        //执行数据库操作
        iRet = sqlite3_step(stmt);
        sqlite3_reset(stmt);	// reset the stmt so that it can be binded again
        //printf("sqlite3_step iRet = %d\n", iRet);
        //printf("sqlite3_step success.\n"); 
    }
    
    // end the transaction
    iRet = sqlite3_exec(db, "COMMIT;", NULL, NULL, &pcErrMsg);    
    if( iRet != SQLITE_OK )
    {
        fprintf(stderr, "SQL error: %s\n", pcErrMsg);
        sqlite3_free(pcErrMsg);
    }
    else
    {
        fprintf(stdout, "Commit transaction successfully\n");
    }
	//销毁语句
	sqlite3_finalize(stmt);	
	//sqlite3_close(db);
	free(pcPicInfo);
    
    //sleep(2);
    return 0;
}