Article Index
SQLite Tutorial
Logging All Inserts, Updates, and Deletes
UTC and Localtime
The ATTACH Command
The Power of the Sign Function
Creating a Permanent Sign Function
Spreadsheet Format to Normalized Data
C and C API
A C Program -- Building a Class to Do the Work
Defining SQLite User Functions
Aggregate Functions
Reading Images (Blob data)

Simple C Program

The following is a simple C program, simplesqlite3.c, which will open a database and execute a SQL string.

#include
#include
#include


static int callback(void *NotUsed, int argc, char **argv, char **azColName){
NotUsed=0;
int i;
for(i=0; i
printf("%s = %sn", azColName[i], argv[i] ? argv[i]: "NULL");
}
printf("n");
return 0;
}

int main(int argc, char **argv){
sqlite3 *db;
char *zErrMsg = 0;
int rc;

if( argc!=3 ){
fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENTn", argv[0]);
exit(1);
}
rc = sqlite3_open(argv[1], &db);
if( rc ){
fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %sn", zErrMsg);
/* This will free zErrMsg if assigned */
if (zErrMsg)
free(zErrMsg);
}
sqlite3_close(db);
return 0;
}

The command to compile and run the program is shown below. Note the "-Wl,-R/usr/local/lib" options, which will be needed if you installed the sqlite3 source, since the path "/usr/local/lib" may not be listed in your "/etc/ld.so.conf" file.

gcc -o simplesqlite3 simplesqlite3.c -Wall -W -O 2 -Wl,-R/usr/local/lib -lsqlite3

You either have to use the compile option above or add the directory where the sqlite3 library "libsqlite3.so" is installed to the file "/etc/ld.so.conf", then run ldconfig from the shell. I prefer to use the "-Wl,-R" option instead, but there are the steps.

$ locate libsqlite3.so

/usr/local/lib/libsqlite3.so.0.8.6

/usr/local/lib/libsqlite3.so.0

/usr/local/lib/libsqlite3.so <--- note directory is /usr/local/lib

$ echo "/usr/local/lib" >> /etc/ld.so.conf

$ ldconfig

After you have entered and compiled the program, it will run as follows:

$ ./simplesqlite3 test.db "create table notes (t text)"

$ ./simplesqlite3 test.db "insert into notes (t) values ('

> This is some random

> stuff to add'

>);"

$ ./simplesqlite3 test.db "select * from notes"

t =

This is some random

stuff to add

There are really only three important statements, sqlite3_open(), which takes the name of the database and a database pointer, sqlite3_exec(), which executes the SQL commands in argv[2] and lists the callback function used to display the results, and sqlite3_close(), which closes the database connection.