std::string Mysql::getOne(std::string sql){ Query query = _con.query(sql); std::string str = ""; UseQueryResult res = query.use(); mysqlpp::Row row = res.fetch_row(); str = (std::string)row[0]; return str; }
void MySQLStorage::init (void) { vector<pair<string, string> > fields; fields.push_back (make_pair ("id", "int(10) unsigned NOT NULL auto_increment")); fields.push_back (make_pair ("eventId", "varchar(255) collate latin1_bin NOT NULL default ''")); fields.push_back (make_pair ("dataId", "varchar(255) collate latin1_bin NOT NULL default ''")); fields.push_back (make_pair ("message", "varchar(255) collate latin1_bin NOT NULL default ''")); fields.push_back (make_pair ("errorCode", "int(10) unsigned NOT NULL default 0")); fields.push_back (make_pair ("datastreamId", "varchar(255) collate latin1_bin NOT NULL default ''")); fields.push_back (make_pair ("datastreamName", "varchar(255) collate latin1_bin NOT NULL default ''")); fields.push_back (make_pair ("hostname", "varchar(255) collate latin1_bin NOT NULL default ''")); fields.push_back (make_pair ("logLevel", "int(10) unsigned NOT NULL default 0")); // could be enum? fields.push_back (make_pair ("pid", "int(10) unsigned NOT NULL default 0")); fields.push_back (make_pair ("time", "DATETIME NOT NULL")); fields.push_back (make_pair ("ms", "int(10) unsigned NOT NULL default 0")); string table (MySQLStorage::defaultDbTable); try { this->dbCheck(); Query query (this->conn.query()); // Create the database if necessary query << "CREATE TABLE IF NOT EXISTS `" << table << "` " << "("; bool first (true); auto iField (fields.begin()), fEnd (fields.end()); while (iField != fEnd) { if (!first) { query << ", "; } else { first = false; } query << "`" << iField->first << "` " << iField->second; ++iField; } query << ", PRIMARY KEY (`id`)" << ") " << "ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin; "; if (query.exec() == false) { stringstream ee; ee << "Query failed, error: '" << query.error() << "'"; throw runtime_error (ee.str()); } // Check that all of the columns exist iField = fields.begin(); while (iField != fEnd) { query.reset(); query << "SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'dt'" << " AND TABLE_NAME = '" << table << "' AND COLUMN_NAME = '" << iField->first << "';"; UseQueryResult res (query.use()); if (!res) { stringstream ee; ee << "Query failed, error: '" << query.error() << "'"; throw runtime_error (ee.str()); } if (Row row = res.fetch_row()) { } else { // Need to add the column //DBG ("Couldn't find the column '" << iField->first << "'"); query.reset(); query << "ALTER TABLE `" << table << "` ADD `" << iField->first << "` " << iField->second << ";"; //DBG (name << " ALTER query string: " << query.str()); if (query.exec() == false) { stringstream ee; ee << "Query failed, error: '" << query.error() << "'"; throw runtime_error (ee.str()); } } ++iField; } } catch (mysqlpp::BadQuery e) { this->handleBadQuery (e); } catch (mysqlpp::Exception e) { this->handleException (e); } }
void TestDatabase::testQuery() { DatabaseAPI::connectToDatabase(); Query query = DatabaseAPI::getQuery(); try { // No errors on CREATE TABLE query << "CREATE TABLE testTable ( num int, str varchar(4) )"; query.execute(); ++testSuccesses; } catch (Exception e) { ++testFailures; } try { // No errors on INSERT query.clear(); query << "INSERT INTO testTable VALUES (" << mysqlpp::quote << 1 << ", " << mysqlpp::quote << "one" << ")"; query.execute(); ++testSuccesses; } catch (Exception e) { ++testFailures; } try { // No errors on SELECT query.clear(); query << "SELECT * FROM testTable"; UseQueryResult result = query.use(); Row row; if (row = result.fetch_row()) { // Row was inserted ++testSuccesses; if (row[0] == "1") { // Proper int inserted ++testSuccesses; } else ++testFailures; if (row[1] == "one") { // Proper string inserted ++testSuccesses; } else ++testFailures; } else ++testFailures; } catch (Exception e) { ++testFailures; std::cout << "EXC" << std::endl; } try { // No errors on DROP TABLE query.clear(); query << "DROP TABLE testTable"; query.execute(); ++testSuccesses; } catch (Exception e) { ++testFailures; } std::cout << "Query tests complete. Successes: " << testSuccesses << "\tFailures: " << testFailures << std::endl; resetTestCounts(); }