Using C Language with Database Programming Interfaces

In modern application development, data storage and management are indispensable parts. The C language, as a low-level programming language, rarely interacts directly with databases, but through various APIs (Application Programming Interfaces), we can achieve this functionality. In this chapter, we will delve into how to interact with databases using the C language, primarily using SQLite as an example. SQLite is a lightweight embedded database that is easy to use, making it very suitable for beginners.

Introduction to SQLite

SQLite is a widely used open-source embedded relational database that does not require complex server configurations and can store data directly in the file system. Therefore, for learning C language and database programming interfaces, SQLite is an ideal choice.

Environment Setup

To use SQLite on your computer, you need to complete the following steps:

1. Download and install the SQLite library.
2. Include the header file <span>sqlite3.h</span> in your code.
3. Link <span>sqlite3.lib</span> (Windows) or <span>libsqlite3.a</span> (Linux).

Ensure that your development environment is set up correctly and can successfully include and link the SQLite library.

Overview of Basic Operations

Database operations using the C language generally involve the following steps:

1. Open/Create Database
2. Create Table
3. Insert Data
4. Query Data
5. Update Data
6. Delete Data
7. Close Database

Next, we will implement each of the above steps and provide corresponding example code.

1. Open/Create Database

First, we need to open our SQLite database. If the file does not exist, a new database.db file will be created automatically.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int main() {
    sqlite3 *db;
    int exit = sqlite3_open("database.db", &db);
    if (exit) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return -1;
    } else {
        printf("Opened database successfully.\n");
    }
    // Close connection
    sqlite3_close(db);
    return 0;
}

2. Create Table

Once we have opened our SQLite database, we can execute SQL commands. In this example, we will create a table named <span>users</span> to store user information:

void createTable(sqlite3 *db) {
    char *errorMessage;
    const char *sql = "CREATE TABLE IF NOT EXISTS users("
                      "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "name TEXT NOT NULL,"
                      "age INTEGER NOT NULL);";
    int exit = sqlite3_exec(db, sql, NULL, 0, &errorMessage);
    if (exit != SQLITE_OK) {
        fprintf(stderr, "Error creating table: %s\n", errorMessage);
        sqlite3_free(errorMessage);
    } else {
        printf("Table created successfully.\n");
    }
}

3. Insert Data

We can use SQL INSERT statements to add records to the table. Here is an example of inserting records:

void insertData(sqlite3 *db) {
    char *errorMessage;
    const char *sql = "INSERT INTO users (name, age) VALUES ('Alice', 30);"
                     "INSERT INTO users (name, age) VALUES ('Bob', 25);";
    int exit = sqlite3_exec(db, sql, NULL, 0, &errorMessage);
    if (exit != SQLITE_OK) {
        fprintf(stderr,"Error inserting data: %s\n", errorMessage);
        sqlite3_free(errorMessage);
    } else {
        printf("Records inserted successfully.\n");
    }
}

4. Query Data

To retrieve records from the table, we can execute a SELECT query. Here is an example of querying all user information:

void queryData(sqlite3 *db) {
    const char* sql = "SELECT id, name, age FROM users;";
    sqlite3_stmt *stmt;
    int rc;
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    while (true) {
        rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE)
            break;
        printf ("ID: %d \t Name: %s\t Age: %d\n ",
                sqlite3_column_int(stmt, 0),
                sqlite3_column_text(stmt, 1),
                sqlite3_column_int(stmt, 2));
    }
    // Release resources
    sqlite3_finalize(stmt);
}

Comprehensive Example: Complete Code Flow

Combining all the above functions, here is a complete example program that demonstrates how to complete the entire workflow, including opening the connection, creating the table, inserting records, and querying results.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
// Function declarations
void createTable(sqlite3 *db);
void insertData(sqlite3 *db);
void queryData(sqlite3 *db);
int main() {
    sqlite3 *db;
    int exit = sqlite3_open("database.db", &db);
    if (exit) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    createTable(db);
    insertData(db);
    queryData(db);
    sqlite3_close(db);
    return 0;
}