mysql connection using ROS

Up:Tutorial?

The aim of this tutorial is to explain how to use mysql database using ROS.

In the example below, data are sent from SIGViewer to SIGVerse controller as messages, which will be sent after that to mysql controller using ROS topic, the mysql controller uses these data to communicate with the database server.

Packages creation †

Create 2 packages, sig_mysql and db_controller:

$ cd ~/catkin_ws/src
$ catkin_create_pkg sig_mysql
$ catkin_create_pkg db_controller

Overview †

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 are sent from the SIGViewer to the controller as messages; then they are published on the database topic to be read after that by the ROS mysql node, this later communicates with the Mysql database by sending/receiving queries.  

Server †

SIGVerse controller †

This controller aims to receive data from SIGViewer and sends them to the mysql controller node.

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 controller †

This controller receives data from the SIGVerse controller then uses them to communicate with the database.

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 †

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 †

Click on the link to download the world file:

mysql_world.xml

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

Running the server †

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:

  $ cd ~/catkin_ws/devel/lib/libsigMysql
  $ sigserver.sh -w ./mysql_world.xml

Download the project †

To download the project from GIT repository, use the following link:

git@socio4.iir.nii.ac.jp:~/SigverseGitServer/unstable/usersContribution/tafifet/mysql_ros.git