C++ Prepared Statements and Batches

On this page

Insert with batches and parameters

Use SQL prepared statements to bind values independently of the statement text. A cursor keeps the prepared statement for subsequent executions. Create another cursor if you need to run a different statement while its result is still in use.

Operation	Call	Input and result
SQL statement batch	`execute(language, statements[, namespace])`	Pass a `std::vector<std::string>` of data manipulation statements. The result is a vector of update counts.
Indexed preparation	`prepare_indexed(language, statement[, namespace])`	Place a `?` at each parameter position.
Indexed execution	`execute_prepared(parameters)`	Pass a mutable `std::vector<Types::TypedValue>` in placeholder order. The result is a regular `Result`.
Indexed batch execution	`execute_prepared(parameter_sets)`	Pass a mutable `std::vector<std::vector<Types::TypedValue>>`. Each inner vector supplies one execution of the prepared data manipulation statement. The result contains the update counts reported by the server.
Named preparation	`prepare_named(language, statement[, namespace])`	Use `:name` placeholders.
Named execution	`execute_prepared(parameters)`	Pass a mutable `std::unordered_map<std::string, Types::TypedValue>`. Use names without the leading colon as keys. The result is a regular `Result`.

Use an explicit namespace or omit it to use the default from the connection. SQL is the supported language for the examples. Bind values using the appropriate TypedValue constructors. For insert, update or delete statements, unwrap the result as Results::ScalarResult to read the statement’s scalar value. Polypheny-DB can return one aggregate update count for an indexed batch.

Insert with batches and parameters

On a development server, use an account that can create and drop tables in public. This example creates and drops the table cpp_docs_customers. Start with the name available. Build the program as described in the integration guide.

#include <cstdint>
#include <iostream>
#include <numeric>
#include <string>
#include <unordered_map>
#include <vector>

#include "PolyphenyCPPDriver.h"

int main() {
    Connection::Connection connection("127.0.0.1", "pa", "");
    Connection::Cursor setup(connection);
    setup.execute(
        "sql",
        "CREATE TABLE cpp_docs_customers (id INTEGER NOT NULL, name VARCHAR(50), PRIMARY KEY (id))",
        "public"
    );

    {
        Connection::Cursor batch(connection);
        const std::vector<std::string> statements = {
            "INSERT INTO cpp_docs_customers (id, name) VALUES (1, 'Maria')",
            "INSERT INTO cpp_docs_customers (id, name) VALUES (2, 'Daniel')"
        };
        const auto counts = batch.execute("sql", statements, "public");
        std::cout << "Batch statements: " << counts.size() << '\n';
    }

    {
        Connection::Cursor indexed(connection);
        indexed.prepare_indexed("sql", "INSERT INTO cpp_docs_customers VALUES (?, ?)", "public");
        std::vector<Types::TypedValue> parameters = {
            Types::TypedValue(std::int32_t{3}), Types::TypedValue("Anna")
        };
        auto result = indexed.execute_prepared(parameters);
        std::cout << "Indexed insert: " << result->unwrap<Results::ScalarResult>()->get_scalar() << '\n';

        result.reset();

        std::vector<std::vector<Types::TypedValue>> parameter_sets = {
            {Types::TypedValue(std::int32_t{4}), Types::TypedValue("Thomas")},
            {Types::TypedValue(std::int32_t{5}), Types::TypedValue("Andreas")}
        };
        const auto counts = indexed.execute_prepared(parameter_sets);
        std::cout << "Indexed batch rows: " << std::accumulate(counts.begin(), counts.end(), std::uint64_t{0}) << '\n';
    }

    {
        Connection::Cursor named(connection);
        named.prepare_named("sql", "INSERT INTO cpp_docs_customers VALUES (:id, :name)", "public");
        std::unordered_map<std::string, Types::TypedValue> parameters = {
            {"id", Types::TypedValue(std::int32_t{6})}, {"name", Types::TypedValue("Michael")}
        };
        auto result = named.execute_prepared(parameters);
        std::cout << "Named insert: " << result->unwrap<Results::ScalarResult>()->get_scalar() << '\n';
    }

    {
        auto result = setup.execute("sql", "SELECT name FROM cpp_docs_customers ORDER BY id", "public");
        auto* rows = result->unwrap<Results::RelationalResult>();
        for (auto row = rows->begin(); row != rows->end(); ++row) {
            std::cout << (*row)[0].as_string() << '\n';
        }
    }

    setup.execute("sql", "DROP TABLE cpp_docs_customers", "public");
}

The program prints the batch counts and six names in ID order.

Querying

API Reference

Quick Search Results

Insert with batches and parameters