SQLite C/C++ API 完整指南：從安裝到實戰範例

環境建置：三步走安裝 SQLite

./configure        # 查看參數：./configure --help
make
make install

安裝後請確認 include、lib、bin 目錄位置，以便後續編譯連結。

核心 API 快速查閱

Callback 函數原型

typedef int (*sqlite3_callback)(
    void*,        // 使用者自訂資料（sqlite3_exec 第 4 參數）
    int,          // 該行欄位數
    char**,       // 欄位值陣列
    char**        // 欄位名稱陣列
);

完整範例：建表、插入、查詢

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

#define DB_FILENAME "/home/user/sqlite/db/sqlite.db"

static int callback(void *userData, int colNum, char **colVal, char **colName) {
    static int headerPrinted = 0;
    if (userData && !headerPrinted++) {
        printf("---------%s-------\n", (const char*)userData);
        for (int i = 0; i < colNum; ++i) printf("%s\t", colName[i]);
        printf("\n");
    }
    for (int i = 0; i < colNum; ++i) 
        printf("%s\t", colVal[i] ? colVal[i] : "NULL");
    printf("\n");
    return 0;
}

int main() {
    sqlite3 *db;
    char *errMsg = 0;
    int rc = sqlite3_open(DB_FILENAME, &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }
    printf("Opened database successfully\n");

    const char *sql = 
        "CREATE TABLE COMPANY("
        "ID INT PRIMARY KEY NOT NULL,"
        "NAME TEXT, AGE INT, ADDRESS CHAR(50), SALARY REAL);"
        "INSERT INTO COMPANY VALUES(1,'Paul',32,'California',20000.00);"
        "INSERT INTO COMPANY VALUES(2,'Allen',25,'Texas',15000.00);"
        "INSERT INTO COMPANY VALUES(3,'Teddy',23,'Norway',20000.00);"
        "INSERT INTO COMPANY VALUES(4,'Mark',25,'Rich-Mond',65000.00);"
        "SELECT * FROM COMPANY;";

    rc = sqlite3_exec(db, sql, callback, (void*)"SELECT * FROM COMPANY", &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        printf("Operation done successfully\n");
    }
    sqlite3_close(db);
    return 0;
}

對應 Makefile

CC = gcc
CFLAGS = -g -Wall -O2 -I /home/user/sqlite/include
LDFLAGS = -L /home/user/sqlite/lib -lsqlite3
PROG = testsqlite
SRCS = testsqlite.c
OBJS = $(SRCS:.c=.o)

all: $(PROG)

$(PROG): $(OBJS)
	$(CC) -o $@ $^ $(LDFLAGS)

%.o: %.c
	$(CC) $(CFLAGS) -c $<

clean:
	rm -f $(OBJS) $(PROG) core *~

執行結果示例

Opened database successfully
Table created successfully
Records created successfully
---------SELECT * FROM COMPANY;-------
ID	NAME	AGE	ADDRESS	SALARY
1	Paul	32	California	20000.0
2	Allen	25	Texas	15000.0
3	Teddy	23	Norway	20000.0
4	Mark	25	Rich-Mond	65000.0
Select records successfully

常見坑點提醒

• SQL 字串中單引號需轉義或使用參數化查詢（sqlite3_prepare_v2 + sqlite3_bind）防止注入。
• Callback 回傳非零值會中止後續查詢。
• 多執行緒環境下需啟用 SQLITE_THREADSAFE 並妥善管理連線。