sqlite3-入门日记5-测试数据输出方法

一、前言：

     今天测试了两种方式输出数据库数据，记录下来。

二、具体介绍：

1.定义回调函数:static int CallBack(void *Temp, int argc, char **argv, char **szColName)

1.1 源码：

1 #include <iostream>
2 #include <string>
3 #include "sqlite3.h"
4usingnamespace std;
5
6/*CallBack Function:*/
7
8staticint CallBack(void*Temp, int argc, char**argv, char**szColName)
9 {
10
11for(int i =0 ; i < argc ; i++)
12 {
13 cout<<szColName[i]<<" = "<<(argv[i]?argv[i]:"NULL")<<", ";
14 }
15
16 std::cout<<"\n";
17
18return0;
19 }
20
21
22int main()
23 {
24int ret;
25
26 sqlite3 *db;
27string szSql;
28string szDir("D:\\sqlite\\demo.db");
29char*errmsg=NULL;
30 ret=sqlite3_open(szDir.c_str(),&db);
31if(ret!=SQLITE_OK) /*if demo.db not existed ,then return error*/
32 {
33 fprintf(stderr,"can't open db!\n",sqlite3_errmsg(db));
34 sqlite3_close(db);
35 exit(1);
36 }
37else
38 {
39 printf("db open successfully!\n");
40
41 }
42
43 szSql="SELECT * FROM foo;";
44/*call the CallBack function to output db data*/
45 ret=sqlite3_exec(db,szSql.c_str(),CallBack,0,&errmsg);
46if(ret!=SQLITE_OK) /*if table foo not existed,then error*/
47 {
48 fprintf(stderr,"execute error!\n",sqlite3_errmsg(db));
49 sqlite3_close(db);
50 exit(1);
51 }
52else
53 cout<<"Execute successfully!"<<endl;
54
55 sqlite3_close(db);
56return0;
57
58 }

 

2.sqlite3_stmt结构：实现数据输出功能

2.1 int sqlite3_prepare(sqlite3 *,char *,int length,sqlite3_stmt *,char *):

2.1.1 功能： 创建一个stmt结构，把一条SQL语句编译成字节码留给后面的执行函数；

2.1.2 参数： 第二个参数为SQL语句，第三个参数为SQL语句的最大长度，第四个参数为STMT结构，第五个参数为指向当超出一条SQL语句长度时返回的下一条语句的对象，通常为空。

2.2 int sqlite3_step(sqlite3_stmt*):

    返回单行结果集，通配符为：SQLITE_ROW;必须在sqlite3_prepare()被定义之后才能调用。

2.3 int sqlite3_column_count(stmt):

    返回指定结果集中列的数目。类似的函数还有：sqlite3_column_*系列，用到时查看官网API。

2.4 int sqlite3_finalize(stmt):

    销毁stmt结构。与sqlite3_prepare() and sqlite3_reset()组成STMT 三剑客。 

2.5 源码：

1 #include <iostream>
2 #include <string>
3 #include "sqlite3.h"
4usingnamespace std;
5
6int main()
7 {
8int ret;
9 sqlite3_stmt* stmt;
10 sqlite3 *db;
11string szStepSql;
12string szDir("D:\\sqlite\\demo.db");
13char*errmsg=NULL;
14 ret=sqlite3_open(szDir.c_str(),&db);
15if(ret!=SQLITE_OK)
16 {
17 fprintf(stderr,"can't open db!\n",sqlite3_errmsg(db));
18 sqlite3_close(db);
19 exit(1);
20 }
21else
22 {
23 printf("db open successfully!\n");
24
25 }
26
27/*create a sqlite_stmt struct,compile the sql statement into a bytes type*/
28 szStepSql="select * from foo;";
29 ret=sqlite3_prepare(db,szStepSql.c_str(),szStepSql.size(),&stmt,NULL);
30if(ret!=SQLITE_OK)
31 {
32 fprintf(stderr,"prepare error!\n",sqlite3_errmsg(db));
33 sqlite3_close(db);
34 exit(1);
35 }
36else
37 {
38 printf("prepare successfully!\n");
39
40 }
41
42/*access each row of data*/
43 ret=sqlite3_step(stmt);
44int cols=sqlite3_column_count(stmt);
45
46while(ret==SQLITE_ROW)
47 {
48for(int i=0;i<cols;++i)
49 {
50 fprintf(stderr,"'%s'",sqlite3_column_text(stmt,i));
51 }
52 fprintf(stderr,"\n");
53 ret=sqlite3_step(stmt);
54
55 }
56
57 sqlite3_finalize(stmt);
58 sqlite3_close(db);
59return0;
60
61 }