- The added line is THIS COLOR.
- The deleted line is THIS COLOR.
[[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.
#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 depository, use the following link:
To download the project from GIT repository, use the following link:
git@socio4.iir.nii.ac.jp:~/SigverseGitServer/unstable/usersContribution/tafifet/sigverseMYSQl.git