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.

#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

Official SQLite Documentation

That’s all Folks! Enjoy :)

About these ads

60 comments on “SQLite with C

  1. [...] blog post on “SQLite with C“. This is enough to make half of the people scratching their heads. C is perceive as a [...]

  2. Juan says:

    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

    • Manish says:

      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

    • ejes says:

      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

  3. Nate says:

    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

  4. vincent says:

    Hey,

    thank you very much for this excellent tutorial. It got me started!

    Cheers,
    Vincent

  5. Waqas says:

    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

    • beet says:

      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.

  6. andres felipe says:

    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

  7. lis says:

    Thanks for your tutorial. I create table and read data very fast.

  8. andres felipe says:

    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!

    • Manish says:

      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

  9. andres felipe says:

    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

  10. Suraj Kamal says:

    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.

    • Manish says:

      Thanks Suraj. This link was very helpful.

    • andres felipe says:

      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??

      • andres felipe says:

        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

  11. andres felipe says:

    HElP!!! PLEASE

  12. GuruPrasad G says:

    Hi Manish.

    could you help me how to create database and how can i search in database

  13. heckler01 says:

    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?

    • heckler01 says:

      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.

  14. Nitin says:

    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!

    • david says:

      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.

  15. Eem says:

    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 ..

  16. Plavuse says:

    Ues, but not everthing black and white, something is gray :)

    Miranda

  17. santosh says:

    i want to know about c program to initiate backup in sqlite

  18. ritesh says:

    Thanks Boss for the code

  19. ritesh says:

    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

  20. Neha says:

    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

  21. 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);

    • Loke Laken says:

      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.

  22. Muska says:

    No i prettry agree with you. author should RTFM.

  23. 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);
    }

  24. Nagaraju J P says:

    Hi. This is Nagaraju J P,

    how can i connect to sqlite database in “c” can u pls tell me

  25. nccamca says:

    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

  26. Naren says:

    Thanks dude Good one.

  27. Tor says:

    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.

  28. Ankit says:

    Is it possible to attach multiple sqlite databases using a C API ?

  29. Rafa says:

    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

  30. tommybee says:

    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

  31. Hruday says:

    Hello,
    How to insert unicode characters in sqlite3?

  32. nepoznata666 says:

    hi Manish thank you for the tutorial and sorry for my stupid question … i have to write C program in linux with sqlite db which program i have to use ? :(

  33. […] main.c: sample code from http://milky.manishsinha.net/2009/03/30/sqlite-with-c/ […]

  34. ettis says:

    Hi manishsinha,

    Thanks for this code. I have written a tutorial using it for Pelles C here:
    http://aptavout.wordpress.com/2013/06/22/getting-started-with-sqlite3-using-pelles-c/

  35. malike says:

    Take a look on a free tool — Valentina Studio. Amazing product! IMO this is the best manager for SQLite for all platforms. http://www.valentina-db.com/en/valentina-studio-overview

  36. Loke Laken says:

    Something wrong with this comment?

  37. Kaklynne says:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s