Starting with SQLite in C++

• Download source code - 11 KB

Introduction

Storing data is an important part of most programs, and before I discovered SQLite, I used to spend a large amount of development time creating file-handling code. About a year ago, I discovered a library called SQLite. This acts as a tiny database you can embed into your program. This not only saves time, but allows you to use SQL to access the correct data.

In this article, I will show how I:

• Downloaded SQLite
• Write a SQLite Handler class
• Write a simple SQLite program that does something
• Download a program which views SQLite databases

Background

There are already a number of tutorials on compiling SQLite into your C++ code, and it is not that hard to do. I am writing this to explain my method and show what tools I use.

Prerequisites for following this tutorial

I have created a CodeProject article that completely shows how I set up the tool chain I use for compiling C++ programs. It is available here: opensource_tool.aspx

Downloaded SQLite

To get SQLite, go to: http://www.sqlite.org/download.html and download the amalgamation. (I downloaded sqlite-amalgamation-3_6_14_2.zip.)

SQLite comes as source files we can compile as part of our build process for our program. To do this, we must set up our project. Launch MSYS and follow these commands:

• cd /c/code/
• mkdir sqlite_hello_world

Extract the contents of the zip file to this directory. (I used 7-zip for this.) We don't need the file sqlite3ext.h, so delete this.

SQLite comes as code that can be compiled directly into applications, so next we should create a makefile and check that we can compile SQLite into an object. (You can copy the command from the webpage and use Shift + Ins to place the command in MSYS.)

• "/c/program files/notepad++/notepad++.exe" /c/code/sqlite_hello_world/makefile
• Paste in the following code, then save and close Notepad++

$(warning Starting Makefile)

CXX=g++

sqlite3.obj: sqlite3.c
    gcc -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1
    
clean: 
    -rm *.obj

Now, select Run -> RUN_CODE from the Notepad++ menu we created in the prerequisite tutorial. If it all went well, you will see something like the following output:

C:\Program Files\Notepad++>c:

C:\Program Files\Notepad++>cd\

C:\>cd c:\code\sqlite_hello_world

C:\code\sqlite_hello_world>make
makefile:1: Starting Makefile
gcc -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1

C:\code\sqlite_hello_world>pause
Press any key to continue . . .

C:\code\sqlite_hello_world>main.exe
'main.exe' is not recognized as an internal or external command,
operable program or batch file.

C:\code\sqlite_hello_world>pause
Press any key to continue . . .

We haven't created a main.exe yet, so don't worry about the error. If you look in the directory, you will see sqlite3.obj.

Note: If you want to find out more about SQLite, the website is http://www.sqlite.org/.

Use the SQLite Handler class

I'm going to cheat a bit now and use some code I have prepared earlier. In this section, I am just going to provide instructions showing how to get the code in place and compiling. In the next section, I am going to dive into the code I have given you and tour you through what it does and why.

There are two sections of code which I am going to use to help me create the SQLite program. Firstly, take the following files out of this articles zip file and place them in the c:\code\sqlite_hello_world directory.

• Glob_Defs.h
• RJMFTime.h
• RJMFTime.cpp
• RJM_SQLite_Resultset.h
• RJM_SQLite_Resultset.cpp

After copying the files, make some changes to the Make file to compile all the objects.

• "/c/program files/notepad++/notepad++.exe" /c/code/sqlite_hello_world/makefile
• Paste in the following code, then save and close Notepad++

$(warning Starting Makefile)

CXX=g++

main.exe: sqlite3.obj RJM_SQLite_Resultset.obj RJMFTime.obj
    $(warning main bit not done)

sqlite3.obj: sqlite3.c
    gcc -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1
    
RJM_SQLite_Resultset.obj: RJM_SQLite_Resultset.cpp 
          RJM_SQLite_Resultset.h Glob_Defs.h RJMFTime.h sqlite3.h
    $(CXX) -c RJM_SQLite_Resultset.cpp -o RJM_SQLite_Resultset.obj

RJMFTime.obj: RJMFTime.cpp RJMFTime.h
    $(CXX) -c RJMFTime.cpp -o RJMFTime.obj
    
clean: 
    -rm *.obj
    -rm *.exe

This new Make file shows some features of Make. When you type Make without any arguments, Make tries to create the first object it finds in the file. In this case, the first section does nothing, since we are not ready to create the .exe yet (we don't have a program). The reason I have put it there is to list its dependencies. This list (sqlite3.obj, RJM_SQLite_Resultset.obj, RJMFTime.obj) tells Make that it needs to create these three files. Instructions on how to create the files are in the makefile program.

Test this by clicking RUN_CODE in the Notepad++ menu we created in the prerequisite article. The output should look something like:

C:\Program Files\Notepad++>c:

C:\Program Files\Notepad++>cd\

C:\>cd C:\code\sqlite_hello_world

C:\code\sqlite_hello_world>make
makefile:1: Starting Makefile
gcc -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1
g++ -c RJM_SQLite_Resultset.cpp -o RJM_SQLite_Resultset.obj
g++ -c RJMFTime.cpp -o RJMFTime.obj
makefile:6: main bit not done

C:\code\sqlite_hello_world>pause
Press any key to continue . . .

C:\code\sqlite_hello_world>main.exe
'main.exe' is not recognized as an internal or external command,
operable program or batch file.

C:\code\sqlite_hello_world>pause
Press any key to continue . . .

Again, ignore the error message.

Explanation of the SQLite Handler class

In this section, we are not going to make any changes to our hello application. Instead, I intend to take you on a tour of the code we copied into place and show what it does, why, and how. We used two separate bits of code, and I will explain each separately.

RJMFTime

This uses the following files:

• RJMFTime.h
• RJMFTime.cpp

I used to program MFC and used its time classes for dealing with storing dates and times. When I moved to code that doesn't rely on propriety stuff, I moved to using Unix time (time_t) instead. The problem with this is it doesn't handle times after 2038, and it is not good for recording dates of birth (it doesn't go back that far). I then created my own Time class and this is it. I created extensive documentation and a testing program (I created a CodeProject article on this some time ago).

Since then I have learnt about GTK+ and glib. This could replace this class. A good idea would be to learn to get this article working and learn how SQLite works, then simply replace RJMFtime with the GTK+ equivalent. My class isn't too bad, and it has been well tested and has some functionality especially designed for working with SQLite times.

SQLite Resultset

This uses the following files:

• Glob_Defs.h
• RJM_SQLite_Resultset.h
• RJM_SQLite_Resultset.cpp

SQLite works on SQL statements and each of these statements returns a result set. The RJMSQLite Resultset class is designed to hold the results and provide translation to data types that the program uses.

Glob Defs explanation

Data types are important to the class, and there is an include file called Glob_Defs.h which includes macro definitions. It has macros for the data types I use (LONG, TIMESTAMP, and VARCHAR). Each type has three definitions to show the data type, the return type, and a default value for initialization. It then includes some macro definitions for standard programming functions, SAFE_DELETE and ASSERT. Finally, some misc functions, including an ID generator. I used this on a distributed database to give random numbers as primary IDs to stop allowing all nodes to generate records that will not clash.

Resultset explanation

The RJM_SQLite_Resultset files contain two classes, RJM_SQLite_ResultsetRow and RJM_SQLite_Resultset. Some points of interest for this file include:

static RJM_SQLite_Resultset* SQL_Execute(const char* cmd, sqlite3* db) {
    RJM_SQLite_Resultset* pRS;
    pRS = new RJM_SQLite_Resultset();
    
    int rc;
    char *zErrMsg = 0;
    rc = sqlite3_exec(db, cmd, sql_callback, pRS, &zErrMsg);
    if( rc!=SQLITE_OK ){
        pRS->SetError(zErrMsg);
        sqlite3_free(zErrMsg);
    }

    return pRS;
};

This is a static function that is called to execute a query. It needs the query string and a pointer to the sqlite3 object which should be setup with an open database. (We will see later how to do this.) You can see that it creates a new instance of the result set class and dispatches it off to SQLite to execute. It passes SQLite reference to a generic callback function as well as a pointer to the newly created class. The callback function is used is in the header file, and is:

static int sql_callback(void *NotUsed, int argc, char **argv, char **azColName){
    RJM_SQLite_Resultset *pRS = (RJM_SQLite_Resultset*) NotUsed;
    return pRS->sql_callback(argc,argv,azColName);
}

I have used a parameter which SQLite lets me have (called NotUsed) to contain a pointer to an instance of my class. I can't call my classes callback function directly. The callback function in the class creates a new instance of the row set class, and puts all the data into the classes it created. This is then put onto the back of the row vector (RJM_RSRowVector m_rvRows) which is simply a list of pointers to all the rows. This way the resultset class holds all the data that SQLite has given us.

Note: This method means that for every call to SQL_Execute, you get a pointer to a newly created result set object. Don't forget to delete this object. Possibly using the SAFE_DELETE macro described earlier.

Create an empty shell program to use this

OK, now we have managed to compile SQLite and my helper classes. We now need to write a program that actually uses them to do something. I am going to build a simple command line application, but this can be built into the GUI and other applications as well. First, let's create a main.cpp file with all the required headers, and compile it to make sure we have got that right:

• "/c/program files/notepad++/notepad++.exe" /c/code/sqlite_hello_world/main.cpp
• Paste in the following code, then save and close Notepad++

#include <iostream>
#include "sqlite3.h"
#include "Glob_Defs.h"


int main( int argc, char *argv[] )
{
    srand ( time(NULL) );
    printf("SQLite Demo program start\n");
    printf("SQLite Demo program end\n");
    return 0;
};

Don't forget the new line at the end of the file, and make sure the <'s and >'s get copied OK.

This is the demo program. We need to adjust the Make file to build it:

• "/c/program files/notepad++/notepad++.exe" /c/code/sqlite_hello_world/makefile
• Paste in the following code, then save and close Notepad++

$(warning Starting Makefile)

CXX=g++

main.exe: sqlite_demo.lib main.cpp
    $(CXX) -s main.cpp -o main.exe -Wl,sqlite_demo.lib

sqlite_demo.lib: sqlite3.obj RJM_SQLite_Resultset.obj RJMFTime.obj
    ar cq $@ RJMFTime.obj
    ar cq $@ RJM_SQLite_Resultset.obj
    ar cq $@ sqlite3.obj
    
sqlite3.obj: sqlite3.c
    $(CXX) -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1
    
RJM_SQLite_Resultset.obj: RJM_SQLite_Resultset.cpp 
               RJM_SQLite_Resultset.h Glob_Defs.h RJMFTime.h sqlite3.h
    $(CXX) -c RJM_SQLite_Resultset.cpp -o RJM_SQLite_Resultset.obj

RJMFTime.obj: RJMFTime.cpp RJMFTime.h
    $(CXX) -c RJMFTime.cpp -o RJMFTime.obj
    
clean: 
    -rm *.obj
    -rm *.exe
    -rm *.lib

As you can see, the Make file has got more complicated. It creates a series of .obj files, and puts these .obj files together into a .lib file. Then, the main application is linked against the lib file. This may seem like overkill at this stage, but one of the benefits of SQLite is you can write lots of different programs which work with the same database. I take this one stage further and split my program development into two parts. Have a backend lib and a front end. I add my own validation code etc., to the standard functions in the backend which gets compiled into the .lib, and then each individual front-end is linked with the .lib file.

Another point with this Make file is that there are .obj, .exe, and .lib files mixed in with your code. I plan to write a CodeProject article to show how we can get these files made in different directories.

Click Run -> Run code, and our basic application will work with all the right bits linked in.

Write a simple program into our shell

I have created a program which goes through a few basic functions with the database and shows error checking code. I was going to put it here and give the usual copy into Notepad instructions, but the file is quite long, so I have put it in the document zip file. Copy main.cpp out of the zip file, and replace the main.cpp you just created. Open it up to look at, and click Run -> Run Code in Notepad++ to execute it. It should compile and run perfectly with the Make file you already have. Every time you run it, you will see more and more records are created in the database. I will go through the main function here, explaining what each piece does.

srand ( time(NULL) );
printf("SQLite Demo program start\n");

Seed the random number generator, this is needed because I will use the rand() function later.

std::string l_filename = "datafile.sqlite";
std::ostringstream l_query;
sqlite3* l_sql_db = NULL;

Declare the variables. We need a string to hold the file name. l_query is a string stream which is useful for building up queries. l_sql_db is going to be a pointer to our SQLite database.

printf("Opening DB\n");
int rc = sqlite3_open(l_filename.c_str(), &l_sql_db);
if( rc ){
    sqlite3_close(l_sql_db);
    printf("Error couldn't open SQLite database %s",l_filename.c_str());
    return 1;
};

Next, we open the database. If there is no file, it will be created; otherwise, the file that is there is used. If an invalid file is selected, this code doesn't cause an error. SQLite will only error when you start running Select queries. I have got into the habit of creating an Info table in all my databases and having my programs check it before continuing. This also allows me to have a data file version and allows the program to deal with different versions of the data file.

RJM_SQLite_Resultset *pRS = NULL;
printf("Checking if table exists\n");
pRS = SQL_Execute("SELECT name FROM sqlite_master " + 
                  "WHERE type='table' and name='simple_table';", l_sql_db);    
if (!pRS->Valid()) {
    printf("Invalid result set returned (%s)\n",pRS->GetLastError());
    SAFE_DELETE(pRS);
    sqlite3_close(l_sql_db);
    return 0;
};
rc = pRS->GetRowCount();
SAFE_DELETE(pRS);

The program I have written only has one table. (You can have multiple tables in your SQLite tables.) This code runs a Select statement which will return a row if simple_table is present, but no rows if it is not there. Before checking how many rows are returned, it checks that the result set object (*pRS) is valid, and errors if it isn't. It then checks the row count.

if (0!=rc) {
    printf("Table exists\n");
} else {

If a row is returned, the table is there and we can skip the next bit. Otherwise, we need to create it before continuing:

printf("Table dosn't exist creating it\n");
l_query.str("");
l_query << "CREATE TABLE [simple_table] (";
l_query << "[ID] INTEGER  NOT NULL PRIMARY KEY,";
l_query << "[some_text] VARCHAR(255)  NULL,";
l_query << "[some_number] INTEGER  NULL,";
l_query << "[created] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL";
l_query << ")";
pRS = SQL_Execute(l_query.str().c_str(), l_sql_db);    
if (!pRS->Valid()) {
    printf("Invalid result set returned (%s)\n",pRS->GetLastError());
    SAFE_DELETE(pRS);
    sqlite3_close(l_sql_db);
    return 0;
};

Now we create the table. Like working with all databases, this is just done by running SQL statements and acting on the returned information. I have used l_query to create the SQL statement.

printf("Checking if table exists\n");
pRS = SQL_Execute("SELECT name FROM sqlite_master WHERE " + 
                  "type='table' and name='simple_table';", l_sql_db);    
if (!pRS->Valid()) {
    printf("Invalid result set returned (%s)\n",pRS->GetLastError());
    SAFE_DELETE(pRS);
    sqlite3_close(l_sql_db);
    return 0;
};
rc = pRS->GetRowCount();
SAFE_DELETE(pRS);
if (0==rc) {
    printf("Error table still dosn't exist despite " + 
           "the fact I created it\n",l_sql_db);
    sqlite3_close(l_sql_db);
    return 0;
};

I have repeated the check for the table to make sure it does exist now.

}; //End If table dosen't exist
SAFE_DELETE(pRS);

This is the end of the Ccreate Table section. I have added an extra SAFE_DELETE which isn't strictly necessary.

printf("Add some data to the table\n");
l_query.str("");
l_query << "insert into simple_table (some_text, some_number)";
l_query << " values ('Some text'," << GEN_ID << ")";
pRS = SQL_Execute(l_query.str().c_str(), l_sql_db);    
if (!pRS->Valid()) {
    printf("Invalid result set returned (%s)\n",pRS->GetLastError());
    SAFE_DELETE(pRS);
    sqlite3_close(l_sql_db);
    return 0;
};
SAFE_DELETE(pRS);

Adding data is just simply executing another SQL query. There is lots of repeated error detection code in this program. In production programs, this can be hidden away in another function.

printf("Listing out some data\n");
l_query.str("");
l_query << "select ID, some_text, some_number, created FROM simple_table";
pRS = SQL_Execute(l_query.str().c_str(), l_sql_db);    
if (!pRS->Valid()) {
    printf("Invalid result set returned (%s)\n",pRS->GetLastError());
    SAFE_DELETE(pRS);
    sqlite3_close(l_sql_db);
    return 0;
};

Yet another query, this time to return some data:

rc = pRS->GetRowCount();
//Declare variables to hold data
DB_DT_LONG r_id;
DB_DT_VARCHAR r_some_text;
DB_DT_LONG r_some_number;
DB_DT_TIMESTAMP r_created;
char buf[1024] = "";

First, get the number of rows returned, then declare the variables we will need to hold the data. This uses the macros declared in Glob_defs.h.

for (unsigned int c=0;c<rc;c++) {

Loop through the rows returned:

pRS->GetColValueINTEGER(c, 0, &r_id);
pRS->GetColValueVARCHAR(c, 1, &r_some_text);
pRS->GetColValueINTEGER(c, 2, &r_some_number);
pRS->GetColValueTIMESTAMP(c, 3, &r_created);

Call functions from the resultset to collect the data. There are different functions for each timestamp. The parameters are the same: the row, the column and a pointer to an object to put the data into.

printf("ID: %d, Text: %s, Num: %d, Created: %s\n", 
       r_id,r_some_text.c_str(),r_some_number,r_created.c_str());

This line outputs the data. DB_DT_LONG can just be placed directly, but DB_DT_VARCHAR needs the .c_str() function. I have created a c_str() in my time object which outputs a default format, but you can use a different function to get a specific format.

};
SAFE_DELETE(pRS);

We now need to delete the resultset object

printf("Closing DB\n");
sqlite3_close(l_sql_db);

printf("SQLite Demo program end\n");
return 0;

Finally, close the database and end the program.

Download a program which views SQLite databases

We can write lots of programs that use the same database and so can other people. I use a tool called SQLite Administrator to view the contents of files my programs create whilst programming and debugging. As you might expect, there are also SQLite ODBC drivers that could provide a great way of getting your data into Excel, or other applications.

Another reason for using SQLite Administrator is that you can use it to test out SQL statements to get them working before putting them into your program's code. To get it, go to http://sqliteadmin.orbmu2k.de/ and download and install the program (link at the bottom of the page).

I extracted the zip files into C:\Program Files\ and put a shortcut on my quick launch bar. To use it, run the program, go to Database -> Open, and select c:\code\sqlite_hello_world\datafile.sqlite.

Enter a query like the following:

select * from simple_table

Then, select Query -> Execute with result. You can use this tool to prototype queries before putting them into your applications. You can even right click on a table and select 'Show SQL' to show the Create statements. This way, you can create objects using this program and copy the create scripts into your code. Not only is this a simple file storage system, but you have all the facilities of a database at your disposal: Tables, Views, etc.

Usage notes

When using Edit Data, you need to click in the table name after you click on the Edit Data tab to get it to refresh. You then need to keep clicking on the table name to refresh the data.

For some reason, if I create the database file in SQLite Administrator, it doesn't work when I access it in my code. As a workaround, I get my program to create the database and tables, and it works fine. The admin program has no problems accessing this data.

Points of interest

There are other advantages to SQLite. I have written various programs that all use the same data file simultaneously. This means I could develop a suite of simple programs for specific tasks that share a common data source.

Your data file naming convention will vary. Sometimes, you might want people to know it's a SQLite file and that they can access it with other programs. Other times, you won't want this. Unfortunately, it is not possible to restrict access to the raw files without purchasing a special version of SQLite libraries which support encrypted files. I recommend my users use TrueCrypt for storing their files as there is no budget for my programming in this area.

This project leaves us with a messy directory. As well as our program code, we have .obj, .lib, and .exe files mixed in. The Make Clean option will clear them up for you, but it would be nice to have them in a separate place. I am planning to create an article showing exactly this.

History

• 22-Jun-2009 - First version.