Master SQLite with C: Essential Functions and Sample Code

Setup

Assume a SQLite database file my.db containing a table student with columns no (INTEGER PRIMARY KEY), name (TEXT) and score (REAL). The table can be created with:

CREATE TABLE IF NOT EXISTS student (no integer primary key, name text, score real);

Common SQLite C Functions

sqlite3_open

int sqlite3_open(const char *path, sqlite3 **db);

Opens the database file at path and returns a handle in db. Returns 0 on success, non‑zero on failure.

sqlite3_close

int sqlite3_close(sqlite3 *db);

Closes the database handle. Returns 0 on success.

sqlite3_errmsg

const char *sqlite3_errmsg(sqlite3 *db);

Returns a human‑readable error message for the most recent error on db.

sqlite3_get_table (non‑callback API)

int sqlite3_get_table(sqlite3 *db, const char *sql, char ***resultp,
    int *nrow, int *ncolumn, char **errmsg);

Executes sql and stores the result in a flat string array *resultp. *nrow and *ncolumn receive the number of rows and columns. Returns 0 on success.

Example: Query with sqlite3_get_table

The function below prints all rows of student using the non‑callback API:

void do_show_sample(sqlite3 *db) {
    char **result, *errmsg;
    int nrow, ncolumn, i, j, index;
    if (sqlite3_get_table(db, "select * from student", &result,
                          &nrow, &ncolumn, &errmsg) != 0) {
        printf("error : %s
", errmsg);
        sqlite3_free(errmsg);
        return;
    }
    index = ncolumn; // skip header row
    for (i = 0; i < nrow; i++) {
        for (j = 0; j < ncolumn; j++) {
            printf("%-8s : %-8s
", result[j], result[index]);
            index++;
        }
        printf("************************
");
    }
    sqlite3_free_table(result);
}

Sample data used in the article:

4, 一口Linux, 77.0

5, 一口peng, 88.0

6, 一口wang, 99.0

7, 一口网, 66.0

The following diagram (kept for reference) shows how ncolumn, nrow and the result array are organized:

Using Callbacks with sqlite3_exec

sqlite3_exec

int sqlite3_exec(sqlite3 *db, const char *sql,
    int (*callback)(void*,int,char**,char**), void *arg,
    char **errmsg);

Executes sql. For each row returned, callback is invoked.

Callback definition

typedef int (*sqlite3_callback)(void *para, int f_num,
    char **f_value, char **f_name);

The callback receives the number of columns ( f_num), an array of column values ( f_value) and an array of column names ( f_name).

Example callback and display function

int callback(void *para, int f_num, char **f_val, char **f_name) {
    for (int i = 0; i < f_num; i++) {
        printf("%-8s", f_val[i]);
    }
    printf("
");
    return 0;
}

void do_show(sqlite3 *db) {
    char *errmsg;
    printf("no      name    score
");
    if (sqlite3_exec(db, "select * from student", callback, NULL, &errmsg) != 0) {
        printf("error : %s
", sqlite3_errmsg(db));
    }
    printf("
");
}

The callback is invoked once per row (fourth invocation illustrated below):

Compilation

Compile the program with the SQLite development library:

gcc student.c -o run -lsqlite3

Additional SQLite API Functions

sqlite3_stmt *stmt

: prepared statement object. sqlite3_prepare(): prepares an SQL statement. sqlite3_step(): steps through the result rows. sqlite3_column_text(), sqlite3_column_blob(), sqlite3_column_int(): retrieve column values of various types.

Complete Sample Program

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

void do_insert(sqlite3 *db) {
    int no; char name[16]; float score; char sqlstr[128], *errmsg;
    printf("input no : "); scanf("%d", &no);
    printf("input name : "); scanf("%s", name);
    printf("input score : "); scanf("%f", &score);
    sprintf(sqlstr, "insert into student values (%d, '%s', %.1f)", no, name, score);
    if (sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg) != 0) {
        printf("error : %s
", sqlite3_errmsg(db));
    } else {
        printf("insert is done
");
    }
}

void do_delete(sqlite3 *db) {
    char sqlstr[128], expression[64], *errmsg;
    printf("input expression : "); scanf("%s", expression); // e.g. name='ma'
    sprintf(sqlstr, "delete from student where %s", expression);
    if (sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg) != 0) {
        printf("error : %s
", sqlite3_errmsg(db));
    } else {
        printf("delete is done
");
    }
}

int callback(void *para, int f_num, char **f_val, char **f_name) {
    for (int i = 0; i < f_num; i++) {
        printf("%-8s", f_val[i]);
    }
    printf("
");
    return 0;
}

void do_show(sqlite3 *db) {
    char *errmsg;
    printf("no      name    score
");
    if (sqlite3_exec(db, "select * from student", callback, NULL, &errmsg) != 0) {
        printf("error : %s
", sqlite3_errmsg(db));
    }
    printf("
");
}

int main() {
    sqlite3 *db;
    int n; char clean[64];
    if (sqlite3_open("my.db", &db) != 0) {
        printf("fail to sqlite3_open : %s
", sqlite3_errmsg(db));
        return -1;
    }
    while (1) {
        printf("*********************************************
");
        printf("1: insert record
2: delete record
3: show record
4: quit
");
        printf("*********************************************
");
        printf("please select : ");
        if (scanf("%d", &n) != 1) {
            fgets(clean, 64, stdin);
            printf("
");
            continue;
        }
        switch (n) {
            case 1: do_insert(db); break;
            case 2: do_delete(db); break;
            case 3: do_show(db); break;
            case 4: sqlite3_close(db); exit(0);
        }
    }
    return 0;
}

This program demonstrates creating, inserting, deleting, and displaying records in a SQLite database using both the non‑callback ( sqlite3_get_table) and callback ( sqlite3_exec) approaches.