[[Tutorial]]

The aim of this tutorial is to explain how to use mysql database using ROS.
In the example below, messages are sent from SIGViewer to SIGVerse controller which will send them to ROS mysql node, this later receives messages and connects to the database to write/read data.


#contents

*Packages creation [#ed1b8136]
Create 2 packages, sig_mysql and db_controller:
 $ cd ~/catkin_ws/src
 $ catkin_create_pkg sig_mysql
 $ catkin_create_pkg db_controller

*Overview[#ed1b8137]
When the server is launched, 2 ros nodes are created, the SIGVerse controller node and ROS mysql node, which involves the creation of a new topic called “database”, it is used to read/write data.
Data is sent from the SIGViewer to the controller as messages; it will be published on the database node to be read then by the ROS mysql node, this later uses this data to send/receives queries to the Mysql database.  

&ref(mysql_ros.PNG,,80%);


*Server [#ed1b9133]
** SIGVerse controller [#ed1b9233]
This controller receives data from SIGViewer

sigMyql.cpp file:

 #include "ControllerEvent.h"
 #include "Controller.h"
 #include "Logger.h"
 #include <unistd.h>
 #include <algorithm>
 #include "ros/ros.h"
 #include "std_msgs/String.h"
 class MyController : public Controller {
  public:
   void onInit(InitEvent &evt);
   double onAction(ActionEvent&);
   void onRecvMsg(RecvMsgEvent &evt);
   ros::Publisher chatter_pub;
 private:
  std::vector <std::string> m_trashes;
  std_msgs::String msg;
 };
 void MyController::onInit(InitEvent &evt)
 {
  int argc =0;
  char** argv = NULL;
  ros::init(argc, argv, "SIG_Mysql");
  ros::NodeHandle n;
  chatter_pub = n.advertise<std_msgs::String>("database", 1);
 }
 double MyController::onAction(ActionEvent &evt)
 {
   return 1.0;
 }
 void MyController::onRecvMsg(RecvMsgEvent &evt){
  msg.data = evt.getMsg();
  if(msg.data != ""){
  LOG_MSG(("message : %s", msg.data.c_str()));
  }
  chatter_pub.publish(msg);
  msg.data ="";
 }
 extern "C" Controller * createController() {
  return new MyController;
 }


** Mysql connector [#ed1b9533]
 mysql_ros.cpp file:

   // ROS includes
 #include <ros/ros.h>
 #include <signal.h>
 #include <termios.h>
 #include <stdio.h>
 #include "std_msgs/String.h"
 // Mysql includes
 #include <my_global.h>
 #include <mysql.h>
    MYSQL *con = mysql_init(NULL);
    int ID = 0;
    std::ostringstream oss;
    std::string querry;
 void static finish_with_error(MYSQL *con)
 {
  fprintf(stderr, "%s\n", mysql_error(con));
  mysql_close(con);
  exit(1);
 }
 void chatterCallback(const std_msgs::String::ConstPtr& msg)
 {
  ID = ID + 1;
  oss << ID;
  std::string IDStr = oss.str();
  oss.str("");
  querry = "INSERT INTO objects VALUES("+IDStr+",'"+msg->data+"','green')";
 if (mysql_query(con, querry.c_str())) {
      finish_with_error(con);
  }
 }
 int main(int argc, char **argv)
 {
  ros::init(argc, argv, "mysql_command");
  ros::NodeHandle n;
  ros::Subscriber sub = n.subscribe("database", 1, chatterCallback);
  if (con == NULL)
  {
      fprintf(stderr, "%s\n", mysql_error(con));
      exit(1);
  }
  if (mysql_real_connect(con, "localhost", "ericgt", "", 
          "sigverse", 0, NULL, 0) == NULL) 
  {
      fprintf(stderr, "%s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }
  if (mysql_query(con, "DROP TABLE IF EXISTS objects")) {
      finish_with_error(con);
 }
  if (mysql_query(con, "CREATE TABLE objects(Id INT, Name TEXT, color TEXT)")) {      
      finish_with_error(con);
  }
  ros::spin();
  return(0);
 }

*Compiling [#ed1b8132]
Add SIGVerse include path to CMakeLists.txt file:

include_directories (PATH-TO-SIGVERSE-LIBRARIES/include/sigverse PATH-TO-SIGVERSE-LIBRARIES/include/sigverse/comm/controller )

Then go to the catkin_ws directory to compile the project:
 $ cd ~/catking_make

 $ catkin_make

*World file [#ed1b8133]

Click on the link to download the world file:
#ref(mysql_world.xml)

The world file should be placed in "~/catkin_ws/devel/lib/libsigMysql", where the .so file is generated.

*Running the server[#ed1b8122]

To run the server you have to follow the following steps:

- Go to catkin_ws and execute the follwing command : 
 $ source ./devel/setup.bash

- Run the ROS system:
 $ roscore
- run the ros mysql node :
 $ rosrun database mysql_ros 
- Go to ~/catkin_ws/devel/lib/libsigMysql and run the sigverse controller:
 $ ~/catkin_ws/devel/lib/libsigMysql
 $ sigserver.sh -w ./mysql_world.xml




*Download the project [#ed1b9563]
To download the project from GIT repository, use the following link:
Front page   New List of pages Search Recent changes   Help   RSS of recent changes