SQLite with C

‘C’ has always been my favourite language due to simple facts that it is beautiful and low level in nature. I don’t claim that am a ‘Geek’ in this language, its just my love that pulls me towards it. Let’s have a look at the other languages usuallyย  liked by the public – VB, Java, Perl , Python. All of them may be good in their own ways but C kicks ass. VB?? urgh… Sorry! I vow not to code in it. It’s syntax is very unusual and every Tom,Dick and Harry claims to be a champ of that language.

The biggest problem which I face in C is storing data or in short making data persistent. One way is to write the required to a file on the disk in a fixed format. This stored data can then be read and parsed as per requirement. This approach is good for small amount of data, but what about huge amount of data? You would spend a big share of your time just for structured file I/O. Finally you would land up writing a small module for this work. Why not use any such existing database software for the same? Here comes SQLite for rescue.

I have seen a lot of tutorials on the net, they are very good but none of them suited my needs. The requirement was to explain a sample code line by line. After lots of googling and tea, I managed to make it work! The code snippet which I made is able to create new database if it does not exist, create a table if it does not exist, enter two rows and then fetch those two rows and print them on the screen. Check the code which I have committed the code to my personal google code repository.

Let me explain the code. Sorry for not aligning it. Please download the raw file.


main(int argc, char** args)
// Create an int variable for storing the return code for each call
int retval;

Include stdio.h, sqlite3.h and stdlib.h , stdlib.h is for malloc and sqlite3.h contains the standard function declarations needed for the required functionality.

// 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);

q_cnt stored the number of queries we may want to do, q_size stores the max size of a SQL query, ind is the index.

**queries is a double array or matrix which stores the multiple queries. The total amount of storage to be allocated is sizeof(char) * q_cnt * q_size

// A prepered statement for fetching tables
sqlite3_stmt *stmt;

// Create a handle for database connection, create a pointer to sqlite3
sqlite3 *handle;

// try to create the database. If it doesnt exist, it would be created
// pass a pointer to the pointer to sqlite3, in short sqlite3**
retval = sqlite3_open(“sampledb.sqlite3”,&handle);
// If connection failed, handle returns NULL
printf(“Database connection failed\n”);
return -1;
printf(“Connection successful\n”);

We need to create a pointer to sqlite3 and sqlite3_stmt structures. sqlite3 is the structure which is to hold the database connection handle. sqlite3_stmt is just like a cursor to a database.

sqlite3_open function needs the address of the sqlite3 database instance on the disk. The second parameter is the pointer to the pointer to sqlite3 structure. One mistake which I stumbled upon was to create a sqlite3 ** handle and then pass it to this function. The correct way is to create a sqlite3* handle and then pass the pointer to it using the & operator

// Create the SQL query for creating a table
char create_table[100] = “CREATE TABLE IF NOT EXISTS users (uname TEXT PRIMARY KEY,pass TEXT NOT NULL,activated INTEGER)”;

// Execute the query for creating the table
retval = sqlite3_exec(handle,create_table,0,0,0);

// Insert first row and second row
queries[ind++] = “INSERT INTO users VALUES(‘manish’,’manish’,1)”;
retval = sqlite3_exec(handle,queries[ind-1],0,0,0);
queries[ind++] = “INSERT INTO users VALUES(‘mehul’,’pulsar’,0)”;
retval = sqlite3_exec(handle,queries[ind-1],0,0,0);

Create a table if it does not exist and then insert two rows. Note that sqlite3 does not support inserting two rows in one single query. Maybe I need to confirm this fact again, but I never worked for me ever.

// select those rows from the table
queries[ind++] = “SELECT * from users”;
retval = sqlite3_prepare_v2(handle,queries[ind-1],-1,&stmt,0);
printf(“Selecting data from DB Failed\n”);

// Read the number of rows fetched
int cols = sqlite3_column_count(stmt);

Create a prepared statement for fetching data from the database using sqlite3_prepare_v2 function call. The first parameter is the database handle itself which is a sqlite3* pointer. The second parameter is the SQL statement which needs to be executed. The third parameter tells upto how long the second parameter to be read. Pass -1 to make it read till line terminator. Fourth statement is the pointer to pointer to prepared statement structure. Take care of the pointer concept as I told about sqlite3 structure. The fifth parameter is filled with the unused portion of the query. Have a look at the official documentation.

sqlite3_column_count function gets the number of columns for the result fetched.

// fetch a row’s status
retval = sqlite3_step(stmt);

if(retval == SQLITE_ROW)
// SQLITE_ROW means fetched a row

// sqlite3_column_text returns a const void* , typecast it to const char*
for(int col=0 ; col<cols;col++)
const char *val = (const char*)sqlite3_column_text(stmt,col);
printf(“%s = %s\t”,sqlite3_column_name(stmt,col),val);
else if(retval == SQLITE_DONE)
// All rows finished
printf(“All rows fetched\n”);
// Some error encountered
printf(“Some error encountered\n”);

We have put this code in infinite while loop as we are not sure how much rows it contains. Usually, the table returns n+1 rows, where 1 extra row is for telling that all rows have been fetched. sqlite3_step returns the status which is actually an enumeration. Check all the results contants here. Two most used are SQLITE_DONE, SQLITE_ROW. The former tells that all the rows have been fetched, now the user can come out of this loop and continue. SQLITE_ROW tells that a valid row has been fetched.

// Close the handle to free memory
return 0;

sqlite3_close simply closes the database connection.

Save the code in a file named, say dataman.c , compile it using the command

$ gcc dataman.c -o dataman -l sqlite –std=c99

You obviously need to have sqlite development headers installed for compiling the same. The name of the package on Ubuntu is libsqlite3-dev

Official SQLite Documentation

That’s all Folks! Enjoy ๐Ÿ™‚

Working with libnotify

Usually when using GNOME, you must have come across notification boxes which show near the time area of the upper panel. Ever wondered how it works? It is handled by the library named as libnotify. It has many bindings to be used with various languages, two most prominent ones being C and Python.

The python binding is very very simple, the real problem is using it with C. I searched the whole internet in hope of finding a good tutorial or code example to give us at last. The only help i could get was the documentation at GNOME. After giving it random attempts, finally I managed to hit the eye ๐Ÿ™‚

Here is the code which I have committed to my personal svn dump at Google Code. Have a look at the code,ย  the comments are enough to give you a basic idea how it works. I still recommend you to download the code from link I gave just now instead the one shown below.


int main(int argc, char **argv)
// initialize gtk

char name[40] = “Sample Notification”;

// initiate notify

// create a new notification
NotifyNotification *example;
example = notify_notification_new(name,“Checking it out”,NULL,NULL);

/*ย  Status Icon is not working properly */
// create an icon for the notification

GtkStatusIcon *icon = gtk_status_icon_new_from_stock (GTK_STOCK_YES);
// attach that icon to the notification
notify_notification_attach_to_status_icon (example,icon);

// set the timeout of the notification to 3 secs

// set the category so as to tell what kind it is
char category[30] = “Testing Notifications”;

// set the urgency level of the notification
notify_notification_set_urgency (example,NOTIFY_URGENCY_CRITICAL);

GError *error = NULL;

I am still not able to make the icons show up properly. I need to work again on it, reading the documentation once more. Frankly, GNOME needs to improve its documentation. Just bringing forward the fact that they are volunteers, doesn’t suffice. ๐Ÿ˜ฆ

Yeah, for compiling the above, you need to have the GTK+ development headers installed on your system. It’s called as libgtk2.0-dev on Ubuntu, somewhat similar for other distos. Use this command to compile your program assuming that your program is saved in a file named notify.c

$ gcc `pkg-config –cflags –libs gtk+-2.0` notify.c -o notify -l notify

Make sure that the quote encircling pkg-config is back-ticks not simple quotes. Now run the program,

$ ./notify

and you can see a notification box coming on the right top corner.

Another news is that Ubuntu Jaunty Jackalope 9.04 is going to have new notification system, which is obviously less obstructive and looks better with transparency.

Finally thanks to #pygtk on irc.freenode.net ๐Ÿ™‚ IRC FTW!

Why is Ubuntu the most popular distro?

I just came across a discussion on Reddit why Ubuntu is the most popular distro? Everyone who comes to know about Linux first hears about Ubuntu. Why is it so? Why do newbies feel like home when they try out Ubuntu? Sure! It has its own set of problems and isnt perfect, that doesn’t stop it from making it the leader.

Here is the dicussion going on Reddit (Linux)

The first answer clears the conception, am shamelessly copying the answer so that people can read it here, all credits goto RalfN . I have fixed the typos in the comments.

Go to that link and read the rest of the comments too. It’s really an eye-opener.

Continue reading

Final Semester Project

I have been working on my Engineering Final Semester Project. We are required to make one big project and the time frame alloted is one complete semester! As everyone expects from me, I am using Free Softwares and Open Specifications for my project.

The technologies being used in my project are: PHP, MySQL, Python, SQLite and XMPP for the initial. More technologies can come into picture as and when required. So lets’s start with what am working on? Its a software development platform like Launchpad. Launchpad had their own source control management software(bazaar), then why cant I? My project has sub-sections as Source Control Management resembling a lot with subversion. I have a client and a server module coded in Python.

The data on the server is being stored using MySQL database and the client uses SQLite as database software. Apart from this we also intend to have a issue tracker which will be written in PHP and MySQL running over Apache. All the modules of this Software Development Platform would be tightly coupled with single login and User Access Control.

I am also into thinking that we need to have some IRC kind of real-time chat room where developers can collaborate to work together. Oh Wait! We can use an XMPP bot to keep a watch on the server. XMPP bot can be another frontend to handling your work just from an IM client. Developers should leave status messages related to specific projects so that visitors can know what work is being undertaken by the developers right now.

I am having a gala time working with Python, its an awesome language having short,precise syntax. If you have a good lightweight editor with basic auto-complete, you don’t even have to write anything. ๐Ÿ™‚

I would be posting soon the experiences with Python and SQLite. Keep a watch!