Lesson 16-C Language and MySQL Database Operations

Installing MySQL Client Library

  • Install MySQL Connector/C, which is the official C language client library provided by MySQL.
  • Ensure that libmysql.lib and related header files are correctly added to the development environment.

Configuring the Compiler

In Visual Studio, you need to set the project properties to include the MySQL library files and header file paths.

  • Additional Include Directories: Add the MySQL include directory.
  • Additional Library Directories: Add the MySQL lib directory.
  • Additional Dependencies: Add libmysql.lib.

Writing Code

  • Include the necessary header files.
  • Connect to the database.
  • Execute SQL commands.
  • Process the result set.
  • Close the connection.

Below is a simple example demonstrating how to connect to a MySQL database using C and execute a query:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h> // MySQL client library header file

int main() {
    MYSQL *conn; // MySQL connection handle
    MYSQL_RES *res; // Result set
    MYSQL_ROW row; // Row pointer
    const char *server = "localhost";
    const char *user = "your_username";
    const char *password = "your_password";
    const char *database = "your_database";

    conn = mysql_init(NULL); // Initialize connection

    if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    // Query statement
    const char *sql = "SELECT * FROM your_table";

    // Execute query
    if (mysql_query(conn, sql)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    // Get result set
    res = mysql_use_result(conn);

    // Output results
    while ((row = mysql_fetch_row(res)) != NULL) {
        int i;
        for (i = 0; i < mysql_num_fields(res); i++) {
            printf("%s ", row[i] ? row[i] : "(null)");
        }
        printf("\n");
    }

    // Clean up resources
    mysql_free_result(res);
    mysql_close(conn);

    return 0;
}

Error Handling

In practical applications, error checking should be performed on every operation that may fail to ensure program robustness and reliability. For example, after connecting to the database, executing queries, etc., you should check for errors.

Dynamic Input Parameters

To improve program flexibility, users can specify database connection information and query statements through command-line arguments or interactive input.

More Complex SQL Operations

In addition to basic queries, you can also implement insert, update, and delete operations.

Example code: Includes error handling and dynamic input

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>

void handle_mysql_error(MYSQL *conn) {
    fprintf(stderr, "MySQL Error: %s\n", mysql_error(conn));
    mysql_close(conn);
    exit(1);
}

int main(int argc, char *argv[]) {
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;

    if (argc != 6) {
        fprintf(stderr, "Usage: %s host user password db query\n", argv[0]);
        exit(1);
    }

    const char *server = argv[1];
    const char *user = argv[2];
    const char *password = argv[3];
    const char *database = argv[4];
    const char *query = argv[5];

    conn = mysql_init(NULL);

    if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
        handle_mysql_error(conn);
    }

    if (mysql_query(conn, query)) {
        handle_mysql_error(conn);
    }

    res = mysql_use_result(conn);

    while ((row = mysql_fetch_row(res)) != NULL) {
        int i;
        for (i = 0; i < mysql_num_fields(res); i++) {
            printf("%s ", row[i] ? row[i] : "(null)");
        }
        printf("\n");
    }

    mysql_free_result(res);
    mysql_close(conn);

    return 0;
}

Explanation

  • Error handling function handle_mysql_error: Called when a MySQL operation fails, prints the error message and exits the program.
  • Dynamic input parameters: The program accepts five command-line arguments corresponding to hostname, username, password, database name, and SQL query statement.
  • Command-line argument validation: If the number of arguments is incorrect, output usage help and exit.
  • SQL query: Retrieve the query statement from command-line arguments and execute it.

Compiling and Running

Compile command:

gcc -o myapp myapp.c -lmysqlclient

Run command:

./myapp localhost root password mydb "SELECT * FROM your_table"

Extended Features

Insert data:

const char *insert_sql = "INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')";
if (mysql_query(conn, insert_sql)) {
    handle_mysql_error(conn);
}

Update data:

const char *update_sql = "UPDATE your_table SET column1 = 'new_value' WHERE id = 1";
if (mysql_query(conn, update_sql)) {
    handle_mysql_error(conn);
}

Delete data:

const char *delete_sql = "DELETE FROM your_table WHERE id = 1";
if (mysql_query(conn, delete_sql)) {
    handle_mysql_error(conn);
}

Parameterized Queries

Using prepared statements can effectively prevent SQL injection attacks. Below is an example using prepared statements:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>

void handle_mysql_error(MYSQL *conn) {
    fprintf(stderr, "MySQL Error: %s\n", mysql_error(conn));
    mysql_close(conn);
    exit(1);
}

int main(int argc, char *argv[]) {
    MYSQL *conn;
    MYSQL_STMT *stmt;
    MYSQL_BIND bind[2];
    MYSQL_RES *res;
    MYSQL_ROW row;
    int param1, param2;

    if (argc != 6) {
        fprintf(stderr, "Usage: %s host user password db query\n", argv[0]);
        exit(1);
    }

    const char *server = argv[1];
    const char *user = argv[2];
    const char *password = argv[3];
    const char *database = argv[4];
    const char *query = argv[5];

    conn = mysql_init(NULL);

    if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
        handle_mysql_error(conn);
    }

    stmt = mysql_stmt_init(conn);

    // Prepare the prepared statement
    const char *sql = "SELECT * FROM your_table WHERE column1 = ? AND column2 = ?";
    if (mysql_stmt_prepare(stmt, sql, strlen(sql))) {
        handle_mysql_error(conn);
    }

    // Set bind parameters
    param1 = 1;
    param2 = 2;
    bind[0].buffer_type = MYSQL_TYPE_LONG;
    bind[0].buffer = ¶m1;
    bind[0].is_null = 0;
    bind[0].length = 0;
    bind[1].buffer_type = MYSQL_TYPE_LONG;
    bind[1].buffer = ¶m2;
    bind[1].is_null = 0;
    bind[1].length = 0;

    // Bind parameters
    if (mysql_stmt_bind_param(stmt, bind)) {
        handle_mysql_error(conn);
    }

    // Execute the prepared statement
    if (mysql_stmt_execute(stmt)) {
        handle_mysql_error(conn);
    }

    // Get result set
    if (mysql_stmt_store_result(stmt)) {
        handle_mysql_error(conn);
    }

    // Output results
    while (mysql_stmt_fetch(stmt)) {
        // Get column values
        // Assuming column names are column1 and column2
        MYSQL_BIND *bind = mysql_stmt_fetch_column(stmt, 0, 0);
        printf("Column1: %ld, Column2: %ld\n", *(long *)bind[0].buffer, *(long *)bind[1].buffer);
    }

    mysql_stmt_close(stmt);
    mysql_close(conn);

    return 0;
}

Transaction Management

Transaction management ensures that a group of operations either all succeed or all fail. Below is an example using transactions:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>

void handle_mysql_error(MYSQL *conn) {
    fprintf(stderr, "MySQL Error: %s\n", mysql_error(conn));
    mysql_close(conn);
    exit(1);
}

int main(int argc, char *argv[]) {
    MYSQL *conn;
    MYSQL_STMT *stmt;
    MYSQL_RES *res;
    MYSQL_ROW row;

    if (argc != 6) {
        fprintf(stderr, "Usage: %s host user password db query\n", argv[0]);
        exit(1);
    }

    const char *server = argv[1];
    const char *user = argv[2];
    const char *password = argv[3];
    const char *database = argv[4];
    const char *query = argv[5];

    conn = mysql_init(NULL);

    if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
        handle_mysql_error(conn);
    }

    // Start transaction
    if (mysql_query(conn, "START TRANSACTION")) {
        handle_mysql_error(conn);
    }

    // Insert data
    const char *insert_sql = "INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')";
    if (mysql_query(conn, insert_sql)) {
        handle_mysql_error(conn);
    }

    // Update data
    const char *update_sql = "UPDATE your_table SET column1 = 'new_value' WHERE id = 1";
    if (mysql_query(conn, update_sql)) {
        handle_mysql_error(conn);
    }

    // Commit transaction
    if (mysql_query(conn, "COMMIT")) {
        handle_mysql_error(conn);
    }

    mysql_close(conn);

    return 0;
}

Calling Stored Procedures

Stored procedures can execute complex logic on the server side, improving performance and security. Below is an example of calling a stored procedure:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>

void handle_mysql_error(MYSQL *conn) {
    fprintf(stderr, "MySQL Error: %s\n", mysql_error(conn));
    mysql_close(conn);
    exit(1);
}

int main(int argc, char *argv[]) {
    MYSQL *conn;
    MYSQL_STMT *stmt;
    MYSQL_BIND bind[1];
    MYSQL_RES *res;
    MYSQL_ROW row;

    if (argc != 6) {
        fprintf(stderr, "Usage: %s host user password db query\n", argv[0]);
        exit(1);
    }

    const char *server = argv[1];
    const char *user = argv[2];
    const char *password = argv[3];
    const char *database = argv[4];
    const char *query = argv[5];

    conn = mysql_init(NULL);

    if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
        handle_mysql_error(conn);
    }

    stmt = mysql_stmt_init(conn);

    // Prepare to call stored procedure
    const char *call_procedure = "CALL your_procedure(?)";
    if (mysql_stmt_prepare(stmt, call_procedure, strlen(call_procedure))) {
        handle_mysql_error(conn);
    }

    // Set bind parameters
    int param = 1;
    bind[0].buffer_type = MYSQL_TYPE_LONG;
    bind[0].buffer = ¶m;
    bind[0].is_null = 0;
    bind[0].length = 0;

    // Bind parameters
    if (mysql_stmt_bind_param(stmt, bind)) {
        handle_mysql_error(conn);
    }

    // Execute stored procedure
    if (mysql_stmt_execute(stmt)) {
        handle_mysql_error(conn);
    }

    mysql_stmt_close(stmt);
    mysql_close(conn);

    return 0;
}
Course: Mastering C Programming From Basics to Advanced Techniques
Share your love

Trending now

Exploring Web 3D Core Technologies Development
Assembly Language Programming From Low-Level Logic to System Kernel
WebRTC Advanced Guide：Media, Transport & Architecture
WebSocket Deep Guide：Building High-Performance Realtime Systems