‘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.
#include<stdio.h>
#include<sqlite3.h>
#include<stdlib.h>
int 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
if(retval)
{
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);
if(retval)
{
printf(“Selecting data from DB Failed\n”);
return -1;
}// 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.
while(1)
{
// 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);
}
printf(“\n”);
}
else if(retval == SQLITE_DONE)
{
// All rows finished
printf(“All rows fetched\n”);
break;
}
else
{
// Some error encountered
printf(“Some error encountered\n”);
return -1;
}
}
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
sqlite3_close(handle);
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
That’s all Folks! Enjoy 
Pingback: Neck deep into technical affairs < Manish’s Scribble Board
when I $ gcc dataman.c -o dataman -l sqlite -std=c99
/tmp/cci1wbEC.o: In function `main’:
dataman.c:(.text+0x6b): undefined reference to `sqlite3_open’
dataman.c:(.text+0x14f): undefined reference to `sqlite3_exec’
dataman.c:(.text+0x1b2): undefined reference to `sqlite3_exec’
dataman.c:(.text+0×215): undefined reference to `sqlite3_exec’
dataman.c:(.text+0x27a): undefined reference to `sqlite3_prepare_v2′
dataman.c:(.text+0x2ac): undefined reference to `sqlite3_column_count’
dataman.c:(.text+0x2c0): undefined reference to `sqlite3_step’
dataman.c:(.text+0x2f1): undefined reference to `sqlite3_column_text’
dataman.c:(.text+0x30f): undefined reference to `sqlite3_column_name’
dataman.c:(.text+0×371): undefined reference to `sqlite3_close’
collect2: ld devolvió el estado de salida 1
You must not have installed the development headers for sqlite. Goto package manager and search for ‘sqlite’ then search for a package which has “dev” or “devel” appended to it.
Install that package and then try.
You can additioanlly try to find the file sqlite3.h in /usr/include
Yes, I thought maybe that was the reason, but isn’t. I have installed the develop libraries.
# find / -iname ‘sqlite3.h’
/usr/include/sqlite3.h
/media/sda2/usr/include/seamonkey-1.1.13/sqlite3/sqlite3.h
the problem persists
Can you find the dynamic libraries also?
ls /usr/lib/libsq*.so
works with -l sqlite3 in gcc command line
you should do:
gcc dataman.c -o dataman -L/usr/local/lib -lsqlite -std=c99
note there isn’t a space after the -l and the -L is to include another directory in your library path
Hi,
I am only in my second semester of uni.. and have an assignment, I want to develop a c program that uses an SQLite database.. problem is I still know so little about SQLite (and I have not learned anything about DBs at Uni).
I have looked at your code and though it is well commented and descriptive, I am still confused (very may I add).
So there any chance I can chat to you via email and ask some questions so I can get a better understanding? Even if it is just pointing me in the direction of somewhere to get a better understanding?
Ok I hope to hear from you..
Nate
Hey,
thank you very much for this excellent tutorial. It got me started!
Cheers,
Vincent
Thanks!
If you are able to go furthur advanced level. Do make a blog post and comment back here so that I can add a link back to your post.
This way people can also be benefited.
one more step:
free queries
Well i tried to run your code but it gives me this error. i am new to programming so dont know much about this.
Error:
error C2440: ‘initializing’ : cannot convert from ‘void *’ to ‘char **’
Can you help me in this regard
waqas,
on ubuntu 10.04 i use
gcc -l sqlite3 -std=c99 basics.c -o basics
and it work. Please look at the parameter -std=c99.
juan,
i had the same problem than you this its the way i can resolve it i hope it stills help you
i compiled my file with this instrution
arm-linux-gcc -o filename filename.c -I include libsqlite3.s0
this file “libsqlite3.s0″ has to be on the folder where im doing my
compilation
Manish,
its a very good material, it was what i was looking for because on sqlite’s page its very difficult to understand the way they explain.
thanks
thanks
Thanks Andres.
I wrote this tutorial since i too faced the problem and hoped that it solves the problem of many people like me.
Thanks for your tutorial. I create table and read data very fast.
HI Manish im using a code in C and i want to store the value of a variable on a fild of a sqlite database, how could be this instrution?
static int abc = 0;
queries[ind++] = “INSERT INTO users VALUES(‘abc’,’manish’,1)”;
retval = sqlite3_exec(handle,queries[ind-1],0,0,0);
i hope you can helpme thanks!
As the first two fields are text, you can store text values only.
static char[] abc = “Hello”
and then
queries[ind++] = string.format(“INSERT INTO users VALUES(%s,’manish’,1)”, abc);
retval = sqlite3_exec(handle,queries[ind-1],0,0,0);
Use string.format in this case. %s is a placeholder for string values which is provided after the first the pattern to string.format
Oh wait. string.format is not meant for C. My bad.
i changed this intruction:
queries[ind++] = “INSERT INTO t1( time, datos ) VALUES ( %a, %d );”,abc, mystr;
but it doesnt work what do i have to change? i think i close to find the answer.. help
Nice post.
Found it much useful.
Anyway some pointers for cross compiling for ARM with static linking (because most of sqlite applications are targeted for embedded applications, other than on x86 platform).
For information about cross compiling sqlite look at
http://www.crosscompile.org/static/pages/SQLite.html
Fetch sqlite amalgamation package
wget http://www.sqlite.org/sqlite-amalgamation-3.6.6.1.tar.gz
Do configure
./configure –host=arm-linux –prefix=/opt/sqlite/ –enable-static –disable-shared –enable-tempstore
make
make install
copy /opt/sqlite/lib contents to your local development directory
Then compile as
arm-linux-gcc -o sqlite-exmpl basics.c -I include libsqlite3.a -std=gnu99 -lpthread -ldl -lpthread -static
The resulting binary will be much larger than dynamically linked one, it will work standalone.
Thanks Suraj. This link was very helpful.
suraj i compiled sqlite , i think it compiled well but i have a problem with the lib directory it made
i dowloaded the package after this i enter this command line:
./configure CROSS_COMPILE=arm-none-linux-gnueabi- ARCH=arm –host=arm-linux –prefix=/opt/sqlitedbms
make and make install
it mades me 3 folder but lib folder has 2 directs links
libsqlite3.so
libsqlite3.s0
how can i fix this??
I forgot i made your second step:
copy /opt/sqlite/lib contents to your local development directory
Then compile as
arm-linux-gcc -o sqlite-exmpl basics.c -I include libsqlite3.a -std=gnu99 -lpthread -ldl -lpthread -static
but when i tried to compiled throwme this:
arm-none-linux-gnueabi-gcc: basics.c: No such file or directory
help
HElP!!! PLEASE
Hi Manish.
could you help me how to create database and how can i search in database
When you close the database, and you get the return value. Like so: “retval = sqlite3_close(handle);”
It returns 5: SQLite3_exec failed: unable to close due to unfinalised statements.
Why do this happen?
You should add “sqlite3_finalize(stmt)” like so:
else if(retval == SQLITE_DONE)
{
// All rows finished
printf(“All rows fetched\n”);
sqlite3_finalize(stmt)
break;
}
That way, if you decide later on to open the database and access another table, you won’t run into any errors.
Thanks. Point noted. I never got such problems
I am using sqlite_exec,_open and sqlite3_prepare_v2
but got problem for sqlite3_prepare_v2
#g++ new.cpp -lsqlite3
getting below problem:
/tmp/ccNNQ8l0.o: In function `main’:
new.cpp:(.text+0x1a0): undefined reference to `sqlite3_prepare_v2′
collect2: ld returned 1 exit status
please help!
Make sure you are using a recent version of the sqlite3 library. Also, make sure you are linking against that versions library. I had two instances of sqlite3 on my computer, one in the default location (/usr/lib) the other in /usr/local/lib. I had to use -L/usr/local/lib in my gcc statement to fix the error you’re getting. Strangely, both directories seemed to have the same version of sqlite3 library (libsqlite3.so.0.8.6) but the one in /usr/local/lib was four times larger in size, so they were obviously not the same. In summary, be sure you are using the version of sqlite3 that you think you’re using and that it is a recent version.
SQLite is very nice serverless database. I love this database rather than another. Anybody out there have an idea about SQLite encryption? I found SQLCiphera is an open source, but have to purchase binary version (http://sqlcipher.sf.net). Compiling the source code seem to be very compicated. Please advice…. thank you ..
Pingback: What’s going on in DeTraS? « DeTraS Project's Blog
Ues, but not everthing black and white, something is gray
Miranda
i want to know about c program to initiate backup in sqlite
Thanks Boss for the code
char query[50];
char abc[20]=”xyz”;
char create_table[100] = “CREATE TABLE IF NOT EXISTS users (uname TEXT PRIMARY KEY)”;
retval = sqlite3_exec(handle,create_table,0,0,0);
sprintf (query, “INSERT INTO users VALUES(‘%s’)”, abc);
queries[ind++] = query;
retval = sqlite3_exec(handle,queries[ind-1],0,0,0);
It works for inserting the variable value which is abc into table ……..Thanks
plz help me in solving this error..
a1.c:49: error: stray ‘\342’ in program
a1.c:49: error: stray ‘\200’ in program
a1.c:49: error: stray ‘\216’ in program
Hello this has being quite helpful tutorial, thanks for making this available. But there is still one more thing, which is when I give a wrong username and password, “retval” still returns 0 which means success (if am correct) which logically should not be. Below is a part of the code am talking about. Pls. your prompt reply will be appreciated.
char query1[60], query2[60];
char username1[20], password1[20], username2[20], password2[20];
printf(“Enter your username and password please…\n”);
scanf(“%s %s”, username1, password1);
// Insert first row and second row
sprintf(query1,”SELECT * from users WHERE uname=’%s’ AND pass=’%s’”, username1, password1);
queries[ind++] = query1;
retval = sqlite3_exec(handle,queries[ind-1],0,0,0);
printf(“The first user and password exist, Hurray !!!\n”);
printf(“retval returns %d\n”,retval);
printf(“Enter your username and password please…\n”);
scanf(“%s %s”, username2, password2);
sprintf(query2,”SELECT * from users WHERE uname=’%s’ AND pass=’%s’”, username2, password2);
queries[ind++] = query2;
retval = sqlite3_exec(handle,queries[ind-1],0,0,0);
printf(“The second user and password exist, Hurray !!!\n”);
printf(“retval returns %d\n”,retval);
The status returned, and here set to retval, is a system status, not an query status.
* retval is set to something else then SQLITE_OK: If query is wrong, ie. you say “SECLETC” instead of “SELECT”, if the database-file can’t be opened, you try to query a table that doesn’t exist and so on and so forth.
http://www.sqlite.org/c3ref/c_abort.html
An empty result-set is not an error.
* I.e: If you say “SELECT name FROM foo WHERE age > 87;” and there is none with age above 87 then the correct “answer” is _none_. Or in other words none is also an answer. None, by that an empty result set, is not an error. It _is_ the correct reply if there is none that meet the criteria you set.
* If you asked for records: “WHERE name LIKE ‘John’; and you found ‘Julia’ amongst the returned names. Now _that_ would be very bad.
No i prettry agree with you. author should RTFM.
char **queries = (char**)malloc(sizeof(char*) * q_cnt);
int x;
for (x = 0; x < q_cnt; x++) {
queries[x] = (char*)malloc(sizeof(char) * q_size);
}
Hi. This is Nagaraju J P,
how can i connect to sqlite database in “c” can u pls tell me
how to get sqllite3.h
can your please tell me from where you get sqllite3.h.
becasue i found it from eevery where even i installed sql lite 3 but still i dont get it can you please tell from from where i get it. thanks
Thanks dude Good one.
Thanks for the example code! Looking closer at the C side of things, I see a major misunderstanding, this is not doing what you think:
char **queries = malloc(sizeof(char) * q_cnt * q_size);
what you try to make, is an array of char pointers, which can be written as
#define MAX 5 // max number of queries
char *queries[MAX]; // pointers to char*
or malloc’ed as
char **queries = malloc(MAX * sizeof(*queries));
if malloc’ed, we need to avoid memory leaks and free that memory. The procedure to allocate room for statement strings, look like this :
#define LEN 150 // one query have length less than 150 bytes
for ( i=0; i < MAX; i++ )
queries[i] = malloc( LEN );
but there is no need to do that, this is perfectly valid code:
char *queries[5];
queries[1] = "INSERT INTO users VALUES('manish','mani',1)";
there is no need to allocate memory for RHS, you just point to that static string which gcc made room for during compilation.
Is it possible to attach multiple sqlite databases using a C API ?
Hi
nice tut so far. The code here:
int q_cnt = 5,q_size = 150,ind = 0;
char **queries = malloc(sizeof(char) * q_cnt * q_size);
is not really needed I think. You are allocating space that would be able to hold all character data you use for your queries below. But you are just storing string-literals, for which you need not allocate memory. So the following should work too, without allocating any memory:
char *queries[5];
This would allow you to store 5 queries as well, without allocating whatever you are allocating.
Your line:
char **queries = malloc(sizeof(char) * q_cnt * q_size);
would actually do the same as
char *queries[150*5*sizeof(char)];
What I am trying to say: You are allocating memory only for base pointer, or in other words: for the first dimension. You do not allocate anything for the second dimension. Your code works because you are storing string literals, so 1 dimension allocated is enough, as string literals are stored in read-only memory and need no allocated space. Hope that was clear, more or less
Otherwise try:
sprintf(queries[1], “somethingnice”);
You will get a segfault, because the memory where string-literals are stored is read-only.
Regards
Thanks in advance
it is really helpful for me to know how the sqlite works!
I compiled it with a little change to your code on the cygwin environment
My sqlite version is sqlite-src-3071602
my error is
basics.c:65:13: note: use option -std=c99 or -std=gnu99 to compile your code
so, I changed the line which is the int definition in the for statements to the very first of which while statment starts like below:
while(1)
{
int col=0 ;
and second is linker commend
gcc dataman.c -o dataman -l sqlite –std=c99
-lsqlite to -lsqlite3
One more thing
I think it would be better adding following line at the end of the main function
if(queries)
{
free(queries);
queries = NULL;
}
Here is my compile options
http://blog.naver.com/tommybee/50169928072
Something wrong with this comment?
Hello this has being quite helpful tuartiol, thanks for making this available. But there is still one more thing, which is when I give a wrong username and password, retval still returns 0 which means success (if am correct) which logically should not be. Below is a part of the code am talking about. Pls. your prompt reply will be appreciated. char query1[60], query2[60];char username1[20], password1[20], username2[20], password2[20]; printf( Enter your username and password please \n ); scanf( %s %s , username1, password1); // Insert first row and second row sprintf(query1, SELECT * from users WHERE uname=’%s’ AND pass=’%s’ , username1, password1); queries[ind++] = query1; retval = sqlite3_exec(handle,queries[ind-1],0,0,0); printf( The first user and password exist, Hurray !!!\n ); printf( retval returns %d\n ,retval); printf( Enter your username and password please \n ); scanf( %s %s , username2, password2); sprintf(query2, SELECT * from users WHERE uname=’%s’ AND pass=’%s’ , username2, password2); queries[ind++] = query2; retval = sqlite3_exec(handle,queries[ind-1],0,0,0); printf( The second user and password exist, Hurray !!!\n ); printf( retval returns %d\n ,retval);