SQLite C API introduction

I guess you are already using some kind of Linux if you are reading this. Install SQLite libraries and dev libraries, on my box they are called libsqlite3-0 and libsqlite3-dev. On your Linux packages may be called slightly differently. For example if your distro is Debian based you will check for packages executing in terminal:

apt-cache search sqlite

You do not need elevated privileges to execute that.
Also install sqlite3 command line interface for SQLite 3.
SQLite is embedded relational database engine. It was popular before but then Android made it part of OS and now it is like totally popular. There is no server and consequently no SQLite database administrator.
In order to build examples we need to specify linker switch, like this:

gcc testme.c -lsqlite3 -o test

Everything should be in path. Also we need to include appropriate header file in example:

#include <sqlite3.h> #include <stdio.h> int main(int argc, char **argv) { int rc, i, col; sqlite3 *db; sqlite3_stmt *stmt; char *sql; rc = sqlite3_open("surogat.db", &db); if(rc != SQLITE_OK) { printf("Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } sql = "select * from sqlite_master"; rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, 0); if(rc != SQLITE_OK) { printf("SQL error: %s\n", sqlite3_errmsg(db)); } rc = sqlite3_step(stmt); col = sqlite3_column_count(stmt); for(i=0; i < col; i++) { printf("%s\t",sqlite3_column_name(stmt, i)); } printf("\n------------------------------------------------------\n"); while(rc == SQLITE_ROW) { for(i=0; i < col; i++) { printf("%s\t", sqlite3_column_text(stmt, i)); } printf("\n"); rc = sqlite3_step(stmt); } sqlite3_finalize(stmt); sqlite3_close(db); return 0; }

We open database file, if it doesn't exist it will create it, if it can't we report error and exit. Next, we prepare statement, using legacy interface, and again check for error. Function sqlite3_step evaluates prepared statement, similarly we retrieve some metadata and print that to standard output.
Before we run example we will create table so that we can show some kind of output from our program. All that in the same folder where is binary, so that we don't have to fiddle with full path.

sqlite3 surogat.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE jane ( id INTEGER PRIMARY KEY NOT NULL, title TEXT, full_name TEXT NOT NULL ); sqlite> .exit

Now we can run our test program and see output like this.

type    name    tbl_name    rootpage    sql   
------------------------------------------------------
table    jane    jane    2    CREATE TABLE jane ( id INTEGER PRIMARY KEY NOT NULL, title TEXT, full_name TEXT NOT NULL )   

That was simple connect to database and select, there will be more in next instalment.