Exemplo n.º 1
0
	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;
	}
Exemplo n.º 2
0
//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;
}
Exemplo n.º 3
0
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;
}
Exemplo n.º 4
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());
	}
}
Exemplo n.º 5
0
///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;
}
Exemplo n.º 6
0
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;
}
Exemplo n.º 7
0
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;
}
Exemplo n.º 8
0
//验证用户微信号
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;
}
Exemplo n.º 9
0
/**
 * 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;
}
Exemplo n.º 10
0
/**
 * 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;
}
Exemplo n.º 11
0
/**
 * 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;
}
Exemplo n.º 12
0
  /**
   * 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 *)
Exemplo n.º 13
0
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);
}
Exemplo n.º 14
0
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);
  }
}
Exemplo n.º 17
0
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;    
 }
Exemplo n.º 18
0
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);
    }
}
Exemplo n.º 19
0
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;
}
Exemplo n.º 20
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;
     }
 }
Exemplo n.º 21
0
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;
}
Exemplo n.º 22
0
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;
}
Exemplo n.º 23
0
 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;
     }
 }
Exemplo n.º 24
0
  /**
   * 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;
    
}
Exemplo n.º 26
0
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;
}
Exemplo n.º 28
0
/**
 *
 * 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;
}
Exemplo n.º 29
0
/*
=====================
 获得数据库表纪录
=====================
*/
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;
}
Exemplo n.º 30
0
/*
=====================
 获得数据库表纪录数量
=====================
*/
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;
}