Latest web development tutorials

SQLite - C / C ++

installation

In the C / C ++ program before using SQLite, we need to ensure that there is the SQLite library on the machine. You can view the SQLite installation section for the installation process.

C / C ++ interface API

The following are the important C & C ++ / SQLite interface program to meet your needs using SQLite database in C / C ++ programs. If you need more details, please see the official SQLite documentation.

序号API & 描述
1sqlite3_open(const char *filename, sqlite3 **ppDb)

该例程打开一个指向 SQLite 数据库文件的连接，返回一个用于其他 SQLite 程序的数据库连接对象。

如果filename参数是 NULL 或 ':memory:'，那么 sqlite3_open() 将会在 RAM 中创建一个内存数据库，这只会在 session 的有效时间内持续。

如果文件名 filename 不为 NULL，那么 sqlite3_open() 将使用这个参数值尝试打开数据库文件。如果该名称的文件不存在，sqlite3_open() 将创建一个新的命名为该名称的数据库文件并打开。

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

该例程提供了一个执行 SQL 命令的快捷方式，SQL 命令由 sql 参数提供，可以由多个 SQL 命令组成。

在这里，第一个参数sqlite3是打开的数据库对象，sqlite_callback是一个回调，data作为其第一个参数，errmsg 将被返回用来获取程序生成的任何错误。

sqlite3_exec() 程序解析并执行由sql参数所给的每个命令，直到字符串结束或者遇到错误为止。

3sqlite3_close(sqlite3*)

该例程关闭之前调用 sqlite3_open() 打开的数据库连接。所有与连接相关的语句都应在连接关闭之前完成。

如果还有查询没有完成，sqlite3_close() 将返回 SQLITE_BUSY 禁止关闭的错误消息。

Connect to the database

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

#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 not open database:% s \ n", sqlite3_errmsg (db));
      exit (0);
   } Else {
      fprintf (stderr, "Opened database successfully \ n");
   }
   sqlite3_close (db);
}

Now, let's compile and run the above program, create our databasetest.db in the current directory.You can change the path as needed. 

$ Gcc test.c -l sqlite3
$. / A.out
Opened database successfully

If you want to use the C ++ source code can be compiled in accordance with the following code shows: 

$ G ++ test.c -l sqlite3

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

-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

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

#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 & lt; 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 not 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 creates COMPANY table in test.db file, the final list of files 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 & lt; 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 not 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 in the COMPANY table for a given record, and displays the following two lines: 

Opened database successfully
Records created successfully

SELECT operations

Before we get started explaining documented examples, let us first understand some of the details of the callback function, which will be our example to use. This callback provides the results obtained from a SELECT statement method. 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 as the third parameter in sqlite_exec () procedure, then SQLite SQL for each record within the parameters of each SELECT statement execution process calls the callback function.

The following C code snippet shows how to get from the COMPANY table created earlier and displays the record: 

#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 & lt; 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 not 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 produces 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 the UPDATE statement to update any records, then get from COMPANY table and display the updated record: 

#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 & lt; 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 not 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 produces 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 the DELETE statement deletes any records, and then taken from the COMPANY table and displays the remaining recording: 

#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 not 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 produces 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