SQLite - C/C++

Install

Before using SQLite in a C/C++ program, we need to ensure that the SQLite library is already present on the machine. You can refer to the SQLite installation chapter to understand the installation process.

C/C++ Interface API

The following are important C/C++ / SQLite interface routines that can meet your needs of using the SQLite database in C/C++ programs. If you need more details, please refer to the official SQLite documentation.

No.	API & Description
1	*sqlite3_open(const char filename, sqlite3 ppDb) This routine opens a connection to an SQLite database file and returns a database connection object for use by other SQLite routines. Iffilenamethe parameter is NULL or ':memory:', then sqlite3_open() will create an in-memory database in RAM, which will last only for the duration of the session. If the filename parameter is not NULL, then sqlite3_open() will attempt to open the database file using this parameter value. If the file with that name does not exist, sqlite3_open() will create a new database file with that name and open it.
2	*sqlite3_exec(sqlite3, const char sql, sqlite_callback, void data, char errmsg) This routine provides a shortcut for executing SQL commands. The SQL commands are provided by the sql parameter and can consist of multiple SQL commands. Here, the first parametersqlite3is the opened database object,sqlite_callbackis a callback,dataAs its first parameter, errmsg will be returned to retrieve any error generated by the program. The sqlite3_exec() routine parses and executessqleach command given by the parameter, until the end of the string or an error is encountered.
3	*sqlite3_close(sqlite3)** This routine closes the database connection previously opened by calling sqlite3_open(). All statements associated with the connection should be completed before the connection is closed. If there are still unfinished queries, sqlite3_close() will return an error message SQLITE_BUSY forbidding the close.

No.

API & Description

sqlite3_open(const char *filename, sqlite3 **ppDb)

This routine opens a connection to an SQLite database file and returns a database connection object for use by other SQLite routines.

Iffilenamethe parameter is NULL or ':memory:', then sqlite3_open() will create an in-memory database in RAM, which will last only for the duration of the session.

If the filename parameter is not NULL, then sqlite3_open() will attempt to open the database file using this parameter value. If the file with that name does not exist, sqlite3_open() will create a new database file with that name and open it.

sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)

This routine provides a shortcut for executing SQL commands. The SQL commands are provided by the sql parameter and can consist of multiple SQL commands.

Here, the first parametersqlite3is the opened database object,sqlite_callbackis a callback,dataAs its first parameter, errmsg will be returned to retrieve any error generated by the program.

The sqlite3_exec() routine parses and executessqleach command given by the parameter, until the end of the string or an error is encountered.

sqlite3_close(sqlite3*)

This routine closes the database connection previously opened by calling sqlite3_open(). All statements associated with the connection should be completed before the connection is closed.

If there are still unfinished queries, sqlite3_close() will return an error message SQLITE_BUSY forbidding the close.

Connect to Database

The following C code snippet shows how to connect to an existing database. If the database does not exist, it will be created, and finally a database object will be returned.

#include <stdio.h>
#include <sqlite3.h>

int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zErrMsg = 0;
   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(stderr, "Opened database successfully\n");
   }
   sqlite3_close(db);
}

Now, let us compile and run the above program to create our database in the current directorytest.db. You can change the path as needed.

$gcc test.c -l sqlite3
$./a.out
Opened database successfully

If you want to use C++ source code, you can compile the code as shown below:

$g++ test.c -l sqlite3

Here, link our program with the sqlite3 library to provide the necessary functions to the C program. This will create a database file test.db in your directory, and you will get the following result:

-rwxr-xr-x. 1 root root 7383 May  8 02:06 a.out
-rw-r--r--. 1 root root  323 May  8 02:05 test.c
-rw-r--r--. 1 root root    0 May  8 02:06 test.db

Create Table

The following C code snippet will be used to create a table in the database created previously:

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *NotUsed, 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;
}

int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zErrMsg = 0;
   int  rc;
   char *sql;

   /* Open database */
   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");
   }

   /* Create SQL statement */
   sql = "CREATE TABLE COMPANY("  \
         "ID INT PRIMARY KEY     NOT NULL," \
         "NAME           TEXT    NOT NULL," \
         "AGE            INT     NOT NULL," \
         "ADDRESS        CHAR(50)," \
         "SALARY         REAL );";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   if( rc != SQLITE_OK ){
   fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Table created successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

When the above program is compiled and executed, it will create the COMPANY table in the test.db file. The final file list is as follows:

-rwxr-xr-x. 1 root root 9567 May  8 02:31 a.out
-rw-r--r--. 1 root root 1207 May  8 02:31 test.c
-rw-r--r--. 1 root root 3072 May  8 02:31 test.db

INSERT Operation

The following C code snippet shows how to create records in the COMPANY table created above:

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *NotUsed, 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;
}

int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create SQL statement */
   sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Records created successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

When the above program is compiled and executed, it will create the given records in the COMPANY table and will display the following two lines:

Opened database successfully
Records created successfully

SELECT Operation

Before we start explaining the example of retrieving records, let's first understand some details of the callback function, which will be used in our example. This callback provides a way to get results from a SELECT statement. It is declared as follows:

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 */
);

If the above callback is passed as the third parameter in the sqlite_exec() routine, then SQLite will call this callback function for each record processed in each SELECT statement executed within the SQL parameter.

The following C code snippet shows how to retrieve and display records from the COMPANY table created earlier:

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

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 *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create SQL statement */
   sql = "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

When the above program is compiled and executed, it will produce the following result:

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

UPDATE Operation

The following C code snippet shows how to use an UPDATE statement to update any record, and then retrieve and display the updated records from the COMPANY table:

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

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 *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create merged SQL statement */
   sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
         "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

When the above program is compiled and executed, it will produce the following result:

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

DELETE Operation

The following C code snippet shows how to use a DELETE statement to delete any record, and then retrieve and display the remaining records from the COMPANY table:

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

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 *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create merged SQL statement */
   sql = "DELETE from COMPANY where ID=2; " \
         "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

When the above program is compiled and executed, it will produce the following result:

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Other Extensions

SQLite Advanced Tutorial

SQLite Interfaces

SQLite - C/C++

Install

C/C++ Interface API

Connect to Database

Create Table

INSERT Operation

SELECT Operation

UPDATE Operation

DELETE Operation