C++ Query::exec方法代码示例

int updateRunning(int id, int running)
{
    Connection con(use_exceptions);
    try
    {
        ostringstream strbuf;
        unsigned int i = 0;
        con.connect(DATABASE, HOST, USER, PASSWORD);
        Query query = con.query();
        strbuf << "UPDATE tasks SET running="<<running<<" WHERE id=" << id;
        query.exec(strbuf.str());
    }
    catch (const BadQuery& er)
    {
    // Handle any query errors
        cerr << "updateDone - Query error: " << er.what() << endl;
        return -1;
    }
    catch (const BadConversion& er)
    {
    // Handle bad conversions
        cerr << "updateDone - Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    }
    catch (const Exception& er)
    {
    // Catch-all for any other MySQL++ exceptions
        cerr << "updateDone - Error: " << er.what() << endl;
        return -1;
    }

    return 0;
}        

int updateProgress(int id, int progress,double sigma0, double bestfitbulk)
{
    Connection con(use_exceptions);
    try
    {
        ostringstream strbuf;
        unsigned int i = 0;
        con.connect(DATABASE, HOST, USER, PASSWORD);
        Query query = con.query();
        strbuf << "UPDATE tasks SET progress=" << progress << ",sigma0="<<sigma0<<",bestfitbulk="<<bestfitbulk<<" WHERE id=" << id;
        query.exec(strbuf.str());
    }
    catch (const BadQuery& er)
    {
    // Handle any query errors
        cerr << "updateProgress - Query error: " << er.what() << endl;
        return -1;
    }
    catch (const BadConversion& er)
    {
    // Handle bad conversions
        cerr << "updateProgress - Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    }
    catch (const Exception& er)
    {
    // Catch-all for any other MySQL++ exceptions
        cerr << "updateProgress - Error: " << er.what() << endl;
        return -1;
    }

    return 0;
}

//保存到数据库
bool MasterServer::save_fileinfo_to_db(FileInfo &fileinfo)
{
	if(m_db_connection == NULL)
		return false;
	char sql_str[1024];
	ChunkPath &chunk_path = fileinfo.get_chunkpath(0);
	snprintf(sql_str, 1024, "insert into SFS.fileinfo_%s (fid, name, size, chunkid, chunkip, chunkport, findex, foffset) "
			"values('%s', '%s', %d, '%s', '%s', %d, %d, %d);"
			,fileinfo.fid.substr(0,2).c_str(), fileinfo.fid.c_str(), fileinfo.name.c_str(), fileinfo.size
			,chunk_path.id.c_str() ,chunk_path.ip.c_str(), chunk_path.port
			,chunk_path.index, chunk_path.offset);
	Query query = m_db_connection->query(sql_str);
	return query.exec();
}

/**
   @brief Create a new order
 
   @param[in] anUid    ID of user that places the order
   @param[in] bsk User basket containing products
 
   @return A pointer to an instance of Order if successful
 */
Order * Order::create(int anUid, Basket & bsk)
{
    Order *o = new Order();
    
    // get current date and format as expected by mySQL
    time_t now = time(NULL);
    struct tm *tmNow = gmtime((const time_t *)&now);
    
    stringstream nowStr;
    nowStr << (1900+tmNow->tm_year) << "-" << tmNow->tm_mon << "-" 
           << setfill('0') << setw(2) << tmNow->tm_mday << " " 
           << tmNow->tm_hour << ":" << tmNow->tm_min  
           << ":" << tmNow->tm_sec;
        
    // set other attributes
    o->setIntForKey(KEY_ORD_OID, 0);
    o->setFloatForKey(KEY_ORD_TOTAL, bsk.total());
    o->setValueForKey(KEY_ORD_DATE, nowStr.str());
    o->setIntForKey(KEY_ORD_UID, anUid);
    if (!o->store()) {
        delete o;
        LOG(2, "Unable to place the order.\n");
        
        return NULL;
    }
    
    ulonglong oid = o->getLastInsertID();
    
    // get an instance of the database
    Database& db = Database::instance();
    
    // ask Database for a valid connection to mySQL
    Connection *conn = db.getConnection();
    
    // obtain an instance of mysqlpp::Query and init it
    Query q = conn->query();
    
    // add to "order_details" all items present in the basket
    for (Basket::const_iterator it=bsk.begin(); it != bsk.end(); it++) {
        q << "INSERT INTO order_details VALUES (" << oid << ", " 
          << (*it).first << ", " << (*it).second << ")";
        q.exec();
        q.reset();
    }

    return o;
}

int  main (void) {
  Connection con(use_exceptions);
	try {
		ostrstream strbuf; unsigned int i=0; 
		con.real_connect (MY_DATABASE,MY_HOST,MY_USER,MY_PASSWORD,3306,(int)0,60,NULL);
		Query query = con.query(); query << MY_QUERY; 
		ResUse res = query.use(); Row row; 
		strbuf << "delete from " << MY_TABLE << " where " << MY_FIELD << " in (";
//  for UPDATE just replace the above DELETE FROM with UPDATE statement
		for(;row=res.fetch_row();i++) strbuf <<	 row[0] << ",";	if (!i) return 0; 
		string output(strbuf.str()); output.erase(output.size()-1,1); output += ")";
		query.exec((const string&)output); // cout << output << endl;
		return 0;
	} catch (BadQuery er) { 
    cerr << "Error: " << er.error << " " << con.errnum() << endl;
    return -1;
	}
}

void ShadeButton::setShade(QAction *act)
{
	bool ok = false;
	int a;
	int c;
	int b = 100;
	for (a = 0; a < FillSh->actions().count(); ++a)
	{
		FillSh->actions()[a]->setChecked(false);
	}
	act->setChecked(true);
	QList<QAction*> actList = FillSh->actions();
	c = actList.indexOf(act);
	if (c < 0)
		return;
	if (c > 0)
		b = (c-1) * 10;

	if (b > 100)
		return; // no need for > 100%, fix needed by SM, Riku
	
	if (c == 0)
	{
		Query* dia = new Query(this, "New", 1, 0, tr("&Shade:"), tr("Shade"));
		if (dia->exec())
    	{
			c = dia->getEditText().toInt(&ok);
			if (ok)
				b = qMax(qMin(c, 100),0);
			else
				b = 100;
			delete dia;
		}
		else
		{
			delete dia;
			return;
		}
	}
	setText(QString::number(b)+" %");
	emit clicked();
}

void JavaDocs::slotAdd()
{
	QString nam;
	Query *dia = new Query(this, "tt", 1, 0, tr("&New Script:"), tr("New Script"));
	dia->setEditText( tr("New Script"), false );
	dia->setTestList(Doc->JavaScripts.keys());
	if (dia->exec())
	{
		nam = dia->getEditText();
		nam.replace( QRegExp("[\\s\\/\\{\\[\\]\\}\\<\\>\\(\\)\\%]"), "_" );
		Editor* dia2 = new Editor(this, "", View);
		dia2->EditTex->setText("function "+nam+"()\n{\n}");
		if (dia2->exec())
		{
			EditScript->setEnabled(true);
			DeleteScript->setEnabled(true);
			Doc->JavaScripts[nam] = dia2->EditTex->toPlainText();
			Scripts->addItem(nam);
			emit docChanged(false);
		}
		delete dia2;
	}
	delete dia;
}

void
MySQLStorage::store (Event* e)
{
        try {
                this->init();
        } catch (const exception& e) {
                stringstream errss;
                errss << "Couldn't initialise MySQL database: " << e.what();
                throw runtime_error (errss.str());
        }

        try {
                // Store the event to the database.
                this->dbCheck();
                Query query (this->conn.query());

                query << "INSERT INTO `log` "
                      << "( `time`"
                      << ", `ms`"
                      << ", `eventId`"
                      << ", `dataId`"
                      << ", `message`"
#ifdef GOT_DT_ERROR_CODE
                      << ", `errorCode`"
#endif
                      << ", `datastreamId`"
                      << ", `datastreamName`"
                      << ", `hostname`"
#ifdef GOT_DT_LOG_LEVEL
                      << ", `logLevel`"
#endif
                      << ", `pid`"
                      << ")"
                      << " VALUES "
                      << " ( FROM_UNIXTIME(" << e->getTime().tv_sec << ")"
                      << " , '" << e->getTime().tv_usec/1000
                      << "', '" << e->getId() // SQL safe? (uuid)
                      << "', '" << e->getDataId() // SQL safe? (uuid)
                      << "', '" << e->getMessage() // Need to escape
#ifdef GOT_DT_ERROR_CODE
                      << "', '" << e->getErrorCode() // SQL safe (int)
#endif
                      << "', '" << e->getDatastreamId() // SQL safe? (uuid)
                      << "', '" << e->getDatastreamName() // SQL safe? (datastreamNames can't contain '; etc)
                      << "', '" << e->getHostname() // SQL safe?
#ifdef GOT_DT_LOG_LEVEL
                      << "', '" << e->getLogLevel() // SQL safe (int)
#endif
                      << "', '" << e->getPid() // SQL safe (int)
                      << "');";

                //DBG ("INSERT Query string: " << query.str());

                if (query.exec() == false) {
                        stringstream ee;
                        ee << "Query failed, error: '" << query.error() << "'";
                        throw runtime_error (ee.str());
                }
        }
        catch (mysqlpp::BadQuery e) { this->handleBadQuery (e); }
        catch (mysqlpp::Exception e) { this->handleException (e); }
}

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); }
}

int Mysql::query(std::string sql){
	Query query = _con.query(sql.c_str());
	query.exec();
	unsigned long long id = query.insert_id();
	return (int)id;
}

int getNextTaskId()
//searching for a task to start
{
    int id = -1,progress;
    ostringstream strbuf;
    strbuf << "SELECT id,progress FROM tasks WHERE started=0 and running=0 ORDER BY date ASC LIMIT 1";
    
    Connection con(use_exceptions);
    
    try
    {
	Query query = con.query();
	con.connect(DATABASE, HOST, USER, PASSWORD);
	query << strbuf.str();
 StoreQueryResult res = query.store();
	cout<<strbuf.str()<<endl;
	
        if (res && res.num_rows() > 0)
	{
		mysqlpp::Row row;
		row = res.at(0);
		id = row["id"];
		progress = row["progress"];
	}

    }
    catch (const BadQuery& er)
    {    
    // Handle any query errors
        cerr << "getNextTaskId - Query error: " << er.what() << endl;
	    id = -1;
    }
    catch (const BadConversion& er)
    {
    // Handle bad conversions
        cerr << "getNextTaskId - Conversion error: " << er.what() << endl <<
			    "\tretrieved data size: " << er.retrieved <<
			    ", actual size: " << er.actual_size << endl;
	    id = -1;
    }
    catch (const Exception& er)
    {
    // Catch-all for any other MySQL++ exceptions
        cerr << "getNextTaskId - Error: " << er.what() << endl;
	    id = -1;
    }
    if(id>=1 && ! (progress >=1)) { //first time run, convert formula/density
    try{
    	strbuf.str("");
    strbuf << "SELECT energy,formula0,formula1,formula12,rhoin0,rhoin1,rhoin12 FROM tasks WHERE id="<<id;
    	Query query = con.query();
	con.connect(DATABASE, HOST, USER, PASSWORD);
	query << strbuf.str();
 StoreQueryResult res = query.store();
	cout<<strbuf.str()<<endl;
	mysqlpp::Row row;
		row = res.at(0);
		string formula0(row["formula0"]), formula1(row["formula1"]), formula12(row["formula12"]);
		double energy=row["energy"];
		double rhoin0=row["rhoin0"];
		double rhoin1=row["rhoin1"];
		double rhoin12=row["rhoin12"];
		double rho0=0.,rho1=0.,rho12=0.;
		double beta0=0.,beta1=0.,beta12=0.;
		cout<<formula0.size()<<endl;
		cout<<"formula0= "<<formula0<<endl;
		cout<<"formula1= "<<formula1<<endl;
		cout<<"formula12= "<<formula12<<endl;
		cout<<"rhoin0= "<<rhoin0<<endl;
		cout<<"rhoin1= "<<rhoin1<<endl;
		cout<<"rhoin12= "<<rhoin12<<endl;
		compound cmpd0(formula0,energy,rhoin0), cmpd1(formula1,energy,rhoin1), cmpd12(formula12,energy,rhoin12);
		strbuf.str("");
        strbuf << "UPDATE tasks SET rho0="<<cmpd0.rho_el<<",rho1="<<cmpd1.rho_el<<",rho12="<<cmpd12.rho_el<<",beta0="<<cmpd0.beta<<",beta1="<<cmpd1.beta<<",beta12="<<cmpd12.beta<<" WHERE id="<< id;
        //cout<<strbuf.str()<<endl;
        query.exec(strbuf.str());
	cout<<"rho0="<<cmpd0.rho_el<<"\tbeta="<<cmpd0.beta<<endl;
	cout<<"rho1="<<cmpd1.rho_el<<"\tbeta="<<cmpd1.beta<<endl;
	cout<<"rho12(Maximum)="<<cmpd12.rho_el<<"\tbeta="<<cmpd12.beta<<endl;
    }
    
       catch (const BadQuery& er)
    {
    // Handle any query errors
        cerr << "updateDone - Query error: " << er.what() << endl;
        return -1;
    }
    catch (const BadConversion& er)
    {
    // Handle bad conversions
        cerr << "updateDone - Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    }
    catch (const Exception& er)
    {
    // Catch-all for any other MySQL++ exceptions
        cerr << "updateDone - Error: " << er.what() << endl;
        return -1;
//.........这里部分代码省略.........

//.........这里部分代码省略.........
				fprintf(stderr,"error reading frame from stdin: %s\n",strerror(errno));
				return -1;
			}
			for(int i=0;i<n;++i)
				dictBuf.push_back(buf[i]);
		}
		Dictionary request(dictBuf);
		dictBuf = "";

		if (!dbCon->connected()) {
			fprintf(stderr,"connection to database server lost\n");
			return -1;
		}

		// Check QNetworkConfigRefresh (MEMORY table) and push network
		// config refreshes to queued peer/network pairs.
		try {
			Dictionary to;
			{
				Query q = dbCon->query();
				q << "SELECT DISTINCT LOWER(HEX(Node_id)) AS Node_id,LOWER(HEX(Network_id)) AS Network_id FROM QNetworkConfigRefresh";
				StoreQueryResult rs = q.store();
				for(unsigned long i=0;i<rs.num_rows();++i) {
					std::string &nwids = to[rs[i]["Node_id"].c_str()];
					if (nwids.length())
						nwids.push_back(',');
					nwids.append(rs[i]["Network_id"]);
				}
			}

			{
				Query q = dbCon->query();
				q << "DELETE FROM QNetworkConfigRefresh";
				q.exec();
			}

			Dictionary response;
			response["type"] = "netconf-push";
			response["to"] = to.toString();
			std::string respm = response.toString();
			uint32_t respml = (uint32_t)htonl((uint32_t)respm.length());

			stdoutWriteLock.lock();
			write(STDOUT_FILENO,&respml,4);
			write(STDOUT_FILENO,respm.data(),respm.length());
			stdoutWriteLock.unlock();
		} catch ( ... ) {}

		try {
			const std::string &reqType = request.get("type");
			if (reqType == "netconf-init") { // initialization to set things like netconf's identity
				Identity netconfId(request.get("netconfId"));
				if ((netconfId)&&(netconfId.hasPrivate())) {
					signingIdentity = netconfId;
					fprintf(stderr,"got netconf signing identity: %s\n",signingIdentity.toString(false).c_str());
				} else {
					fprintf(stderr,"netconfId invalid or lacks private key\n");
					return -1;
				}
			} else if (reqType == "netconf-request") { // NETWORK_CONFIG_REQUEST packet
				if (!signingIdentity) {
					fprintf(stderr,"no signing identity; missing netconf-init?\n");
					return -1;
				}

				// Deserialize querying peer identity and network ID