SQL Query Execution
SQL (Structured Query Language) is a powerful tool for managing and manipulating data in relational databases. In this chapter, we will explore how to execute SQL queries within a C program. We will cover everything from establishing connections to databases, preparing and executing queries, to handling results.
Inserting Data
Inserting data into a database involves adding new records or rows to a table. We’ll demonstrate how to execute INSERT queries in C.
#include <stdio.h>
#include <sqlite3.h>
int main() {
sqlite3 *db;
char *err_message = 0;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK) {
fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
char *sql = "INSERT INTO users (name, age) VALUES ('Alice', 28)";
rc = sqlite3_exec(db, sql, NULL, 0, &err_message);
if (rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", err_message);
sqlite3_free(err_message);
} else {
printf("Data inserted successfully.\n");
}
sqlite3_close(db);
return 0;
}
// output //
Data inserted successfully.
Select Records
Querying data from a database involves retrieving information based on specified criteria using SQL queries. We’ll demonstrate how to execute SELECT queries in C.
#include <stdio.h>
#include <sqlite3.h>
int callback(void *data, int argc, char **argv, char **azColName) {
int i;
for(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 *err_message = 0;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK) {
fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
char *sql = "SELECT * FROM users";
rc = sqlite3_exec(db, sql, callback, 0, &err_message);
if (rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", err_message);
sqlite3_free(err_message);
}
sqlite3_close(db);
return 0;
}
// output //
ID = 1
Name = John
Age = 30
ID = 2
Name = Emma
Age = 25
Updating Records
Updating records in a database involves modifying existing data. We’ll demonstrate how to execute UPDATE queries in C.
#include <stdio.h>
#include <mysql/mysql.h>
int main() {
MYSQL *conn;
conn = mysql_init(NULL);
if (conn == NULL) {
fprintf(stderr, "Error initializing MySQL: %s\n", mysql_error(conn));
return 1;
}
if (mysql_real_connect(conn, "localhost", "user", "password", "database", 0, NULL, 0) == NULL) {
fprintf(stderr, "Error connecting to MySQL: %s\n", mysql_error(conn));
mysql_close(conn);
return 1;
}
if (mysql_query(conn, "UPDATE users SET age = 35 WHERE name = 'John'") != 0) {
fprintf(stderr, "Error updating record: %s\n", mysql_error(conn));
mysql_close(conn);
return 1;
}
printf("Record updated successfully.\n");
mysql_close(conn);
return 0;
}
Deleting Data
Deleting data from a database involves removing records from a table based on specified criteria. We’ll demonstrate how to execute DELETE queries in C.
#include <stdio.h>
#include <mysql/mysql.h>
int main() {
MYSQL *conn;
conn = mysql_init(NULL);
if (conn == NULL) {
fprintf(stderr, "Error initializing MySQL: %s\n", mysql_error(conn));
return 1;
}
if (mysql_real_connect(conn, "localhost", "user", "password", "database", 0, NULL, 0) == NULL) {
fprintf(stderr, "Error connecting to MySQL: %s\n", mysql_error(conn));
mysql_close(conn);
return 1;
}
if (mysql_query(conn, "DELETE FROM users WHERE name = 'Alice'") != 0) {
fprintf(stderr, "Error deleting record: %s\n", mysql_error(conn));
mysql_close(conn);
return 1;
}
printf("Record deleted successfully.\n");
mysql_close(conn);
return 0;
}
// output //
Record deleted successfully.
SQL query execution in C allows for robust database interactions within C applications. By mastering SQL queries in C, developers can build efficient and scalable database-driven applications. Remember to handle errors effectively to ensure the reliability of your applications.Happy coding!❤️