Click here to Skip to main content
Click here to Skip to main content

Accessing a SQLite Database with C++

By , 4 May 2012
Rate this:
Please Sign up or sign in to vote.

Introduction

A few days ago I need to move some data without changing the data structure. I have a program which uses this data. So I needed to port it somehow so that its structure will remain the same. I chose to use a SQLite database and a simple C++ console application. I learned how to work with a database using C++.

SQLite files

We need the following files: sqlite3.dll, sqlite3.h, and sqlite3.lib. In your project, include the .lib file and the header file (sqlite3.h).

Implementation

Basically, there are some common operations performed in accessing a database. Let's see in detail the following operations in SQLite:

  • Open/Connect
  • Close/Disconnect
  • Insert Row
  • Update Row
  • Delete Row
  • Select Rows

The SQLite data file name is sea.s3db. Each open SQLite database is represented by a pointer, here it is *dbfile.

...
#define DB "sea.s3db"
// sqlite database pointer 
sqlite3 *dbfile;
...

DB Connection/Disconnection

Open a SQLite database. We can use sqlite3_open() or sqlite3_open16() or sqlite3_open_v2(). sqlite3_open(*db, **handler) takes two parameters. *db is the input database file and **handler is the SQLite database handle. If we successfully open a connection with a database file then the method returns SQLITE_OK otherwise returns a SQLite error code value. You need to do all query operations through an open connection.

...
bool isOpenDB = false;
bool ConnectDB ()
{
    if ( sqlite3_open(DB, &dbfile) == SQLITE_OK )
    {
        isOpenDB = true;
        return true;
    }         

    return false;
}

When your operations finish, tyou can close/disconnect the handle of the SQLite db. sqlite3_close() is the destructor of the sqlite3 object (dbfile). sqlite3_close() returns SQLITE_OK if the object is successfully destroyed and all associated resources are deallocated.

void DisonnectDB ()
{
    if ( isOpenDB == true ) 
    {
        sqlite3_close(dbfile)
    }
}

Now we will try to perform some basic operations (insert, delete, update) in a SQLite database. Let us get to the details.

I. Insert row

Insert data into a SQLite database: We need to call sqlite3_prepare() and also pass the db handler (dbfile), query, and sqlite3_stmt object. If the query being executed returns any data, the result is accessed using sqlite3_step(). Then sqlite3_finalize() is called subsequently. The sqlite3_finalize() function is called to delete a prepared statement. The application must finalize every prepared statement in order to avoid resource leaks.

// Insert a data row into the table 
int addDataRow()
{
    // get the student data from user 
    student stud = getStudent();
    
    std::stringstream strm;
    strm << "insert into student(roll,name,cgpa) values(" << stud.getid() 
         << ",'" << stud.getName() << "'," << stud.getcgpa() << ")";

    string s = strm.str();
    char *str = &s[0];
    
    sqlite3_stmt *statement;
    int result;
    //char *query="insert into student(roll,name,cgpa)values(4,'uuu',6.6)";
    char *query = str;
    {
        if(sqlite3_prepare(dbfile,query,-1,&statement,0)==SQLITE_OK)
        {
            int res=sqlite3_step(statement);
            result=res;
            sqlite3_finalize(statement);
        }
        return result;
    }
    return 0;
}

II. Update row

Updating a row is same as the insert operation, just the executing query changes to an update query.

// update a row 
int updateRow()
{    
    ...
    
    //char *query="update student set cgpa=3.66 where roll=11";
    char *query = str;    

    {
        if(sqlite3_prepare(dbfile,query,-1,&statement,0)==SQLITE_OK)
        {
            int res=sqlite3_step(statement);
            result=res;
            sqlite3_finalize(statement);
        }
        return result;
    }
    return 0;
}

III. Select rows

Now we will try to fetch all table data and show them in the console. Same as the previous example, you need a sqlite3_stmt object, query, and prepare statement. Also we count the number of columns (sqlite3_column_count) in a row and then iterate the rows and columns. Here, a while() loop runs until the sqlite3_step() return code is SQLITE_DONE.

// seelct all data from table 
void getTableData()
{
    sqlite3_stmt *statement;    

    char *query = "select * from student";

    if ( sqlite3_prepare(dbfile, query, -1, &statement, 0 ) == SQLITE_OK ) 
    {
        int ctotal = sqlite3_column_count(statement);
        int res = 0;

        while ( 1 )         
        {
            res = sqlite3_step(statement);

            if ( res == SQLITE_ROW ) 
            {
                for ( int i = 0; i < ctotal; i++ ) 
                {
                    string s = (char*)sqlite3_column_text(statement, i);
                    // print or format the output as you want 
                    cout << s << " " ;
                }
                cout << endl;
            }
            
            if ( res == SQLITE_DONE || res==SQLITE_ERROR)    
            {
                cout << "done " << endl;
                break;
            }    
        }
    }
}

Put it all together

Now you can arrange all these together as you want.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Nikson Kanti Paul
Software Developer
Bangladesh Bangladesh
Point of interest programming and software application development. Always like to know about new technologies and system.

Comments and Discussions

 
GeneralMy vote of 1 Pinmember10BRG30-Oct-13 13:02 
GeneralMy vote of 4 Pinmemberrasteron@live.com15-Oct-13 21:32 
Questionerror with other table Pinmembersr333016-Aug-13 22:06 
AnswerRe: error with other table PinmemberNikson Kanti Paul17-Aug-13 18:58 
GeneralRe: error with other table Pinmembersr333017-Aug-13 19:38 
GeneralMy vote of 2 PinmemberAescleal1-Jun-12 10:53 
GeneralMy vote of 1 Pinmemberjsh_ec4-May-12 17:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 4 May 2012
Article Copyright 2012 by Nikson Kanti Paul
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid