News

Open source programming for beginners

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…

OpenSourceDevelopment

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.

@code:sqliteprog.c
[sourcecode language=”cpp”]#include<stdio.h>
#include<sqlite3.h>
#include<stdlib.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);
if(retcode)
{
printf(“Database connection failedn”);
return -1;
}
printf(“Connection successfuln”);

// 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);
if(retcode)
{
printf(“Selecting data from DB Failedn”);
return -1;
}

// Read the number of rows fetched
int cols = sqlite3_column_count(statement);
while(1)
{
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 = %st”,sqlite3_column_name(statement,col),val);
}
printf(“n”);
}
else if(retcode == SQLITE_DONE)
{
printf(“All rows fetchedn”);
break;
}
else
{
printf(“Some error encounteredn”);
return -1;
}
}
sqlite3_close(handle);
return 0;
}
[/sourcecode]
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]
Compile:
[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
[/sourcecode]
[twitter username=”linuxusermag”]

×