【翠字营原创】：SQLite基础应用基于sqlite3_stmt

int main(int argc, char* argv[])
{

 

 

   sqlite3 *db;

   sqlite3_stmt *stat;

   char *zErrMsg = 0;

   char buffer2[1024]="0";

 

   sqlite3_open("./db/MetaInfo.db", &db);

   int result;

   if(result)

   {

      std::cout<<"Open the database sqlite.db failed"<<std::endl;

   }

 

   else

   {

      std::cout<<"Open the database sqlite.db sucessfully"<<std::endl;

      return 0;

 

   }

 

  sqlite3_exec(db, "CREATE TABLE list (fliename varchar(128) UNIQUE, fzip blob, ntest int, ntest1 int);", 0, 0, &zErrMsg);

   if (zErrMsg)

   {

      std::cout<<"execute sql failed. "<<zErrMsg<<std::endl;

      sqlite3_free(zErrMsg);

   }

  

 

   //sqlite3_prepare 第一个参数是sqlite3 * 指针

   //第二个参数是sql 字符串，可以包含多个sql语句

   //第三个参数是sql 字符串长度，-1 sqlite 自己计算

   //第四个参数是sqlite3_stmt ** 类型返回的是sql 解析的语句

   //第个参数是const char *pzTail 用于指向zSql中下个sql语句开始的地址上,所以pzTail并没有重新开辟内存空间

   const char *pzTail =NULL;

   const char *zSql = "insert into list values ('5.bmp',?,?,1); insert into list values ('4.bmp',?,?,1);";

   int nRet = sqlite3_prepare(db, zSql, -1, &stat, &pzTail);

   std::cout<<"nRet of sqlite3_prepare: "<<nRet<<std::endl;

 

   FILE *fp;

   long filesize = 0;

   char * ffile;

   fp = fopen("./2.bmp", "rb");

   if(fp != NULL)

   {

      fseek(fp, 0, SEEK_END);

      filesize = ftell(fp);

      fseek(fp, 0, SEEK_SET);

      ffile = new char[filesize+1];

      size_t sz = fread(ffile, sizeof(char), filesize+1, fp);

 

      fclose(fp);

   }

 

 

   //sqlite3_bind_xxx 函数第一个参数是sqlite3_prepare 后的sqlite3_stmt 指针变量

   //第二个参数是列的索引（从索引0开始）

   sqlite3_bind_blob(stat, 1, ffile, filesize, NULL);

   delete(ffile);

 

   sqlite3_bind_int(stat, 2, 100);

   sqlite3_step(stat);

 

   //每次调用sqlite3_prepare 函数sqlite 会重新开辟sqlite3_stmt空间，

   //所以在使用同一个sqlite3_stmt 指针再次调用sqlite3_prepare 前

   //需要调用sqlite3_finalize先释放空间

   sqlite3_finalize(stat);

 

   //--------------------------------------

 

   sqlite3_prepare(db, "select * from list;", -1, &stat, 0);

  

   //在调用sqlite3_column_count获取查询结果列信息的时候不需要先执行sqlite3_step就可以获取

   int nColNum = sqlite3_column_count(stat);

   std::cout<<"total column: "<<nColNum<<std::endl;

 

   int r = sqlite3_step(stat);

 

   //在调用sqlite3_column_type 和sqlite3_column_name获取列类型和列名称信息前需要先执行sqlite3_step

   /*

   #define SQLITE_INTEGER  1

   #define SQLITE_FLOAT    2

   #define SQLITE_TEXT     3

   #define SQLITE_BLOB     4

   #define SQLITE_NULL     5

   */

   int colType = sqlite3_column_type(stat, 0);

   std::cout<<"type of column 0 : "<<colType<<std::endl;

   const char *pchColName = sqlite3_column_name(stat, 0);

   std::cout<<"col name of column 0 : "<<pchColName<<std::endl;

 

   colType = sqlite3_column_type(stat, 1);

   std::cout<<"type of column 1 : "<<colType<<std::endl;

   pchColName = sqlite3_column_name(stat, 1);

   std::cout<<"col name of column 1 : "<<pchColName<<std::endl;

 

   colType = sqlite3_column_type(stat, 2);

   std::cout<<"type of column 2 : "<<colType<<std::endl;

   pchColName = sqlite3_column_name(stat, 2);

   std::cout<<"col name of column 2 : "<<pchColName<<std::endl;

 

   colType = sqlite3_column_type(stat, 3);

   std::cout<<"type of column 3 : "<<colType<<std::endl;

   pchColName = sqlite3_column_name(stat, 3);

   std::cout<<"col name of column 3 : "<<pchColName<<std::endl;

 

   while(r == SQLITE_ROW)

   {

      const void * test = sqlite3_column_blob(stat, 1);

      int size = sqlite3_column_bytes(stat, 1);

      sprintf(buffer2, "%s", test);

 

      FILE *fp2;

      fp2 = fopen("outfile.bmp", "wb");

      if(fp2 != NULL)

      {

        size_t ret = fwrite(test, sizeof(char), size, fp2);

        fclose(fp2);

      }

 

      const unsigned char * ptest = sqlite3_column_text(stat, 0);

      std::cout<<ptest<<std::endl;

      int ntest = sqlite3_column_int(stat, 2);

      std::cout<<ntest<<std::endl;

      int ntest1 = sqlite3_column_int(stat, 3);

      std::cout<<ntest1<<std::endl;

 

      r = sqlite3_step(stat);

   }

   sqlite3_finalize(stat);

   sqlite3_close(db);

   return 0;

}

 return 0;
}