Adam Richardson's Site

SQLite Notes

Table of Contents

<2023-10-03 Tue>

C Programming Interface

Database Handles

  • Database handles are references to an open sqlite connection
  • They are created by the sqlite3_open function
  • The type of the database handle is sqlite3 *

Opening and Closing the Database

  • Use the sqlite3_open method to open a database
  • This will create the database if it doesn't already exist
  • Use the sqlite3_close method to close the database
#include <stdio.h>

#include "sqlite_3.43.1/sqlite3.h"

int main(int argc, char **argv)
{
        sqlite3 *db = NULL;
        int result;

        printf("Hello, SQLite3\n");

        result = sqlite3_open("test.db", &db);
        if (result != SQLITE_OK) {
                return result;
        }

        result = sqlite3_close(db);
        if (result != SQLITE_OK) {
                return result;
        }

        return 0;
}

Running SQL Statements

  • The sqlite3_exec function takes a SQL string and executes each statement in the SQL string
  • It can also take a row data callback that gets called with each rows data if there is any (this field is nullable)
  • If there is an error the errmsg pointer will have a message
    • In this case it will need to be free'd using sqlite3_free
  • The following example shows creating a database and executing multiple statements to insert data
#include <stdio.h>

#include "sqlite_3.43.1/sqlite3.h"

int main(int argc, char **argv)
{
        sqlite3 *db = NULL;
        int result;
        char *err_msg = NULL;

        printf("Hello, SQLite3\n");

        result = sqlite3_open("test.db", &db);
        if (result != SQLITE_OK) {
                fprintf(stderr, "Unable to open db: %s\n",
                        sqlite3_errmsg(db));

                return 1;
        }

        char *sql =
                "DROP TABLE IF EXISTS test;"
                "CREATE TABLE test(id INTEGER PRIMARY KEY, msg TEXT);"
                "INSERT INTO test(msg) VALUES(\"abc\");"
                "INSERT INTO test(msg) VALUES(\"123\");";

        result = sqlite3_exec(db, sql, NULL, NULL, &err_msg);

        if (result != SQLITE_OK) {
                fprintf(stderr, "SQL error: %s\n", err_msg);
                sqlite3_free(err_msg);
                sqlite3_close(db);

                return 1;
        }

        sqlite3_close(db);

        return 0;
}
  • The following example shows defining a row callback when querying the db
#include <stdio.h>

#include "sqlite_3.43.1/sqlite3.h"

int row_callback(void *user_data, int row_count, char **values,
                 char **columns);

int main(int argc, char **argv)
{
        sqlite3 *db = NULL;
        int result;
        char *err_msg = NULL;

        printf("Hello, SQLite3\n");

        result = sqlite3_open("test.db", &db);
        if (result != SQLITE_OK) {
                fprintf(stderr, "Unable to open db: %s\n",
                        sqlite3_errmsg(db));

                return 1;
        }

        char *sql =
                "DROP TABLE IF EXISTS test;"
                "CREATE TABLE test(id INTEGER PRIMARY KEY, msg TEXT);"
                "INSERT INTO test(msg) VALUES(\"abc\");"
                "INSERT INTO test(msg) VALUES(\"123\");";

        result = sqlite3_exec(db, sql, NULL, NULL, &err_msg);

        if (result != SQLITE_OK) {
                fprintf(stderr, "SQL error: %s\n", err_msg);
                sqlite3_free(err_msg);
                sqlite3_close(db);

                return 1;
        }

        sql = "SELECT * FROM test;";
        result = sqlite3_exec(db, sql, row_callback, NULL, &err_msg);
        if (result != SQLITE_OK) {
                fprintf(stderr, "SQL error: %s\n", err_msg);
                sqlite3_free(err_msg);
                sqlite3_close(db);

                return 1;
        }

        sqlite3_close(db);

        return 0;
}

int row_callback(void *user_data, int row_count, char **values,
                 char **columns)
{
        printf("test row\n");
        for (int i = 0; i < row_count; i++) {
                printf("\t%s: %s\n", values[i], columns[i]);
        }
        return 0;
}
  • This returns the following output
./hello_sqlite
Hello, SQLite3
test row
        1: id
        abc: msg
test row
        2: id
        123: msg

Result Codes

SQLite Amalgamation

  • Amalgamation Docs
  • All of the individual C source files concatenated into a single sqlite3.c source file
  • The goal of this is to make it easy to add to your own repo to compile SQLite with your application
  • Inside the amalgamation zip are the following files:
    • sqlite3.c
    • shell.c
    • sqlite3.h
    • sqlite3ext.h
  • sqlite3.c is the main source file for the library and must be compiled along side your program
  • shell.c is the CLI interface to sqlite

Shell

  • The sqlite amalgamation code comes with a program in shell.c
  • This program allows you to query a sqlite3 file
  • Use C-d to exit the program
$ ./sqlite3 test.db
./sqlite3 test.db
SQLite version 3.43.1 2023-09-11 12:01:27
Enter ".help" for usage hints.
sqlite> .mode column
.mode column
sqlite> .headers on
.headers on
sqlite> SELECT * FROM test;
SELECT * FROM test;
id  value
--  -----
1   abc
2   123

Compiling

  • Compiling SQLite Docs
  • SQLite has no dependencies so it should be as easy as compiling to a .o file and then building your binary with it
CFLAGS = -pedantic -fstack-protector-strong -Warray-bounds -O2 -Wall -Wextra -Wundef -Wunreachable-code -Wshadow -Wno-unused-parameter
LIBS =
CC = gcc

hello_sqlite: main.c sqlite3.o
        $(CC) $(CFLAGS) main.c sqlite3.o -o hello_sqlite $(LIBS)

sqlite3.o:
        $(CC) $(CFLAGS) -c sqlite_3.43.1/sqlite3.c -o sqlite3.o

sqlite3: sqlite3.o sqlite_3.43.1/shell.c
        $(CC) $(CFLAGS) sqlite_3.43.1/shell.c sqlite3.o -o sqlite3