30 if (sqlite3_step(stmt) != SQLITE_DONE) {
31 sqlite3_finalize(stmt);
32 sqlite3_close(conn);
33 return;
34 }
35 //4. 释放创建表语句对象的资源。
36 sqlite3_finalize(stmt);
37 printf("Succeed to create test table now.\n");
38
39 //5. 为后面的查询操作插入测试数据。
40 sqlite3_stmt* stmt2 = NULL;
41 const char* insertSQL = "INSERT INTO TESTTABLE VALUES(20,21.0,'this is a test.')";
42 if (sqlite3_prepare_v2(conn,insertSQL,strlen(insertSQL),&stmt2,NULL) != SQLITE_OK) {
43 if (stmt2)
44 sqlite3_finalize(stmt2);
45 sqlite3_close(conn);
46 return;
47 }
48 if (sqlite3_step(stmt2) != SQLITE_DONE) {
49 sqlite3_finalize(stmt2);
50 sqlite3_close(conn);
51 return;
52 }
53 printf("Succeed to insert test data.\n");
54 sqlite3_finalize(stmt2);
55
56 //6. 执行SELECT语句查询数据。
57 const char* selectSQL = "SELECT * FROM TESTTABLE";
58 sqlite3_stmt* stmt3 = NULL;
59 if (sqlite3_prepare_v2(conn,selectSQL,strlen(selectSQL),&stmt3,NULL) != SQLITE_OK) {
60 if (stmt3)
61 sqlite3_finalize(stmt3);
62 sqlite3_close(conn);
63 return;
64 }
65 int fieldCount = sqlite3_column_count(stmt3);
66 do {
67 int r = sqlite3_step(stmt3);
68 if (r == SQLITE_ROW) {
69 for (int i = 0; i < fieldCount; ++i) {
70 //这里需要先判断当前记录当前字段的类型，再根据返回的类型使用不同的API函数
71 //获取实际的数据值。
72 int vtype = sqlite3_column_type(stmt3,i);
73 if (vtype == SQLITE_INTEGER) {
75 printf("The INTEGER value is %d.\n",v);
76 } else if (vtype == SQLITE_FLOAT) {
77 double v = sqlite3_column_double(stmt3,i);
78 printf("The DOUBLE value is %f.\n",v);
79 } else if (vtype == SQLITE_TEXT) {
80 const char* v = (const char*)sqlite3_column_text(stmt3,i);
81 printf("The TEXT value is %s.\n",v);
82 } else if (vtype == SQLITE_NULL) {
83 printf("This value is NULL.\n");
84 }
85 }
86 } else if (r == SQLITE_DONE) {
87 printf("Select Finished.\n");
88 break;
89 } else {
90 printf("Failed to SELECT.\n");
91 sqlite3_finalize(stmt3);
92 sqlite3_close(conn);
93 return;
94 }
95 } while (true);
96 sqlite3_finalize(stmt3);
97
98 //7. 为了方便下一次测试运行，我们这里需要删除该函数创建的数据表，否则在下次运行时将无法
99 //创建该表，因为它已经存在。
100 const char* dropSQL = "DROP TABLE TESTTABLE";
101 sqlite3_stmt* stmt4 = NULL;
102 if (sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt4,NULL) != SQLITE_OK) {
103 if (stmt4)
104 sqlite3_finalize(stmt4);
105 sqlite3_close(conn);
106 return;
107 }
108 if (sqlite3_step(stmt4) == SQLITE_DONE) {
109 printf("The test table has been dropped.\n");
110 }
111 sqlite3_finalize(stmt4);
112 sqlite3_close(conn);
113 }
114
115 int main()
116 {
117 doTest