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

// Recursive Function
void _ftpEntry::removeFolderChildren( size_t folderID ) {
    Connection                     *sqlConnect = dbConnect();
    bool							isDone						= false;
	Query							serverQuery					= sqlConnect->query();
	StoreQueryResult				serverResults;
	StoreQueryResult::iterator		storeQueryIT;
    size_t                          ID;

    // Find all folders that parent us
    serverQuery << "select * from ServerFolders where parentID = "      << quote << folderID;
    serverQuery.execute();

    // 
    for(storeQueryIT = serverResults.begin(); storeQueryIT != serverResults.end(); storeQueryIT++ ) {
        
        // Remove all children from this parent
        ID = (size_t) (*storeQueryIT)["folderID"];
        removeFolderChildren( ID );
        
        // Now remove the parent
        serverQuery.str("");
	    serverQuery << "delete from ServerFolders where folderID = "	<< quote << ID;
        serverQuery.execute();
    }

    delete sqlConnect;
}

void GameModel::DeleteGame() {
	using namespace ManaCraft::Database;

	try {

		this->teams->DeleteTeams();

		Query query = DatabaseAPI::getQuery();
		query.clear();
		query << "DELETE FROM Game WHERE ID = " << mysqlpp::quote << this->id;
		query.execute();

		query = DatabaseAPI::getQuery();
		query.clear();
		query << "DELETE FROM Game_Teams WHERE ID = " << mysqlpp::quote << this->id;
		query.execute();

		query = DatabaseAPI::getQuery();
		query.clear();
		query << "DELETE FROM Game_Player_Towers WHERE ID = " << mysqlpp::quote << this->id;
		query.execute();
	}
	catch (mysqlpp::BadConversion e) {
		std::cout << e.what() << "\n";
	}
	catch (mysqlpp::BadIndex e) {
		std::cout << e.what() << "\n";
	}
	catch (Exception e) {
		throw e;
	}
}

void TestDatabase::testDelete()
{
	DatabaseAPI::connectToDatabase();

	Query query = DatabaseAPI::getQuery();
	query.clear();
	query << "DELETE FROM Test_Table WHERE ID = 1";
	query.execute();

	query = DatabaseAPI::getQuery();
	query.clear();
	query << "DELETE FROM Test_Table WHERE ID = 2";
	query.execute();
}

void SaslConnection::close()
{
    Endpoint client = peer();
    Connection::close();

    if ( !u || logged ||
         !client.valid() || client.protocol() == Endpoint::Unix )
        return;

    logged = true;

    Query * q = new Query(
        "insert into connections "
        "(username,address,port,mechanism,authfailures,"
        "syntaxerrors,started_at,ended_at,userid) "
        "values ($1,$2,$3,$4,$5,$6,"
        "$7::interval + 'epoch'::timestamptz,"
        "$8::interval + 'epoch'::timestamptz,$9)", 0
    );

    q->bind( 1, u->login() );
    q->bind( 2, client.address() );
    q->bind( 3, client.port() );
    q->bind( 4, m );
    q->bind( 5, af );
    q->bind( 6, sf );
    q->bind( 7, s );
    q->bind( 8, (uint)time( 0 ) );
    q->bind( 9, u->id() );
    q->execute();

}

/**
 * Insert a contact into the database.
 *
 * Demonstrates:
 * - use of a sequence
 * - opening of a table
 * - insert mode
 * - assigning data to a row
 * - posting data to the database
 * - closing a table
 * - inserting data into a BLOB field
 */
db_uint PhoneBook::insert_contact(const wchar_t *name, db_uint ring_id, const char *picture_name)
{
    Query       q;
    Sequence    id_sequence;
    db_uint         id;

    id_sequence.open(db, "contact_id");
    id_sequence.get_next_value(id);

    q.prepare(db,
        "insert into contact (id, name, ring_id, picture_name) "
        "  values ($<integer>0, $<nvarchar>1, $<integer>2, $<varchar>3) ");
    q.param(0) = id;
    q.param(1) = name;
    q.param(2) = ring_id;
    q.param(3) = picture_name;
    if  (DB_SUCCESS(print_error(q.execute(), q))) {
        //---------------------------------------------------------------
        // Insert the BLOB field
        //---------------------------------------------------------------
        update_contact_picture(id, picture_name);
    } else {
        //---------------------------------------------------------------
        // Error returned from execute
        //---------------------------------------------------------------
        id = 0;
    }

    return id;
}

void GameModel::SaveGame()
{
	using namespace ManaCraft::Database;

	// Game Table
	Query query = DatabaseAPI::getQuery();
	query.clear();
	query << "SELECT * FROM Game WHERE ID = " << mysqlpp::quote << id;

	if (UseQueryResult result = query.use()) {
		if (Row row = result.fetch_row()) {
			DeleteGame();
		}
	}

	try {
		query = DatabaseAPI::getQuery();
		query.clear();
		query << "INSERT INTO Game VALUES(" << mysqlpp::quote << id << ", " << mysqlpp::quote << "Another Game" << ")";
		query.execute();

		teams->SaveTeams(id);
	}
	catch (mysqlpp::BadConversion e) {
		std::cout << e.what() << "\n";
	}
	catch (mysqlpp::BadIndex e) {
		std::cout << e.what() << "\n";
	}
	catch (Exception e) {
		throw e;
	}

}

 Deliverator( Injectee * message,
              const UString & mailbox, const EString & user )
     : q( 0 ), i( 0 ), m( message ), mbn( mailbox ), un( user ),
       p( 0 ), mb( 0 )
 {
     Allocator::addEternal( this, "deliver object" );
     q = new Query( "select al.mailbox, n.name as namespace, u.login "
                    "from aliases al "
                    "join addresses a on (al.address=a.id) "
                    "left join users u on (al.id=u.alias) "
                    "left join namespaces n on (u.parentspace=n.id) "
                    "where (lower(a.localpart)=$1 and lower(a.domain)=$2) "
                    "or (lower(u.login)=$3)", this );
     if ( user.contains( '@' ) ) {
         int at = user.find( '@' );
         q->bind( 1, user.mid( 0, at ).lower() );
         q->bind( 2, user.mid( at + 1 ).lower() );
     }
     else {
         q->bindNull( 1 );
         q->bindNull( 2 );
     }
     q->bind( 3, user.lower() );
     q->execute();
 }

void ShotResult::saveRemark()
{
	int index = mtw_Shotremark->currentRow();
	qDebug("Index:%d",index);
	if (index < 0) return;
	QTableWidgetItem *pItem = mtw_Shotremark->item(index, WRITER);
	string strwriter = pItem->text().toStdString();
	pItem = mtw_Shotremark->item(index, REMARK);
	string strremark = pItem->text().toStdString();
	try {
		sql_datetime wdate;
		QDateTime datetime = QDateTime::currentDateTime();
		wdate.year = datetime.date().year();
		wdate.month = datetime.date().month();
		wdate.day= datetime.date().day();
		wdate.hour=datetime.time().hour();
		wdate.minute=datetime.time().minute();
		wdate.second=datetime.time().second();

		Query query = m_con.query();
		REMARKT remark(0,mshotno,strwriter,strremark,wdate);
		query.insert(remark);
		query.execute();
	} catch (const mysqlpp::BadQuery& er) {
		cerr << "Query error: " << er.what() << endl;
	} catch (const mysqlpp::Exception& er) {
		cerr << er.what() << endl;
	};
}

/**
 * Export picture file to disk
 *
 * Demonstrates:
 * - reading the contents of a BLOB
 */
void PhoneBook::export_picture(db_uint id, const char *file_name)
{
    Query q;
    BlobField   blob;

    enum FieldOrder {
        PICTURE_FIELD = 0
    };

    //-------------------------------------------------------------------
    // Select a specific record from the contact table.
    //-------------------------------------------------------------------
    print_error(q.prepare(db, "select picture from contact where id = $<integer>0"), q);
    q.param(0) = id;

    if  (DB_SUCCESS(print_error(q.execute(), q))) {

        blob.attach(q, PICTURE_FIELD);

        //---------------------------------------------------------------
        // Position the cursor to the first record (only 1 record).
        //---------------------------------------------------------------
        if  (q.seek_first() == DB_NOERROR) {
            int             offset, bytes_read;
            db_len_t        blob_size = blob.size();
            const db_len_t  data_size = 256;
            char            data[data_size];

            //-----------------------------------------------------------
            // Open the output file.
            //-----------------------------------------------------------
            FILE *picture_file;
            if ((picture_file = fopen(file_name, "wb")) != NULL) {

                //-------------------------------------------------------
                // Export the BLOB to the output image file
                //-------------------------------------------------------
                for(offset = 0; offset < blob_size; offset += data_size) {
                    if  (bytes_read = blob.read(offset, data, data_size)) {
                        fwrite(data, bytes_read, 1, picture_file);
                    }
                }

                //-------------------------------------------------------
                // Close the output file.
                //-------------------------------------------------------
                fclose(picture_file);

            } else {
                cerr << "Cannot open " << file_name << endl;
            }

        } else {
            cerr << "Could not find contact with id " << (long) id << endl;
        }
    }
    return;
}

	void Editor::update()
	{
		sh::Factory::getInstance().doMonitorShaderFiles();

		if (!mMainWindow)
			return;


		{
			boost::mutex::scoped_lock lock(mSync.mActionMutex);

			// execute pending actions
			while (mMainWindow->mActionQueue.size())
			{
				Action* action = mMainWindow->mActionQueue.front();
				action->execute();
				delete action;
				mMainWindow->mActionQueue.pop();
			}
		}
		{
			boost::mutex::scoped_lock lock(mSync.mQueryMutex);

			// execute pending queries
			for (std::vector<Query*>::iterator it = mMainWindow->mQueries.begin(); it != mMainWindow->mQueries.end(); ++it)
			{
				Query* query = *it;
				if (!query->mDone)
					query->execute();
			}
		}

		boost::mutex::scoped_lock lock2(mSync.mUpdateMutex);

		// update the list of materials
		mMainWindow->mState.mMaterialList.clear();
		sh::Factory::getInstance().listMaterials(mMainWindow->mState.mMaterialList);

		// update global settings
		mMainWindow->mState.mGlobalSettingsMap.clear();
		sh::Factory::getInstance().listGlobalSettings(mMainWindow->mState.mGlobalSettingsMap);

		// update configuration list
		mMainWindow->mState.mConfigurationList.clear();
		sh::Factory::getInstance().listConfigurationNames(mMainWindow->mState.mConfigurationList);

		// update shader list
		mMainWindow->mState.mShaderSets.clear();
		sh::Factory::getInstance().listShaderSets(mMainWindow->mState.mShaderSets);

		mMainWindow->mState.mErrors += sh::Factory::getInstance().getErrorLog();
	}

// 사용자 로그인 Error나면 Error의 이유를 리턴한다.
int CDBManager::LoginUser(char * szNickname, char * szPassword, int * pRealUserID)
{
	try
	{
		Connection mCon(m_szDBName, m_szIP, m_szUser, m_szPassword);
		Query mQuery = mCon.query();

		// Nickname을 통해 유저의 정보를 얻어 온다.
		mQuery << "SELECT id, nickname, password, online FROM userdata WHERE nickname = \"" << szNickname << "\"";

		Result res = mQuery.store();

		Result::iterator i;
		Row row;
		i = res.begin();

		if(i != res.end())
		{
			row = *i;

			if(!strcmp(szPassword, row["password"]))
			{
				*pRealUserID = row["id"];

				int Online = row["online"];
				if(Online == 1) // 중복 로그인을 막기위해 로그인 필드를 검사한다.
				{
					return MSG_SERV_LOGINBAD_LOGINED;
				}
				// Password가 일치하면 온라인으로 표기 한다.
				mQuery << "UPDATE userdata SET online = 1, lastlogin = NULL WHERE id = " << row["id"];
				mQuery.execute();
			}
			else // Password가 틀렸다.
			{
				return MSG_SERV_LOGINBAD_PASSBAD;
			}
		}
		else // Nickname이 틀렸다.
		{
			return MSG_SERV_LOGINBAD_IDBAD;
		}
	}
	catch(BadQuery er) // DB에러 본래는 쿼리에러다.
	{
		return MSG_SERV_LOGINBAD_DATABASEBAD;
	}

	return -1; // 성공하면 음수를 리턴
}

/**
 * Update an existing contact's name.
 *
 * Demonstrates:
 * - searching for existence of a record using an index
 * - edit mode
 */
void PhoneBook::update_contact_name(db_uint id, const wchar_t *newname)
{
    Query q;

    q.prepare(db,
        "update contact "
        "  set name = $<nvarchar>1 "
        "  where id = $<integer>0 ");

    q.param(0) = id;
    q.param(1) = newname;

    print_error(q.execute(), q);
}

bool BaseDeDatos::guardar_registro_sensor(float valor_sensor, int id_sensor_, int tipo_sensor){
  try {
    time_t now = time(0);
    Query query = conn.query();

    switch (tipo_sensor) {
      case 2:{
        registro_temp registro_t(mysqlpp::DateTime(now), valor_sensor, id_sensor_);
        query.insert(registro_t);
      }
      break;

      case 1:{
        registro_humedad registro_h(mysqlpp::DateTime(now), valor_sensor, id_sensor_);
        query.insert(registro_h);
      }
      break;

      case 0:{
        registro_luz registro_l(mysqlpp::DateTime(now), valor_sensor, id_sensor_);
        query.insert(registro_l);
      }
        break;
    }
      // Show the query about to be executed.
      cout << "Query: " << query << endl;
      query.execute();

    }
    catch (const mysqlpp::BadQuery& er) {
        // Handle any query errors
        cerr << "Query error: " << er.what() << endl;
        return false;
    }
    catch (const mysqlpp::BadConversion& er) {
        // Handle bad conversions
        cerr << "Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return false;
    }
    catch (const mysqlpp::Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return false;
    }

    return true;

}

void _ftpEntry::checkDBFolders() {
	bool							isDone						= false;
	Query							serverQuery					= _sqlConnection->query();
	StoreQueryResult				serverResults;
	StoreQueryResult::iterator		storeQueryIT;
	vector<_ftpEntry*>::iterator	entryIT;

	// Nothing to do here...
	if( directorys.empty() )
		return;

	// Get a list of all the directorys that we parent
	serverQuery << "select * from ServerFolders where serverID = " << quote << _serverID;
	serverQuery << " AND parentID = "	<< quote << _folderID;

	// Do the query
	serverResults	= serverQuery.store();

	// None?
	if( !serverResults.size() )
		return;

	// We now have to check each DB result, against the results in directorys vector
	for(storeQueryIT = serverResults.begin(); storeQueryIT != serverResults.end(); storeQueryIT++ , isDone = false) {

		for( entryIT = directorys.begin(); entryIT != directorys.end(); entryIT++) {

			// Does this directory still exist?
			if((*entryIT)->getName() == (string) (*storeQueryIT)["name"]) {
				isDone = true;	// Yes it exists
				break;
			}	// parentID check
		}	// entryIT

		// Deleted?
		if(!isDone) {
			// Yes, remove the entry from the DB

            removeFolderChildren( (size_t) (*storeQueryIT)["folderID"] );
			serverQuery.str("");
			serverQuery << "delete from ServerFolders where folderID = "	<< quote << (*storeQueryIT)["folderID"];

			// bye bye :)
			serverQuery.execute();

		}	// !isDone

	}	// storeQueryIT

}	// checkDBFolders

bool _ftpEntry::rescanUpdateParent(bool value) {
 	Query QueryNewFolder = _sqlConnection->query();

	QueryNewFolder << "UPDATE ServerFolders SET rescanForce = "	<< quote << value;
	QueryNewFolder << " WHERE folderID = "	<< quote << _parentID;

	// go go go
	SimpleResult res = QueryNewFolder.execute();

	if(!res.rows())
		return false;	// No Row changed

	return true;
}