Sqlite 简明教程

SQLite - C/C++

在本章中，您将学习如何在 C/C++ 程序中使用 SQLite。

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

Installation

在 C/C++ 程序中开始使用 SQLite 之前，需要确保计算机上已设置好 SQLite 库。您可以查看 SQLite 安装章节以了解安装过程。

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

C/C++ Interface APIs

以下是重要的 C/C SQLite 接口例程，这些例程可满足您使用 C/C 程序处理 SQLite 数据库的要求。如果您正在寻找更复杂的应用程序，则可以查看 SQLite 官方文档。

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 application, then you can look into SQLite official documentation.

Sr.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 to be used by other SQLite routines. If the filename argument is NULL or ':memory:', sqlite3_open() will create an in-memory database in RAM that lasts only for the duration of the session. If the filename is not NULL, sqlite3_open() attempts to open the database file by using its value. If no file by that name exists, sqlite3_open() will open a new database file by that name.

2

sqlite3_exec(sqlite3, const char sql, sqlite_callback, void *data, char **errmsg) This routine provides a quick, easy way to execute SQL commands provided by sql argument which can consist of more than one SQL command. Here, the first argument sqlite3 is an open database object, sqlite_callback is a call back for which data is the 1st argument and errmsg will be returned to capture any error raised by the routine. SQLite3_exec() routine parses and executes every command given in the sql argument until it reaches the end of the string or encounters an error.

3

sqlite3_close(sqlite3)* This routine closes a database connection previously opened by a call to sqlite3_open(). All prepared statements associated with the connection should be finalized prior to closing the connection. If any queries remain that have not been finalized, sqlite3_close() will return SQLITE_BUSY with the error message Unable to close due to unfinalized statements.

Connect To Database

以下 C 代码片段演示了如何连接到现有数据库。如果数据库不存在，则将创建它，最后将返回一个数据库对象。

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 <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));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }
   sqlite3_close(db);
}

现在，让我们来编译并运行上面的程序，在当前目录中创建我们的数据库 test.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 sqlite3
$./a.out
Opened database successfully

如果你打算使用 C++ 源代码，那么可以如下编译代码：

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

$g++ test.c -l sqlite3

此处，我们将我们的程序与 sqlite3 库链接，以向 C 程序提供所需函数。这会在你的目录中创建数据库文件 test.db，并且你会得到以下结果。

Here, we are linking our program with sqlite3 library 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

以下 C 代码段将用于在之前已创建的数据库中创建表：

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

#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));
      return(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;
}

当以上程序编译并且执行时，它会在你的 test.db 中创建 COMPANY 表，而文件的最终列表如下：

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

以下 C 代码段显示了你如何在以上示例中创建的 COMPANY 表中创建记录：

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

#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));
      return(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;
}

当以上程序编译并且执行时，它会在 COMPANY 表中创建给定的记录，并且会显示以下两行：

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

Opened database successfully
Records created successfully

SELECT Operation

在继续使用实际示例来获取记录之前，让我们了解一些关于回调函数的详细信息，而我们在示例中使用了此函数。此回调提供了一种从 SELECT 语句获取结果的方式。它具有以下声明：

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

如果在 sqlite_exec() 例程中以第三个参数的形式提供了以上回调，那么 SQLite 会在执行 SQL 参数内的每个 SELECT 语句时为在其中处理的每个记录调用此回调函数。

If the above callback is provided in sqlite_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.

以下 C 代码段显示了你如何从在以上示例中创建的 COMPANY 表获取并显示记录：

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 <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));
      return(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

以下 C 代码段显示了我们如何使用 UPDATE 语句更新任何记录，然后从 COMPANY 表中获取和显示已更新的记录。

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 <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));
      return(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

以下 C 代码段显示了你如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取和显示其余的记录。

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 <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));
      return(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