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

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

void ConfigConnector::updateFilterBlack(bool enable) {
	Connection conn = _pool->getConnection();

	PreparedStatement stmt = conn->prepareStatement("UPDATE `config_resource` SET `value` = ? WHERE `cid` = '1'");
	stmt->setBoolean(1, enable);
	stmt->execute();

	_pool->returnConnection(conn);
}

void constantQuery(const std::string &queryName = std::string(), IOManager *ioManager = NULL)
{
    Connection conn(g_goodConnString, ioManager);
    PreparedStatement stmt = conn.prepare("SELECT 1, 'mordor'", queryName);
    Result result = stmt.execute();
    MORDOR_TEST_ASSERT_EQUAL(result.rows(), 1u);
    MORDOR_TEST_ASSERT_EQUAL(result.columns(), 2u);
    MORDOR_TEST_ASSERT_EQUAL(result.get<int>(0, 0), 1);
    MORDOR_TEST_ASSERT_EQUAL(result.get<long long>(0, 0), 1);
    MORDOR_TEST_ASSERT_EQUAL(result.get<const char *>(0, 1), "mordor");
    MORDOR_TEST_ASSERT_EQUAL(result.get<std::string>(0, 1), "mordor");
}

void queryForParam(const std::string &query, ParamType param, size_t expectedCount,
    ExpectedType expected,
    const std::string &queryName = std::string(), IOManager *ioManager = NULL)
{
    Connection conn(g_goodConnString, ioManager);
    fillUsers(conn);
    PreparedStatement stmt = conn.prepare(query, queryName);
    Result result = stmt.execute(param);
    MORDOR_TEST_ASSERT_EQUAL(result.rows(), expectedCount);
    MORDOR_TEST_ASSERT_EQUAL(result.columns(), 1u);
    MORDOR_TEST_ASSERT_EQUAL(result.get<ExpectedType>(0, 0), expected);
}

static void copyOut(IOManager *ioManager = NULL)
{
    Connection conn(g_goodConnString, ioManager);
    conn.execute("CREATE TEMP TABLE country (code TEXT, name TEXT)");
    PreparedStatement stmt = conn.prepare("INSERT INTO country VALUES($1, $2)",
        "insertcountry");
    Transaction transaction(conn);
    stmt.execute("AF", "AFGHANISTAN");
    stmt.execute("AL", "ALBANIA");
    stmt.execute("DZ", "ALGERIA");
    stmt.execute("ZM", "ZAMBIA");
    stmt.execute("ZW", "ZIMBABWE");

    Stream::ptr stream = conn.copyOut("country").csv().delimiter('|')();
    MemoryStream output;
    transferStream(stream, output);
    MORDOR_ASSERT(output.buffer() ==
        "AF|AFGHANISTAN\n"
        "AL|ALBANIA\n"
        "DZ|ALGERIA\n"
        "ZM|ZAMBIA\n"
        "ZW|ZIMBABWE\n");
}

int nuodb_statement_query(struct nuodb *db, struct nuodb_statement *st,
                          struct nuodb_resultset **rs, int *column_count) {
    ResultSet *resultSet = 0;
    PreparedStatement *stmt = reinterpret_cast<PreparedStatement *>(st);
    try {
        bool hasResults = stmt->execute();
        if (hasResults) {
            resultSet = stmt->getResultSet();
        } else {
            resultSet = stmt->getGeneratedKeys();
        }
        *column_count = resultSet->getMetaData()->getColumnCount();
        *rs = reinterpret_cast<struct nuodb_resultset *>(resultSet);
        return 0;
    } catch (SQLException &e) {
        if (resultSet) {
            resultSet->close();
        }
        return setError(db, e);
    }
}

/*
 * Overload Function: setup_test_table
 */
void setup_test_table(Connection *con, int numOfPocs, int sensorsPerPoc) {
    int i, j;
    Statement *stmt;
    PreparedStatement *pstmt;

    try {
        stmt = con->createStatement();
        stmt->execute("DROP TABLE IF EXISTS ParkingSpot");
        stmt->execute("CREATE TABLE ParkingSpot ("
                      "PocID int NOT NULL,"
                      "SensorID int NOT NULL,"
                      "SpotStatus int NOT NULL,"
                      "StartTime datetime DEFAULT NULL,"
                      "ExpireTime datetime DEFAULT NULL,"
                      "PRIMARY KEY (PocID, SensorID),"
                      "UNIQUE KEY LocationSensor_UNIQUE (PocID, SensorID))");
        delete stmt;

        pstmt = con->prepareStatement("INSERT INTO ParkingSpot (PocID, SensorID, SpotStatus) VALUES (?, ?, 0)");
        for(i = 0; i < numOfPocs; i++)
        {
            pstmt->setInt(1, i);
            for(j = 0; j < sensorsPerPoc; j++)
            {
                pstmt->setInt(2, j);
                pstmt->execute();
            }
        }
    } catch (sql::SQLException &e) {
        cout << "# ERR: SQLException in " << __FILE__;
        cout << " (function: " << __FUNCTION__ << ")" << endl;
        cout << "# ERR: " << e.what();
        cout << " (MySQL error code: " << e.getErrorCode();
        cout << ", SQLState: " << e.getSQLState() << " )" << endl;
    }

    delete pstmt;
}

/**
 *
 * 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)
//.........这里部分代码省略.........