SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.
For more information about this library, visit their official website.
You can find SRombauts’ C++ SQLite3 wrapper library at sqlite/sqlite and plain SQLite is at fenix/sqlite.
This example uses SQLiteC to create a database called LIST in which stores information about your shopping list. Take a deep look into the code in order to understand how it works and make your own one soon!
Create a new project with a simple layout and place the code inside.
$ bii init sqlite_basic -L
$ cd sqlite_basic
$ # create shopping_db.cpp and copy its content
shopping_db.cpp
#include <stdlib.h>
#include <sqlite3.h>
#include <string>
#include <stdio.h>
using namespace std;
static int select_callback(void *data, int argc, char **argv, char **azColName){
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
void execute_sql(sqlite3 *db, string zSql, sqlite3_callback xCallback, void *pArg){
int rc;
char *pzErrMsg = 0;
rc = sqlite3_exec(db, zSql.c_str(), xCallback, pArg, &pzErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", pzErrMsg);
sqlite3_free(pzErrMsg);
}
}
void connect(sqlite3 **db){
int rc;
rc = sqlite3_open("test.db", db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(*db));
exit(0);
}else{
fprintf(stdout, "Opened database successfully\n");
}
}
int main(int argc, char* argv[])
{
sqlite3 *db;
sqlite3_callback void_callback;
string query;
/* Open database */
connect(&db);
query = "CREATE TABLE IF NOT EXISTS LIST (STORE CHAR(50),NAME TEXT NOT NULL UNIQUE, NUMBER INT);";
execute_sql(db, query, void_callback, 0);
query = "INSERT OR REPLACE INTO LIST (STORE, NAME, NUMBER) VALUES ('Veggies', 'Spinach', 3);"\
"INSERT OR REPLACE INTO LIST (NUMBER, STORE, NAME) VALUES (7,'Drinks', 'Coffee');"\
"INSERT OR REPLACE INTO LIST (NAME, STORE, NUMBER) VALUES ('Onion', 'Veggies', 1);";
execute_sql(db, query, void_callback, 0);
printf("\nSELECT, List Veggies\n\n");
query = "SELECT * from LIST where STORE='Veggies'";
execute_sql(db, query, select_callback, 0);
printf("\nSELECT, List Drinks\n\n");
query = "SELECT * from LIST where STORE='Drinks'";
execute_sql(db, query, select_callback, 0);
query = "UPDATE LIST set NUMBER = 2 where NAME='Coffee'";
execute_sql(db, query, void_callback, 0);
query = "DELETE from LIST where NAME='Spinach';";
execute_sql(db, query, void_callback, 0);
printf("\nSELECT, Updated Lists:\n\n");
query = "SELECT * from LIST";
execute_sql(db, query, select_callback, 0);
/* Close database */
sqlite3_close(db);
fprintf(stdout, "Closed database successfully\n");
return 0;
}
Check the dependencies of the project with bii deps:
$ bii deps
INFO: Processing changes...
your_user/sqlite_basic depends on:
system:
stdio.h
stdlib.h
string
unresolved:
sqlite3.h
Edit the biicode.conf file generated in the project folder. Add your [requirements]
depending on the version you want and map your [includes]
:
[requirements]
sqlite/sqlite: 8
[includes]
sqlite3.h: sqlite/sqlite/sqlite3
Check again with bii deps to show all dependencies are now resolved.
Build the shopping_db.cpp and execute it.
$ bii build
$ cd bin
$ # execute it!
You can see the results of the queries at the output:
SELECT, List Veggies
STORE = Veggies
NAME = Spinach
NUMBER = 3
STORE = Veggies
NAME = Onion
NUMBER = 1
SELECT, List Drinks
STORE = Drinks
NAME = Coffee
NUMBER = 7
SELECT, Updated Lists:
STORE = Drinks
NAME = Coffee
NUMBER = 2
STORE = Veggies
NAME = Onion
NUMBER = 1
Closed database successfully
The following example from SRombauts, explains how-to use the SQLite++ wrapper. Following the previous example, we’ll develop this in the same project’s folder.
Jus place main.cpp, example.db3 and logo.png files inside:
sqlite_basic/main.cpp
/**
* @file main.cpp
* @brief A few short examples in a row.
*
* Demonstrate how-to use the SQLite++ wrapper
*
* Copyright (c) 2012-2014 Sebastien Rombauts (sebastien.rombauts@gmail.com)
*
* Distributed under the MIT License (MIT) (See accompanying file LICENSE.txt
* or copy at http://opensource.org/licenses/MIT)
*/
#include <iostream>
#include <cstdio>
#include <cstdlib>
#include <cstdlib>
#include <SQLiteCpp/SQLiteCpp.h>
#ifdef SQLITECPP_ENABLE_ASSERT_HANDLER
namespace SQLite
{
/// definition of the assertion handler enabled when SQLITECPP_ENABLE_ASSERT_HANDLER is defined in the project (CMakeList.txt)
void assertion_failed(const char* apFile, const long apLine, const char* apFunc, const char* apExpr, const char* apMsg)
{
// Print a message to the standard error output stream, and abort the program.
std::cerr << apFile << ":" << apLine << ":" << " error: assertion failed (" << apExpr << ") in " << apFunc << "() with message \"" << apMsg << "\"\n";
std::abort();
}
}
#endif
/// Example Database
static const char* filename_example_db3 = "examples/sqlite_basic/example.db3"; //NOTE "examples" should be your user
/// Image
static const char* filename_logo_png = "examples/sqlite_basic/logo.png"; //NOTE "examples" should be your user
/// Object Oriented Basic example
class Example
{
public:
// Constructor
Example() :
mDb(filename_example_db3), // Open a database file in readonly mode
mQuery(mDb, "SELECT * FROM test WHERE weight > :min_weight")// Compile a SQL query, containing one parameter (index 1)
{
}
virtual ~Example()
{
}
/// List the rows where the "weight" column is greater than the provided aParamValue
void ListGreaterThan (const int aParamValue)
{
std::cout << "ListGreaterThan (" << aParamValue << ")\n";
// Bind the integer value provided to the first parameter of the SQL query
mQuery.bind(":min_weight", aParamValue); // same as mQuery.bind(1, aParamValue);
// Loop to execute the query step by step, to get one a row of results at a time
while (mQuery.executeStep())
{
std::cout << "row (" << mQuery.getColumn(0) << ", \"" << mQuery.getColumn(1) << "\", " << mQuery.getColumn(2) << ")\n";
}
// Reset the query to be able to use it again later
mQuery.reset();
}
private:
SQLite::Database mDb; ///< Database connection
SQLite::Statement mQuery; ///< Database prepared SQL query
};
int main ()
{
// Basic example (1/6) :
try
{
// Open a database file in readonly mode
SQLite::Database db(filename_example_db3); // SQLITE_OPEN_READONLY
std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n";
// Test if the 'test' table exists
bool bExists = db.tableExists("test");
std::cout << "SQLite table 'test' exists=" << bExists << "\n";
// Get a single value result with an easy to use shortcut
std::string value = db.execAndGet("SELECT value FROM test WHERE id=2");
std::cout << "execAndGet=" << value.c_str() << std::endl;
// Compile a SQL query, containing one parameter (index 1)
SQLite::Statement query(db, "SELECT id as test_id, value as test_val, weight as test_weight FROM test WHERE weight > ?");
std::cout << "SQLite statement '" << query.getQuery().c_str() << "' compiled (" << query.getColumnCount () << " columns in the result)\n";
// Bind the integer value 2 to the first parameter of the SQL query
query.bind(1, 2);
std::cout << "binded with integer value '2' :\n";
// Loop to execute the query step by step, to get one a row of results at a time
while (query.executeStep())
{
// Demonstrate how to get some typed column value (and the equivalent explicit call)
int id = query.getColumn(0); // = query.getColumn(0).getInt()
//const char* pvalue = query.getColumn(1); // = query.getColumn(1).getText()
std::string value2 = query.getColumn(1); // = query.getColumn(1).getText()
int bytes = query.getColumn(1).getBytes();
double weight = query.getColumn(2); // = query.getColumn(2).getInt()
static bool bFirst = true;
if (bFirst)
{
// Show how to get the aliased names of the result columns.
std::string name0 = query.getColumn(0).getName();
std::string name1 = query.getColumn(1).getName();
std::string name2 = query.getColumn(2).getName();
std::cout << "aliased result [\"" << name0.c_str() << "\", \"" << name1.c_str() << "\", \"" << name2.c_str() << "\"]\n";
#ifdef SQLITE_ENABLE_COLUMN_METADATA
// Show how to get origin names of the table columns from which theses result columns come from.
// Requires the SQLITE_ENABLE_COLUMN_METADATA preprocessor macro to be
// also defined at compile times of the SQLite library itself.
name0 = query.getColumn(0).getOriginName();
name1 = query.getColumn(1).getOriginName();
name2 = query.getColumn(2).getOriginName();
std::cout << "origin table 'test' [\"" << name0.c_str() << "\", \"" << name1.c_str() << "\", \"" << name2.c_str() << "\"]\n";
#endif
bFirst = false;
}
std::cout << "row (" << id << ", \"" << value2.c_str() << "\" " << bytes << " bytes, " << weight << ")\n";
}
// Reset the query to use it again
query.reset();
std::cout << "SQLite statement '" << query.getQuery().c_str() << "' reseted (" << query.getColumnCount () << " columns in the result)\n";
// Bind the string value "6" to the first parameter of the SQL query
query.bind(1, "6");
std::cout << "binded with string value \"6\" :\n";
while (query.executeStep())
{
// Demonstrate that inserting column value in a std:ostream is natural
std::cout << "row (" << query.getColumn(0) << ", \"" << query.getColumn(1) << "\", " << query.getColumn(2) << ")\n";
}
}
catch (std::exception& e)
{
std::cout << "SQLite exception: " << e.what() << std::endl;
return EXIT_FAILURE; // unexpected error : exit the example program
}
////////////////////////////////////////////////////////////////////////////
// Object Oriented Basic example (2/6) :
try
{
// Open the database and compile the query
Example example;
// Demonstrate the way to use the same query with different parameter values
example.ListGreaterThan(8);
example.ListGreaterThan(6);
example.ListGreaterThan(2);
}
catch (std::exception& e)
{
std::cout << "SQLite exception: " << e.what() << std::endl;
return EXIT_FAILURE; // unexpected error : exit the example program
}
// The execAndGet wrapper example (3/6) :
try
{
// Open a database file in readonly mode
SQLite::Database db(filename_example_db3); // SQLITE_OPEN_READONLY
std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n";
// WARNING: Be very careful with this dangerous method: you have to
// make a COPY OF THE result, else it will be destroy before the next line
// (when the underlying temporary Statement and Column objects are destroyed)
std::string value = db.execAndGet("SELECT value FROM test WHERE id=2");
std::cout << "execAndGet=" << value.c_str() << std::endl;
}
catch (std::exception& e)
{
std::cout << "SQLite exception: " << e.what() << std::endl;
return EXIT_FAILURE; // unexpected error : exit the example program
}
////////////////////////////////////////////////////////////////////////////
// Simple batch queries example (4/6) :
try
{
// Open a database file in create/write mode
SQLite::Database db("test.db3", SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE);
std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n";
// Create a new table with an explicit "id" column aliasing the underlying rowid
db.exec("DROP TABLE IF EXISTS test");
db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)");
// first row
int nb = db.exec("INSERT INTO test VALUES (NULL, \"test\")");
std::cout << "INSERT INTO test VALUES (NULL, \"test\")\", returned " << nb << std::endl;
// second row
nb = db.exec("INSERT INTO test VALUES (NULL, \"second\")");
std::cout << "INSERT INTO test VALUES (NULL, \"second\")\", returned " << nb << std::endl;
// update the second row
nb = db.exec("UPDATE test SET value=\"second-updated\" WHERE id='2'");
std::cout << "UPDATE test SET value=\"second-updated\" WHERE id='2', returned " << nb << std::endl;
// Check the results : expect two row of result
SQLite::Statement query(db, "SELECT * FROM test");
std::cout << "SELECT * FROM test :\n";
while (query.executeStep())
{
std::cout << "row (" << query.getColumn(0) << ", \"" << query.getColumn(1) << "\")\n";
}
db.exec("DROP TABLE test");
}
catch (std::exception& e)
{
std::cout << "SQLite exception: " << e.what() << std::endl;
return EXIT_FAILURE; // unexpected error : exit the example program
}
remove("test.db3");
////////////////////////////////////////////////////////////////////////////
// RAII transaction example (5/6) :
try
{
// Open a database file in create/write mode
SQLite::Database db("transaction.db3", SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE);
std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n";
db.exec("DROP TABLE IF EXISTS test");
// Exemple of a successful transaction :
try
{
// Begin transaction
SQLite::Transaction transaction(db);
db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)");
int nb = db.exec("INSERT INTO test VALUES (NULL, \"test\")");
std::cout << "INSERT INTO test VALUES (NULL, \"test\")\", returned " << nb << std::endl;
// Commit transaction
transaction.commit();
}
catch (std::exception& e)
{
std::cout << "SQLite exception: " << e.what() << std::endl;
return EXIT_FAILURE; // unexpected error : exit the example program
}
// Exemple of a rollbacked transaction :
try
{
// Begin transaction
SQLite::Transaction transaction(db);
int nb = db.exec("INSERT INTO test VALUES (NULL, \"second\")");
std::cout << "INSERT INTO test VALUES (NULL, \"second\")\", returned " << nb << std::endl;
nb = db.exec("INSERT INTO test ObviousError");
std::cout << "INSERT INTO test \"error\", returned " << nb << std::endl;
return EXIT_FAILURE; // unexpected success : exit the example program
// Commit transaction
transaction.commit();
}
catch (std::exception& e)
{
std::cout << "SQLite exception: " << e.what() << std::endl;
// expected error, see above
}
// Check the results (expect only one row of result, as the second one has been rollbacked by the error)
SQLite::Statement query(db, "SELECT * FROM test");
std::cout << "SELECT * FROM test :\n";
while (query.executeStep())
{
std::cout << "row (" << query.getColumn(0) << ", \"" << query.getColumn(1) << "\")\n";
}
}
catch (std::exception& e)
{
std::cout << "SQLite exception: " << e.what() << std::endl;
return EXIT_FAILURE; // unexpected error : exit the example program
}
remove("transaction.db3");
////////////////////////////////////////////////////////////////////////////
// Binary blob and in-memory database example (6/6) :
try
{
// Open a database file in create/write mode
SQLite::Database db(":memory:", SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE);
std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n";
db.exec("DROP TABLE IF EXISTS test");
db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value BLOB)");
FILE* fp = fopen(filename_logo_png, "rb");
if (NULL != fp)
{
char buffer[16*1024];
void* blob = &buffer;
int size = static_cast<int>(fread(blob, 1, 16*1024, fp));
buffer[size] = '\0';
fclose (fp);
std::cout << "blob size=" << size << " :\n";
// Insert query
SQLite::Statement query(db, "INSERT INTO test VALUES (NULL, ?)");
// Bind the blob value to the first parameter of the SQL query
query.bind(1, blob, size);
std::cout << "blob binded successfully\n";
// Execute the one-step query to insert the blob
int nb = query.exec ();
std::cout << "INSERT INTO test VALUES (NULL, ?)\", returned " << nb << std::endl;
}
else
{
std::cout << "file " << filename_logo_png << " not found !\n";
return EXIT_FAILURE; // unexpected error : exit the example program
}
fp = fopen("out.png", "wb");
if (NULL != fp)
{
const void* blob = NULL;
size_t size;
SQLite::Statement query(db, "SELECT * FROM test");
std::cout << "SELECT * FROM test :\n";
if (query.executeStep())
{
SQLite::Column colBlob = query.getColumn(1);
blob = colBlob.getBlob ();
size = colBlob.getBytes ();
std::cout << "row (" << query.getColumn(0) << ", size=" << size << ")\n";
size_t sizew = fwrite(blob, 1, size, fp);
SQLITECPP_ASSERT(sizew == size, "fwrite failed"); // See SQLITECPP_ENABLE_ASSERT_HANDLER
fclose (fp);
}
}
else
{
std::cout << "file out.png not created !\n";
return EXIT_FAILURE; // unexpected error : exit the example program
}
}
catch (std::exception& e)
{
std::cout << "SQLite exception: " << e.what() << std::endl;
return EXIT_FAILURE; // unexpected error : exit the example program
}
remove("out.png");
std::cout << "everything ok, quitting\n";
return EXIT_SUCCESS;
}
Download: example.db3
, logo.png
.
Check again with bii deps and edit the biicode.conf file.
[requirements]
sqlite/sqlite: 8
[includes]
sqlite3.h: sqlite/sqlite/sqlite3
SQLiteCpp/*: sqlite/sqlite/include
[data]
main.cpp + examle.db3 logo.png
Compile it and run the executable by doing:
$ bii build
$ cd bin
$ # run executable
You will see next console output:
SQLite database file 'examples/sqlite/example.db3' opened successfully
SQLite table 'test' exists=1
execAndGet=second line
SQLite statement 'SELECT id as test_id, value as test_val, weight as test_weight FROM test WHERE weight > ?' compiled (3 columns in the result)
binded with integer value '2' :
aliased result ["test_id", "test_val", "test_weight"]
row (1, "first word" 10 bytes, 2.3)
row (2, "second line" 11 bytes, 6.7)
row (3, "and a last one" 14 bytes, 9.5)
row (4, "" 0 bytes, 18)
...
This examples are already in biicode at examples/sqlite_basic and examples/sqlite.
This is a way to give them a quick look and check how it works.
Both examples are simple to run, just open the blocks and build them like this:
$ bii init sqlite_project
$ cd sqlite_project
$ bii open examples/sqlite_basic
$ bii open examples/sqlite
$ bii build
Any doubts? Do not hesitate to contact us visit our forum and feel free to ask any questions.