Application of C Language in Database Programming and Interface Usage

The C language is a powerful programming language widely used in system programming, embedded development, and database programming. In this article, we will explore how to interact with databases using C, primarily focusing on the SQLite database. SQLite is a lightweight relational database that is very suitable for small projects and learning.

1. Environment Preparation

Before we begin, please ensure that the following software is installed on your computer:

C Compiler: such as GCC.
SQLite3: can be downloaded and installed from the SQLite official website.

Installing SQLite3

On Linux systems, you can install it via the package manager:

sudo apt-get install sqlite3 libsqlite3-dev

On Windows systems, you can download the precompiled binaries and add them to your system path.

2. Basic Concepts

Database Connection

To interact with a database, you first need to establish a connection. We will use the sqlite3_open function to open or create an SQLite database file.

SQL Operations

Common SQL operations include:

Creating tables (CREATE TABLE)
Inserting data (INSERT INTO)
Querying data (SELECT)
Updating data (UPDATE)
Deleting data (DELETE)

3. Code Example

Below is a simple example program that demonstrates how to operate an SQLite database using C, including creating tables, inserting records, and querying records.

Example Code

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
// Callback function to handle query results
static int callback(void *data, int argc, char **argv, char **azColName) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int main() {
    sqlite3 *db;
    char *errMsg = 0;
    // Open or create database
    if (sqlite3_open("test.db", &db)) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        return EXIT_FAILURE;
    }
    // Create table
    const char *sqlCreateTable = "CREATE TABLE IF NOT EXISTS Users(Id INT PRIMARY KEY NOT NULL, Name TEXT NOT NULL);";
    if (sqlite3_exec(db, sqlCreateTable, 0, 0, &errMsg) != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
        return EXIT_FAILURE;
    }
    // Insert data
    const char *sqlInsert = "INSERT INTO Users (Id, Name) VALUES (1, 'Alice'), (2, 'Bob');";
    if (sqlite3_exec(db, sqlInsert, 0, 0, &errMsg) != SQLITE_OK) {
        fprintf(stderr,"SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
        return EXIT_FAILURE;
    }
    // Query data
    const char *sqlSelect = "SELECT * FROM Users;";
    if (sqlite3_exec(db, sqlSelect , callback , 0 , &errMsg) != SQLITE_OK ) {
        fprintf(stderr,"SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
        return EXIT_FAILURE;
    }
    // Close database connection
    sqlite3_close(db);
    return EXIT_SUCCESS;
}

4. Code Analysis

Including Header Files: We include stdio.h, stdlib.h, and sqlite3.h. Among them, stdio.h is used for input and output operations, stdlib.h provides some general utility functions, while sqlite3.h is the core header file for SQLite.
Callback Function: A callback function callback() is defined to handle query results. When executing a SELECT query, this function will be called, and each row of results will be passed to it.

static int callback(void *data,int argc,char **argv,char **azColName)

Parameter Explanation:

data: User-defined data, which is not used here.
argc: The number of columns in the current row.
argv: Stores the data for each column in the current row.
azColName: Stores information about the names of each column.

Opening/Creating Database: The sqlite3_open() function is called to open or create a database named "test.db". If it fails, an error message is printed and the program exits.
Executing SQL Commands:

Using CREATE TABLE IF NOT EXISTS ... to create a user table, which will not be recreated if it already exists.
Using the INSERT command to insert two records into the user table.

Querying and Displaying Results: The SELECT command is used to retrieve all user information, and the callback function is used to output the results row by row.

Closing Connection: Finally, the sqlite3_close() function is called to close the connection to the database and free resources.

5. Conclusion

This article introduced how to use the C language to interact with the SQLite database, including establishing connections, executing SQL commands, and handling query results. This is just a simple example; in practical applications, you may need to consider more complex situations, such as transaction management and security. However, I hope this basic tutorial helps you take the first step and gain a deeper understanding of the interaction between C and databases.