SQL using C/C++ and SQLite
Last Updated :
22 Aug, 2018
In this article, we'd like to introduce the article about
SQLITE combined with
C++ or
C.
Before we go on with this tutorial, we need to follow the
SQLITE3 installation procedure that can be easily found
here. At the same time it is required a basic knowledge of
SQL.
We will show the following operations:
- Database Connection/Creation
- Create Table
- Insert
- Delete
- Select
For the sake of simplicity, let's use a simple database made of just one table.
Database Connection/Creation of Table
In this snippet, we will use two routines contained in
sqlite3.h library.
- sqlite3_open(const char *filename, sqlite3 **ppDb)
- sqlite3_close(sqlite3 *ppDb)
The Compiling is executed by the add of command
-l sqlite3 .
CPP
#include <iostream>
#include <sqlite3.h>
int main(int argc, char** argv)
{
sqlite3* DB;
int exit = 0;
exit = sqlite3_open("example.db", &DB);
if (exit) {
std::cerr << "Error open DB " << sqlite3_errmsg(DB) << std::endl;
return (-1);
}
else
std::cout << "Opened Database Successfully!" << std::endl;
sqlite3_close(DB);
return (0);
}
Output:
$ g++ createDB.cpp -l sqlite3
$ ./a.out
Opened Database Successfully!
$ ls
create.cpp
a.out
example.db
The first routine returns an integer: if the integer is equal to 0, it succeeded. If the database doesn’t exist yet, it is going to be created in the same directory where the process was executed.
The second one just closes the connection previously opened by
SQLITE3_OPEN().
All prepared statement associated with the connection should be finalized prior to closing the connection.
Create Table
In this snippet we will use the routine:
- sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)
CPP
#include <iostream>
#include <sqlite3.h>
int main(int argc, char** argv)
{
sqlite3* DB;
std::string sql = "CREATE TABLE PERSON("
"ID INT PRIMARY KEY NOT NULL, "
"NAME TEXT NOT NULL, "
"SURNAME TEXT NOT NULL, "
"AGE INT NOT NULL, "
"ADDRESS CHAR(50), "
"SALARY REAL );";
int exit = 0;
exit = sqlite3_open("example.db", &DB);
char* messaggeError;
exit = sqlite3_exec(DB, sql.c_str(), NULL, 0, &messaggeError);
if (exit != SQLITE_OK) {
std::cerr << "Error Create Table" << std::endl;
sqlite3_free(messaggeError);
}
else
std::cout << "Table created Successfully" << std::endl;
sqlite3_close(DB);
return (0);
}
Output:
$ g++ createTable.cpp -l sqlite3
$ ./a.out
Table created Successfully
This routine returns an integer: if the integer is equal to the
SQLITE_OK macro, everything worked well.
For this example, the controls about open of Database are omitted, that are written in the past code.
Insert and Delete
We will use the
SQLITE3_EXEC() routine for Insert as well. Procedure and checks are similar to the previous one. There is just one difference in the string that we use with the
SQLITE3_EXEC().
CPP
#include <iostream>
#include <sqlite3.h>
#include <string>
using namespace std;
static int callback(void* data, int argc, char** argv, char** azColName)
{
int i;
fprintf(stderr, "%s: ", (const char*)data);
for (i = 0; i < argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char** argv)
{
sqlite3* DB;
char* messaggeError;
int exit = sqlite3_open("example.db", &DB);
string query = "SELECT * FROM PERSON;";
cout << "STATE OF TABLE BEFORE INSERT" << endl;
sqlite3_exec(DB, query.c_str(), callback, NULL, NULL);
string sql("INSERT INTO PERSON VALUES(1, 'STEVE', 'GATES', 30, 'PALO ALTO', 1000.0);"
"INSERT INTO PERSON VALUES(2, 'BILL', 'ALLEN', 20, 'SEATTLE', 300.22);"
"INSERT INTO PERSON VALUES(3, 'PAUL', 'JOBS', 24, 'SEATTLE', 9900.0);");
exit = sqlite3_exec(DB, sql.c_str(), NULL, 0, &messaggeError);
if (exit != SQLITE_OK) {
std::cerr << "Error Insert" << std::endl;
sqlite3_free(messaggeError);
}
else
std::cout << "Records created Successfully!" << std::endl;
cout << "STATE OF TABLE AFTER INSERT" << endl;
sqlite3_exec(DB, query.c_str(), callback, NULL, NULL);
sql = "DELETE FROM PERSON WHERE ID = 2;";
exit = sqlite3_exec(DB, sql.c_str(), NULL, 0, &messaggeError);
if (exit != SQLITE_OK) {
std::cerr << "Error DELETE" << std::endl;
sqlite3_free(messaggeError);
}
else
std::cout << "Record deleted Successfully!" << std::endl;
cout << "STATE OF TABLE AFTER DELETE OF ELEMENT" << endl;
sqlite3_exec(DB, query.c_str(), callback, NULL, NULL);
sqlite3_close(DB);
return (0);
}
Output:
$ g++ insertDelete.cpp -l sqlite3
$ ./a.out
STATE OF TABLE BEFORE INSERT
Records created Successfully!
STATE OF TABLE AFTER INSERT
ID = 1
NAME = STEVE
SURNAME = GATES
AGE = 30
ADDRESS = PALO ALTO
SALARY = 1000.0
ID = 2
NAME = BILL
SURNAME = ALLEN
AGE = 20
ADDRESS = SEATTLE
SALARY = 300.22
ID = 3
NAME = PAUL
SURNAME = JOBS
AGE = 24
ADDRESS = SEATTLE
SALARY = 9900.0
Record deleted Successfully!
STATE OF TABLE AFTER DELETE OF ELEMENT
ID = 1
NAME = STEVE
SURNAME = GATES
AGE = 30
ADDRESS = PALO ALTO
SALARY = 1000.0
ID = 3
NAME = PAUL
SURNAME = JOBS
AGE = 24
ADDRESS = SEATTLE
SALARY = 9900.0
Select
Before we go on showing the Select operation, that we might just as easily consider the most important of all the tutorial, let’s have a look at the
Callback prototype, that we are going to use in our example.
This operation lets us to get an output from the Select operation:
typedef int (*sqlite3_callback)(
void*, /* Data provided in the 4th argument of sqlite3_exec() */
int, /* The number of columns in row */
char**, /* An array of strings representing fields in the row */
char** /* An array of strings representing column names */
);
Now, we will use the Callback function into the Select as shown in the next snippet:
CPP
#include <iostream>
#include <sqlite3.h>
using namespace std;
static int callback(void* data, int argc, char** argv, char** azColName)
{
int i;
fprintf(stderr, "%s: ", (const char*)data);
for (i = 0; i < argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char** argv)
{
sqlite3* DB;
int exit = 0;
exit = sqlite3_open("example.db", &DB);
string data("CALLBACK FUNCTION");
string sql("SELECT * FROM PERSON;");
if (exit) {
std::cerr << "Error open DB " << sqlite3_errmsg(DB) << std::endl;
return (-1);
}
else
std::cout << "Opened Database Successfully!" << std::endl;
int rc = sqlite3_exec(DB, sql.c_str(), callback, (void*)data.c_str(), NULL);
if (rc != SQLITE_OK)
cerr << "Error SELECT" << endl;
else {
cout << "Operation OK!" << endl;
}
sqlite3_close(DB);
return (0);
}
Output:
$ g++ select.cpp -l sqlite3
$ ./a.out
Opened Database Successfully!
RESULT OF SELECT
ID = 1
NAME = STEVE
SURNAME = GATES
AGE = 30
ADDRESS = PALO ALTO
SALARY = 1000.0
ID = 3
NAME = PAUL
SURNAME = JOBS
AGE = 24
ADDRESS = SEATTLE
SALARY = 9900.0
Operation OK!
This article provides a quick guide to Databases with SQLITE3 using C++/C.
In our examples, we used C++, but when we passed our strings, we called the
c_str() method, in order to convert the string in a character array: this proves that all the routines utilised can be utilised in C as well.
This article was written in collaboration with
Vittorio Triassi student of
Department of Science and Technology University of Naples Parthenope.
Similar Reads
SQL using Python and SQLite | Set 2
Databases offer numerous functionalities by which one can manage large amounts of information easily over the web, and high-volume data input and output over a typical file such as a text file. SQL is a query language and is very popular in databases. Many websites use MySQL. SQLite is a "light" ver
3 min read
Differences between SQL and SQLite
1. Structured Query Language (SQL) : SQL stands for Structured Query Language. SQL can access, created and manage databases. SQL has became standard of American National Standards Institute. 2. SQLite : SQLite is software which provides relational database management system. SQLite lightweight in te
2 min read
Interesting Facts about C++
C++ is a general-purpose, object-oriented programming language. It supports generic programming and low-level memory manipulation. Bjarne Stroustrup (Bell Labs) in 1979, introduced the C-With-Classes, and in 1983 with the C++. Here are some awesome facts about C++ that may interest you: The name of
2 min read
SQL using Python
In this article, integrating SQLite3 with Python is discussed. Here we will discuss all the CRUD operations on the SQLite3 database using Python. CRUD contains four major operations - Note: This needs a basic understanding of SQL. Here, we are going to connect SQLite with Python. Python has a nati
7 min read
SQLite Statements and Syntax
SQLite is a self-contained, file-based SQL database that follows a specific syntax. Statements in SQLite are used to query the database and are essential for database management. Every statement follows a fixed syntax in SQLite. In this guide, we will look at some important SQLite statements and syn
3 min read
SQL Concepts and Queries
In this article, we will discuss the overview of SQL and will mainly focus on Concepts and Queries and will understand each with the help of examples. Let's discuss it one by one.Overview :SQL is a computer language that is used for storing, manipulating, and retrieving data in a structured format.
5 min read
Difference between SQL and T-SQL
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases, enabling operations like querying, updating, and deleting data. T-SQL (Transact-SQL), an extension of SQL developed by Microsoft, adds advanced features and procedural capabilities specifical
4 min read
Avoid Bugs Using Modern C++
C++ has more constructs that can cause undefined behavior or exceptions as compared to languages like Java and Python. This is because C++ was developed with a primary objective to include classes in C and hence, support bug-inviting features such as pointers, macros, etc. It is also devoid of tools
3 min read
How to Connect SQLite3 Database using Node.js ?
Connecting SQLite3 database with Node.js involves a few straightforward steps to set up and interact with the database. SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine, making it ideal for small to medium-sized applications. Here’s how you can connect an
2 min read
SQL using Python | Set 3 (Handling large data)
It is recommended to go through SQL using Python | Set 1 and SQL using Python and SQLite | Set 2 In the previous articles the records of the database were limited to small size and single tuple. This article will explain how to write & fetch large data from the database using module SQLite3 cove
4 min read