DML with MariaDB Connector/C++

C++ developers can use MariaDB Connector/C++ to perform basic DML (Data Manipulation Language) operations with MariaDB database products.

DML Operations

DML (Data Manipulation Language) refers to all SQL-data statements in the SQL standard (ISO/IEC 9075-2:2016). Some examples of DML include DELETE, INSERT, REPLACE, SELECT, and UPDATE.

Code Example: INSERT, UPDATE, DELETE

INSERT, UPDATE, and DELETE are DML (Data Manipulation Language) operations that modify the data in a table.

The following code demonstrates how to execute INSERT on the example table.

To update or delete data, replace the INSERT statement in the code example with an UPDATE or DELETE statement:

// Includes
#include <iostream>
#include <mariadb/conncpp.hpp>

// Function to Add Contact
void addContact(std::shared_ptr<sql::PreparedStatement> &stmnt,
   sql::SQLString first_name,
   sql::SQLString last_name,
   sql::SQLString email)
{
   try {
      // Bind variables to prepared statement parameters
      // Note that the index starts at 1--not 0
      stmnt->setString(1, first_name);
      stmnt->setString(2, last_name);
      stmnt->setString(3, email);

      // Execute Statement
      stmnt->executeUpdate();
   }

   // Handle Exceptions
   catch (sql::SQLException &e) {
      std::cerr << "Error adding contact to database: "
         << e.what() << std::endl;
   }
}

// Main Process
int main(int argc, char **argv)
{
   try {
      // Instantiate Driver
      sql::Driver* driver = sql::mariadb::get_driver_instance();

      // Configure Connection
      // The URL or TCP connection string format is
      // ``jdbc:mariadb://host:port/database``.
      sql::SQLString url("jdbc:mariadb://192.0.2.1:3306/test");

      // Use a properties map for the other connection options
      sql::Properties properties({
            {"user", "db_user"},
            {"password", "db_user_password"},
         });

      // Establish Connection
      // Use a smart pointer for extra safety
      std::unique_ptr<sql::Connection> conn(driver->connect(url, properties));

      // Created a PreparedStatement
      // Use a smart pointer for extra safety
      std::shared_ptr<sql::PreparedStatement> stmnt(
            conn->prepareStatement(
               "INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)"
            )
         );

      // Use prepared statement to add data
      addContact(stmnt, "John", "Smith", "john.smith@example.com");

      // Close Connection
      conn->close();
   }

   // Catch Exceptions
   catch (sql::SQLException& e) {
      std::cerr << "Error Connecting to the database: "
         << e.what() << std::endl;

      // Exit (Failed)
      return 1;
   }

   // Exit (Success)
   return 0;
}

Confirm the data was properly inserted by using MariaDB Client to execute a SELECT statement:

SELECT * from test.contacts;

+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
|  1 | John       | Smith     | john.smith@example.com |
+----+------------+-----------+------------------------+

Code Example: SELECT

SELECT is a DML (Data Manipulation Language) operation that reads the data from a table.

The following code demonstrates how to execute SELECT on the example table:

// Includes
#include <iostream>
#include <mariadb/conncpp.hpp>

// Function to print Contacts
void printContacts(std::shared_ptr<sql::Statement> &stmnt)
{
   try {
      // Execute SELECT Statement
      std::unique_ptr<sql::ResultSet> res(
            stmnt->executeQuery("SELECT first_name, last_name, email FROM test.contacts")
         );

      // Loop over Result-set
      while (res->next())
      {
         // Retrieve Values and Print Contacts
         std::cout << "- "
            << res->getString("first_name")
            << " "
            << res->getString("last_name")
            << " <"
            << res->getString("email")
            << ">"
            << std::endl;
      }
   }

   // Catch Exception
   catch (sql::SQLException& e) {
      std::cerr << "Error printing contacts: "
         << e.what() << std::endl;
   }
}

// Main Process
int main(int argc, char **argv)
{
   try {
      // Instantiate Driver
      sql::Driver* driver = sql::mariadb::get_driver_instance();

      // Configure Connection
      // The URL or TCP connection string format is
      // ``jdbc:mariadb://host:port/database``.
      sql::SQLString url("jdbc:mariadb://192.0.2.1:3306/test");

      // Use a properties map for the other connection options
      sql::Properties properties({
            {"user", "db_user"},
            {"password", "db_user_password"},
         });

      // Establish Connection
      // Use a smart pointer for extra safety
      std::unique_ptr<sql::Connection> conn(driver->connect(url, properties));

      // Create a Statement
      // Use a smart pointer for extra safety
      std::shared_ptr<sql::Statement> stmnt(conn->createStatement());

      printContacts(stmnt);

      // Close Connection
      conn->close();
   }

   // Catch Exceptions
   catch (sql::SQLException &e) {
      std::cerr << "Error Connecting to the database: "
         << e.what() << std::endl;

      // Exit (Failed)
      return 1;
   }

   // Exit (Success)
   return 0;
}

Example output:

- John Smith <john.smith@example.com>
- Jon Smith <jon.smith@example.com>
- Johnny Smith <johnny.smith@example.com>