sqlite库学习(8)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");

             }

         }

         //释放stat 

         sqlite3_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");

}