Page 1 of 1

SQLite in C++ Using SQLite databases in C++ Rate Topic: ***** 1 Votes

#1 TyVeryMuch  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 7
  • View blog
  • Posts: 33
  • Joined: 24-March 07

Posted 26 August 2009 - 03:17 AM

*
POPULAR

What you will need:
-Basic C++ and SQL knowledge
-SQLite (Download)

Introduction
SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process, but instead reads and writes directly to ordinary disk files. This makes SQLite an easy and good solution to store data for your application.

Opening a database
The first thing you need to do, is open a database. If the database does not exist yet, it will be created.
sqlite3 *database;
sqlite3_open("Database.sqlite", &database);

The first argument is the filename, the second is the sqlite3 database handle.
If everything goes right, SQLITE_OK is returned.

Query's
Once the database is opened, you can actually start doing something. The following code shows how:
sqlite3_stmt *statement;

	if(sqlite3_prepare_v2(database, "CREATE TABLE a (b INTEGER, c INTEGER);", -1, &statement, 0) == SQLITE_OK)
	{
		int cols = sqlite3_column_count(statement);
		int result = 0;
		while(true)
		{
			result = sqlite3_step(statement);
			
			if(result == SQLITE_ROW)
			{
				for(int col = 0; col < cols; col++)
				{
					string s = (char*)sqlite3_column_text(statement, col);
					//do something with it
				}
			}
			else
			{
				break;   
			}
		}
	   
		sqlite3_finalize(statement);
	}

The first thing you have to do is prepare the statement using sqlite3_prepare_v2(), if everything goes right, SQLITE_OK will be returned.
Then we actually have to execute the statement using sqlite3_step(). This function will return a value which we will need to determine our next action.
If the query is not supposed to return anything, like with CREATE TABLE and INSERT, we just have to finalize the statement using sqlite3_finalize() to avoid a memory leak. If the query returns colums of data, like with SELECT, the function will return SQLITE_ROW. If that happens, we will need read the data.
First we will need to know the amount of columns it has returned. We can do this using sqlite3_column_count(). Then all we have to do is request the columns using sqlite3_column_text().

Closing the database
Now all we have to do is close the database, so the data is saved.
sqlite3_close(database);


Examples
I have written the following class to make it some simpler:
#include "Database.h"
#include <iostream>

Database::Database(char* filename)
{
	database = NULL;
	open(filename);
}

Database::~Database()
{
}

bool Database::open(char* filename)
{
	if(sqlite3_open(filename, &database) == SQLITE_OK)
		return true;
		
	return false;   
}

vector<vector<string> > Database::query(char* query)
{
	sqlite3_stmt *statement;
	vector<vector<string> > results;

	if(sqlite3_prepare_v2(database, query, -1, &statement, 0) == SQLITE_OK)
	{
		int cols = sqlite3_column_count(statement);
		int result = 0;
		while(true)
		{
			result = sqlite3_step(statement);
			
			if(result == SQLITE_ROW)
			{
				vector<string> values;
				for(int col = 0; col < cols; col++)
				{
					values.push_back((char*)sqlite3_column_text(statement, col));
				}
				results.push_back(values);
			}
			else
			{
				break;   
			}
		}
	   
		sqlite3_finalize(statement);
	}
	
	string error = sqlite3_errmsg(database);
	if(error != "not an error") cout << query << " " << error << endl;
	
	return results;  
}

void Database::close()
{
	sqlite3_close(database);   
}


Header:
#ifndef __DATABASE_H__
#define __DATABASE_H__

#include <string>
#include <vector>
#include <sqlite3.h>

using namespace std;

class Database
{
public:
	Database(char* filename);
	~Database();
	
	bool open(char* filename);
	vector<vector<string> > query(char* query);
	void close();
	
private:
	sqlite3 *database;
};

#endif



And the following piece of code shows how to use it:
#include <iostream>
#include "Database/Database.h"
using namespace std;
Database *db;
db = new Database("Database.sqlite");
db->query("CREATE TABLE a (a INTEGER, b INTEGER);");
db->query("INSERT INTO a VALUES(1, 2);");
db->query("INSERT INTO a VALUES(5, 4);");
vector<vector<string> > result = db->query("SELECT a, b FROM a;");
for(vector<vector<string> >::iterator it = result.begin(); it < result.end(); ++it)
{
	vector<string> row = *it;
	cout << "Values: (A=" << row.at(0) << ", B=" << row.at(1) << ")" << endl;
}
db->close();



I would like to apologize for my english and the possible mistakes the code may contain. I am not a pro, but i like to share things that i i make. Feedback on how to improve this tutorial is welcome.

Is This A Good Question/Topic? 5
  • +

Replies To: SQLite in C++

#2 eker676  Icon User is offline

  • Software Engineer
  • member icon

Reputation: 378
  • View blog
  • Posts: 1,833
  • Joined: 18-April 09

Posted 06 March 2010 - 05:35 PM

Please, do not quote tutorials!
It just makes reading comments harder.

This is a nice tutorial. I haven't tested it so I can't comment on its correctness.
Was This Post Helpful? 0
  • +
  • -

#3 Guest_a1rex2003*


Reputation:

Posted 06 March 2010 - 05:53 PM

I like your query function vector<vector<string> > Database::query(char* query) very much!

Very nice usage of STL + SQLite!!!

However, there is protection needed for the following line:

values.push_back((char*)sqlite3_column_text(statement, col));

In STL you cannot push_back NULL to pointer to string. It will not work! It will produce run-time crash.

In SQLite NULL is a valid value for any column type.

Fix is below:
       if(result == SQLITE_ROW)
       {
          vector<string> values;
          for(int col = 0; col < cols; col++)
          {
            std::string  val;
            char * ptr = (char*)sqlite3_column_text(statement, col);

            if(ptr)
            {
              val = ptr;
            }
            else val = ""; // this can be commented out since std::string  val;
                           // initialize variable 'val' to empty string anyway

            values.push_back(val);  // now we will never push NULL
          }
          results.push_back(values);
       }
       else
       {
          break;  
       }


Sorry for previous empty posting – this posting system is awkward
Regards,
Samuel.

This post has been edited by JackOfAllTrades: 06 March 2010 - 08:16 PM
Reason for edit:: Added code tags. PLEASE, FOR THE LOVE OF ALL...[code]...POST YOUR CODE IN HERE!!!...[/code]

Was This Post Helpful? 4

#4 a1rex2003  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 7
  • Joined: 06-March 10

Posted 07 March 2010 - 08:05 AM

View PostTyVeryMuch, on 26 August 2009 - 04:17 AM, said:

string s = (char*)sqlite3_column_text(statement, col);
	//do something with it


}


Please notice that sqlite3_column_text() can return NULL pointer as a valid value for same columns and:

  string s = NULL; // will crash the program 



Add the protection:

string s;
char *ptr = (char*)sqlite3_column_text(statement, col);
if(ptr) s = ptr;



Regards,
Samuel
Was This Post Helpful? 2
  • +
  • -

#5 wallrock  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 19-July 12

Posted 19 July 2012 - 06:59 AM

Hi there,

i have a question: i`m trying to create in C++ on Linux an app with the support of SQLite but i can`t cause Eclipse gives me an error while i include in the project the Header <sqlite3.h>:

Multiple markers at this line
- fatal error: sqlite3.h: No such file or directory
- Unresolved inclusion: <sqlite3.h>

Do you know a solution for this error?
Thanks a lot.

Wallrock.
Was This Post Helpful? 0
  • +
  • -

#6 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6058
  • View blog
  • Posts: 23,495
  • Joined: 23-August 08

Posted 20 July 2012 - 05:24 AM

View Postwallrock, on 19 July 2012 - 09:59 AM, said:

Hi there,

i have a question: i`m trying to create in C++ on Linux an app with the support of SQLite but i can`t cause Eclipse gives me an error while i include in the project the Header <sqlite3.h>:

Multiple markers at this line
- fatal error: sqlite3.h: No such file or directory
- Unresolved inclusion: <sqlite3.h>

Do you know a solution for this error?
Thanks a lot.

Wallrock.


I don't know Eclipse, but you need to add the path to the SQLite include files to the list of paths to search for include files. You will likely also need to do the same -- in a different location -- for paths to library files. And probably add the library name to a list of libraries to link.

This might be helpful, which was found with a Google search of eclipse c++ +sqlite.
Was This Post Helpful? 0
  • +
  • -

#7 wallrock  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 19-July 12

Posted 20 July 2012 - 09:07 AM

View PostJackOfAllTrades, on 20 July 2012 - 05:24 AM, said:

View Postwallrock, on 19 July 2012 - 09:59 AM, said:

Hi there,

i have a question: i`m trying to create in C++ on Linux an app with the support of SQLite but i can`t cause Eclipse gives me an error while i include in the project the Header <sqlite3.h>:

Multiple markers at this line
- fatal error: sqlite3.h: No such file or directory
- Unresolved inclusion: <sqlite3.h>

Do you know a solution for this error?
Thanks a lot.

Wallrock.


I don't know Eclipse, but you need to add the path to the SQLite include files to the list of paths to search for include files. You will likely also need to do the same -- in a different location -- for paths to library files. And probably add the library name to a list of libraries to link.

This might be helpful, which was found with a Google search of eclipse c++ +sqlite.


Thanks again for the trick, i will find the right way with that.

Regards,

Wallrock

View Postwallrock, on 20 July 2012 - 09:03 AM, said:

View PostJackOfAllTrades, on 20 July 2012 - 05:24 AM, said:

I don't know Eclipse, but you need to add the path to the SQLite include files to the list of paths to search for include files. You will likely also need to do the same -- in a different location -- for paths to library files. And probably add the library name to a list of libraries to link.

This might be helpful, which was found with a Google search of eclipse c++ +sqlite.


Thanks again for the trick, i will find the right way with that.

Regards,

Wallrock


On google i have found the right way to the solution.

oh yeah!!!!

Wallrock
Was This Post Helpful? 0
  • +
  • -

#8 jiapei100  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 08-February 13

Posted 08 February 2013 - 08:53 AM

Fabulous job !!! I love it...
Was This Post Helpful? 0
  • +
  • -

#9 nathanpc  Icon User is offline

  • SCIENCE!
  • member icon

Reputation: 112
  • View blog
  • Posts: 1,171
  • Joined: 31-July 09

Posted 10 May 2013 - 05:03 PM

Great tutorial, thanks very much!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1