SQLite C example: insert data

This is a simple example of inserting text into an SQLite database using the C interface.

The database has one table, called "t", with one column, called "xyz", which you can create as follows:

$ sqlite3 test.db
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t (xyz text);
sqlite> .quit

The following macro definition is in mysqlite.h:

#define CALL_SQLITE(f)                                          \
    {                                                           \
        int i;                                                  \
        i = sqlite3_ ## f;                                      \
        if (i != SQLITE_OK) {                                   \
            fprintf (stderr, "%s failed with status %d: %s\n",  \
                     #f, i, sqlite3_errmsg (db));               \
            exit (1);                                           \
        }                                                       \
    }                                                           \

#define CALL_SQLITE_EXPECT(f,x)                                 \
    {                                                           \
        int i;                                                  \
        i = sqlite3_ ## f;                                      \
        if (i != SQLITE_ ## x) {                                \
            fprintf (stderr, "%s failed with status %d: %s\n",  \
                     #f, i, sqlite3_errmsg (db));               \
            exit (1);                                           \
        }                                                       \
    }                                                           \

(download)

The main program inserts the word "fruit" into the table "xyz" and then returns the row number of the inserted row:

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
#include "mysqlite.h"

int main ()
{
    sqlite3 * db;
    char * sql;
    sqlite3_stmt * stmt;
    int i;

    CALL_SQLITE (open ("test.db", & db));
    sql = "INSERT INTO t (xyz) VALUES (?)";
    CALL_SQLITE (prepare_v2 (db, sql, strlen (sql) + 1, & stmt, NULL));
    CALL_SQLITE (bind_text (stmt, 1, "fruit", 6, SQLITE_STATIC));
    CALL_SQLITE_EXPECT (step (stmt), DONE);
    printf ("row id was %d\n", (int) sqlite3_last_insert_rowid (db));
    return 0;
}

(download)