Category Archives: Databases

How to access a Sqlite database in C

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 sqlite2.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 <stdio.h>
#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 sqlite2_exec() function later. Once per row of results from query. It will print a list with "column name = cell value" rows

#include <stdio.h>
#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, sqlite2_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

My SQLite3 cheat sheet

Start SQLite

sqlite3 <filename>

Show all tables

.tables

Show whole database as SQL (kind of a “describe” function in MySQL)

.schema

Create new table example

CREATE TABLE users (id INTEGER PRIMARY KEY,login TEXT,pass TEXT);

The ‘id’ column is here what you call autoincrement in MySQL – if you assign NULL to this column the value will be incremented by 1 from last value

Valid columntypes:
TEXT
INTEGER
REAL
BLOB

Rename table example

ALTER TABLE users RENAME TO client_users

This renames table users to client_users

SELECT example

SELECT * FROM table_name WHERE column_name = value

Selects all rows in table table_name where column column_name is equal to value

UPDATE example

UPDATE table_name SET column_name = update_value WHERE some_column = some_value

Updates column_name with the value update_value in table table_name, on row/s where some_column is equal to some_value

DELETE example

DELETE FROM table_name WHERE column_name < 4

Deletes all rows from table_name where column_name is less than 4

INSERT example

INSERT INTO table_name (column1, column4, column7) VALUES (value1, 'value2', value3)

Inserts a new row into table table_name with values value1 in column1, value2 in column4 and value3 in column7 (other columns in table are left null or with default values, if set)

Read sql from file

sqlite3 my.db < filewithsql

This will read and execute the sql statements from the file filewithsql.

My PostgreSQL cheat sheet

Here I have collected all common commands and SQL for PostgreSQL that I use in my work

Help on psql slash commands
\?

Help on SQL commands
\h

List databases
\l

Connect to different database
\c database

Eqivavent to DESCRIBE table
\d+ tablename

List database settings
\set

List all tables
\dt

List users
\du

Quit psql command line
\q

Create user

CREATE USER niklas WITH PASSWORD 'myPassword';

Grant all privilegies on database to a user

GRANT ALL PRIVILEGES ON DATABASE myDatabase to niklas;

Grant alla privilegies on table to a user

GRANT ALL PRIVILEGES ON TABLE myTable to niklas;

Create table syntax (standard)

CREATE TABLE myTable (id serial, time integer);

Insert statement

INSERT INTO public.user (id, login, password) VALUES (1, 'test','test');# NOTE the single quotes!

Update statement

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';# NOTE the single quotes!

Delete statement

DELETE FROM films WHERE producer_id = 1;

I hope to come back to this sheet to add stuff as time goes by