SQLite - C/C++

In this chapter, you will learn how to use SQLite in C/C++ programs.

Installation

Before you start using SQLite in our C/C++ programs, you need to make sure that you have SQLite pbrary set up on the machine. You can check SQLite Installation chapter to understand the installation process.

C/C++ Interface APIs

Following are important C/C++ SQLite interface routines, which can suffice your requirement to work with SQLite database from your C/C++ program. If you are looking for a more sophisticated apppcation, then you can look into SQLite official documentation.

Connect To Database

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

#include <stdio.h>
#include <sqpte3.h> 

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

   rc = sqpte3_open("test.db", &db);

   if( rc ) {
      fprintf(stderr, "Can t open database: %s
", sqpte3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully
");
   }
   sqpte3_close(db);
}

Now, let s compile and run the above program to create our database test.db in the current directory. You can change your path as per your requirement.

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

If you are going to use C++ source code, then you can compile your code as follows −

$g++ test.c -l sqpte3

Here, we are pnking our program with sqpte3 pbrary to provide required functions to C program. This will create a database file test.db in your directory and you will have 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 a Table

Following C code segment will be used to create a table in the previously created database −

#include <stdio.h>
#include <stdpb.h>
#include <sqpte3.h> 

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
   int i;
   for(i = 0; i<argc; i++) {
      printf("%s = %s
", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("
");
   return 0;
}

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

   /* Open database */
   rc = sqpte3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can t open database: %s
", sqpte3_errmsg(db));
      return(0);
   } else {
      fprintf(stdout, "Opened database successfully
");
   }

   /* 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 = sqpte3_exec(db, sql, callback, 0, &zErrMsg);
   
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s
", zErrMsg);
      sqpte3_free(zErrMsg);
   } else {
      fprintf(stdout, "Table created successfully
");
   }
   sqpte3_close(db);
   return 0;
}

When the above program is compiled and executed, it will create COMPANY table in your test.db and the final psting of the file will be 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

Following C code segment shows how you can create records in COMPANY table created in the above example −

#include <stdio.h>
#include <stdpb.h>
#include <sqpte3.h> 

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
   int i;
   for(i = 0; i<argc; i++) {
      printf("%s = %s
", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("
");
   return 0;
}

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

   /* Open database */
   rc = sqpte3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can t open database: %s
", sqpte3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully
");
   }

   /* Create SQL statement */
   sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  
         "VALUES (1,  Paul , 32,  Capfornia , 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 = sqpte3_exec(db, sql, callback, 0, &zErrMsg);
   
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s
", zErrMsg);
      sqpte3_free(zErrMsg);
   } else {
      fprintf(stdout, "Records created successfully
");
   }
   sqpte3_close(db);
   return 0;
}

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

Opened database successfully
Records created successfully

SELECT Operation

Before proceeding with actual example to fetch records, let us look at some detail about the callback function, which we are using in our examples. This callback provides a way to obtain results from SELECT statements. It has the following declaration −

typedef int (*sqpte3_callback)(
   void*,    /* Data provided in the 4th argument of sqpte3_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 provided in sqpte_exec() routine as the third argument, SQLite will call this callback function for each record processed in each SELECT statement executed within the SQL argument.

Following C code segment shows how you can fetch and display records from the COMPANY table created in the above example −

#include <stdio.h>
#include <stdpb.h>
#include <sqpte3.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
", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   
   printf("
");
   return 0;
}

int main(int argc, char* argv[]) {
   sqpte3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqpte3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can t open database: %s
", sqpte3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully
");
   }

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

   /* Execute SQL statement */
   rc = sqpte3_exec(db, sql, callback, (void*)data, &zErrMsg);
   
   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %s
", zErrMsg);
      sqpte3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfully
");
   }
   sqpte3_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 = Capfornia
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

Following C code segment shows how we can use UPDATE statement to update any record and then fetch and display updated records from the COMPANY table.

#include <stdio.h>
#include <stdpb.h>
#include <sqpte3.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
", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("
");
   return 0;
}

int main(int argc, char* argv[]) {
   sqpte3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqpte3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can t open database: %s
", sqpte3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully
");
   }

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

   /* Execute SQL statement */
   rc = sqpte3_exec(db, sql, callback, (void*)data, &zErrMsg);
   
   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %s
", zErrMsg);
      sqpte3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfully
");
   }
   sqpte3_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 = Capfornia
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

Following C code segment shows how you can use DELETE statement to delete any record and then fetch and display the remaining records from the COMPANY table.

#include <stdio.h>
#include <stdpb.h>
#include <sqpte3.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
", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("
");
   return 0;
}

int main(int argc, char* argv[]) {
   sqpte3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqpte3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can t open database: %s
", sqpte3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully
");
   }

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

   /* Execute SQL statement */
   rc = sqpte3_exec(db, sql, callback, (void*)data, &zErrMsg);
   
   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %s
", zErrMsg);
      sqpte3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfully
");
   }
   sqpte3_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 = Capfornia
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