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

转载于 http://www.cnblogs.com/ballwql/archive/2011/03/28/1997797.html

一、前言：

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

二、具体介绍：

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

1.1 源码：

View Code

1 #include <iostream>
2 #include <string>
3 #include "sqlite3.h"
4 using namespace std;
5
6 /*CallBack Function:*/
7
8 static int CallBack(void *Temp, int argc, char **argv, char **szColName)
9 {
10
11 for(int i = 0 ; i < argc ; i++)
12 {
13 cout<<szColName[i]<<" = "<<(argv[i]?argv[i]:"NULL")<<", ";
14 }
15
16 std::cout<< "\n";
17
18 return 0;
19 }
20
21
22 int main()
23 {
24 int ret;
25
26 sqlite3 *db;
27 string szSql;
28 string szDir("D:\\sqlite\\demo.db");
29 char *errmsg=NULL;
30 ret=sqlite3_open(szDir.c_str(),&db);
31 if(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 }
37 else
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);
46 if(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 }
52 else
53 cout<<"Execute successfully!"<<endl;
54
55 sqlite3_close(db);
56 return 0;
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 源码：

View Code

1 #include <iostream>
2 #include <string>
3 #include "sqlite3.h"
4 using namespace std;
5
6 int main()
7 {
8 int ret;
9 sqlite3_stmt* stmt;
10 sqlite3 *db;
11 string szStepSql;
12 string szDir("D:\\sqlite\\demo.db");
13 char *errmsg=NULL;
14 ret=sqlite3_open(szDir.c_str(),&db);
15 if(ret!=SQLITE_OK)
16 {
17 fprintf(stderr,"can't open db!\n",sqlite3_errmsg(db));
18 sqlite3_close(db);
19 exit(1);
20 }
21 else
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);
30 if(ret!=SQLITE_OK)
31 {
32 fprintf(stderr,"prepare error!\n",sqlite3_errmsg(db));
33 sqlite3_close(db);
34 exit(1);
35 }
36 else
37 {
38 printf("prepare successfully!\n");
39
40 }
41
42 /*access each row of data*/
43 ret=sqlite3_step(stmt);
44 int cols=sqlite3_column_count(stmt);
45
46 while(ret==SQLITE_ROW)
47 {
48 for(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);
59 return 0;
60
61 }