3. C++ et le monde extérieur




3.4. Interopérabilité avec les bases de données - MySQL

3.4.1. Installation de la base de données

On utilise ici un SGBD (Système de Gestion de Bases de Données) relationnel en l'occurrence MySQL.

Installer les modules suivants avec le mot de passe mysqlpass (il faudra le saisir deux fois) :

Puis installer grâce à synaptic les packages :

Créez la base de données, comme suit :

  1. -- phpMyAdmin SQL Dump
  2. -- version 4.0.10deb1
  3. -- http://www.phpmyadmin.net
  4. --
  5. -- Client: localhost
  6. -- Généré le: Jeu 04 Décembre 2014 à 17:53
  7. -- Version du serveur: 5.5.40-0ubuntu0.14.04.1
  8. -- Version de PHP: 5.5.9-1ubuntu4.4
  9.  
  10. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  11. SET time_zone = "+00:00";
  12.  
  13.  
  14. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  15. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  16. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  17. /*!40101 SET NAMES utf8 */;
  18.  
  19. --
  20. -- Base de données: `mydb`
  21. --
  22. CREATE DATABASE IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  23. USE `mydb`;
  24.  
  25. -- --------------------------------------------------------
  26.  
  27. --
  28. -- Structure de la table `users`
  29. --
  30.  
  31. CREATE TABLE IF NOT EXISTS `users` (
  32.   `us_id` INT(11) NOT NULL AUTO_INCREMENT,
  33.   `us_first_name` VARCHAR(30) NOT NULL,
  34.   `us_last_name` VARCHAR(30) NOT NULL,
  35.   `us_salary` FLOAT NOT NULL,
  36.   PRIMARY KEY (`us_id`),
  37.   KEY `us_last_name` (`us_last_name`)
  38. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
  39.  
  40. --
  41. -- Vider la table avant d'insérer `users`
  42. --
  43.  
  44. TRUNCATE TABLE `users`;
  45. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  46. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  47. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  48.  
mysql -u root -p <mydb.sql

3.4.2. Appel depuis C++

Voici un exemple qui permet d'insérer, puis de récupérer des enregistrements :

  1. #include <string>
  2. #include <vector>
  3. using namespace std;
  4. #include "mysql.h"
  5. #include "mysql++.h"
  6.  
  7. using namespace mysqlpp;
  8.  
  9. Connection my_connection;
  10.  
  11. /**
  12.  * Definition of a User identified by:
  13.  * - identifier (int)
  14.  * - last name (string / VC 30)
  15.  * - first name (string / VC 30)
  16.  * - salary (float)
  17.  */
  18. class User {
  19. protected:
  20.     int id;
  21.     string first_name;
  22.     string last_name;
  23.     float salary;
  24. public:
  25.     // default constructor
  26.     User() : id(-1), first_name(""), last_name(""), salary(0.0) {
  27.     }
  28.    
  29.     // constructor with arguments, strings for last and first name
  30.     User(int _id, string _first_name, string _last_name,
  31.         float _salary) : id(_id), first_name(_first_name),
  32.         last_name(_last_name), salary(_salary) {
  33.     }
  34.    
  35.     // constructor with arguments, const char * for last and first name
  36.     User(int _id, const char *_first_name, const char *_last_name,
  37.         float _salary) : id(_id), first_name(_first_name),
  38.         last_name(_last_name), salary(_salary) {
  39.     }
  40.    
  41.     // getters
  42.     int get_id() { return id; }
  43.     string get_first_name() { return first_name; }
  44.     string get_last_name() { return last_name; }
  45.     float get_salary() { return salary; }
  46.    
  47.     // printer
  48.     friend ostream& operator<<(ostream& out, User& obj) {
  49.         out << obj.id << ": " << obj.first_name << ": " << obj.last_name
  50.             << ": " << obj.salary;
  51.         return out;
  52.     }
  53. };
  54.  
  55. // function that opens a connection to the database
  56. bool open_connection(Connection &c) {
  57.     try {
  58.         if (c.connected()) return true;
  59.         cerr << ">>> open connection..." << endl;
  60.         c.connect("mydb", "localhost", "root", "mysqlpass");
  61.         cerr << ">>> SUCCESS" << endl;
  62.         return true;
  63.     } catch(Exception& exc) {
  64.         cout << ">>> FAILURE: Connection could not be established." << endl <<
  65.             "Please create a MySQL database" << endl;
  66.         return false;
  67.     }
  68. }
  69.  
  70. // retrieve the Users that corresponds to some last name criterion
  71. void retrieve_all(string name, vector<User *>& v) {
  72.    
  73.     if (!open_connection(my_connection)) return ;
  74.  
  75.     try {
  76.         User us;
  77.         Query query = my_connection.query();
  78.         query << "SELECT * FROM users WHERE us_last_name LIKE \"" << name << "\";";
  79.         cout << query << endl;
  80.         StoreQueryResult result = query.store();
  81.         if (result.num_rows() == 0) {
  82.             cout << "no record found" << endl;
  83.         } else {
  84.             for (size_t i=0; i<result.num_rows(); ++i) {
  85.            
  86.                 const char *fn = result[i]["us_first_name"];
  87.                 const char *ln = result[i]["us_last_name"];
  88.                 User *new_us = new User(atoi(result[i]["us_id"]),
  89.                     fn, ln, static_cast<float>(atof(result[i]["us_salary"])) );
  90.                 v.push_back(new_us);
  91.             }
  92.         }
  93.  
  94.     } catch(Exception& exc) {
  95.         cout << ">>> FAILURE: could not retrieve users: " << exc.what() << endl;
  96.     }
  97.  
  98. }
  99.  
  100. // insert a new User in the database
  101. void insert(User& us) {
  102.  
  103.     if (!open_connection(my_connection)) return ;
  104.  
  105.     try {
  106.         Query query = my_connection.query();
  107.         query << "INSERT INTO users VALUES(''," <<
  108.             "\"" << us.get_first_name() << "\"," <<
  109.             "\"" << us.get_last_name() << "\"," <<
  110.             us.get_salary() << ");";
  111.         cout << query << endl;
  112.         query.execute();
  113.     } catch(Exception& exc) {
  114.         cout << ">>> FAILURE: could not insert user" << endl;
  115.     }
  116. }
  117.  
  118. int main() {
  119.     // insert users
  120.     User us1(-1, "jean", "pinon", 1000.0);
  121.     User us2(-1, "john", "pinson", 2000.0);
  122.     User us3(-1, "tom", "robinson", 3000.0);
  123.    
  124.     insert(us1);
  125.     insert(us2);
  126.     insert(us3);
  127.    
  128.     // retrieve users
  129.     vector<User *> users;
  130.     retrieve_all("%in%", users);
  131.     if (users.size() > 0) {
  132.         for (User *us : users) {
  133.             cout << (*us) << endl;
  134.         }
  135.     } else {
  136.         cout << "no user found" << endl;
  137.     }
  138.     return 0;  
  139. }
  140.  
  141.  

Compilation

g++ -o mysql.exe mysql.cpp -I/usr/include/mysql -I/usr/include/mysql++ -lmysqlpp -std=c++11

le résultat de l'exécution est le suivant :

>>> open connection...
>>> SUCCESS
INSERT INTO users VALUES('',"jean","pinon",1000);
INSERT INTO users VALUES('',"john","pinson",2000);
INSERT INTO users VALUES('',"tom","robinson",3000);
SELECT * FROM users WHERE us_last_name LIKE "%in%";
1: jean: pinon: 1000
2: john: pinson: 2000
3: tom: robinson: 3000