Fetching Data from MySQL Database in C++

A detailed guide on how to fetch data from a MySQL database using C++ with Prepared Statements. The article helps you understand how to connect, execute queries, and handle results using MySQL Connector/C++.

This article will guide you on how to use Prepared Statements to fetch data from a MySQL database using the C++ programming language. Using Prepared Statements ensures that your queries are more secure and efficient.

C++ Code

#include <iostream>
#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>

int main() {
    try {
        // Connect to the MySQL database
        sql::mysql::MySQL_Driver *driver = sql::mysql::get_mysql_driver_instance();
        std::unique_ptr<sql::Connection> con(driver->connect("tcp://127.0.0.1:3306", "username", "password"));

        // Select the database
        con->setSchema("database_name");

        // Create a Prepared Statement
        std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement("SELECT id, name, age FROM users WHERE age > ?"));

        // Set parameter value
        pstmt->setInt(1, 25);

        // Execute the query and fetch the result
        std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

        // Iterate through the result set and display the data
        while (res->next()) {
            std::cout << "ID: " << res->getInt("id") << " | "
                      << "Name: " << res->getString("name") << " | "
                      << "Age: " << res->getInt("age") << std::endl;
        }
    } catch (sql::SQLException &e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }

    return 0;
}

Detailed explanation

• sql::mysql::get_mysql_driver_instance(): Retrieves the MySQL driver.
• driver->connect(...): Establishes a connection to the MySQL database.
• con->setSchema("database_name"): Selects the target database.
• prepareStatement(...): Creates a Prepared Statement with the query.
• pstmt->setInt(1, 25): Sets the parameter value for the query.
• executeQuery(): Executes the query and returns the result set.
• res->next(): Iterates through each row in the result set.

System Requirements:

• C++ Compiler (GCC, MSVC, etc.)
• MySQL Connector/C++ (Version 1.1.9 or later)
• MySQL Server 5.7 or 8.0

How to install the libraries needed to run the C++ code above:

1. Download MySQL Connector/C++ from the official MySQL website.
2. Extract and configure the library paths when compiling your source code.

Tips:

• Always use Prepared Statements to avoid SQL Injection.
• Check for errors when connecting to the database to handle potential issues.