PlayerInfo * db_findPlayerInfo(Connection *conn, const std::string &sql)
{
PlayerInfo *info = NULL;
Statement *stmt = conn->createStatement();
ResultSet *res = stmt->executeQuery(sql.c_str());
if (res->next())
{
info = new PlayerInfo;
info->user_id = res->getUInt("user_id");
std::string user_name = res->getString("user_name");
size_t size = user_name.size() < MAX_NAME ? user_name.size() : MAX_NAME;
memcpy(info->user_name, user_name.c_str(), size);
info->level = res->getInt("level");
info->gender = res->getInt("gender");
info->role_id = res->getInt("role_id");
info->scene_id = res->getInt("scene_id");
info->x = res->getInt("x");
info->y = res->getInt("y");
std::string user_pwd = res->getString("user_pwd");
size = user_pwd.size() < MAX_PWD ? user_pwd.size() : MAX_PWD;
memcpy(info->user_pwd, user_pwd.c_str(), size);
}
delete stmt;
delete res;
return info;
}
//through the product info get the best channel to create order
//@return the channel num
int ChargeBusiness::SelectBestChannel(int value, int province, int op, vector<ChannelInfo>& channels){
int ret = 0;
Statement *stmt = NULL;
try{
stmt = conn->createStatement(SQL_SELECT_CHANNEL);
stmt->setInt(1, value); //product value
stmt->setInt(2, province); //provice of product
stmt->setInt(3, op); //operator of product
ResultSet *rs = stmt->executeQuery();
while(rs->next())
{
ChannelInfo channel;
channel.channelId = rs->getInt(1); //channel id
//channel.channelName = rs->getString(2); //channel name
channel.sname = rs->getString(2); //short name
channel.priority = rs->getInt(3); //priority
channel.repeat = rs->getInt(4); //repeat times
channel.discount = rs->getFloat(5); //discount of product
channel.interfaceName = rs->getString(6); //the interface of channel,through it to find the class to handle order
channel.pid = rs->getString(7); //the product id given by channel
channel.private_key = rs->getString(8); //the private key given by channel
channel.query_interval = rs->getInt(9); //query order interval
channels.push_back(channel);
ret++;
}
stmt->closeResultSet(rs);
}catch(std::exception &e){
HandleException(e);
ret = -1;
}
Finish();
if(stmt)
conn->terminateStatement(stmt);
return ret;
}
int main(int argc, char** argv)
{
USE_NETWORK;
using namespace sql;
Statement *state;
ResultSet *result;
Connection *con = DbLib::instance().connect("tcp://127.0.0.1:3306", "root", "");
state = con->createStatement();
state->execute("use net_db");
result = state->executeQuery("select * from g_user");
//printf(" id user_id user_name level profession\n");
printf("%7s%10s%20s%7s%10s\n", "id", "user_id", "user_name", "level", "role_id");
while(result->next())
{
int64_t id = result->getInt64("id");
int user_id = result->getInt("user_id");
string user_name = result->getString("user_name");
int level = result->getInt("level");
int profession = result->getInt("role_id");
printf(" %7d ", id);
printf(" %10d ", user_id);
printf(" %20s ", user_name.c_str());
printf(" %7d ", level);
printf(" %10d \n", profession);
}
delete state;
delete con;
system("pause");
return 0;
}
void MergedAuthorDao::load_into(unordered_map<int,int>& id_map)
{
Connection* conn = NULL;
Statement* stat = NULL;
ResultSet* rs = NULL;
int from, to;
try
{
conn = ConnectionPool::getInstance()->getConnection();
stat = conn->createStatement();
rs = stat->executeQuery(sql_fetch);
while(rs->next())
{
from = rs->getInt(1);
to = rs->getInt(2);
id_map[from] = to;
}
ConnectionPool::close(conn, stat, rs);
}
catch (sql::SQLException &e)
{
LOG(ERROR) << boost::str(boost::format("# ERR: SQLException in %1% %2%") % __FILE__ %__FUNCTION__);
LOG(ERROR) << boost::str(boost::format("%1% error code %2%") %e.what() % e.getErrorCode());
}
}
///ret = 2 no such product
///ret = 1 exception not find product
int ChargeBusiness::GetTmallProduct(string productId, Product &product){
int ret = 0;
Statement *stmt = NULL;
try{
//初始化数据库连接
stmt = conn->createStatement(SQL_QUERY_PRODUCT);
stmt->setString(1, productId);
ResultSet *rs = stmt->executeQuery();
while (rs->next())
{
product.productId = rs->getString(1);
product.provinceId = rs->getInt(2);
product.price = rs->getInt(3);
product.op = rs->getInt(4);
}
stmt->closeResultSet(rs);
if(product.productId.empty() || product.provinceId == 0){
ret = 2;
}
}
catch(std::exception &e)
{
HandleException(e);
ret = 1;
}
Finish();
if(stmt)
conn->terminateStatement(stmt);
return ret;
}
int ChargeBusiness::QueryOrder(TopupInfo *topupInfo){
int ret = 0;
Statement *stmt = NULL;
try{
stmt = conn->createStatement(SQL_CREATE_ORDER);
string tbOrderNo = topupInfo->qs_info.tbOrderNo;
fprintf(stderr, "ChargeBusiness::QueryOrder %s\n", tbOrderNo.c_str());
stmt->setString(1, tbOrderNo);
ResultSet *rs = stmt->executeQuery();
while(rs->next())
{
topupInfo->qs_info.tbOrderNo = rs->getString(1);
topupInfo->qs_info.coopOrderNo = rs->getString(2);
topupInfo->status = (OrderStatus)rs->getInt(3);
string ts = rs->getString(4);
trans_time(ts, topupInfo->update_time);
ret++;
}
conn->terminateStatement(stmt);
}catch(SQLException &sqlExcp){
HandleException(sqlExcp);
ret = -1;
}catch(std::exception &e){
HandleException(e);
ret = -1;
}
Finish();
if(stmt)
conn->terminateStatement(stmt);
return ret;
}
unsigned int CallManager::storeCall(Connection *sqlCon, bool phone, unsigned int client, unsigned int translator)
{
if (!sqlCon)
return 0;
PreparedStatement *pstmt = sqlCon->prepareStatement(
"INSERT INTO calls SET phone=(?), client=(?), translator=(?), request_time=NOW()");
pstmt->setInt(1, phone);
pstmt->setInt(2, client);
pstmt->setInt(3, translator);
try {
pstmt->execute();
} catch (SQLException &ex) {
log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
delete pstmt;
return 0;
}
delete pstmt;
pstmt = sqlCon->prepareStatement("SELECT LAST_INSERT_ID()");
ResultSet *res;
try {
res = pstmt->executeQuery();
} catch (SQLException &ex) {
log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
delete pstmt;
return 0;
}
delete pstmt;
res->first();
unsigned int id = res->getInt(1);
delete res;
return id;
}
//验证用户微信号
int ChargeBusiness::VerifyWeixin(string userId, string openId){
Statement *stmt = NULL;
int ret = 0;
try{
stmt = conn->createStatement(QUERY_USER_SQL);
stmt->setString(1, userId);
ResultSet *rs = stmt->executeQuery();
string user_open_id;
int id = -1;
while(rs->next())
{
id = rs->getInt(1);
user_open_id = rs->getString(2);
}
if(id == -1){
ret = 1;
}else if(user_open_id.empty()){
conn->terminateStatement(stmt);
stmt = conn->createStatement(VERIFY_SQL);
stmt->setString(1, openId);
stmt->setString(2, userId);
stmt->executeUpdate();
}else{
ret = 2;
}
}catch(SQLException &sqlExcp){
HandleException(sqlExcp);
}catch(std::exception &e){
HandleException(e);
}
if(stmt)
conn->terminateStatement(stmt);
return ret;
}
/**
* Insert the sheet into the database
*/
int SheetController::AddSheet(Sheet& sheet)
{
PreparedStatement* stmt = conn->prepareStatement("INSERT INTO sheets (name, username, layoutid) VALUES (?,?,?)");
//Populate the query with the values from the passed sheet
stmt->setString(1, sheet.name);
stmt->setString(2, sheet.username);
stmt->setInt(3, sheet.layoutId);
//Insert
stmt->executeUpdate();
delete stmt;
//Create another query to get the ID of the inserted sheet
Statement* lastStmt = conn->createStatement();
ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()");
if(rs != NULL)
{
while(rs->next())
{
int lastId = rs->getInt("LAST_INSERT_ID()");
delete rs;
delete lastStmt;
return lastId;
}
}
else
{
delete lastStmt;
return -1;
}
return -1;
}
/**
* Insert the stat into the database
*/
int StatController::AddStat(Stat& stat)
{
PreparedStatement* stmt = conn->prepareStatement("INSERT INTO stats (users, sheets, feeds, items, comments) VALUES (?,?,?,?,?)");
//Populate the query based on the passed stat
stmt->setInt(1, stat.users);
stmt->setInt(2, stat.sheets);
stmt->setInt(3, stat.feeds);
stmt->setInt(4, stat.items);
stmt->setInt(5, stat.comments);
//Insert
stmt->executeUpdate();
delete stmt;
//Create another query to get the ID of the inserted stat
Statement* lastStmt = conn->createStatement();
ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()");
if(rs != NULL)
{
while(rs->next())
{
int lastId = rs->getInt("LAST_INSERT_ID()");
delete rs;
delete lastStmt;
return lastId;
}
}
else
{
delete lastStmt;
return -1;
}
return -1;
}
/**
* Generate a layout based on the data in a result set row
*/
Layout LayoutController::GenerateLayout(ResultSet& rs)
{
Layout l;
l.id = rs.getInt("id");
return l;
}
/**
* The testing logic of the test case.
*/
dvoid select ()
{
cout << "occipool - Selecting records using ConnectionPool interface" <<
endl;
const string poolUserName = "******";
const string poolPassword = "******";
const string connectString = "";
const string username = "******";
const string passWord = "******";
unsigned int maxConn = 5;
unsigned int minConn = 3;
unsigned int incrConn = 2;
ConnectionPool *connPool;
try{
connPool = env->createConnectionPool
(poolUserName, poolPassword, connectString, minConn, maxConn, incrConn);
if (connPool)
cout << "SUCCESS - createConnectionPool" << endl;
else
cout << "FAILURE - createConnectionPool" << endl;
con = connPool->createConnection (username, passWord);
if (con)
cout << "SUCCESS - createConnection" << endl;
else
cout << "FAILURE - createConnection" << endl;
}catch(SQLException ex)
{
cout<<"Exception thrown for createConnectionPool"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
return;
}
cout << "retrieving the data" << endl;
try{
stmt = con->createStatement
("SELECT author_id, author_name FROM author_tab");
ResultSet *rset = stmt->executeQuery();
while (rset->next())
{
cout << "author_id:" << rset->getInt (1) << endl;
cout << "author_name:" << rset->getString (2) << endl;
}
stmt->closeResultSet (rset);
con->terminateStatement (stmt);
connPool->terminateConnection (con);
env->terminateConnectionPool (connPool);
}catch(SQLException ex)
{
cout<<"Exception thrown for retrieving data"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
cout << "occipool - done" << endl;
} // end of test (Connection *)
void CHandleMessage::handleGetStoreBooksList (Buf *p) {
#ifdef __DEBUG_HANDLE_HEAD_
cout << "CT_GetStoreBooksList" << endl;
#endif
const epUser* pUser = EPMANAGER->getUserByFd(p->getfd());
CHECK_P(pUser);
cGetPublicBooksList cgbl;
UNPACKET(p, cgbl);
bookList book_list;
bookNode* book_node;
try {
MutexLockGuard guard(DATABASE->m_mutex);
PreparedStatement* pstmt = DATABASE->preStatement(SQL_GET_COURSE_LIST_IN_STORE);
ResultSet* prst = pstmt->executeQuery ();
while (prst->next ()) {
book_node = book_list.add_book_list();
book_node->set_book_id (prst->getInt ("book_id"));
book_node->set_book_name(prst->getString("book_name"));
book_node->set_book_type(prst->getInt ("book_type"));
book_node->set_auth_id (prst->getInt ("auth_id"));
book_node->set_auth_type((enum LoginType)prst->getInt ("auth_type"));
#ifdef __DEBUG__
std::cout << "book_node->book_id () = " << book_node->book_id () << std::endl;
std::cout << "book_node->book_name() = " << book_node->book_name() << std::endl;
std::cout << "book_node->book_type() = " << book_node->book_type() << std::endl;
std::cout << "book_node->auth_id () = " << book_node->auth_id () << std::endl;
std::cout << "book_node->auth_type() = " << book_node->auth_type() << std::endl;
#endif
}
delete prst;
delete pstmt;
}catch (SQLException e) {
PRINT_CATCH(e);
RETURN(p);
}
Buf* pBuf = packet_list(ST_GetStoreBooksList, book_list, p->getfd());
CHECK_P(pBuf);
SINGLE->sendqueue.enqueue(pBuf);
RETURN(p);
}
Player *Database::authenticateUser(const QString &user, const QString &password)
{
if (!isConnected())
init();
if (!m_isConnected)
return 0;
Player *player = 0;
Statement *stmt = 0;
ResultSet *res = 0;
stmt = m_database->createStatement();
try {
QString qry = QString("SELECT id, username, kills, deaths FROM accounts where username='******' and secret='%2'").arg(user, password);
res = stmt->executeQuery(qry.toStdString());
if (res -> rowsCount() != 0)
{
player = new Player();
while (res->next())
{
player->setUserId(res->getInt("id"));
player->setUserName(QString::fromStdString(res->getString("username")));
player->setKills(res->getInt("kills"));
player->setDeaths(res->getInt("deaths"));
}
}
} catch (SQLException &e)
{
emit writeToConsole("Query failed: " + QLatin1String(e.getSQLStateCStr()));
return false;
}
delete stmt;
delete res;
return player;
}
void AdminServlet::handleGet(HttpRequest* request, HttpResponse* response) {
ResultSet* result;
try {
result = ServerDatabase::instance()->executeQuery("SELECT DISTINCT account_id, ip, COUNT(*) FROM account_ips WHERE timestamp >= DATE_SUB(timestamp, INTERVAL 1 DAY) GROUP BY ip HAVING COUNT(*) > 2 ORDER BY COUNT(*) DESC");
} catch (DatabaseException& e) {
response->println(e.getMessage());
return;
}
response->println("HTTP/1.1 200 OK");
response->println("Content-Type: text/html");
response->println("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">");
response->println("<html xmlns=\"http://www.w3.org/1999/xhtml\">");
response->println(" <head>");
response->println(" <title>SWGEmu Multiple Account Administration</title>");
response->println(" <meta http-equiv=\"content-type\" content=\"text/html;charset=utf-8\" />");
response->println(" <link rel=\"stylesheet\" type=\"text/css\" href=\"css/style.css\" />");
response->println(" </head>");
response->println(" <body>");
response->println(" <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\">");
response->println(" <caption>Suspect List</caption>");
response->println(" <tr>");
response->println(" <th>IP Address</th>");
response->println(" <th>Count</th>");
response->println(" </tr>");
if (result->size() > 0) {
while (result->next()) {
String ip = "<a href=\"http://ip-lookup.net/?ip=";
ip += String::valueOf(result->getString(1));
ip += "\">";
ip += String::valueOf(result->getString(1));
ip += "</a>";
response->println(" <tr>");
response->println(" <td>" + ip + "</td>");
response->println(" <td>" + String::valueOf(result->getInt(2)) + "</td>");
response->println(" <tr>");
}
} else {
response->println(" <tr>");
response->println(" <td>No suspects found.</td>");
response->println(" <tr>");
}
delete result;
result = NULL;
response->println(" </body>");
response->println("</html>");
}
void JobWatcher::run() {
Connection con(std::string(Config::getProperty("db.connection")));
// Statement stmt_job=con.createStatement("update jobs set complete = now() where id=:id");
while (!_isStopSignal) {
// logdebug("JobWatcher cycle");
Statement stmt = con.createStatement("select jobs.id, job_details.id as detailid ,inputfile, outputfile, instream, outstream from jobs, job_details where jobs.id=job_details.job_id and complete is null order by jobs.id");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
if (_handler->getJob(rs.getInt("detailid")) != NULL)continue;
Job * job = new Job();
job->setId(rs.getInt("detailid"));
job->setSourceStream(rs.getInt("instream"));
job->setTargetStream(rs.getInt("outstream"));
_handler->addJob(*job);
}
list<Job*>jobs = _handler->getJobList();
list<Job*>::iterator it = jobs.begin();
for (; it != jobs.end(); it++) {
Job * job = *it;
}
Thread::sleep2(1000);
}
}
void addSkeletons(Connection *con, SkeletonSequence *skelSeq){
ResultSet *resSkeletons;
PreparedStatement *prep_stmt;
/* create a statement object */
prep_stmt = con -> prepareStatement("SELECT * FROM Skeleton WHERE id_behaviour=?");
prep_stmt->setInt(1, skelSeq->idBehaviour);
resSkeletons = prep_stmt->executeQuery();
while (resSkeletons->next()) {
Skeleton skel;
skel.idBehaviour = resSkeletons->getInt(1);
skel.frame=resSkeletons->getInt(2);
skel.joints= new double*[15];
for (int i = 0; i < 15; i++) {
skel.joints[i]=new double[3];
skel.joints[i]= string2Vector3D(resSkeletons->getString(i + 3));
}
skelSeq->sequence.push_back(skel);
}
delete resSkeletons;
delete prep_stmt;
}
void Student::List()
{
Statement *stmt = NULL;
ResultSet *rs = NULL;
string sql = "select * from sql_test_table";
try
{
stmt = conn->createStatement(sql);
}
catch (SQLException &e)
{
cout << e.getMessage();
}
if (stmt)
{
try
{
rs = stmt->executeQuery();
}
catch (SQLException &e)
{
cout << e.getMessage();
}
if (rs)
{
cout << endl << setw(3) << left <<"ID"
<< setw(6) << left << "NAME"
<< setw(8) << left << "DEFINITION"
<< endl;
cout << "===================" << endl;
while (rs->next())
{
cout << setw(3) << left << rs->getInt(1)
<< setw(6) << left << rs->getString(2)
<< setw(8) << left << rs->getString(3)
<< endl;
}
cout << "===================" << endl;
cout << endl;
stmt->closeResultSet(rs);
}
conn->terminateStatement(stmt);
}
}
int PhoneCall::DBread(Connection *sqlCon)
{
if (!sqlCon || !id)
return -1;
PreparedStatement *pstmt = sqlCon->prepareStatement(
"SELECT phone, client, translator, client_country, translator_country, lang, price, start_time, accounted, cost, error, request_time, confirm_time, accepted FROM calls WHERE id=(?)");
pstmt->setInt(1, id);
ResultSet *res;
try {
res = pstmt->executeQuery();
} catch (SQLException &ex) {
log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
delete pstmt;
return 0;
}
delete pstmt;
if (res->rowsCount() != 1)
return -1;
res->first();
if (!res->getInt("phone")) {
delete res;
return -1;
}
client = res->getInt("client");
translator = res->getInt("translator");
translateLang = res->getString("lang").c_str();
price = res->getInt("price");
const char *time = res->getString("start_time").c_str();
if (strlen(time) > 0)
start_time = mktime(getdate(time));
accounted = res->getInt("accounted");
cost = res->getInt("cost");
if (res->getInt("error"))
state = ERROR;
time = res->getString("request_time").c_str();
if (strlen(time) > 0)
request_time = mktime(getdate(time));
time = res->getString("confirm_time").c_str();
if (strlen(time) > 0)
confirm_time = mktime(getdate(time));
accepted = res->getBoolean("accepted");
setClientCountry(res->getString("client_country").c_str());
setTranslatorCountry(res->getString("translator_country").c_str());
delete res;
return 0;
}
int validateToken(string token)
{
PreparedStatement * prepstmt;
prepstmt = conn -> prepareStatement("select ((select expiry from tokens where token=?) > now() is true) as res, (select userid from tokens where token=?) as userid;");
prepstmt->setString(1, token);
prepstmt->setString(2, token);
try
{
ResultSet * rs;
rs = prepstmt->executeQuery();
rs->next();
if(rs->getBoolean("res")) return rs->getInt("userid");
throw 3;
}
catch(exception e)
{
throw 2;
}
}
int ChargeBusiness::GetNotifyStatus(string &sysNo){
int notify = 0;
Statement *stmt = NULL;
try{
stmt = conn->createStatement(SQL_QUERY_NOTIFY);
stmt->setString(1, sysNo);
ResultSet *rs = stmt->executeQuery();
while(rs->next())
{
notify = rs->getInt(1);
}
}catch(SQLException &sqlExcp){
HandleException(sqlExcp);
}catch(std::exception &e){
HandleException(e);
}
if(stmt)
conn->terminateStatement(stmt);
return notify;
}
std::list<Classroom> find(std::string condition)
{
ResultSet *res;
std::list<Classroom> classroom_list;
std::string sql = "SELECT * FROM classroom WHERE "
+ condition;
DButil *myDB;
res = myDB->execQuery(sql);
while(res->next())
{
Classroom *classroom = new Classroom();
classroom->set_classroom_id(res->getInt("classroom_id"));
classroom->set_classroom_name(res->getString("classroom_name"));
classroom->set_classroom_way(res->getString("classroom_way"));
classroom_list.push_back(*classroom);
}
return classroom_list;
}
vector<map<string, string>> retrievePendingMessages(Document & request)
{
int recvid= validateToken(request["usertoken"].GetString());
try
{
PreparedStatement * prepstmt;
prepstmt = conn->prepareStatement("select senderuid, receiveruid, msg, datesent from msgs where (receiveruid=? and status=0);");
prepstmt->setInt(1, recvid);
ResultSet * rs;
rs = prepstmt->executeQuery();
prepstmt = conn->prepareStatement("update msgs set status=1 where receiveruid=?;"); //ATTENTION! Threading error can occur!
prepstmt->setInt(1, recvid);
prepstmt->executeUpdate();
conn->commit();
ResultSet * namers;
prepstmt = conn->prepareStatement("select login from users where id=?;");
prepstmt->setInt(1, recvid);
namers = prepstmt->executeQuery();
namers->next();
string receiver = namers->getString("login");
vector<map<string, string>> msgs;
while(rs->next()){
map<string, string> onemsg;
prepstmt = conn->prepareStatement("select login from users where id=?;");
prepstmt->setInt(1, rs->getInt("senderuid"));
namers = prepstmt->executeQuery();
namers->next();
onemsg.insert(make_pair("sender", namers->getString("login")));
onemsg.insert(make_pair("receiver", receiver));
onemsg.insert(make_pair("message", rs->getString("msg")));
onemsg.insert(make_pair("datesent", rs->getString("datesent")));
//cout << namers->getString("login") << " " << rs->getString("msg") << endl;
msgs.push_back(onemsg);
}
return msgs;
}
catch(exception e)
{
throw 2;
}
}
/**
* displaying all the rows in the table
*/
void displayAllRows ()
{
string sqlStmt = "SELECT author_id, author_name FROM author_tab \
order by author_id";
stmt = conn->createStatement (sqlStmt);
ResultSet *rset = stmt->executeQuery ();
try{
while (rset->next ())
{
cout << "author_id: " << rset->getInt (1) << " author_name: "
<< rset->getString (2) << endl;
}
}catch(SQLException ex)
{
cout<<"Exception thrown for displayAllRows"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
stmt->closeResultSet (rset);
conn->terminateStatement (stmt);
}
void SimpleDataSet::read(int type){
openMysqlSession();
this->dataSet.clear();
ResultSet *resSet;
PreparedStatement *prep_stmt;
/* create a statement object */
prep_stmt = con -> prepareStatement("SELECT * FROM `SimpleDataSet` WHERE `compression_rate`=? AND `num_clusters`=? AND `type`=?");
prep_stmt->setInt(1, compressionRate);
prep_stmt->setInt(2, numClusters);
prep_stmt->setInt(3, type);
resSet = prep_stmt->executeQuery();
while (resSet->next()) {
std::stringstream datastream(resSet->getString("sequence"));
//for each loop we save one sequence
vector<int> sequence;
while (!datastream.eof()) {
int state;
datastream >> state;
sequence.push_back(state);
}
SimpleData newSD;
newSD.sequence=sequence;
newSD.idAction=resSet->getInt("id_action");
this->dataSet.push_back(newSD);
}
delete resSet;
delete prep_stmt;
closeMysqlSession();
cout<<"SimpleData readad: "<<this->dataSet.size()<<" data, each sequence length:"<<this->dataSet[0].sequence.size()<<endl;
}
std::auto_ptr<std::map<std::string , model::ConfigItem>> ConfigConnector::getConfigInfo()
{
Connection conn = _pool->getConnection();
PreparedStatement stmt = conn->prepareStatement("select * from config_resource");
ResultSet result = stmt->executeQuery();
std::auto_ptr<std::map<std::string ,model::ConfigItem >> mapConfig(new std::map<std::string , model::ConfigItem>);
model::ConfigItem item;
while (result->next()) {
item.setCid(result->getInt(1));
item.setName(result->getString(2));
item.setValue(result->getString(3));
item.setDetail(result->getString(4));
(*mapConfig)[item.getName()] = item;
}
_pool->returnConnection(conn);
return mapConfig;
}
/**
* Insert the content placeholder into the database
*/
int ContentPlaceholderController::AddContentPlaceholder(ContentPlaceholder& cph)
{
PreparedStatement* stmt = conn->prepareStatement("INSERT INTO cphs (sheetid, type, column, order) VALUES (?,?,?,?)");
//Set provided parameters
stmt->setInt(1, cph.sheetId);
stmt->setInt(2, cph.type);
stmt->setInt(3, cph.column);
stmt->setInt(4, cph.order);
//Insert
stmt->executeUpdate();
delete stmt;
//Perform another query to get the index of the inserted content placeholder
Statement* lastStmt = conn->createStatement();
ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()");
if(rs != NULL)
{
while(rs->next())
{
int lastId = rs->getInt("LAST_INSERT_ID()");
delete rs;
delete lastStmt;
return lastId;
}
}
else
{
delete lastStmt;
return -1;
}
return -1;
}
/**
*
* Executes a query and returns the result
*
* @param VariantVector arguments The query arguments. The first argument should
* be the query and any subsequent arguments are the bind parameters
*
* @return The results from the query
*/
QueryResult DatabaseConnection::execute(VariantVector arguments)
{
int i;
int count;
QueryResult result;
ResultSet *sqlResult;
ResultSetMetaData *sqlMetadata;
result = connect();
if (result.error.isError) {
// There was an error connecting. Return the result.
return result;
}
PreparedStatement *sqlStatement = nullptr;
try {
// Prepare query
sqlStatement = connection->prepareStatement(
arguments.front().toString());
if (arguments.size() > 1) {
// Bind arguments
int i = 1;
for (auto it = arguments.begin() + 1; it != arguments.end(); ++it) {
sqlStatement->setString(i, it->toString());
i++;
}
}
// Execute query
sqlStatement->execute();
// Fetch results
sqlResult = sqlStatement->getResultSet();
} catch (SQLException &e) {
if (sqlStatement != nullptr) {
// Free memory
delete sqlStatement;
}
result.error.isError = true;
result.error.code = e.getErrorCode();
result.error.string = e.getSQLState() + ": " + e.what();
return result;
}
sqlMetadata = sqlResult->getMetaData();
count = sqlMetadata->getColumnCount();
for (i = 1; i <= count; i++) {
// Add to collection
result.columns.push_back(sqlMetadata->getColumnName(i).asStdString());
}
// Read rows
while (sqlResult->next()) {
VariantVector row;
for (i = 1; i <= count; i++) {
Variant column;
switch (sqlMetadata->getColumnType(i)) {
default:
case ::DataType::UNKNOWN:
case ::DataType::CHAR:
case ::DataType::VARCHAR:
case ::DataType::LONGVARCHAR:
case ::DataType::BINARY:
case ::DataType::VARBINARY:
case ::DataType::LONGVARBINARY:
case ::DataType::TIMESTAMP:
case ::DataType::DATE:
case ::DataType::GEOMETRY:
case ::DataType::ENUM:
case ::DataType::SET:
case ::DataType::BIGINT:
case ::DataType::REAL:
case ::DataType::DOUBLE:
case ::DataType::DECIMAL:
// @TODO: store binary, timestamp, date, & geometry differently
// Also numeric types (need to be added to Variant class)
column = sqlResult->getString(i).asStdString();
break;
case ::DataType::SQLNULL:
column = Variant();
break;
case ::DataType::BIT:
case ::DataType::TINYINT:
case ::DataType::SMALLINT:
case ::DataType::MEDIUMINT:
case ::DataType::INTEGER:
case ::DataType::NUMERIC:
column = sqlResult->getInt(i);
break;
case ::DataType::YEAR:
column = static_cast<unsigned short>(sqlResult->getUInt(i));
break;
}
// Add column to collection
row.push_back(column);
}
// Add row to collection
result.rows.push_back(row);
}
// Free memory
delete sqlResult;
delete sqlStatement;
return result;
}
/*
=====================
获得数据库表纪录
=====================
*/
bool CHandleMessage::postDBRecord (Buf* buf, int iCase)
{
#if 0
if (NULL == buf) {
printf("null buf\n");
return false;
}
MSG_HEAD head;
try {
MutexLockGuard guard(DATABASE->m_mutex);
PreparedStatement* pstmt = NULL;
if (iCase == 1)
pstmt = DATABASE->preStatement (SQL_SELECT_COURSE_DB);
else if (iCase == 2)
pstmt = DATABASE->preStatement (SQL_SELECT_GRADE_DB);
else if (iCase == 3)
pstmt = DATABASE->preStatement (SQL_SELECT_CLASS_DB);
else if (iCase == 4)
pstmt = DATABASE->preStatement (SQL_SELECT_CLASSROOM_DB);
else if (iCase == 5)
pstmt = DATABASE->preStatement (SQL_SELECT_STUDENT_DB);
else if (iCase == 6) {
pstmt = DATABASE->preStatement (SQL_SELECT_COURSEITEM_DB);
#if 1 // send count of all selected course by teacher
#ifdef _TEACHER_NOLOGIN
// only for h**king test............
pstmt->setString (1, "拼图");
pstmt->setString (2, "造房子");
pstmt->setString (3, "暖身操");
pstmt->setString (4, "动画片");
#else
CRoom* room = ROOMMANAGER->get_room_by_fd (buf->getfd());
if (room != NULL)
{
CRoom::COURSELIST::iterator it;
int ii = 1;
for (it = room->m_course_list.begin (); it != room->m_course_list.end (); ++it)
pstmt->setString (ii++, (*it)->getName());
}
#endif
#else
sGetCourseItem* ci = (sGetCourseItem *) ((char*)((MSG_HEAD*)buf->ptr()) + sizeof (MSG_HEAD));
pstmt->setString (1, ci->sCourseName);
#endif
}
else {
cout << "error: index" << endl;
return false;
}
ResultSet* prst = pstmt->executeQuery();
unsigned int index = 0, type = 0;
while(prst->next()) {
//printf ("index = %d------------------------------------------------------\n", index);
if (iCase == 1) {
memset (&head, 0x00, sizeof (head));
//head.cType = CT_GetCourseDB * 100 + index++;
type = 5000 + index++;
memcpy (&head.cType, &type, sizeof (unsigned int));
head.cLen = sizeof(MSG_HEAD) + sizeof(struct sGetCourseDB);
struct sGetCourseDB course_info;
(void) memset (&course_info, 0x00, sizeof (sGetCourseDB));
strcpy(course_info.sGradeName, prst->getString ("grade_name").c_str());
strcpy(course_info.sGroupName, prst->getString ("group_name").c_str());
strcpy(course_info.sCourseName, prst->getString("course_name").c_str());
course_info.iLanguage = prst->getInt ("language");
course_info.iArt = prst->getInt ("art");
course_info.iCommunity = prst->getInt ("community");
course_info.iHealth = prst->getInt ("health");
course_info.iScience = prst->getInt ("science");
Buf* p = SINGLE->bufpool.malloc ();
memcpy(p->ptr(), &head, sizeof(MSG_HEAD));
memcpy((char*)p->ptr() + sizeof(MSG_HEAD), &course_info, sizeof(struct sGetCourseDB));
p->setfd(buf->getfd());
p->setsize(head.cLen);
SINGLE->sendqueue.enqueue(p);
}
else if (iCase == 2) {
memset (&head, 0x00, sizeof (head));
type = 3000 + index++;
memcpy (&head.cType, &type, sizeof (unsigned int));
//head.cType = CT_GetGradeDB;
head.cLen = sizeof (MSG_HEAD)+ sizeof (struct sGetGradeDB);
struct sGetGradeDB grade_info;
(void) memset (&grade_info, 0x00, sizeof (grade_info));
strcpy (grade_info.sGradeName, prst->getString ("grade_name").c_str());
Buf* p = SINGLE->bufpool.malloc ();
memcpy (p->ptr(), &head, sizeof(MSG_HEAD));
memcpy ((char *)p->ptr() + sizeof(MSG_HEAD), &grade_info, sizeof(struct sGetGradeDB));
p->setfd (buf->getfd ());
p->setsize(head.cLen);
SINGLE->sendqueue.enqueue (p);
}
else if (iCase == 3) {
memset (&head, 0x00, sizeof (head));
type = 4000 + index++;
memcpy (&head.cType, &type, sizeof (unsigned int));
//head.cType = CT_GetClassDB;
head.cLen = sizeof(MSG_HEAD)+ sizeof (struct sGetClassDB);
struct sGetClassDB class_info;
(void) memset (&class_info, 0x00, sizeof (class_info));
strcpy (class_info.sClassName, prst->getString ("class_name").c_str());
Buf* p = SINGLE->bufpool.malloc ();
memcpy (p->ptr(), &head, sizeof (MSG_HEAD));
memcpy ((char *)p->ptr() + sizeof (MSG_HEAD), &class_info, sizeof (struct sGetClassDB));
p->setfd (buf->getfd ());
p->setsize(head.cLen);
SINGLE->sendqueue.enqueue (p);
}
else if (iCase == 4) {
memset (&head, 0x00, sizeof (head));
//head.cType = CT_GetClassRoomDB;
type = 6000 + index++;
memcpy (&head.cType, &type, sizeof (unsigned int));
head.cLen = sizeof(MSG_HEAD) + sizeof (struct sGetClassRoomDB);
struct sGetClassRoomDB room_info;
(void) memset (&room_info, 0x00, sizeof (room_info));
strcpy (room_info.sClassRoomName, prst->getString ("classroom_name").c_str());
Buf* p = SINGLE->bufpool.malloc ();
memcpy (p->ptr(), &head, sizeof (MSG_HEAD));
memcpy ((char *)p->ptr() + sizeof (MSG_HEAD), &room_info, sizeof (struct sGetClassRoomDB));
p->setfd (buf->getfd ());
p->setsize(head.cLen);
SINGLE->sendqueue.enqueue (p);
}
else if (iCase == 5) {
memset (&head, 0x00, sizeof (MSG_HEAD));
type = 10000 + index++;
memcpy (&head.cType, &type, sizeof (unsigned int));
/// cout << "begin:-head.cType = " << head.cType << endl;
head.cLen = sizeof (MSG_HEAD) + sizeof (struct sGetAllStudentInfo);
struct sGetAllStudentInfo stu_info;
(void) memset (&stu_info, 0x00, sizeof (stu_info));
strcpy (stu_info.sPicName, prst->getString ("picture_name").c_str());
strcpy (stu_info.sStudentName, prst->getString ("student_name").c_str());
stu_info.iStudentId= prst->getInt ("student_id");
/// cout << "stu_info.iStudentId:" << stu_info.iStudentId << endl;
/// cout << "stu_info.sPicName:" << stu_info.sPicName << endl;
/// cout << "stu_info.sStudentName:" << stu_info.sStudentName << endl;
Buf* p = SINGLE->bufpool.malloc ();
memcpy (p->ptr(), &head, sizeof (MSG_HEAD));
memcpy ((char *)p->ptr() + sizeof (MSG_HEAD), &stu_info, sizeof (struct sGetAllStudentInfo));
p->setfd (buf->getfd ());
p->setsize(head.cLen);
SINGLE->sendqueue.enqueue (p);
/// cout << "address = " << p << endl;
/// cout << "ended:-head.cLen = " << head.cLen << endl;
/// cout << "ended:-head.cType = " << head.cType << endl;
}
else if (iCase == 6) {
memset (&head, 0x00, sizeof (head));
type = 7000 + index++;
memcpy (&head.cType, &type, sizeof (unsigned int));
//head.cType = CT_GetCourseItem;
head.cLen = sizeof(MSG_HEAD) + sizeof (struct sCourseItem);
struct sCourseItem course_item;
(void) memset (&course_item, 0x00, sizeof (sCourseItem));
strcpy (course_item.sCourseName, prst->getString ("course_name").c_str());
strcpy (course_item.sItemName, prst->getString ("item_name").c_str());
strcpy (course_item.sDesc, prst->getString ("fck_desc").c_str());
cout << "courseName: " << course_item.sCourseName << endl;
cout << "itemName: " << course_item.sItemName << endl;
cout << "Desc: " << course_item.sDesc << endl;
Buf* p = SINGLE->bufpool.malloc ();
memcpy (p->ptr(), &head, sizeof(MSG_HEAD));
memcpy ((char *)p->ptr() + sizeof(MSG_HEAD), &course_item, sizeof (struct sCourseItem));
p->setfd (buf->getfd ());
p->setsize(head.cLen);
SINGLE->sendqueue.enqueue (p);
}
else {
cout << "error: index" << endl;
return false;
}
}
#if 1 // send finished flags
do {
sleep (1);
//cout << "send finished flags -----------" << endl;
Buf* p = SINGLE->bufpool.malloc ();
MSG_HEAD* phead = (MSG_HEAD*)p->ptr();
struct sDBRecordFinished finished;
memset (&finished, 0x00, sizeof (sDBRecordFinished));
if (iCase == 5)
finished.iFlagFinished = 10000;
else
finished.iFlagFinished = 1;
phead->cLen = sizeof (MSG_HEAD) + sizeof (struct sDBRecordFinished);
phead->cType = ST_GetDBRecordFinished;
memcpy (((char*)p->ptr()) + MSG_HEAD_LEN, &finished, sizeof (struct sDBRecordFinished));
p->setfd (buf->getfd());
p->setsize (phead->cLen);
SINGLE->sendqueue.enqueue (p);
} while (0);
#endif
}
catch(SQLException e){
LOG(ERROR) << e.what() << std::endl;
}
buf->reset();
SINGLE->bufpool.free(buf);
#endif
return true;
}
/*
=====================
获得数据库表纪录数量
=====================
*/
bool CHandleMessage::postDBRecordCount (Buf* p, int iCase)
{
#if 0
char str[1024] = "SELECT count(*) AS ccount FROM ";
if (NULL == p) {
printf("null buf\n");
return false;
}
//printf (" postDBRecordCount ..., iCase=%d\n", iCase);
#if 1
if (iCase == 1) {
strcat (str, "course_group_course AS cgc, course_group AS cg, course AS c, grade AS g, grade_course AS gc WHERE cgc.group_id = cg.group_id AND cgc.course_id = c.course_id AND gc.grade_id = g.grade_id AND c.course_id = gc.course_id");
}
else if (iCase == 2) {
strcat (str, "grade");
}
else if (iCase == 3) {
strcat (str, "class");
}
else if (iCase == 4) {
strcat (str, "classroom");
}
else if (iCase == 5) {
strcat (str, "student");
}
else if (iCase == 6) {
//strcat (str, "course_item AS ci, course AS c, item AS i WHERE ci.course_id=c.course_id AND ci.item_id=i.item_id AND c.course_name=?");
strcat (str, "course_item AS ci, course AS c, item AS i WHERE ci.course_id=c.course_id AND ci.item_id=i.item_id AND (c.course_name=? OR c.course_name=? OR c.course_name=? OR c.course_name=?)");
}
#endif
// dbCount.count = 100;
//
#if 0
struct sDBCount dbCount;
dbCount.count = 5;
MSG_HEAD *head = (MSG_HEAD*) buf->ptr();
head->cLen = sizeof (MSG_HEAD) + sizeof (dbCount);
head->cType = CT_GetClassRoomDBCount;
memcpy (head->cData(), &dbCount, sizeof (dbCount));
buf->setsize (head->cLen);
SINGLE->sendqueue.enqueue (buf);
#endif
#if 1
try {
MutexLockGuard guard(DATABASE->m_mutex);
PreparedStatement* pstmt = DATABASE->preStatement(str);
if (iCase == 6) {
#if 0 // fixed: to update
sGetCourseItem* ci = (sGetCourseItem *) ((char*)((MSG_HEAD*)p->ptr()) + sizeof (MSG_HEAD));
pstmt->setString (1, ci->sCourseName);
#else
#ifdef _TEACHER_NOLOGIN
// only for h**king test............
pstmt->setString (1, "拼图");
pstmt->setString (2, "造房子");
pstmt->setString (3, "暖身操");
pstmt->setString (4, "动画片");
#else
CRoom* room = ROOMMANAGER->get_room_by_fd (p->getfd());
if (room != NULL)
{
CRoom::COURSELIST::iterator it;
int ii = 1;
for (it = room->m_course_list.begin (); it != room->m_course_list.end (); ++it) {
pstmt->setString (ii++, (*it)->getName());
cout << "COURSE NAME: " << (*it)->getName() << endl;
}
}
#endif
#endif
}
ResultSet* prst = pstmt->executeQuery ();
while (prst->next ()) {
struct sDBCount dbCount;
dbCount.count = prst->getInt ("ccount");
printf ("get db count: %d\n", dbCount.count);
MSG_HEAD* head = (MSG_HEAD*)p->ptr();
head->cLen = MSG_HEAD_LEN + sizeof (dbCount);
if (iCase == 1)
head->cType = CT_GetCourseDBCount;
else if (iCase == 2)
head->cType = CT_GetGradeDBCount;
else if (iCase == 3)
head->cType = CT_GetClassDBCount;
else if (iCase == 4)
head->cType = CT_GetClassRoomDBCount;
else if (iCase == 5)
head->cType = CT_GetAllStudentInfoCount;
else if (iCase == 6)
head->cType = CT_GetCourseItemCount;
//memcpy (head->cData(), &dbCount, sizeof (dbCount));
memcpy ((char *)p->ptr() + MSG_HEAD_LEN, &dbCount, sizeof (struct sDBCount));
p->setsize (head->cLen);
SINGLE->sendqueue.enqueue (p);
}
delete pstmt;
delete prst;
}
catch (SQLException e) {
printf ("%s\n", e.what ());
}
#endif
#endif
return true;
}
