La Vita è Bella

Tuesday, May 22, 2007

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 <stdio.h>
 2 #include <stdlib.h>
 3 #include <sqlite3.h>
 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<n; 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



tags: , ,

00:38:33 by fishy - dev - Permanent Link

Revision: 1.1/1.1, last modified on 2007-05-22 @ 00:41.

Karma: 537 (90.38% out of 665 were positive) [+/-]

You can subscribe to RSS 2.0 feed for comments and trackbacks

Trackbacks:
There are currently no trackbacks for this item.
Use this TrackBack url to ping this item (right-click, copy link target). If your blog does not support Trackbacks you can manually add your trackback by using this form.

No comments yet

Add Comment

 

May the Force be with you. RAmen