Code:
#include <stdio.h>
#include "sqlite3.h"
#define ADULT_THRESHOLD 21
struct person
{
char name[100];
int age;
};
int create_table(sqlite3 *db)
{
int result = 1; /* success */
sqlite3_stmt *stmt = NULL;
const char *sql = "CREATE TABLE persons (name TEXT, age INTEGER);";
if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK)
{
if (sqlite3_step(stmt) != SQLITE_DONE)
{
printf("Error: Could not create table 'persons':\n>>> %s\n",
sqlite3_errmsg(db));
result = 0;
}
}
else
{
printf("Error: Could not prepare creation of table 'persons':\n>>> %s\n",
sqlite3_errmsg(db));
result = 0;
}
sqlite3_finalize(stmt);
return result;
}
int populate_table(sqlite3 *db, const struct person *persons, size_t num_persons)
{
int result = 1; /* success */
sqlite3_stmt *stmt = NULL;
const char *sql = "INSERT INTO persons (name, age) VALUES (:name, :age);";
sqlite3_exec(db, "BEGIN;", NULL, NULL, NULL);
if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK)
{
size_t i;
for (i = 0; i < num_persons; ++i)
{
sqlite3_bind_text(stmt,
sqlite3_bind_parameter_index(stmt, ":name"),
persons[i].name,
-1,
SQLITE_TRANSIENT);
sqlite3_bind_int(stmt,
sqlite3_bind_parameter_index(stmt, ":age"),
persons[i].age);
if (sqlite3_step(stmt) != SQLITE_DONE)
{
printf("Error: Could not insert into table 'persons':\n>>> %s\n",
sqlite3_errmsg(db));
result = 0;
break;
}
sqlite3_reset(stmt);
}
}
else
{
printf("Error: Could not prepare insertion into table 'persons':\n>>> %s\n",
sqlite3_errmsg(db));
result = 0;
}
sqlite3_finalize(stmt);
sqlite3_exec(db, (result ? "COMMIT;" : "ROLLBACK;"), NULL, NULL, NULL);
return result;
}
int print_adults(sqlite3 *db, int adult_threshold)
{
int result = 1; /* success */
sqlite3_stmt *stmt = NULL;
const char *sql = "SELECT name, age FROM persons WHERE age>=:age;";
if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK)
{
sqlite3_bind_int(stmt,
sqlite3_bind_parameter_index(stmt, ":age"),
adult_threshold);
printf("Adults:\n");
while (sqlite3_step(stmt) == SQLITE_ROW)
{
printf("%s (%d)\n",
sqlite3_column_text(stmt, 0),
sqlite3_column_int(stmt, 1));
}
}
else
{
printf("Error: Could not prepare selection from table 'persons':\n>>> %s\n",
sqlite3_errmsg(db));
result = 0;
}
sqlite3_finalize(stmt);
return result;
}
int main(void)
{
const struct person persons[] = {
{"Alice", 20},
{"Bob", 15},
{"Charlie", 18},
{"Mallory", 25},
{"Trent", 30},
};
const size_t num_persons = sizeof(persons) / sizeof(persons[0]);
const char *filename = ":memory:";
sqlite3 *db = NULL;
if (sqlite3_open(filename, &db) == SQLITE_OK)
{
if (create_table(db) && populate_table(db, persons, num_persons))
{
print_adults(db, ADULT_THRESHOLD);
}
}
else
{
printf("Error: Could not create/open database '%s'\n", filename);
}
sqlite3_close(db);
return 0;
}
Now, this just saves into an in-memory database, and it does less error checking than it should, but it should give you a good idea of how this might be done.