SQLite3 Sample C Program

Click here to Download sample code.

/* Basic C code for SQLite3 operations
 *
 * Connect to DB
 * Create a Table
 * Insert into DB
 * Fetch Schema from DB
 * Fetch Data from DB
 * Transactions
 * Error handling
 * Release connections
 *
 */

/* DB Schema
 *
 * Table Name : JI
 *
 * Columns : id (int) - Primary Key
 * name (string)
 * age (unsigned short)
 *
 */

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

#define DB_FILE "test.db"
#define DB_PRAGMA "PRAGMA foreign_keys = ON;PRAGMA synchronous = OFF;PRAGMA journal_mode = MEMORY;"

#define ENABLE_DB_TRANSACTION
#define BUFFER_SIZE 1024

/* === Utility functions to handle error === */
#define HANDLE_ERROR(r, f, h) \
 if (r != SQLITE_OK && r != SQLITE_DONE) \
 { \
 extract_error(r, f, h, __LINE__,__func__); \
 goto exit; \
 }

void
extract_error (int retcode, char *fn, sqlite3 *dbc,
 int line, const char *func)
{
 const char *err = NULL;

 printf ("\nError!!! In '%s' (line no. %d) ", func, line-1);
 printf ("\nSQLite reported following error for %s : Error-code = %d", fn, retcode);

 err = sqlite3_errmsg (dbc);
 if (err)
 printf ("\nError-Message : %s\n\n",err);
}

/* Main Routine */
int
main ()
{
 sqlite3 *dbc = NULL;
 sqlite3_stmt *stmt = NULL;
 int ret = 0;
 char buf[BUFFER_SIZE] = {0};
 int i = 0;

 const char *column = NULL;
 const char *typeName = NULL;
 int id = 0;
 const char *name = NULL;
 unsigned short age = 0;

/* ===== Connect to DB ===== */

 ret = sqlite3_open_v2 (DB_FILE, &dbc,
 SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
 NULL);
 HANDLE_ERROR(ret, "sqlite3_open_v2", dbc);

 /* Set PRAGMA for this connection */
 ret = sqlite3_prepare_v2 (dbc, DB_PRAGMA, -1, &stmt, NULL);
 HANDLE_ERROR(ret, "sqlite3_prepare_v2", dbc);

 ret = sqlite3_step (stmt);
 HANDLE_ERROR(ret, "sqlite3_step", dbc);

 ret = sqlite3_reset (stmt);
 HANDLE_ERROR(ret, "sqlite3_reset", dbc);

/* ======== Create a table in Database ====== */

 memset (buf, 0, BUFFER_SIZE);
 sprintf (buf, "CREATE TABLE JI ( id integer, name varchar(64), age SMALLINT, PRIMARY KEY(id))");

 ret = sqlite3_prepare_v2 (dbc, buf, -1, &stmt, NULL);
 HANDLE_ERROR(ret, "sqlite3_prepare_v2", dbc);

 ret = sqlite3_step (stmt);
 HANDLE_ERROR(ret, "sqlite3_step", dbc);

 ret = sqlite3_reset (stmt);
 HANDLE_ERROR(ret, "sqlite3_reset", dbc);

/* ===== Insert 5 Rows ===== */

#ifdef ENABLE_DB_TRANSACTION
 ret = sqlite3_prepare_v2 (dbc, "BEGIN IMMEDIATE TRANSACTION", -1, &stmt, NULL);
 HANDLE_ERROR(ret, "sqlite3_prepare_v2", dbc);

 ret = sqlite3_step (stmt);
 HANDLE_ERROR(ret, "sqlite3_step", dbc);

 ret = sqlite3_reset (stmt);
 HANDLE_ERROR(ret, "sqlite3_reset", dbc);
#endif

 for (i=1; i<=5; i++ )
 {
 sprintf (buf, "INSERT INTO JI (id, name, age) values (%d, 'sp%d', %d)" , i,i,24+i);

 ret = sqlite3_prepare_v2 (dbc, buf, -1, &stmt, NULL);
 HANDLE_ERROR(ret, "sqlite3_prepare_v2", dbc);

 ret = sqlite3_step (stmt);
 HANDLE_ERROR(ret, "sqlite3_step", dbc);

 ret = sqlite3_reset (stmt);
 HANDLE_ERROR(ret, "sqlite3_reset", dbc);
 }

#ifdef ENABLE_DB_TRANSACTION
 ret = sqlite3_prepare_v2 (dbc, "COMMIT", -1, &stmt, NULL);
 HANDLE_ERROR(ret, "sqlite3_prepare_v2", dbc);

 ret = sqlite3_step (stmt);
 HANDLE_ERROR(ret, "sqlite3_step", dbc);

 ret = sqlite3_reset (stmt);
 HANDLE_ERROR(ret, "sqlite3_reset", dbc);
#endif


/* ========== Fetch Info from DB ======== */

 /* ============ Fetch table info ============= */

 ret = sqlite3_prepare_v2 (dbc, "PRAGMA table_info(JI);", -1, &stmt, NULL);
 HANDLE_ERROR(ret, "sqlite3_prepare_v2", dbc);

 printf ("\n====== DB Schema ====== \n");
 
 ret = sqlite3_step (stmt);
 while (ret == SQLITE_ROW) 
 {
 column = sqlite3_column_text (stmt, 1); 
 if (NULL == column)
 {
 printf ("Error!!! Malloc Failed in SQLite\n");
 goto exit;
 }
 typeName = sqlite3_column_text (stmt, 2);
 printf( " Table - JI : Column - %s : Type - %s \n", column, typeName );
 ret = sqlite3_step (stmt);
 }
 HANDLE_ERROR(ret, "sqlite3_step", dbc);

 ret = sqlite3_reset (stmt);
 HANDLE_ERROR(ret, "sqlite3_reset", dbc);

 printf ("\n");

 /* ====== Fetch Data ====== */

 ret = sqlite3_prepare_v2 (dbc, "SELECT * FROM JI", -1, &stmt, NULL);
 HANDLE_ERROR(ret, "sqlite3_prepare_v2", dbc);

 printf ("\n====== DB Data ====== ");

 ret = sqlite3_step (stmt);
 i = 1;
 while (ret == SQLITE_ROW) 
 {
 printf ("\n");
 id = sqlite3_column_int (stmt, 0);
 name = sqlite3_column_text (stmt, 1); 
 if (NULL == name)
 {
 printf ("Error!!! Malloc Failed in SQLite\n");
 goto exit;
 }
 age = sqlite3_column_int (stmt, 2);
 printf(" Row %d : %d %s %d", i, id, name, age);
 i++;
 ret = sqlite3_step (stmt);
 }
 HANDLE_ERROR(ret, "sqlite3_step", dbc);

 ret = sqlite3_reset (stmt);
 HANDLE_ERROR(ret, "sqlite3_reset", dbc);

 printf ("\n\n");

/* =========== Free handlers ============ */
exit:

 /* Free statement handler */
 if (stmt)
 {
 ret = sqlite3_finalize (stmt);
 HANDLE_ERROR(ret, "sqlite3_finalize", dbc);
 }

 /* Free Connection handler */
 if (dbc)
 {
 ret = sqlite3_close_v2 (dbc);
 HANDLE_ERROR(ret, "sqlite3_close_v2", dbc);
 }

 return 0;
}

                  ====== Sample Output =====

[root@localhost anukul]# gcc -o ji ji_sqlite3.c -lsqlite3

[root@localhost anukul]# ./ji

====== DB Schema ======
 Table - JI : Column - id : Type - integer
 Table - JI : Column - name : Type - varchar(64)
 Table - JI : Column - age : Type - SMALLINT

====== DB Data ======
 Row 1 : 1 sp1 25
 Row 2 : 2 sp2 26
 Row 3 : 3 sp3 27
 Row 4 : 4 sp4 28
 Row 5 : 5 sp5 29
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s