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.
$ sudo apt-get install mysql-server
Now mysql server is installed on your server.
$ mysql
$ mysql -u username -p
$ show databases;
$ create database sigverse;
$ use sigverse;this will switch to sigverse database, then:
$ CREATE TABLE bottle (name char (50), material char (50), target char (50));
$ INSERT INTO bottle VALUES ('coca','plastic','pet_bottle');
$ select target from bottle where name = 'coca';
Download and extract the Connector/C++ source code.
$ cd C:\path_to_mysql_cpp
$ cmake -G "Visual Studio 9 2008"
in the same window, go to linker -> general, in "Additional Library Directories", add the following dependencies:
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; }
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.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>