SQLite C 教程 - 《ZetCode 中文系列教程》

SQLite 数据库
sqlite3 工具
C99
SQLite 版本
插入数据
最后插入的行 ID
检索数据
参数化查询
插入图像
读取图像
元数据
事务
- 自动提交
- 事务
数据来源

原文： http://zetcode.com/db/sqlitec/

这是 SQLite 数据库的 C 编程教程。它涵盖了使用 C 语言进行 SQLite 编程的基础。您可能还想查看 ZetCode 上的 SQLite 教程， MySQL C 教程或 PostgreSQL C 教程。

SQLite 数据库

SQLite 是嵌入式关系数据库引擎。它的开发者称其为自包含，无服务器，零配置和事务型 SQL 数据库引擎。它目前非常流行，当今全球使用着数亿册。 SQLite 在 Solaris 10，Mac OS，Android 或 iPhone 中使用。 Qt4 库内置了对 SQLite 以及 Python 和 PHP 的支持。许多流行的应用内部都使用 SQLite，例如 Firefox，Google Chrome 或 Amarok。

sqlite3 工具

sqlite3 工具是 SQLite 库的基于终端的前端。它以交互方式求值查询，并以多种格式显示结果。它也可以在脚本中使用。它具有自己的元命令集，包括.tables，.load，.databases或.dump。要获取所有指令的列表，我们键入.help命令。

现在，我们将使用sqlite3工具创建一个新数据库。

$ sqlite3 test.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

我们为sqlite3 tool提供了一个参数； test.db是数据库名称。这是我们磁盘上的文件。如果存在，则将其打开。如果不是，则创建它。

sqlite> .tables
sqlite> .exit
$ ls
test.db

.tables命令提供test.db数据库中的表的列表。当前没有表。 .exit命令终止sqlite3命令行工具的交互式会话。 ls Unix 命令显示当前工作目录的内容。我们可以看到test.db文件。所有数据将存储在该单个文件中。

C99

本教程使用 C99。对于 GNU C 编译器，我们需要使用-std=c99选项。对于 Windows 用户，强烈建议使用 Pelles C IDE。（MSVC 不支持 C99。）

int rc = sqlite3_open("test.db", &db);

在 C99 中，我们可以将声明与代码混合使用。在较早的 C 程序中，我们需要将这一行分成两行。

SQLite 版本

在第一个代码示例中，我们将获得 SQLite 数据库的版本。

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

int main(void) {

    printf("%s\n", sqlite3_libversion()); 

    return 0;
}

sqlite3_libversion()函数返回指示 SQLite 库的字符串。

#include <sqlite3.h>

该头文件定义了 SQLite 库提供给客户端程序的接口。它包含定义，函数原型和注释。它是 SQLite API 的权威来源。

$ gcc -o version version.c -lsqlite3 -std=c99

我们使用 GNU C 编译器编译程序。

$ ./version
3.8.2

这是示例的输出。

在第二个示例中，我们再次获得 SQLite 数据库的版本。这次我们将使用 SQL 查询。

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

int main(void) {

    sqlite3 *db;
    sqlite3_stmt *res;

    int rc = sqlite3_open(":memory:", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    rc = sqlite3_prepare_v2(db, "SELECT SQLITE_VERSION()", -1, &res, 0);    

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Failed to fetch data: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }    

    rc = sqlite3_step(res);

    if (rc == SQLITE_ROW) {
        printf("%s\n", sqlite3_column_text(res, 0));
    }

    sqlite3_finalize(res);
    sqlite3_close(db);

    return 0;
}

SQLITE_VERSION()查询用于获取 SQLite 库的版本。

sqlite3 *db;

sqlite3结构定义数据库句柄。每个打开的 SQLite 数据库均由数据库句柄表示。

sqlite3_stmt *res;

sqlite3_stmt结构表示单个 SQL 语句。

int rc = sqlite3_open(":memory:", &db);

sqlite3_open()函数打开一个新的数据库连接。它的参数是数据库名称和数据库句柄。 :memory:是一个特殊的数据库名称，使用它可以打开内存数据库。该函数的返回代码指示数据库是否已成功打开。成功建立连接后，将返回SQLITE_OK。

if (rc != SQLITE_OK) {

    fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);

    return 1;
}

如果返回代码指示错误，我们将消息打印到控制台，关闭数据库句柄，然后终止程序。 sqlite3_errmsg()函数返回错误的描述。无论打开时是否发生错误，都应通过将其传递给sqlite3_close()函数来释放与数据库连接句柄关联的资源。

rc = sqlite3_prepare_v2(db, "SELECT SQLITE_VERSION()", -1, &res, 0);

在执行 SQL 语句之前，必须先使用sqlite3_prepare*函数之一将其编译为字节码。（不推荐使用sqlite3_prepare()函数。）

sqlite3_prepare_v2()函数具有五个参数。第一个参数是从sqlite3_open()函数获得的数据库句柄。第二个参数是要编译的 SQL 语句。第三个参数是 SQL 语句的最大长度，以字节为单位。传递 -1 将导致 SQL 字符串被读取到第一个零终止符，即第一个零终止符。根据文档，通过传递所提供的 SQL 字符串的确切字节数，可以获得一些小的性能优势。第四个参数是语句句柄。如果sqlite3_prepare_v2()成功运行，它将指向预编译的语句。最后一个参数是指向 SQL 语句未使用部分的指针。只编译 SQL 字符串的第一条语句，因此该参数指向未编译的内容。我们传递 0，因为参数对我们而言并不重要。

成功时，sqlite3_prepare_v2()返回SQLITE_OK；否则返回错误代码。

if (rc != SQLITE_OK) {

    fprintf(stderr, "Failed to fetch data: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);

    return 1;
}

这是sqlite3_prepare_v2()函数调用的错误处理代码。

rc = sqlite3_step(res);

sqlite3_step()运行 SQL 语句。 SQLITE_ROW返回码表示还有另一行准备就绪。我们的 SQL 语句仅返回一行数据，因此，我们只调用一次此函数。

sqlite3_finalize(res);

sqlite3_finalize()函数破坏预备语句对象。

sqlite3_close(db);

sqlite3_close()函数关闭数据库连接。

插入数据

我们创建一个Cars表并在其中插入几行。

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

int main(void) {

    sqlite3 *db;
    char *err_msg = 0;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    char *sql = "DROP TABLE IF EXISTS Cars;" 
                "CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" 
                "INSERT INTO Cars VALUES(1, 'Audi', 52642);" 
                "INSERT INTO Cars VALUES(2, 'Mercedes', 57127);" 
                "INSERT INTO Cars VALUES(3, 'Skoda', 9000);" 
                "INSERT INTO Cars VALUES(4, 'Volvo', 29000);" 
                "INSERT INTO Cars VALUES(5, 'Bentley', 350000);" 
                "INSERT INTO Cars VALUES(6, 'Citroen', 21000);" 
                "INSERT INTO Cars VALUES(7, 'Hummer', 41400);" 
                "INSERT INTO Cars VALUES(8, 'Volkswagen', 21600);";

    rc = sqlite3_exec(db, sql, 0, 0, &err_msg);

    if (rc != SQLITE_OK ) {

        fprintf(stderr, "SQL error: %s\n", err_msg);

        sqlite3_free(err_msg);        
        sqlite3_close(db);

        return 1;
    } 

    sqlite3_close(db);

    return 0;
}

我们连接到test.db数据库，创建Cars表，然后在创建的表中插入 8 行。

char *err_msg = 0;

如果发生错误，该指针将指向创建的错误消息。

int rc = sqlite3_open("test.db", &db);

与test.db数据库的连接已创建。

char *sql = "DROP TABLE IF EXISTS Cars;" 
            "CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" 
            "INSERT INTO Cars VALUES(1, 'Audi', 52642);" 
            "INSERT INTO Cars VALUES(2, 'Mercedes', 57127);" 
            "INSERT INTO Cars VALUES(3, 'Skoda', 9000);" 
            "INSERT INTO Cars VALUES(4, 'Volvo', 29000);" 
            "INSERT INTO Cars VALUES(5, 'Bentley', 350000);" 
            "INSERT INTO Cars VALUES(6, 'Citroen', 21000);" 
            "INSERT INTO Cars VALUES(7, 'Hummer', 41400);" 
            "INSERT INTO Cars VALUES(8, 'Volkswagen', 21600);";

这些 SQL 语句创建一个Cars表并用数据填充它。语句必须用分号分隔。

rc = sqlite3_exec(db, sql, 0, 0, &err_msg);

sqlite3_exec()函数是sqlite3_prepare_v2()，sqlite3_step()和sqlite3_finalize()的便利包装，它允许应用运行多个 SQL 语句而无需使用大量 C 代码。

该函数的第三个参数是为从求值的 SQL 语句中出来的每个结果行调用的回调函数。第四个参数是回调函数的第一个参数。如果不需要它们，可以将 0 传递给这些参数。

如果发生错误，则最后一个参数指向分配的错误消息。

sqlite3_free(err_msg);

必须使用sqlite3_free()函数调用释放分配的消息字符串。

sqlite> .mode column  
sqlite> .headers on

我们使用sqlite3工具验证写入的数据。首先，我们修改数据在控制台中的显示方式。我们使用列模式并打开标题。

sqlite> SELECT * FROM Cars;
Id          Name        Price     
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600

这是我们已写入Cars表的数据。

最后插入的行 ID

有时，我们需要确定最后插入的行的 ID。为此，我们具有sqlite3_last_insert_rowid()函数。

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

int main(void) {

    sqlite3 *db;
    char *err_msg = 0;

    int rc = sqlite3_open(":memory:", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    char *sql = "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);"
    "INSERT INTO Friends(Name) VALUES ('Tom');"
    "INSERT INTO Friends(Name) VALUES ('Rebecca');"
    "INSERT INTO Friends(Name) VALUES ('Jim');"
    "INSERT INTO Friends(Name) VALUES ('Roger');"
    "INSERT INTO Friends(Name) VALUES ('Robert');";

    rc = sqlite3_exec(db, sql, 0, 0, &err_msg);

    if (rc != SQLITE_OK ) {

        fprintf(stderr, "Failed to create table\n");
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);

    } else {

        fprintf(stdout, "Table Friends created successfully\n");
    }

    int last_id = sqlite3_last_insert_rowid(db);
    printf("The last Id of the inserted row is %d\n", last_id);

    sqlite3_close(db);

    return 0;
}

在内存中创建一个Friends表。其Id列会自动增加。

char *sql = "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);"
"INSERT INTO Friends(Name) VALUES ('Tom');"
"INSERT INTO Friends(Name) VALUES ('Rebecca');"
"INSERT INTO Friends(Name) VALUES ('Jim');"
"INSERT INTO Friends(Name) VALUES ('Roger');"
"INSERT INTO Friends(Name) VALUES ('Robert');";

在 SQLite 中，INTEGER PRIMARY KEY列自动增加。还有一个AUTOINCREMENT关键字。当在INTEGER PRIMARY KEY AUTOINCREMENT中应用时，会使用稍微不同的 ID 创建算法。

使用自动递增的列时，除了自动递增的列（省略了该列）外，我们需要明确声明列名。

int last_id = sqlite3_last_insert_rowid(db);
printf("The last Id of the inserted row is %d\n", last_id);

sqlite3_last_insert_rowid()返回表中最近一次成功插入的行 ID。

$ ./last_row_id 
Table Friends created successfully
The last Id of the inserted row is 5

我们看到了程序的输出。

检索数据

我们已经将一些数据插入test.db数据库。在下面的示例中，我们从数据库中检索数据。

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

int callback(void *, int, char **, char **);

int main(void) {

    sqlite3 *db;
    char *err_msg = 0;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", 
                sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    char *sql = "SELECT * FROM Cars";

    rc = sqlite3_exec(db, sql, callback, 0, &err_msg);

    if (rc != SQLITE_OK ) {

        fprintf(stderr, "Failed to select data\n");
        fprintf(stderr, "SQL error: %s\n", err_msg);

        sqlite3_free(err_msg);
        sqlite3_close(db);

        return 1;
    } 

    sqlite3_close(db);

    return 0;
}

int callback(void *NotUsed, int argc, char **argv, 
                    char **azColName) {

    NotUsed = 0;

    for (int i = 0; i < argc; i++) {

        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }

    printf("\n");

    return 0;
}

我们使用SELECT * FROM Cars SQL 语句从Cars表中检索所有行。

int callback(void *, int, char **, char **);

这是与sqlite3_exec()函数结合使用的回调函数的函数原型。

int rc = sqlite3_open("test.db", &db);

我们连接到test.db数据库。

char *sql = "SELECT * FROM Cars";

在这里，我们定义 SQL 语句以从Cars表中选择所有数据。

rc = sqlite3_exec(db, sql, callback, 0, &err_msg);

sqlite3_exec()函数对 SQL 语句进行赋值。对于从求值的 SQL 语句出来的每个结果行，都会调用其回调函数。

int callback(void *NotUsed, int argc, char **argv, 
                    char **azColName) {

    NotUsed = 0;

    for (int i = 0; i < argc; i++) {

        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }

    printf("\n");

    return 0;
}

回调函数的第一个参数是sqlite3_exec()的第 4 个参数中提供的数据；它通常不被使用。第二个参数是结果中的列数。第三个参数是表示行中字段的字符串数组。最后一个参数是代表列名的字符串数组。

在函数主体中，我们遍历所有列并打印其名称和内容。

$ ./select_all 
Id = 1
Name = Audi
Price = 52642

Id = 2
Name = Mercedes
Price = 57127

Id = 3
Name = Skoda
Price = 9000
...

这是示例的部分输出。

参数化查询

现在我们将提到参数化查询。参数化查询（也称为预准备语句）可提高安全性和性能。当使用参数化查询时，我们使用占位符，而不是直接将值写入语句。

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

int main(void) {

    sqlite3 *db;
    char *err_msg = 0;
    sqlite3_stmt *res;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    char *sql = "SELECT Id, Name FROM Cars WHERE Id = ?";

    rc = sqlite3_prepare_v2(db, sql, -1, &res, 0);

    if (rc == SQLITE_OK) {

        sqlite3_bind_int(res, 1, 3);
    } else {

        fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
    }

    int step = sqlite3_step(res);

    if (step == SQLITE_ROW) {

        printf("%s: ", sqlite3_column_text(res, 0));
        printf("%s\n", sqlite3_column_text(res, 1));

    } 

    sqlite3_finalize(res);
    sqlite3_close(db);

    return 0;
}

在该示例中，问号（？）用作占位符，稍后将其替换为实际值。

char *sql = "SELECT Id, Name FROM Cars WHERE Id = ?";

问号用于为 SQL 查询提供 ID。

rc = sqlite3_prepare_v2(db, sql, -1, &res, 0);

sqlite3_prepare_v2()函数编译 SQL 查询。

sqlite3_bind_int(res, 1, 3);

sqlite3_bind_int()将整数值绑定到预备语句。占位符将替换为整数 3。该函数的第二个参数是要设置的 SQL 参数的索引，第三个参数是要绑定到该参数的值。

int step = sqlite3_step(res);

sqlite3_step()函数求值 SQL 语句。

if (step == SQLITE_ROW) {

    printf("%s: ", sqlite3_column_text(res, 0));
    printf("%s\n", sqlite3_column_text(res, 1));

}

如果有可用的数据行，则可以使用sqlite3_column_text()函数获得两列的值。

$ ./parameterized 
3: Skoda

该示例返回 ID 和汽车的名称。

第二个示例使用带有命名占位符的参数化语句。

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

int main(void) {

    sqlite3 *db;
    char *err_msg = 0;
    sqlite3_stmt *res;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    char *sql = "SELECT Id, Name FROM Cars WHERE Id = @id";

    rc = sqlite3_prepare_v2(db, sql, -1, &res, 0);

    if (rc == SQLITE_OK) {

        int idx = sqlite3_bind_parameter_index(res, "@id");
        int value = 4;
        sqlite3_bind_int(res, idx, value);

    } else {

        fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
    }

    int step = sqlite3_step(res);

    if (step == SQLITE_ROW) {

        printf("%s: ", sqlite3_column_text(res, 0));
        printf("%s\n", sqlite3_column_text(res, 1));

    } 

    sqlite3_finalize(res);
    sqlite3_close(db);

    return 0;
}

我们使用命名的占位符选择汽车的名称和价格。

char *sql = "SELECT Id, Name FROM Cars WHERE Id = @id";

命名的占位符以冒号（:)或符号（@）字符为前缀。

int idx = sqlite3_bind_parameter_index(res, "@id");

sqlite3_bind_parameter_index()函数返回给定名称的 SQL 参数的索引。

插入图像

在本节中，我们将图像插入到 SQLite 数据库中。请注意，有些人反对将图像放入数据库。在这里，我们只展示如何做。我们不讨论是否将图像保存在数据库中的技术问题。

sqlite> CREATE TABLE Images(Id INTEGER PRIMARY KEY, Data BLOB);

对于此示例，我们创建一个名为Images的新表。对于图像，我们使用BLOB数据类型，表示二进制大型对象。

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

int main(int argc, char **argv) {

    FILE *fp = fopen("woman.jpg", "rb");

    if (fp == NULL) {

        fprintf(stderr, "Cannot open image file\n");    

        return 1;
    }

    fseek(fp, 0, SEEK_END);

    if (ferror(fp)) {

        fprintf(stderr, "fseek() failed\n");
        int r = fclose(fp);

        if (r == EOF) {
            fprintf(stderr, "Cannot close file handler\n");          
        }    

        return 1;
    }  

    int flen = ftell(fp);

    if (flen == -1) {

        perror("error occurred");
        int r = fclose(fp);

        if (r == EOF) {
            fprintf(stderr, "Cannot close file handler\n");
        }

        return 1;     
    }

    fseek(fp, 0, SEEK_SET);

    if (ferror(fp)) {

        fprintf(stderr, "fseek() failed\n");
        int r = fclose(fp);

        if (r == EOF) {
            fprintf(stderr, "Cannot close file handler\n");
        }    

        return 1;
    }

    char data[flen+1];

    int size = fread(data, 1, flen, fp);

    if (ferror(fp)) {

        fprintf(stderr, "fread() failed\n");
        int r = fclose(fp);

        if (r == EOF) {
            fprintf(stderr, "Cannot close file handler\n");
        }

        return 1;     
    }

    int r = fclose(fp);

    if (r == EOF) {
        fprintf(stderr, "Cannot close file handler\n");
    }    

    sqlite3 *db;
    char *err_msg = 0;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    sqlite3_stmt *pStmt;

    char *sql = "INSERT INTO Images(Data) VALUES(?)";

    rc = sqlite3_prepare(db, sql, -1, &pStmt, 0);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot prepare statement: %s\n", sqlite3_errmsg(db));

        return 1;
    }    

    sqlite3_bind_blob(pStmt, 1, data, size, SQLITE_STATIC);    

    rc = sqlite3_step(pStmt);

    if (rc != SQLITE_DONE) {

        printf("execution failed: %s", sqlite3_errmsg(db));
    }

    sqlite3_finalize(pStmt);    

    sqlite3_close(db);

    return 0;
}

在此程序中，我们从当前工作目录中读取图像，并将其写入 SQLite test.db数据库的Images表中。

FILE *fp = fopen("woman.jpg", "rb");

if (fp == NULL) {

    fprintf(stderr, "Cannot open image file\n");    

    return 1;
}

我们从文件系统读取二进制数据。我们有一个名为woman.jpg的 JPG 图像。 fopen()函数打开指定的文件以供读取。如果操作失败，它将返回一个指向FILE对象的指针或NULL。

fseek(fp, 0, SEEK_END);

if (ferror(fp)) {

    fprintf(stderr, "fseek() failed\n");
    int r = fclose(fp);

    if (r == EOF) {
        fprintf(stderr, "Cannot close file handler\n");          
    }    

    return 1;
}

我们使用fseek()函数将文件指针移到文件末尾。我们需要确定图像的大小。如果发生错误，则设置错误指示器。我们使用fseek()函数检查指示器。如果发生错误，将关闭打开的文件处理器。

int flen = ftell(fp);

if (flen == -1) {

    perror("error occurred");
    int r = fclose(fp);

    if (r == EOF) {
        fprintf(stderr, "Cannot close file handler\n");
    }

    return 1;     
}

对于二进制流，ftell()函数返回文件开头的字节数，例如图像文件的大小。发生错误时，函数将返回 -1 并设置errno。 perror()函数将errno的值解释为错误消息，并将其打印到标准错误输出流。

char data[flen+1];

该数组将存储图像数据。

int size = fread(data, 1, flen, fp);

fread()函数从文件指针读取数据并将其存储在数据数组中。该函数返回成功读取的元素数。

int r = fclose(fp);

if (r == EOF) {
    fprintf(stderr, "Cannot close file handler\n");
}

读取数据后，我们可以关闭文件处理器。

char *sql = "INSERT INTO Images(Data) VALUES(?)";

该 SQL 语句用于将映像插入数据库。

rc = sqlite3_prepare(db, sql, -1, &pStmt, 0);

SQL 语句已编译。

sqlite3_bind_blob(pStmt, 1, data, size, SQLITE_STATIC);

sqlite3_bind_blob()函数将二进制数据绑定到已编译的语句。 SQLITE_STATIC参数表示指向内容信息的指针是静态的，不需要释放。

rc = sqlite3_step(pStmt);

执行该语句，并将图像写入表中。

读取图像

在本节中，我们将执行相反的操作。我们将从数据库表中读取图像。

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

int main(void) {

    FILE *fp = fopen("woman2.jpg", "wb");

    if (fp == NULL) {

        fprintf(stderr, "Cannot open image file\n");    

        return 1;
    }    

    sqlite3 *db;
    char *err_msg = 0;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    char *sql = "SELECT Data FROM Images WHERE Id = 1";

    sqlite3_stmt *pStmt;
    rc = sqlite3_prepare_v2(db, sql, -1, &pStmt, 0);

    if (rc != SQLITE_OK ) {

        fprintf(stderr, "Failed to prepare statement\n");
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));

        sqlite3_close(db);

        return 1;
    } 

    rc = sqlite3_step(pStmt);

    int bytes = 0;

    if (rc == SQLITE_ROW) {

        bytes = sqlite3_column_bytes(pStmt, 0);
    }

    fwrite(sqlite3_column_blob(pStmt, 0), bytes, 1, fp);

    if (ferror(fp)) {            

        fprintf(stderr, "fwrite() failed\n");

        return 1;      
    }  

    int r = fclose(fp);

    if (r == EOF) {
        fprintf(stderr, "Cannot close file handler\n");
    }       

    rc = sqlite3_finalize(pStmt);   

    sqlite3_close(db);

    return 0;
}

我们从Images表中读取图像数据，并将其写入另一个文件woman2.jpg中。

FILE *fp = fopen("woman2.jpg", "wb");

if (fp == NULL) {

    fprintf(stderr, "Cannot open image file\n");    

    return 1;
}

我们以写入模式打开一个二进制文件。来自数据库的数据被写入文件。

char *sql = "SELECT Data FROM Images WHERE Id = 1";

该 SQL 语句从Images表中选择数据。我们从第一行获取二进制数据。

if (rc == SQLITE_ROW) {

    bytes = sqlite3_column_bytes(pStmt, 0);
}

sqlite3_column_bytes()函数返回BLOB中的字节数。

fwrite(sqlite3_column_blob(pStmt, 0), bytes, 1, fp);

使用fwrite()函数将二进制数据写入文件。 sqlite3_column_blob()函数返回指向所选二进制数据的指针。

if (ferror(fp)) {            

    fprintf(stderr, "fwrite() failed\n");

    return 1;      
}

ferror()函数检查与流相关的错误指示符是否已设置。

元数据

元数据是有关数据库中数据的信息。 SQLite 中的元数据包含有关表和列的信息，我们在其中存储数据。受 SQL 语句影响的行数是元数据。结果集中返回的行数和列数也属于元数据。

可以使用PRAGMA命令获取 SQLite 中的元数据。 SQLite 对象可能具有属性，即元数据。最后，我们还可以通过查询 SQLite 系统sqlite_master表来获取特定的元数据。

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

int callback(void *, int, char **, char **);

int main(void) {

    sqlite3 *db;
    char *err_msg = 0;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", 
                sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    char *sql = "PRAGMA table_info(Cars)";

    rc = sqlite3_exec(db, sql, callback, 0, &err_msg);

    if (rc != SQLITE_OK ) {

        fprintf(stderr, "Failed to select data\n");
        fprintf(stderr, "SQL error: %s\n", err_msg);

        sqlite3_free(err_msg);
        sqlite3_close(db);

        return 1;
    } 

    sqlite3_close(db);

    return 0;
}

int callback(void *NotUsed, int argc, char **argv, 
                    char **azColName) {

    NotUsed = 0;

    for (int i = 0; i < argc; i++) {

        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }

    printf("\n");

    return 0;
}

在此示例中，我们发出PRAGMA table_info(tableName)命令，以获取有关Cars表的一些元数据信息。

char *sql = "PRAGMA table_info(Cars)";

PRAGMA table_info(tableName)命令为Cars表中的每一列返回一行。结果集中的列包括列顺序号，列名称，数据类型，该列是否可以为NULL以及该列的默认值。

$ ./column_names 
cid = 0
name = Id
type = INT
notnull = 0
dflt_value = NULL
pk = 0
...

这是示例的输出。

在与元数据有关的下一个示例中，我们将列出test.db数据库中的所有表。

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

int callback(void *, int, char **, char **);

int main(void) {

    sqlite3 *db;
    char *err_msg = 0;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", 
                sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    char *sql = "SELECT name FROM sqlite_master WHERE type='table'";

    rc = sqlite3_exec(db, sql, callback, 0, &err_msg);

    if (rc != SQLITE_OK ) {

        fprintf(stderr, "Failed to select data\n");
        fprintf(stderr, "SQL error: %s\n", err_msg);

        sqlite3_free(err_msg);
        sqlite3_close(db);

        return 1;
    } 

    sqlite3_close(db);

    return 0;
}

int callback(void *NotUsed, int argc, char **argv, 
                    char **azColName) {

    NotUsed = 0;

    for (int i = 0; i < argc; i++) {

        printf("%s\n", argv[i] ? argv[i] : "NULL");
    }

    return 0;
}

该代码示例将当前数据库中的所有可用表打印到终端。

char *sql = "SELECT name FROM sqlite_master WHERE type='table'";

表名存储在系统sqlite_master表中。

$ ./list_tables 
Cars
Images

这是一个示例输出。

事务

事务是针对一个或多个数据库中数据的数据库操作的基本单位。事务中所有 SQL 语句的影响可以全部提交给数据库，也可以全部回滚。

在 SQLite 中，除SELECT以外的任何命令都将启动隐式事务。同样，在事务中，诸如CREATE TABLE …，VACUUM和PRAGMA之类的命令将在执行之前提交先前的更改。

手动事务以BEGIN TRANSACTION语句开始，并以COMMIT或ROLLBACK语句结束。

SQLite 支持三种非标准事务级别：DEFERRED，IMMEDIATE和EXCLUSIVE。

自动提交

默认情况下，SQLite 版本 3 在自动提交模式下运行。在自动提交模式下，对数据库的所有更改将在与当前数据库连接关联的所有操作完成后立即提交。自动提交模式由BEGIN语句禁用，并由COMMIT或ROLLBACK重新启用。

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

int main() {

    sqlite3 *db;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", 
                sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }    

    printf("Autocommit: %d\n", sqlite3_get_autocommit(db));

    sqlite3_close(db);

    return 0;
}

本示例检查数据库是否处于自动提交模式。

printf("Autocommit: %d\n", sqlite3_get_autocommit(db));

如果数据库未处于自动提交模式，则sqlite3_get_autocommit()函数返回零。如果处于自动提交模式，它将返回非零值。

$ ./get_ac_mode 
Autocommit: 1

该示例确认默认情况下 SQLite 处于自动提交模式。

下一个示例进一步阐明了自动提交模式。在自动提交模式下，每个非SELECT语句都是一个立即提交的小事务。

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

int main(void) {

    sqlite3 *db;
    char *err_msg = 0;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    char *sql = "DROP TABLE IF EXISTS Friends;" 
                "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);" 
                "INSERT INTO Friends(Name) VALUES ('Tom');" 
                "INSERT INTO Friends(Name) VALUES ('Rebecca');" 
                "INSERT INTO Friends(Name) VALUES ('Jim');" 
                "INSERT INTO Friend(Name) VALUES ('Robert');";

    rc = sqlite3_exec(db, sql, 0, 0, &err_msg);

    if (rc != SQLITE_OK ) {

        fprintf(stderr, "SQL error: %s\n", err_msg);

        sqlite3_free(err_msg);        
        sqlite3_close(db);

        return 1;
    } 

    sqlite3_close(db);

    return 0;
}

我们创建Friends表，并尝试用数据填充它。

char *sql = "DROP TABLE IF EXISTS Friends;" 
            "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);" 
            "INSERT INTO Friends(Name) VALUES ('Tom');" 
            "INSERT INTO Friends(Name) VALUES ('Rebecca');" 
            "INSERT INTO Friends(Name) VALUES ('Jim');" 
            "INSERT INTO Friend(Name) VALUES ('Robert');";

最后一条 SQL 语句有一个错误；没有朋友表。

$ ./autocommit 
SQL error: no such table: Friend
$ sqlite3 test.db
sqlite> .tables
Cars     Friends  Images 
sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim

创建表并插入三行。

事务

在下一个示例中，我们将一些 SQL 语句放入事务中。

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

int main(void) {

    sqlite3 *db;
    char *err_msg = 0;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK) {

        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);

        return 1;
    }

    char *sql = "DROP TABLE IF EXISTS Friends;"
                "BEGIN TRANSACTION;" 
                "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);" 
                "INSERT INTO Friends(Name) VALUES ('Tom');" 
                "INSERT INTO Friends(Name) VALUES ('Rebecca');" 
                "INSERT INTO Friends(Name) VALUES ('Jim');" 
                "INSERT INTO Friend(Name) VALUES ('Robert');"
                "COMMIT;";

    rc = sqlite3_exec(db, sql, 0, 0, &err_msg);

    if (rc != SQLITE_OK ) {

        fprintf(stderr, "SQL error: %s\n", err_msg);

        sqlite3_free(err_msg);        
        sqlite3_close(db);

        return 1;
    }

    sqlite3_close(db);

    return 0;
}

我们继续使用Friends表。

char *sql = "DROP TABLE IF EXISTS Friends;"
            "BEGIN TRANSACTION;" 
            "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);" 
            "INSERT INTO Friends(Name) VALUES ('Tom');" 
            "INSERT INTO Friends(Name) VALUES ('Rebecca');" 
            "INSERT INTO Friends(Name) VALUES ('Jim');" 
            "INSERT INTO Friend(Name) VALUES ('Robert');"
            "COMMIT;";

第一个语句删除Friends表（如果存在）。其他语句放在事务内。事务以全有或全无的方式工作。要么什么都不做，要么什么都不做。

sqlite> .tables
Cars    Images

由于最后一条语句有错误，因此将回滚事务，并且不会创建Friends表。

数据来源

SQLite 文档用于创建本教程。

这是 SQLite C 教程。 ZetCode 拥有用于 SQLite Python 的完整电子书：
SQLite Python 电子书。