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
