int mycallfunction(void *pv,int argc,char **argv,char** col)
{
for(int i=0;i<argc;i )
{
printf("%s\t%s\n", col[i],argv[i]);
}
return 0;//这里得有返回值,不然得报错
}
char *sql="select *from Student";
ret=sqlite3_exec(db,sql,mycallfunction,NULL,&errmsg);
4.增删查改操作
只需把对应的sql语句传递给sqlite3_exec即可。
har *sql1="INSERT INTO 'Student_Table' VALUES(1,'HUANGYANG',25);";
ret=sqlite3_exec(db,sql1,NULL,NULL,&zErrMsg);
5.查询数据,使用sqlite3_get_table(过时的函数)
SQLITE_API int sqlite3_get_table(
sqlite3 *db, /* An open database */
const char *zSql, /* SQL to be evaluated */
char ***pazResult, /* Results of the query */
int *pnRow, /* Number of result rows written here */
int *pnColumn, /* Number of result columns written here */
char **pzErrmsg /* Error msg written here */
);
char ***pazResult,pazResult指向一块查询结果表。尺寸为(pnColumn 1)*pnRow,因为它把表的标题那一行也算进来了
pnRow:表的列数;pnColumn:查询结果行数
char **pzErrmsg:保存错误信息
char *sql="select *from Student";
sqlite3_get_table(db,sql,&azResult,&nrow,&ncolumn,&azErrMsg);
printf("row=%d,column=%d\n",nrow,ncolumn);
for(int i=0;i<(nrow 1)*ncolumn;i )
{
printf("azResult[%d]=%s\n",i,azResult[i]);
}
sqlite3_free_table(azResult);
需要使用sqlite3_free_table函数对azResult的内存进行释放,这里不能使用sqlite3_free。
6.sql语言中存在变量问题时,使用sqlite3_mprintf函数。(格式化字符串输出函数)
SQLITE_API char *sqlite3_mprintf(const char*,...);
char* sql=sqlite3_mprintf("insert into Student values('%d','%s','%d')",4,"lc",27);
sqlite3_exec(db,sql,NULL,NULL,&azErrMsg);
五、一个完整的例子
转载自:https://blog.csdn.net/zouleideboke/article/details/73649886
#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"
#define _DEBUG_
int main(int argc, char**argv)
{
sqlite3 *db=NULL;
int len;
int i=0;
int nrow=0;
int ncolumn = 0;
char *zErrMsg =NULL;
char **azResult=NULL; //二维数组存放结果
/* 打开数据库 */
len = sqlite3_open("user",&db);
if( len )
{
/* fprintf函数格式化输出错误信息到指定的stderr文件流中 */
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));//sqlite3_errmsg(db)用以获得数据库打开错误码的英文描述。
sqlite3_close(db);
exit(1);
}
else printf("You have opened a sqlite3 database named user successfully!\n");
/* 创建表 */
char *sql = " CREATE TABLE SensorData(\
ID INTEDER PRIMARY KEY,\
SensorID INTEGER,\
siteNum INTEGER,\
Time VARCHAR(12),\
SensorParameter REAL\
);" ;
sqlite3_exec(db,sql,NULL,NULL,&zErrMsg);
#ifdef _DEBUG_
printf("%s\n",zErrMsg);
sqlite3_free(zErrMsg);
#endif
/*插入数据 */
char*sql1 ="INSERT INTO 'SensorData'VALUES(NULL,1,2,201430506201,13.5);";
sqlite3_exec(db,sql1,NULL,NULL,&zErrMsg);
char*sql2 ="INSERT INTO 'SensorData'VALUES(NULL,3,4,201530506302,14.5);";
sqlite3_exec(db,sql2,NULL,NULL,&zErrMsg);
char*sql3 ="INSERT INTO 'SensorData'VALUES(NULL,5,6,201630506413,18.6);";
sqlite3_exec(db,sql3,NULL,NULL,&zErrMsg);
/* 查询数据 */
sql="select *from SensorData";
sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
printf("nrow=%d ncolumn=%d\n",nrow,ncolumn);
printf("the result is:\n");
for(i=0;i<(nrow 1)*ncolumn;i )
{
printf("azResult[%d]=%s\n",i,azResult[i]);
}
/* 删除某个特定的数据 */
sql="delete from SensorData where SensorID = 1 ;";
sqlite3_exec( db , sql , NULL , NULL , &zErrMsg );
#ifdef _DEBUG_
printf("zErrMsg = %s \n", zErrMsg);
sqlite3_free(zErrMsg);
#endif
/* 查询删除后的数据 */
sql = "SELECT * FROM SensorData ";
sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
printf( "row:%d column=%d\n " , nrow , ncolumn );
printf( "After deleting , the result is : \n" );
for( i=0 ; i<( nrow 1 ) * ncolumn ; i )
{
printf( "azResult[%d] = %s\n", i , azResult[i] );
}
sqlite3_free_table(azResult);
#ifdef _DEBUG_
printf("zErrMsg = %s \n", zErrMsg);
sqlite3_free(zErrMsg);
#endif
sqlite3_close(db);
return 0;
}