La Vita è Bella

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; i 36                                         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