Niklas Tech Blog

I’m here going to demonstrate a simple connection and query to a SQLite database using C.

First you need to download and build (if necessary) the SQLite code so you get the sqlite3.so (or sqlite3.lib on Windows) and the necessary header and c files (sqlite3.h, sqlite3.c). I will not go into how this is done here.

After the environment is setup we start with connecting to the SQLite database:

 
#include "sqlite3.h"

int main(int argc, char const *argv[]) {
        
    sqlite3 *conn; // Database struct handle
    const char *dbname = "test.db"; // Database file name
    int dbhandle; // Resource handle

    // Open database
    dbhandle = sqlite3_open_v2(dbname, &conn, SQLITE_OPEN_READONLY, NULL);

    if(dbhandle == SQLITE_OK) {
        fprintf(stdout, "Database open\n");
    }
    else {		
        fprintf(stderr, "Could not open database: %s\n", sqlite3_errmsg(conn) );
        return 1;
   }

   // Cleanup
   sqlite3_close(conn);

   return 0;

}

In short this code will do the following:
1. Create necessary variables for connection, resource handle and the database file name
2. Call sqlite3_open_v2() to open a read only connection to the SQLite database (test.db)
3. Check if we succeeded and report result to console
4. Cleanup connection object

I choose here to use the sqlite3_open_v2() function due to two main reasons:
1. I don’t want to create the database if it does not exist. This is the default behavior of sqlite3_open()
2. I want to be able to open the database as readonly (SQLITE_OPEN_READONLY) or as readwrite (SQLITE_OPEN_READWRITE)

Now we are also going to query the database and present the result. We start with adding a callback function to handle the output like this:

static int callback(void *NotUsed, int argc, char **argv, char **colname){

	NotUsed = 0;
	int i;
	for (i = 0; i < argc; i++){

		printf("%s = %s\n", colname[i], argv[i] ? argv[i] : "NULL");
	}
	printf("\n");
	return 0;
}

This function is going to be called from sqlite3_exec() function later. Once per row of results from query. It will print a list with “column name = cell value” rows

 
#include "sqlite3.h"

int main(int argc, char const *argv[]) {
    
    // Connection variables    
    sqlite3 *conn; // Database struct handle
    const char *dbname = "test.db"; // Database file name
    int dbhandle; 

    // Query variables
    int stmthandle;  // Statement handel
    char *errorMsg = 0; // Error message holder
    const char *query = "SELECT * FROM user";  // Query to run 

    // Open database
    dbhandle = sqlite3_open_v2(dbname, &conn, SQLITE_OPEN_READONLY, NULL);

    if(dbhandle == SQLITE_OK) {
        fprintf(stdout, "Database open\n");
    }
    else {		
        fprintf(stderr, "Could not open database: %s\n", sqlite3_errmsg(conn) );
        return 1;
    }
   
    // Execute query
    stmthandle = sqlite3_exec(conn, query, callback, 0, &errorMsg);

    if (stmthandle == SQLITE_OK) {
        fprintf(stdout, "Query successful\n");
    }
    else {
         fprintf(stderr, "Could not run query: %s\n", errorMsg );
         return 1;
    }

    // Cleanup
    sqlite3_close(conn);
    
    return 0;

}

I have now added the sqlite3_exec() wrapper function (it handles sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() all into one nifty function) and the necessary query variables (statement handle, error message holder and SQL statement).

The sqlite3_exec() function takes the following input:
1. Connection handle to the database
2. SQL query
3. Name of callback function to call for every row of results from database
4. First argument to callback function (the NotUsed argument in the callback function above)
5. Error message holder to return error messages in (if any)

After these arguments have all been satisfied, sqlite3_exec() will call the callback function with the results. Here is an example of output:

Database open
id = 1
login = anders
password = dator123

id = 2
login = niklas
password = dator123

id = 3
login = miew
password = dator123

Query successful

and that is it. We now have a small and simple example of how you connect and query a SQLite database using C.

NOTE: When compiling do not forget to add the -lsqlite3 argument to gcc:

gcc test.c -o test -lsqlite3 -Wall

This has been tested on SQLite 3.7.15.2, gcc 4.2 and OSX 10.7.5