Click here to Skip to main content
12,622,032 members (33,665 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

45.6K views
3K downloads
14 bookmarked
Posted

Accessing a SQLite Database with C++

, 4 May 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
SQLite databases are easy to use and portable. SQLite is a file system database. This example illustrates the basic access of a SQLite databse using C/C++.

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)

Share

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.

You may also be interested in...

Comments and Discussions

 
QuestionHow to get a particular value for a table Pin
VISWESWARAN199825-Apr-16 0:18
professionalVISWESWARAN199825-Apr-16 0:18 
QuestionGood and Simple Pin
Member 1154455821-Mar-15 20:33
memberMember 1154455821-Mar-15 20:33 
GeneralMy vote of 4 Pin
MariuszKa4-Jul-14 4:33
memberMariuszKa4-Jul-14 4:33 
GeneralMy vote of 1 Pin
10BRG30-Oct-13 14:02
member10BRG30-Oct-13 14:02 
GeneralMy vote of 4 Pin
rasteron@live.com15-Oct-13 22:32
memberrasteron@live.com15-Oct-13 22:32 
Questionerror with other table Pin
sr333016-Aug-13 23:06
membersr333016-Aug-13 23:06 
AnswerRe: error with other table Pin
Nikson Kanti Paul17-Aug-13 19:58
memberNikson Kanti Paul17-Aug-13 19:58 
GeneralRe: error with other table Pin
sr333017-Aug-13 20:38
membersr333017-Aug-13 20:38 
GeneralMy vote of 2 Pin
Aescleal1-Jun-12 11:53
memberAescleal1-Jun-12 11:53 
GeneralMy vote of 1 Pin
jsh_ec4-May-12 18:01
memberjsh_ec4-May-12 18:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.161128.1 | Last Updated 4 May 2012
Article Copyright 2012 by Nikson Kanti Paul
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid