Official website for Linux User & Developer

Open source programming for beginners

by Kunal Deo

Sometimes even experienced developers can be a bit overwhelmed by Linux’s extensive development capabilities. Sit back and soak up these tips to become a smarter and more productive Linux developer overnight…

Use an embedded SQL database for program data storage
Every program has to store data in one way or the other. Most of the programs depend upon flat file storage or some custom data specification. As the data in these programs grows, maintaining the flat file or custom file storage can become messy. Meet embedded SQL database, SQLite. Now, you may say that using an SQL database could be an overkill for your application. And you would have been correct if we were talking about traditional SQL databases like Oracle, IBM DB2 etc. Embedded SQL databases like SQLite are super-easy to implement. SQLite requires no standalone processes with which the application program communicates.

Instead, the SQLite library is linked directly to the main application and hence becomes an integral part of the application. A lot of modern applications and platforms make use of SQLite as the main data storage engine, such as Amarok, Mozilla Firefox and Rails. SQLite is also popular with embedded devices such as those using Google Android and Apple iOS.
The following C program demonstrates basic SQL operations done using SQLite.

[sourcecode language=”cpp”]#include<stdio.h>
int main(int argc, char** args)
int retcode; //track the return code

// The number of queries to be handled,size of each query and pointer
int q_cnt = 5,q_size = 150,ind = 0;
char **queries = malloc(sizeof(char) * q_cnt * q_size);

// SQL Statements
sqlite3_stmt *statement;

// Database connection handler
sqlite3 *handle;

// Open the database. If it is not available it will be created
retcode = sqlite3_open(“ludDB.sqlite3”,&handle);
printf(“Database connection failed\n”);
return -1;
printf(“Connection successful\n”);

// Create table
char create_table[100] = “CREATE TABLE IF NOT EXISTS mytable (uname TEXT PRIMARY KEY,pass TEXT NOT NULL,activated INTEGER)”;
retcode = sqlite3_exec(handle,create_table,0,0,0);

// Insert few rows
queries[ind++] = “INSERT INTO mytable VALUES(‘Homer’,’mysecret’,1)”;
retcode = sqlite3_exec(handle,queries[ind-1],0,0,0);
queries[ind++] = “INSERT INTO mytable VALUES(‘Bart’,’yoursecret’,0)”;
retcode = sqlite3_exec(handle,queries[ind-1],0,0,0);

// select all the avaiable rows
queries[ind++] = “SELECT * from mytable”;
retcode = sqlite3_prepare_v2(handle,queries[ind-1],-1,&statement,0);
printf(“Selecting data from DB Failed\n”);
return -1;

// Read the number of rows fetched
int cols = sqlite3_column_count(statement);
retcode = sqlite3_step(statement);
if(retcode == SQLITE_ROW)
for(int col=0 ; col<cols;col++)
const char *val = (const char*)sqlite3_column_text(statement,col);
printf(“%s = %s\t”,sqlite3_column_name(statement,col),val);
else if(retcode == SQLITE_DONE)
printf(“All rows fetched\n”);
printf(“Some error encountered\n”);
return -1;
return 0;
To compile this program, you will need to have sqlite3 development libraries installed in your system. If you are using Ubuntu, you can use the following command to install it:
[sourcecode language=”cpp”]$ sudo apt-get install sqlite3
[sourcecode language=”cpp”]$ gcc sqliteprog.c -o sqliteprog -lsqlite3 -std=c99

$ ./sqliteprog
Connection successful
uname = Homer   pass = mysecret activated = 1
uname = Bart    pass = yoursecret       activated = 0
All rows fetched
twitter follow us

Pages: 1 2 3 4 5 6
  • Tell a Friend
  • Follow our Twitter to find out about all the latest Linux news, reviews, previews, interviews, features and a whole more.
    • grep

      Personally I’d hesitate telling a beginning programmer to use the system() function call. But if I mentioned it, I’d at least explain that it could have grave system security implications.

      For instance, the command that the system() function call executes may run at the same privileges as the program containing the system() function call. This could be bad news if your program somehow gets “root” privileges (via the Set-User-ID or Set-Group-ID settings, or otherwise).

      Next, the program containing the system() function call may not execute the command that you think it will. For instance, using your example ( system(“ls -l”) ), if a cracker can somehow get you to execute your program with a bogus executable file called “ls” in the current directory, then what’s actually executed will be what the cracker intended, but not what you expected. Probably not the best example, but you get the idea. Using a full path name in the parameter for the system() function call may help mitigate this problem, at the possible expense of cross compatibility, but not necessarily eliminate it.

      Admittedly, there’s a time and place to use any of the hundreds of Linux function calls, but a good programmer will (or should) be aware of the implications of those that they chose.

    • Pingback: LXer: Open Source programming for beginners - oBlurb()

    • Ken Jennings

      SQL in a C program is a LOT more simple when using a database that provides an embedded SQL compiler. (PostgreSQL for one.)
      Not the end-all-be-all of perfect examples, but here’s a sample:

    • Pingback: Linux User & Developer issue 93 is out now! | Linux User()

    • Pingback: Introduction to Linux / Newbies Guide()

    • Pingback: Linux coding tips for newbies « 0ddn1x: tricks with *nix()

    • Pingback: Nikon D3100 Dslr Reviews()

    • Morgan

      A new book covering the basics: Programming From Scratch by Gary Crandall available here

    • It really is really a good plus practical little bit of info. I’m fulfilled you provided this handy facts along with us. Remember to keep us current this way. Appreciate your spreading.