[[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: git@socio4.iir.nii.ac.jp:~/SigverseGitServer/unstable/usersContribution/tafifet/sigverseMYSQl.git