DML with MariaDB Connector/C++

This page is part of MariaDB's Documentation.

The parent of this page is: MariaDB Connector/C++

Topics on this page:

Overview
DML Operations
Code Example: INSERT, UPDATE, DELETE
Code Example: SELECT

Overview

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 DELETE, INSERT INSERT, REPLACE REPLACE, SELECT SELECT, and UPDATE UPDATE.

Code Example: INSERT, UPDATE, DELETE

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

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

To update or delete data, replace the INSERT INSERT statement in the code example with an UPDATE UPDATE or DELETE 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;
}

// 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, including initial database name "test":
      sql::SQLString url("jdbc:mariadb://example.skysql.net:5009/test");

      // Use a properties map for the other connection options
      sql::Properties properties({
            {"user", "db_user"},
            {"password", "db_user_password"},
            {"autocommit", false},
            {"useTls", true},
            {"tlsCert", "classpath:static/skysql_chain.pem"},
         });

      // 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 SELECT statement:

SELECT * from test.contacts;

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

Code Example: SELECT

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

The following code demonstrates how to execute SELECT 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;
}

// 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, including initial database name "test":
      sql::SQLString url("jdbc:mariadb://example.skysql.net:5009/test");

      // Use a properties map for the other connection options
      sql::Properties properties({
            {"user", "db_user"},
            {"password", "db_user_password"},
            {"autocommit", false},
            {"useTls", true},
            {"tlsCert", "classpath:static/skysql_chain.pem"},
         });

      // 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>