Danny's tech notebook | 丹尼技術手札: Sqlite

Wednesday, September 12, 2012

[Sqlite] A simple example of Sqlite in C

Here is a simple example of how to deal with Sqlite Database in C language.

#include <stdio.h>
#include <sqlite3.h>

static char *createsql = "CREATE TABLE Employee("
               "ID INTEGER PRIMARY KEY,"
               "Name VARCHAR(10),"
               "BadgeID VARCHAR(10));";

static char *insertsql = "INSERT INTO Employee VALUES(NULL, 'Danny', '12345');";

static char *querysql = "SELECT * FROM Employee;";

void main(void)
{
   int rows, cols;
   sqlite3 *db;
   char *errMsg = NULL;
   char **result;

   /* Open database file */
   if (sqlite3_open("my_example.db3", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)) {
       return;
   }

   /* Build Table */
   sqlite3_exec(db, createsql, 0, 0, &errMsg);

   /* Add a new record */
   sqlite3_exec(db, insertsql, 0, 0, &errMsg);
   /* Get the last insert record's ID */
   printf("%d\n", sqlite3_last_insert_rowid(db));

   /* Get all records in database  */
   sqlite3_get_table(db , querysql, &result , &rows, &cols, &errMsg);

   /* List all the data */
   for (i=0;i<rows;i++) {
       for (j=0;j<cols;j++) {
           printf("%s\t", result[i*cols+j]);
       }
       printf("\n");
   }

   /* Free table */
   sqlite3_free_table(result);

   /* Close database */
   sqlite3_close(db);
}

When Compiling, just add sqlite's flag as follows:

gcc -lsqlite3 -o sqlite_exp sqlite_exp.c