Lesson 18-C Language and PostgreSQL Database Operations

Installing libpq Library

  • Install the PostgreSQL client library libpq.
  • Ensure that the libpq-fe.h header file and related library files are correctly added to the development environment.

Configuring the Compiler

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

  • Additional Include Directories: Add the PostgreSQL include directory.
  • Additional Library Directories: Add the PostgreSQL lib directory.
  • Additional Dependencies: Add libpq.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 PostgreSQL database using C and execute a query:

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h> // PostgreSQL client library header file

int main() {
    PGconn *conn; // PostgreSQL connection handle
    PGresult *res; // Result set
    const char *connection_string = "dbname=mydb user=myuser password=mypassword hostaddr=127.0.0.1 port=5432";
    const char *sql = "SELECT * FROM mytable";

    conn = PQconnectdb(connection_string); // Connect to the database

    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        return 1;
    }

    // Execute query
    res = PQexec(conn, sql);

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        fprintf(stderr, "Query execution failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Output results
    int num_rows = PQntuples(res);
    int num_cols = PQnfields(res);
    for (int i = 0; i < num_rows; i++) {
        for (int j = 0; j < num_cols; j++) {
            printf("%s ", PQgetvalue(res, i, j));
        }
        printf("\n");
    }

    // Clean up resources
    PQclear(res);
    PQfinish(conn);

    return 0;
}

Notes:

  • Ensure the PostgreSQL service is running.
  • Use the correct connection string.
  • Handle error cases, such as connection failure or query failure.
  • Ensure all resources are properly released.

Compiling and Running

When compiling, ensure to link the libpq library, for example using the gcc command:

gcc -o myapp myapp.c -lpq

If using Visual Studio, ensure the project properties are configured as described above.

Parameterized Queries

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

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int main() {
    PGconn *conn; // PostgreSQL connection handle
    PGresult *res; // Result set
    PGresult *stmt_res; // Prepared statement result set
    const char *connection_string = "dbname=mydb user=myuser password=mypassword hostaddr=127.0.0.1 port=5432";
    const char *sql = "PREPARE get_data(integer) AS SELECT * FROM mytable WHERE id = $1";
    const char *execute_sql = "EXECUTE get_data(1)";
    const char *deallocate_sql = "DEALLOCATE get_data";

    conn = PQconnectdb(connection_string); // Connect to the database

    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        return 1;
    }

    // Prepare the prepared statement
    stmt_res = PQexec(conn, sql);
    if (PQresultStatus(stmt_res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Preparing statement failed: %s", PQerrorMessage(conn));
        PQclear(stmt_res);
        PQfinish(conn);
        return 1;
    }

    // Execute the prepared statement
    res = PQexec(conn, execute_sql);
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        fprintf(stderr, "Executing prepared statement failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Output results
    int num_rows = PQntuples(res);
    int num_cols = PQnfields(res);
    for (int i = 0; i < num_rows; i++) {
        for (int j = 0; j < num_cols; j++) {
            printf("%s ", PQgetvalue(res, i, j));
        }
        printf("\n");
    }

    // Clean up resources
    PQclear(res);
    PQclear(stmt_res);

    // Deallocate the prepared statement
    stmt_res = PQexec(conn, deallocate_sql);
    if (PQresultStatus(stmt_res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Deallocating statement failed: %s", PQerrorMessage(conn));
        PQclear(stmt_res);
        PQfinish(conn);
        return 1;
    }

    PQclear(stmt_res);
    PQfinish(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 <libpq-fe.h>

int main() {
    PGconn *conn; // PostgreSQL connection handle
    PGresult *res; // Result set
    const char *connection_string = "dbname=mydb user=myuser password=mypassword hostaddr=127.0.0.1 port=5432";

    conn = PQconnectdb(connection_string); // Connect to the database

    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        return 1;
    }

    // Start transaction
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Starting transaction failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Insert data
    const char *insert_sql = "INSERT INTO mytable (column1, column2) VALUES ('value1', 'value2')";
    res = PQexec(conn, insert_sql);
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Inserting data failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Update data
    const char *update_sql = "UPDATE mytable SET column1 = 'new_value' WHERE id = 1";
    res = PQexec(conn, update_sql);
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Updating data failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Commit transaction
    res = PQexec(conn, "COMMIT");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Committing transaction failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    PQclear(res);
    PQfinish(conn);

    return 0;
}

Error Handling

Enhancing the error handling mechanism ensures that the program provides clear feedback when issues occur and handles errors gracefully. Below is an improved error handling example:

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

void handle_pq_error(PGconn *conn, const char *message) {
    fprintf(stderr, "%s: %s", message, PQerrorMessage(conn));
    PQfinish(conn);
    exit(1);
}

int main() {
    PGconn *conn; // PostgreSQL connection handle
    PGresult *res; // Result set
    const char *connection_string = "dbname=mydb user=myuser password=mypassword hostaddr=127.0.0.1 port=5432";
    const char *sql = "SELECT * FROM mytable";

    conn = PQconnectdb(connection_string); // Connect to the database

    if (PQstatus(conn) != CONNECTION_OK) {
        handle_pq_error(conn, "Connection to database failed");
    }

    // Execute query
    res = PQexec(conn, sql);

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        handle_pq_error(conn, "Query execution failed");
    }

    // Output results
    int num_rows = PQntuples(res);
    int num_cols = PQnfields(res);
    for (int i = 0; i < num_rows; i++) {
        for (int j = 0; j < num_cols; j++) {
            printf("%s ", PQgetvalue(res, i, j));
        }
        printf("\n");
    }

    // Clean up resources
    PQclear(res);
    PQfinish(conn);

    return 0;
}

Batch Inserting Data

When inserting large amounts of data, using batch inserts can significantly improve efficiency. Below is an example using batch insertion:

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int main() {
    PGconn *conn; // PostgreSQL connection handle
    PGresult *res; // Result set
    const char *connection_string = "dbname=mydb user=myuser password=mypassword hostaddr=127.0.0.1 port=5432";

    conn = PQconnectdb(connection_string); // Connect to the database

    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        return 1;
    }

    // Create table
    const char *create_table_sql = "CREATE TABLE IF NOT EXISTS mytable (id SERIAL PRIMARY KEY, name TEXT)";
    res = PQexec(conn, create_table_sql);
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Creating table failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Start transaction
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Starting transaction failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Batch insert data
    const char *batch_insert_sql = "INSERT INTO mytable (name) VALUES ";
    for (int i = 0; i < 1000; i++) {
        if (i > 0) {
            batch_insert_sql = strcat(batch_insert_sql, ", ");
        }
        batch_insert_sql = strcat(batch_insert_sql, "('data'");
        if (i == 999) {
            batch_insert_sql = strcat(batch_insert_sql, ")");
        } else {
            batch_insert_sql = strcat(batch_insert_sql, "), ");
        }
    }

    res = PQexec(conn, batch_insert_sql);
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Batch insert failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Commit transaction
    res = PQexec(conn, "COMMIT");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Committing transaction failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    PQclear(res);
    PQfinish(conn);

    return 0;
}

Asynchronous Queries

Using asynchronous queries allows other tasks to be performed while waiting for the database response, thereby improving application responsiveness. Below is an example using asynchronous queries:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#include <libpq-fe.h>

int main() {
    PGconn *conn; // PostgreSQL connection handle
    PGresult *res; // Result set
    const char *connection_string = "dbname=mydb user=myuser password=mypassword hostaddr=127.0.0.1 port=5432";
    const char *sql = "SELECT * FROM mytable";

    conn = PQconnectdb(connection_string); // Connect to the database

    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        return 1;
    }

    // Send asynchronous query
    if (!PQsendQuery(conn, sql)) {
        fprintf(stderr, "Sending query failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        return 1;
    }

    // Wait for query to complete
    while (PQisBusy(conn)) {
        sleep(1); // Wait one second
    }

    // Get result
    res = PQgetResult(conn);

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        fprintf(stderr, "Query execution failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Output results
    int num_rows = PQntuples(res);
    int num_cols = PQnfields(res);
    for (int i = 0; i < num_rows; i++) {
        for (int j = 0; j < num_cols; j++) {
            printf("%s ", PQgetvalue(res, i, j));
        }
        printf("\n");
    }

    // Clean up resources
    PQclear(res);
    PQfinish(conn);

    return 0;
}

Using JSON Type

PostgreSQL supports the JSON type, which can be used to store structured data. Below is an example using the JSON type:

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int main() {
    PGconn *conn; // PostgreSQL connection handle
    PGresult *res; // Result set
    const char *connection_string = "dbname=mydb user=myuser password=mypassword hostaddr=127.0.0.1 port=5432";
    const char *json_data = "{\"key\": \"value\"}";

    conn = PQconnectdb(connection_string); // Connect to the database

    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        return 1;
    }

    // Create table
    const char *create_table_sql = "CREATE TABLE IF NOT EXISTS mytable (id SERIAL PRIMARY KEY, data JSON)";
    res = PQexec(conn, create_table_sql);
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Creating table failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Insert JSON data
    const char *insert_sql = "INSERT INTO mytable (data) VALUES ($1)";
    res = PQexecParams(conn, insert_sql, 1, NULL, &json_data, NULL, NULL, 0);
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Inserting JSON data failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Query JSON data
    const char *query_sql = "SELECT data FROM mytable";
    res = PQexec(conn, query_sql);
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        fprintf(stderr, "Querying JSON data failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return 1;
    }

    // Output results
    int num_rows = PQntuples(res);
    int num_cols = PQnfields(res);
    for (int i = 0; i < num_rows; i++) {
        for (int j = 0; j < num_cols; j++) {
            printf("%s ", PQgetvalue(res, i, j));
        }
        printf("\n");
    }

    // Clean up resources
    PQclear(res);
    PQfinish(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