Simple SQLite test C program, without callbacks
I need some efficient way for Miao for word database, and I guess SQLite is the solution. So I wrote this simple test program to get familiar with SQLite API. (read more for the program)
And there's something should be kept in mind: don't use "`"s.
As I used to use MySQL before, I always put "`" in SQL statements, for example:
create table `foo` (`bar` int)
It's OK in SQLite command line program, but not in the C library.
When I execute a SQL query with "`", I'll get this error message:
sql error #1: unrecognized token: "`"
So I deleted "`"s in the SQL statement, but still get this error message:
sql error #4: malformed database schema - unrecognized token: "`"
This really drive me crazy, as I don't know what's wrong. But finally I've got the reason: that's because there's "`" in the "create table" statement. So I recreated the table and rerun the program, it's finally OK now.
The program:1 #include
2 #include
3 #include
4
5 #define NAME "miao.db"
6
7 int main() {
8 sqlite3 *db;
9 sqlite3_stmt *stmt;
10 int rc;
11 char *errmsg = 0;
12
13 rc = sqlite3_open(NAME, &db);
14 if(rc) {
15 fprintf(stderr, "can't open db %s: %s\n", NAME, sqlite3_errmsg(db));
16 sqlite3_close(db);
17 exit(1);
18 }
19 rc = sqlite3_prepare(db, "select * from words", 0, &stmt, 0);
20 if(rc!=SQLITE_OK) {
21 fprintf(stderr, "sql error #%d: %s\n", rc, sqlite3_errmsg(db));
22 } else while((rc = sqlite3_step(stmt)) != SQLITE_DONE) {
23 switch(rc) {
24 case SQLITE_BUSY:
25 fprintf(stderr, "busy, wait 1 seconds\n");
26 sleep(1);
27 break;
28 case SQLITE_ERROR:
29 fprintf(stderr, "step error: %s\n", sqlite3_errmsg(db));
30 break;
31 case SQLITE_ROW:
32 {
33 int n = sqlite3_column_count(stmt);
34 int i;
35 for(i=0; i36 printf("%s = ", sqlite3_column_name(stmt, i));
37 switch(sqlite3_column_type(stmt, i)) {
38 case SQLITE_TEXT:
39 printf("%s", sqlite3_column_text(stmt, i));
40 break;
41 case SQLITE_INTEGER:
42 printf("%d", sqlite3_column_int(stmt, i));
43 break;
44 case SQLITE_FLOAT:
45 printf("%f", sqlite3_column_double(stmt, i));
46 break;
47 case SQLITE_BLOB:
48 printf("(blob)");
49 break;
50 case SQLITE_NULL:
51 printf("(null)");
52 break;
53 default:
54 printf("(unknown: %d)", sqlite3_column_type(stmt, i));
55 }
56 printf("\n");
57 }
58 }
59 break;
60 }
61 }
62 sqlite3_finalize(stmt);
63 sqlite3_close(db);
64 }
65