bool db_writer::Connect(void){
ofxXmlSettings config;
config.loadFile("measureconfig.xml");
MySql_enabled = config.getValue("MYSQL:enabled",true);
if (MySql_enabled){
ofLogVerbose("APP") << "Iniciando MySQL...";
try {
driver = get_driver_instance();
string server, usr, pwd,schema;
server = config.getValue("MYSQL:server","tcp://localhost:3306");
usr = config.getValue("MYSQL:usr","mastermidictl");
pwd = config.getValue("MYSQL:pwd","frozen");
schema = config.getValue("MYSQL:schema","master");
con = driver->connect(server.c_str(), usr.c_str(), pwd.c_str());
con->setSchema(schema.c_str());
connected = true;
ofLogVerbose("MySQL") << "MySQL inicializado...";
return true;
} catch (sql::SQLException &e) {
ofLogVerbose("MySQL") << "# ERR: SQLException in " << __FILE__ << "(" << __FUNCTION__ << ") on line " << __LINE__ ;
ofLogVerbose("MySQL") << "# ERR: " << e.what() << " (MySQL error code: " << e.getErrorCode() << ", SQLState: " << e.getSQLState() << " )";
connected = false;
return false;
}
} else {return false;}
}
authenticator::authenticator(dbinfo dbInfo, logger *_logger)
{
log = _logger;
driver = get_driver_instance();
con = driver->connect(dbInfo.host, dbInfo.user, dbInfo.pass);
con->setSchema(dbInfo.name);
}
void HomeControlDal::writeHeaterState(const std::string& roomId, bool state)
{
try
{
VLOG(1) << "Writing heaterstate: " << state << ", from room: " << roomId;
std::stringstream insert;
insert << "INSERT INTO RoomHeaterState(idRoom, heater, date) ";
insert << " SELECT idRoom, " << state << ", NOW() FROM Room WHERE RoomId = '" << roomId << "'";
sql::Driver *driver;
sql::Connection *con;
sql::Statement *stmt;
/* Create a connection */
driver = get_driver_instance();
driver->threadInit();
con = driver->connect(mServer, mUser, mPwd);
con->setSchema("HC_DB");
stmt = con->createStatement();
stmt->execute(insert.str());
delete stmt;
con->close();
delete con;
driver->threadEnd();
}
catch (sql::SQLException &ex)
{
LOG(ERROR) << "Write heater state, SQLExceptin: " << ex.what() << ", MySQL error code: " << ex.getErrorCode() << ", SQLState: " << ex.getSQLState();
}
}
databaseConnector::databaseConnector( std::string hostname,std::string username, std::string password, std::string schema ) {
sql::Driver* driver = get_driver_instance();
con = driver->connect ( hostname,username,password );
con->setSchema ( schema );
stmt = con->createStatement();
}
RoomConfig* HomeControlDal::findRoomBySensorId(const std::string& sensorId)
{
LOG(INFO) << "Find room by SendorId: " << sensorId;
RoomConfig* result = nullptr;
try
{
std::stringstream select;
select << "SELECT slaveSensor.sensorAddress, Room.Name, Room.RoomId, Room.HeaterOutput FROM HC_DB.TemperatureSensor as masterSensor ";
select << " INNER JOIN Room on masterSensor.idRoom = Room.idRoom ";
select << " INNER JOIN TemperatureSensor as slaveSensor on masterSensor.idRoom = slaveSensor.idRoom ";
select << " WHERE masterSensor.sensorAddress = '" << sensorId << "'";
sql::Driver *driver;
sql::Connection *con;
sql::Statement *stmt;
/* Create a connection */
driver = get_driver_instance();
driver->threadInit();
con = driver->connect(mServer, mUser, mPwd);
/* Connect to the MySQL test database */
con->setSchema("HC_DB");
stmt = con->createStatement();
sql::ResultSet *res = stmt->executeQuery(select.str());
if (res->rowsCount() > 0)
{
result = new RoomConfig;
while (res->next())
{
result->RoomName = res->getString("Name");
result->RoomId = res->getString("RoomId");
result->HeaterOutput = res->getInt("HeaterOutput");
result->SensorIds.push_back(res->getString("sensorAddress"));
}
}
delete res;
delete stmt;
con->close();
delete con;
driver->threadEnd();
}
catch (sql::SQLException &ex)
{
LOG(ERROR) << "clientConnected, SQLExceptin: " << ex.what() << ", MySQL error code: " << ex.getErrorCode() << ", SQLState: " << ex.getSQLState();
}
if (result == nullptr)
{
LOG(INFO) << "Room not found";
}
else
{
LOG(INFO) << "Room found: " << result->RoomName << ", config: " << result->toString();
}
return result;
}
bool Database::init()
{
Driver* driver = get_driver_instance();
QString loginServerUrl = m_settings.value("loginServerUrl", DefaultSettings::loginServerUrl()).toString();
QString serverUserName = m_settings.value("serverUserName").toString();
QString serverPassword = m_settings.value("serverPassword").toString();
try
{
m_database = driver->connect(loginServerUrl.toLatin1().data(),
serverUserName.toLatin1().data(),
serverPassword.toLatin1().data());
m_database->setSchema("tis");
}
catch (SQLException &e)
{
emit writeToConsole("DB connect failed: " + QLatin1String(e.getSQLStateCStr()));
return false;
}
m_isConnected = true;
return true;
}
void CMyProblem::ReadFromDB(int idproblem)
{
try {
sql::Driver *driver;
sql::Connection *con;
sql::PreparedStatement *ProblemStmt;
sql::ResultSet *res;
/* Create a connection */
driver = get_driver_instance();
con = driver->connect("tcp://localhost:3306", "root", "TestPassword");
/* Connect to the MySQL test database */
con->setSchema("theproblem");
//cout<< "Connected succesfully";
ProblemStmt = con->prepareStatement("SELECT name, n, p FROM problems WHERE idproblems=?");
ProblemStmt->setInt(1, idproblem);
res = ProblemStmt->executeQuery();
delete ProblemStmt;
//cout<< "Statment executed succesfully";
res->next();
name = res->getString("name");
n = res->getInt("n");
p = res->getInt("p");
delete res;
//cout << name << n << p;
_alloc(n);
ProblemStmt = con->prepareStatement(
"SELECT i, r, w FROM parameters WHERE idproblems=?"
);
ProblemStmt->setInt(1, idproblem);
res = ProblemStmt->executeQuery();
int i; //int r; double w;
while(res->next())
{
i = res->getInt("i");
Set_r(i-1,res->getInt("r"));
Set_w(i-1,res->getDouble("w"));
//cout << res->getInt("i") << " " << res->getInt("r") << " " << res->getDouble("w") << endl;
}
delete res;
delete ProblemStmt;
delete con;
}
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;
}
}
CMySqlDataBasePlus::CMySqlDataBasePlus()
{
m_driver = get_driver_instance();
m_connection = NULL;
m_stmt = NULL;
m_prestmt.reset(NULL);
}
SQLManager::SQLManager()
{
mDriver = get_driver_instance();
if (mDriver == NULL)
mp::Console::errprintln("An error occurred while creating SQLManager: get_driver_instance failed.");
mLastLink = NULL;
}
std::unique_ptr<sql::Connection> connect(){
sql::Driver* driver = get_driver_instance();
std::unique_ptr<sql::Connection> apConnection(
driver->connect(MYSQL_SERVER, MYSQL_USERNAME, MYSQL_PASSWORD)
);
apConnection->setSchema(MYSQL_DATABASE);
return apConnection;
}
int CMyProblem::WriteToDB()
{
int idproblems;
try {
sql::Driver *driver;
sql::Connection *con;
sql::PreparedStatement *PrepStmt;
sql::ResultSet *res;
/* Create a connection */
driver = get_driver_instance();
con = driver->connect("tcp://localhost:3306", "root", "TestPassword");
/* Connect to the MySQL test database */
con->setSchema("theproblem");
PrepStmt = con->prepareStatement(
"INSERT INTO problems(name, n, p) values (?,?,?)"
);
PrepStmt->setString(1, name);
PrepStmt->setInt(2, n);
PrepStmt->setInt(3, p);
PrepStmt->execute();
delete PrepStmt;
PrepStmt = con->prepareStatement(
"SELECT LAST_INSERT_ID()"
);
res = PrepStmt->executeQuery();
delete PrepStmt;
res->next();
idproblems = res->getInt(1);
delete res;
PrepStmt = con->prepareStatement(
"INSERT INTO parameters(idproblems, i, r, w) values (?,?,?,?)"
);
PrepStmt->setInt(1, idproblems);
for (int i=0; i<n; i++)
{
PrepStmt->setInt(2, i+1);
PrepStmt->setInt(3, r[i]);
PrepStmt->setDouble(4, w[i]);
PrepStmt->execute();
}
delete PrepStmt;
delete con;
}
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;
}
return idproblems;
}
sql::Connection* GetConnection(){
sql::Driver *driver;
sql::Connection *con;
/* Create a connection */
driver = get_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "root", "root");
return con;
}
sql::Connection* GetConnection(string host, string username, string password){
sql::Driver *driver;
sql::Connection *con;
/* Create a connection */
driver = get_driver_instance();
con = driver->connect(host, username, password);
return con;
}
DBConnection::DBConnection() {
try {
driver_ = get_driver_instance();
connect();
} catch (sql::SQLException e) {
LOG(Log::ERROR, "Connection to database failed: " +
std::string(e.what()));
}
// fut_ = std::async(std::launch::async, &DBQueue::execute, &commands_);
}
float ConnectToDatabase::pobierzCeneP(string query) {
float retValue=0;
string url(EXAMPLE_HOST);
const string user(EXAMPLE_USER);
const string pass(EXAMPLE_PASS);
const string database(EXAMPLE_DB);
try {
/* INSERT TUTORIAL CODE HERE! */
Driver* driver = get_driver_instance();
auto_ptr<Connection> con(driver->connect(url, user, pass));
con->setSchema(database);
auto_ptr<Statement> stmt(con->createStatement());
// We need not check the return value explicitly. If it indicates
// an error, Connector/C++ generates an exception.
// executeQuery() wysyla zapytanie SELECT zwraca resultSet
// executeUpdate() wysyla zapytanie INSERT, UPDATE lub DELETE i nie zwraca zadej wartosci
// Jeśli nie wiesz z gory jakiego zapytania uzyjesz, uzyj funkcji execute()
// execute() zwraca "true" jesli zapytaniem SQL bylo SELECT i "false" jesli - INSERT, UPDATE lub DELETE
auto_ptr<ResultSet> res (stmt->executeQuery(query));
while (res->next()) {
/* Access column data by numeric offset, 1 is the first column */
retValue = res->getInt(1);
}
/* END OF TUTORIAL CODE */
} catch (sql::SQLException &e) {
/*
MySQL Connector/C++ throws three different exceptions:
- sql::MethodNotImplementedException (derived from sql::SQLException)
- sql::InvalidArgumentException (derived from sql::SQLException)
- sql::SQLException (derived from std::runtime_error)
*/
cout << "# ERR: SQLException in " << __FILE__;
cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
/* what() (derived from std::runtime_error) fetches error message */
cout << "# ERR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << " )" << endl;
}
return retValue;
};
sql::Connection * DataBase::connect(
const std::string& server, const std::string& user, const std::string& password, const std::string& database)
{
/* Create a connection */
driver = get_driver_instance();
con = driver->connect(server, user, password);
/* Connect to the MySQL test database */
con->setSchema(database);
return con;
}
ConexionDB::ConexionDB(string db_name, string user, string password, string host){
this->DB_host = host;
this->DB_user = user;
this->DB_password = password;
this->DB_name = db_name;
this->driver = get_driver_instance();
this->con = driver->connect("tcp://" + this->DB_host,this->DB_user, this->DB_password);
this->con->setSchema(this->DB_name);
}
int MySqlDatabase::createConn(){
try {
driver = get_driver_instance();
con = driver->connect(DBHOST,USER,PASSWORD);
con -> setSchema(DATABASE);
}catch(SQLException &e) {
std::cout<<"ERROR :"<<e.what();
return 0;
}
return 1;
}
double HomeControlDal::getSensorCalibration(const std::string& sensorId)
{
LOG(INFO) << "Find Calibration for SendorId: " << sensorId;
double result = 0;
try
{
std::stringstream select;
select << "SELECT calibration FROM HC_DB.TemperatureSensor ";
select << " WHERE sensorAddress = '" << sensorId << "'";
sql::Driver *driver;
sql::Connection *con;
sql::Statement *stmt;
/* Create a connection */
driver = get_driver_instance();
driver->threadInit();
con = driver->connect(mServer, mUser, mPwd);
/* Connect to the MySQL test database */
con->setSchema("HC_DB");
stmt = con->createStatement();
sql::ResultSet *res = stmt->executeQuery(select.str());
if (res->rowsCount() > 0)
{
while (res->next())
{
result = res->getDouble("calibration");
}
}
else
{
LOG(ERROR) << "Calibration not found";
}
delete res;
delete stmt;
con->close();
delete con;
driver->threadEnd();
}
catch (sql::SQLException &ex)
{
LOG(ERROR) << "clientConnected, SQLExceptin: " << ex.what() << ", MySQL error code: " << ex.getErrorCode() << ", SQLState: " << ex.getSQLState();
}
LOG(INFO) << "Calibration for sensor: " << sensorId << ": " << result;
return result;
}
int HomeControlDal::locationInterval(const std::string& clientId)
{
VLOG(1) << "Find location interval for client: " << clientId;
int result = 0;
try
{
std::stringstream select;
select << "SELECT locationInterval FROM HC_DB.Client ";
select << " WHERE clientName = '" << clientId << "'";
sql::Driver *driver;
sql::Connection *con;
sql::Statement *stmt;
/* Create a connection */
driver = get_driver_instance();
driver->threadInit();
con = driver->connect(mServer, mUser, mPwd);
/* Connect to the MySQL test database */
con->setSchema("HC_DB");
stmt = con->createStatement();
sql::ResultSet *res = stmt->executeQuery(select.str());
if (res->rowsCount() > 0)
{
while (res->next())
{
result = res->getInt("locationInterval");
}
}
else
{
LOG(ERROR) << "Interval not found for client: " << clientId;
}
delete res;
delete stmt;
con->close();
delete con;
driver->threadEnd();
}
catch (sql::SQLException &ex)
{
LOG(ERROR) << "clientConnected, SQLExceptin: " << ex.what() << ", MySQL error code: " << ex.getErrorCode() << ", SQLState: " << ex.getSQLState();
}
VLOG(1) << "Location interval for client: " << clientId << ": " << result;
return result;
}
void DBWorker::_Connect()
{
driver=0;
con=0;
try {
driver = get_driver_instance();
con = driver->connect("tcp://localhost:3306", "root", "TestPassword");
/* Connect to the MySQL test database */
con->setSchema("theproblem");
}
catch (sql::SQLException &e) {
SQLError(e);
}
}
/**
*
* Connects to the database
*
* Note that this expects that the database driver has setup all the things in
* the connection.
*
* @return A QueryResult. error.isError will be false on success.
*/
QueryResult DatabaseConnection::connect()
{
QueryResult result;
if (driver == nullptr) {
// Get driver instance
driver = get_driver_instance();
// Init SQL thread
driver->threadInit();
}
if (connection == nullptr) {
try {
connection = driver->connect(hostname, username,
password);
} catch (SQLException &e) {
result.error.isError = true;
result.error.code = e.getErrorCode();
result.error.string = e.getSQLState() + ": " + e.what();
return result;
}
// Reset result
result = QueryResult();
// Get connection id
result = this->execute(VariantVector() << "SELECT CONNECTION_ID()");
if (result.rows.size() == 0 || result.error.isError) {
// No row returned
return result;
}
connection_id = result.rows.front().front().toUInt();
// Reset result
result = QueryResult();
return result;
}
// Successful connection?
return result;
}
MySQLTable::MySQLTable(string host, string user, string pwd, string dbname,
string tablename, string tablefields) {
con = NULL;
this->tablename = tablename;
driver = get_driver_instance();
con = driver->connect(host, user, pwd);
con->setSchema(dbname);
//Extract fields
std::stringstream ss(tablefields);
std::string item;
while (std::getline(ss, item, ',')) {
this->fieldTables.push_back(item);
}
}
void MySqlHandler::init_connection(){
// Initiating the connector
try {
driver = get_driver_instance(); // gets the driver
con = driver->connect("tcp://"+HOST, DB, PWD); // opens a connection
con->setSchema("imt3601h13gr01"); // what table should it use
cout << "\n MySql Connection Established ";
} 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;
}
}
void database::run() {
sql::Connection *conn = NULL;
sql::Statement *stmt = NULL;
string query;
stringstream host;
host << "tcp://" << config::get("pool.host") << ":" << config::iget("pool.port");
try {
if(database::driver == NULL)
database::driver = get_driver_instance();
}
catch(sql::SQLException &e) {
BOOST_LOG_TRIVIAL(debug) << "(MySQL error code: " << e.getErrorCode() << ", SQLState: " << e.getSQLState() << ") " << e.what();
}
BOOST_LOG_TRIVIAL(debug) << "POOL INIT";
while(true) {
try {
conn = driver->connect(host.str(), config::get("pool.user"), config::get("pool.pass"));
conn->setSchema(config::get("pool.name"));
stmt = conn->createStatement();
while(true) {
if(memory.size() > 0 && !(query = memory.front_pop()).empty())
stmt->execute(query);
else
boost::this_thread::sleep(boost::posix_time::milliseconds(100));
}
}
catch(sql::SQLException &e) {
BOOST_LOG_TRIVIAL(debug) << "(MySQL error code: " << e.getErrorCode() << ", SQLState: " << e.getSQLState() << ") " << e.what();
}
delete stmt;
delete conn;
boost::this_thread::sleep(boost::posix_time::seconds(1));
}
}
// Any exceptions thrown here should be caught elsewhere
std::shared_ptr<Connection> create() {
// Get the driver
sql::Driver *driver;
driver=get_driver_instance();
// Create the connection
std::shared_ptr<MySQLConnection>conn(new MySQLConnection());
// Connect
conn->sql_connection = std::shared_ptr<sql::Connection>(driver->connect(this->server,this->username,this->password));
conn->sql_connection->setSchema("paydw");
//conn->setAutoCommit(0);
return std::static_pointer_cast<Connection>(conn);
};
bool CMySQL::initConnector ( ){
//...Get connector
if ( driver == NULL ){
driver = get_driver_instance();
driver->threadInit();
}
//...Get meta
//dbcon_meta = con -> getMetaData();
//...Print info
//printf ("%s Version: %i.%i.%i \n" , driver->getMajorVersion() ,
// driver->getMinorVersion() , driver->getPatchVersion() );
return driver != NULL;
}
Connection * Database::getConnection() throw (SQLException)
{
if (dbConnection == NULL)
{
driver = get_driver_instance();
if (driver != NULL)
{
dbConnection = driver ->connect(hostname, username, password);
dbConnection ->setSchema(schema);
}
else
{
throw SQLException("Unable to find a driver");
}
}
return dbConnection;
}
sql::Connection* Database::getConnection()
{
if(m_transaction == NULL)
{
sql::Connection* con = NULL;
con = get_driver_instance()->connect(m_url,m_user,m_password);
if(con == NULL)
{
return NULL;
}
con->setSchema(m_schema);
return con;
}
return m_transaction;
}
CMySqlDriver::CMySqlDriver(void)
:driver(0)
,con(0)
,stmt(0)
{
try
{
driver = get_driver_instance();
}
catch (sql::SQLException &e)
{
std::stringstream strss;
strss<<"# ERR: SQLException in "<<__FILE__<<"("<<__FUNCTION__<<")"
<<" on line "<<__LINE__<<"\n";
strss<<"# ERR:"<<e.what()<<" (MySQL error code:"<<e.getErrorCode()
<<", SQLState:"<<e.getSQLState()<<")";
WriteLog(strss.str());
}
}
