Mysql connector

Tutorial?

This is a simple tutorial introducing how to connect SIGVerse to Mysql database.

The example below is created on windows 7 64 bits and Visual studio 2010, the aim is to send the name of an object as a message from the controller to the service provider, then connect to the database to retrieve the target.

Installation of mysql server †

Installation on ubuntu †

• To install mysql server on ubuntu:

$ sudo apt-get install mysql-server

Now mysql server is installed on your server.

Some basic commands †

• To access the database:
  • as root:

    $ mysql

• as simple user:

  $ mysql -u username -p

• To list the existing databases:

  $ show databases;

• To create a new database named "sigverse":

  $ create database sigverse;

• To create a new table named "bottle":

  $ use sigverse;

  this will switch to sigverse database, then:

  $ CREATE TABLE bottle  (name char (50), material char (50), target char (50));

• To insert a new item in table:

  $ INSERT INTO bottle VALUES ('coca','plastic','pet_bottle');

• To select data from a table:

  $ select target from bottle where name = 'coca';

Installation of MySQL Connector/C++ †

Download and extract the Connector/C++ source code.

• Building MySQL Connector/C++ using visual studio: Download and install Cmake.
  • 1) open a new command line and change the location:

    $ cd C:\path_to_mysql_cpp

  • 2) Run CMake to generate build files for your generator:

    $ cmake -G "Visual Studio 9 2008"

  • 3) Open the newly generated project files in the Visual Studio GUI or use a Visual Studio command line to build the driver. The project files contain a variety of different configurations, debug and nondebug versions among them.

Creation of the service provider †

Add dependencies †

• First, you have to download boost and sigverse libraries.

Includes dependencies †

• Open Visual studio 2010 and add new C++ empty project.
• Go to : Project -> References -> Configuration Properties -> C/C++, in "Additional Include Directories", add the following dependencies:
  • C:\boost
  • PATH-TO-MYSQL-CONNECTOR\mysql-connector-c++-1.1.3\driver
  • PATH-TO-MYSQL-CONNECTOR\mysql-connector-c++-1.1.3
  • PATH-TO-SIGSERVICE\SIGService_v2-1-0_vc2010\include

Libraries dependencies †

in the same window, go to linker -> general, in "Additional Library Directories", add the following dependencies:

• PATH-TO-MYSQL-CONNECTOR/mysql-connector-c++-1.1.3\driver\Release
• PATH-TO-SIGSERVICE/SIGService_v2-1-0_vc2010\lib\Debug

SIGVerseDB.cpp:

#include "mysql_connection.h"
// Database include files
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
//Sigverse include files 
#include "SIGService.h"  
#include <tchar.h>  
#include <string>  
 
//namespace
using namespace std;
using namespace sql;
//////////////////////////////////////////
struct vector3{
	double x;
	double y;
	double z;
};  
class MyService : public sigverse::SIGService  
{    
public:  
   MyService(std::string name) : SIGService(name){};  
   ~MyService();  
   void onRecvMsg(sigverse::RecvMsgEvent &evt);  
	void mysqlRead(std::string object);
	void mysqlWrite();
   double onAction(); 
	void onInit();
private:
	string objectName;
};  
 
MyService::~MyService()  
{  
   this->disconnect();  
}    
double MyService::onAction()  
{  
	this->mysqlWrite();
	this->mysqlRead(objectName);
   return 1.0;  
}  
 
void MyService::onRecvMsg(sigverse::RecvMsgEvent &evt)  
{  
	    std::string sender = evt.getSender();  
       std::string msg = evt.getMsg();  
	    objectName = msg;
}
// Write to the database
void MyService::mysqlWrite(){
	try {
 Driver *driver;
 Connection *con;
 Statement *stmt;  
 /* Create a connection */
 driver = get_driver_instance();
 con = driver->connect("tcp://192.168.186.177:3306", "ericgt", "");
 /* Connect to the MySQL test database */
 con->setSchema("sigverse");
 stmt = con->createStatement();
 stmt->execute("DROP TABLE IF EXISTS bottle");
 stmt->execute("DROP TABLE IF EXISTS can");  
 // create table
 stmt->execute("CREATE TABLE bottle  (name char (50), material char (50),    target char (50)) ");
 stmt->execute("INSERT INTO bottle VALUES ('coca','plastic','trash box for pet_bottle')");
 stmt->execute("INSERT INTO bottle VALUES ('water','plastic','trash box for pet_bottle')");
 
  // create can
 stmt->execute("CREATE TABLE can  (name char (50), material char (50), target char (50)) ");
 stmt->execute("INSERT INTO can VALUES ('can','aluminium','trash box for recycle')");
 stmt->execute("INSERT INTO can VALUES ('juice_can','aluminium','trash box for recycle')");  
 delete stmt;
 delete con;
 // Error handler
} catch (sql::SQLException &e) {
 cout << "# ERR: SQLException in " << __FILE__;
 cout << "(" << __FUNCTION__ << ") on line "  << __LINE__ << endl;
 cout << "# ERR: " << e.what();
 cout << " (MySQL error code: " << e.getErrorCode();
 cout << ", SQLState: " << e.getSQLState() <<  " )" << endl;
}
cout << endl;
}
// Read from the database
void MyService::mysqlRead(string name){
	try {
 Driver *driver;
 Connection *con;
 Statement *stmt;
 ResultSet *res;
 PreparedStatement *pstmt;
 
 /* Create a connection */
 driver = get_driver_instance();
 con = driver->connect("tcp://192.168.186.177:3306", "ericgt", "");
 /* Connect to the MySQL test database */
 con->setSchema("sigverse");
  pstmt = con->prepareStatement ("select target from bottle where name = '"+objectName+"';");  
 res = pstmt->executeQuery();
 while (res->next()) { 
string select = res->getString("target"); 
cout<<"target for coca is: "<<select<<endl;
} 
 delete res;
 delete pstmt;
 delete con;
 // Error handler
} catch (sql::SQLException &e) {
 cout << "# ERR: SQLException in " << __FILE__;
 cout << "(" << __FUNCTION__ << ") on line "  << __LINE__ << endl;
 cout << "# ERR: " << e.what();
 cout << " (MySQL error code: " << e.getErrorCode();
 cout << ", SQLState: " << e.getSQLState() <<  " )" << endl;
}
cout << endl;
}
using namespace std;
int main(int argc, char** argv)  
{  
   MyService srv("sigverse_DB");  
   srv.connect("192.168.186.177", 9005);  
	srv.connectToViewer();
   srv.startLoop();  
   return 0;  
system("pause");
return EXIT_SUCCESS;
}

Server †

Controller †

db_controller.cpp

#include "ControllerEvent.h"
#include "Controller.h"
#include "Logger.h"
#include <unistd.h>
#include <algorithm>
#define PI 3.1415926535
#define DEG2RAD(DEG) ( (PI) * (DEG) / 180.0 )
class MyController : public Controller {
 public:
  void onInit(InitEvent &evt);
  double onAction(ActionEvent&);
private:
 std::vector<std::string> m_trashes;
};
void MyController::onInit(InitEvent &evt)
{
 m_trashes.push_back("can_0");
 m_trashes.push_back("can_1");
 m_trashes.push_back("petbottle_0");
 srand((unsigned)time( NULL ));
}
double MyController::onAction(ActionEvent &evt)
{
std::string msg = "coca";
     sendMsg("sigverse_DB",msg);  
 return 0.1;
}
extern "C" Controller * createController() {
 return new MyController;
}

World file †

World.xml

<?xml version="1.0" encoding="utf8"?>
<world name="myworld1">
 <gravity x="0.0" y="-980.7" z="0.0"/>
 <instanciate class="WheelRobot-nii-v1.xml" type="Robot">
   <set-attr-value name="name" value="robot_000"/>
   <set-attr-value name="language" value="c++"/>
   <set-attr-value name="implementation"
                   value="./db_controller.so"/>
   <set-attr-value name="dynamics" value="false"/>
   <set-attr-value name="x" value="0.0"/>
   <set-attr-value name="y" value="30.0"/>
   <set-attr-value name="z" value="10.0"/>
   <set-attr-value name="collision" value="true"/>
   <camera id="1"
           link="LEYE_LINK"
           position="0.0 0.0 3.0"/>
   <camera id="2"
           link="REYE_LINK"
           position="2.0 0.0 3.0"/>
 </instanciate>
 <instanciate class="seSidetable_B.xml">
   <set-attr-value name="name" value="table_0"/>
   <set-attr-value name="dynamics" value="false"/>
   <set-attr-value name="x" value="0.0"/>
   <set-attr-value name="y" value="24.0"/>
   <set-attr-value name="z" value="60.0"/>
   <set-attr-value name="scalex" value="1.5"/>
   <set-attr-value name="scaley" value="1.5"/>
   <set-attr-value name="scalez" value="1.5"/>
 </instanciate>
 <instanciate class="seCannedjuice_200ml_c01.xml">
   <set-attr-value name="name" value="can_0"/>
   <set-attr-value name="dynamics" value="false"/>
   <set-attr-value name="x" value="0.0"/>
   <set-attr-value name="y" value="52.535"/>
   <set-attr-value name="z" value="60.0"/>
   <set-attr-value name="collision" value="true"/>
 </instanciate>
 <instanciate class="seCannedjuice_200ml_c02.xml">
   <set-attr-value name="name" value="can_1"/>
   <set-attr-value name="dynamics" value="false"/>
   <set-attr-value name="x" value="20.0"/>
   <set-attr-value name="y" value="52.535"/>
   <set-attr-value name="z" value="50.0"/>
   <set-attr-value name="collision" value="true"/>
 </instanciate>
 <instanciate class="sePetbottle_500ml_empty_c01.xml">
   <set-attr-value name="name" value="petbottle_0"/>
   <set-attr-value name="dynamics" value="false"/>
   <set-attr-value name="x" value="-20.0"/>
   <set-attr-value name="y" value="59.15"/>
   <set-attr-value name="z" value="50.0"/>
   <set-attr-value name="collision" value="true"/>
 </instanciate>
</world>

Download the project †

• you can use the link below to download the project from GIT repository:

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