Tutorial: Connecting C to a Database116

In this tutorial, we will explore how to connect a C program to a database. We will cover the necessary steps to establish a connection, execute queries, and fetch results from the database. To follow along, make sure you have a C compiler and a compatible database system installed on your system.

Prerequisites* C compiler (e.g., gcc or clang)
* Database system (e.g., MySQL, PostgreSQL, or SQLite)

Step 1: Include the Database Header

To access database functionality in C, you need to include the appropriate header file. This header will provide declarations for the functions and data types used for database operations.```c
#include
#include
#include // For MySQL
// Or
#include // For PostgreSQL
// Or
#include // For SQLite
```

Step 2: Establish a Connection

Once the header is included, you can establish a connection to the database using the appropriate connection function.```c
// MySQL
MYSQL *connection = mysql_init(NULL);
if (mysql_real_connect(connection, "localhost", "root", "password", "database_name", 0, NULL, 0) == NULL) {
fprintf(stderr, "Error connecting to the database: %s", mysql_error(connection));
mysql_close(connection);
return EXIT_FAILURE;
}
// PostgreSQL
PGconn *connection = PQconnectdb("host=localhost port=5432 dbname=database_name user=postgres password=password");
if (PQstatus(connection) != CONNECTION_OK) {
fprintf(stderr, "Error connecting to the database: %s", PQerrorMessage(connection));
PQfinish(connection);
return EXIT_FAILURE;
}
// SQLite
sqlite3 *connection;
if (sqlite3_open("", &connection) != SQLITE_OK) {
fprintf(stderr, "Error connecting to the database: %s", sqlite3_errmsg(connection));
sqlite3_close(connection);
return EXIT_FAILURE;
}
```

Step 3: Execute a Query

With a connection established, you can now execute queries against the database.```c
// MySQL
MYSQL_RES *result = mysql_query(connection, "SELECT * FROM table_name");
if (result == NULL) {
fprintf(stderr, "Error executing query: %s", mysql_error(connection));
mysql_close(connection);
return EXIT_FAILURE;
}
// PostgreSQL
PGresult *result = PQexec(connection, "SELECT * FROM table_name");
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "Error executing query: %s", PQerrorMessage(connection));
PQclear(result);
PQfinish(connection);
return EXIT_FAILURE;
}
// SQLite
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(connection, "SELECT * FROM table_name", -1, &statement, NULL) != SQLITE_OK) {
fprintf(stderr, "Error preparing query: %s", sqlite3_errmsg(connection));
sqlite3_finalize(statement);
sqlite3_close(connection);
return EXIT_FAILURE;
}
```

Step 4: Fetch Results

Once the query is executed, you can retrieve the results using the appropriate fetch function.```c
// MySQL
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
// Access the columns of the current row
}
mysql_free_result(result);
// PostgreSQL
PQntuples(result); // Get the number of rows in the result
for (int i = 0; i < PQntuples(result); i++) {
// Access the values of the ith row
}
PQclear(result);
// SQLite
while (sqlite3_step(statement) == SQLITE_ROW) {
// Access the columns of the current row
}
sqlite3_finalize(statement);
```

Step 5: Close the Connection

Finally, when you are finished with the database connection, remember to close it properly.```c
mysql_close(connection);
PQfinish(connection);
sqlite3_close(connection);
```

Example Code

Here is an example C program that connects to a MySQL database, executes a query, and prints the results:```c
#include
#include
#include
int main() {
// Establish a connection to the database
MYSQL *connection = mysql_init(NULL);
if (mysql_real_connect(connection, "localhost", "root", "password", "database_name", 0, NULL, 0) == NULL) {
fprintf(stderr, "Error connecting to the database: %s", mysql_error(connection));
mysql_close(connection);
return EXIT_FAILURE;
}
// Execute a query
MYSQL_RES *result = mysql_query(connection, "SELECT * FROM table_name");
if (result == NULL) {
fprintf(stderr, "Error executing query: %s", mysql_error(connection));
mysql_close(connection);
return EXIT_FAILURE;
}
// Fetch and print the results
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
printf("%s", row[0]); // Assuming the first column is a string
}
mysql_free_result(result);
// Close the connection
mysql_close(connection);
return EXIT_SUCCESS;
}
```

Conclusion

This tutorial has provided a step-by-step guide on how to connect a C program to a database. By following these steps, you can establish a connection, execute queries, and fetch results from various database systems.

2024-11-19

Previous：Is Cloud Computing Hard to Learn?

Next：Three-Tier Database Architecture: A Comprehensive Guide