Handling Database Results
Handling database results in C involves retrieving data from a database and processing it within a C program. This chapter explores various techniques and strategies for effectively working with database results in C applications.
Retrieving Data from Database
Before handling database results, you need to retrieve data from the database. This typically involves executing SQL queries using a database library such as SQLite.
#include <stdio.h>
#include <sqlite3.h>
int main() {
sqlite3 *db;
sqlite3_stmt *stmt;
int rc;
rc = sqlite3_open("example.db", &db);
if (rc != SQLITE_OK) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return 0;
}
const char *sql = "SELECT * FROM EMPLOYEE";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
return 0;
}
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
printf("ID: %d, NAME: %s, AGE: %d, SALARY: %f\n",
sqlite3_column_int(stmt, 0),
sqlite3_column_text(stmt, 1),
sqlite3_column_int(stmt, 2),
sqlite3_column_double(stmt, 3));
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
// output //
ID: 1, NAME: Paul, AGE: 32, SALARY: 20000.000000
ID: 2, NAME: Allen, AGE: 25, SALARY: 15000.000000
ID: 3, NAME: Teddy, AGE: 23, SALARY: 20000.000000
ID: 4, NAME: Mark, AGE: 25, SALARY: 65000.000000
This code retrieves data from the EMPLOYEE table and prints it to the console.
Processing Database Results
Processing database results involves handling the data retrieved from the database within the C program. This section will provide a detailed explanation, along with a complete code example, to illustrate how to process database results effectively.
#include <stdio.h>
#include <sqlite3.h>
int main() {
sqlite3 *db;
sqlite3_stmt *stmt;
int rc;
rc = sqlite3_open("example.db", &db);
if (rc != SQLITE_OK) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return 0;
}
const char *sql = "SELECT * FROM EMPLOYEE";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
return 0;
}
printf("Processing Data from Database:\n");
// Processing data
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
int id = sqlite3_column_int(stmt, 0);
const char *name = (const char*)sqlite3_column_text(stmt, 1);
int age = sqlite3_column_int(stmt, 2);
double salary = sqlite3_column_double(stmt, 3);
// Displaying information
printf("ID: %d, NAME: %s, AGE: %d, SALARY: %.2f\n", id, name, age, salary);
// Perform additional processing tasks here
// Example: Calculate bonus based on salary
double bonus = salary * 0.1;
printf("Employee %s has a bonus of $%.2f\n", name, bonus);
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
// output //
Processing Data from Database:
ID: 1, NAME: Paul, AGE: 32, SALARY: 20000.00
Employee Paul has a bonus of $2000.00
ID: 2, NAME: Allen, AGE: 25, SALARY: 15000.00
Employee Allen has a bonus of $1500.00
ID: 3, NAME: Teddy, AGE: 23, SALARY: 20000.00
Employee Teddy has a bonus of $2000.00
ID: 4, NAME: Mark, AGE: 25, SALARY: 65000.00
Employee Mark has a bonus of $6500.00
Explanation:
- This code establishes a connection to the SQLite database named “example.db” and prepares an SQL query to retrieve data from the EMPLOYEE table.
- Inside the while loop, each row of data retrieved from the database is processed.
- For each row, the values of different columns (ID, NAME, AGE, SALARY) are retrieved using appropriate sqlite3_column_* functions.
- The retrieved data is then displayed to the console, and additional processing tasks can be performed if needed.
- In this example, a bonus is calculated based on the employee’s salary and displayed along with other information.
Aggregation
Aggregation involves calculating summary statistics or totals based on groups of data. In this example, we’ll calculate the total salary of all employees retrieved from the database.
#include <stdio.h>
#include <sqlite3.h>
int main() {
sqlite3 *db;
sqlite3_stmt *stmt;
int rc;
rc = sqlite3_open("example.db", &db);
if (rc != SQLITE_OK) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return 0;
}
const char *sql = "SELECT SALARY FROM EMPLOYEE";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
return 0;
}
double totalSalary = 0;
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
totalSalary += sqlite3_column_double(stmt, 0);
}
printf("Total salary of all employees: $%.2f\n", totalSalary);
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
// output //
Total salary of all employees: $120000.00
Explanation:
We first open the SQLite database “example.db” and prepare the SQL query to select the salary of all employees.
Inside the while loop, we iterate over each row of the result set using
sqlite3_step(stmt). For each row, we extract the salary usingsqlite3_column_double(stmt, 0)and add it to thetotalSalaryvariable.After processing all rows, we print the total salary of all employees.
Further Resources
- SQLite Documentation: https://www.sqlite.org/docs.html
- C Programming Language Documentation: https://en.cppreference.com/w/c
Handling database results in C involves retrieving data from a database, processing it within the C program, and performing advanced data processing techniques. By mastering these concepts, developers can build powerful database-driven applications using C. Effective handling of database results is crucial for creating robust and efficient software solutions. Happy coding!❤️