【翠字营原创】：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);",&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,SEEK_END);

filesize = ftell(fp);

fseek(fp,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;",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,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;}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!