[[Tutorial]]

//
// This pas was written by Tafifet
//
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.

#contents

*Installation of mysql server [#ed1b8136]
** Installation on ubuntu: [#cdfdb084]
-- To install mysql server on ubuntu:

 $ sudo apt-get install mysql-server

Now mysql server is installed on your server.

**Some basic commands: [#l6db0d8a]

-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++ [#ed1b8136]
Download and extract the [[Connector/C++:http://dev.mysql.com/downloads/connector/cpp/]] source code.

-Building MySQL Connector/C++ using visual studio:
Download and install [[Cmake:http://www.cmake.org/]].
--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[#ed1b8136]
** Add dependencies [#bbf9f5ce]
- First, you have to download [[boost:http://www.boost.org/users/download/]] and  [[sigverse libraries>サービスライブラリ]].

** Includes dependencies: [#x1c3a410]
- 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: [#obe8256d]
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

** Additional dependencies: [#obe8236d]
Go to linker -> Input -> Additional Dependencies, and add the following dependencies:

SIGService.lib
dxguid.lib
dinput8.lib
comctl32.lib
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[#ed1b8137]
**Controller  [#ed1b8139]

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[#ed1b8166]

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 [#ed1b8162]
To download the project from GIT repository, use the following link:

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

[[Tutorial]]
Front page   Edit Diff Backup Upload Copy Rename Reload   New List of pages Search Recent changes   Help   RSS of recent changes