Apart from regular statements in various languages, the Polypheny CPP Driver also supports statement preparation in combination with various batched and non-batched execution modes. These are explained below.
General Concept
Preparation and execution are performed using the same cursor object as used for regular statements. For preparation, the method prepare is used to define a statement with placeholders for parameters. The placeholders can be indexed (?) or named (:name). The execute_prepared method is then used to execute the prepared statement with actual parameter values.
Currently, preparing statements is only fully supported for SQL. For other languages, conflict between the placeholder names and language-specific syntax might occur. Support is therefor experimental.
Unparameterized Statement Batch
An unparameterized statement batch can execute multiple statements as a single batch. This is useful for performing bulk operations efficiently. It’s important to not that all statements inside such a batch must be DML or DDL statements. If any other statement type is used, an exception is thrown.
// Insert multiple entries into the customers table
std::vector<std::string> insert_statements = {
"INSERT INTO customers(id, name, year_joined) VALUES (1, 'Maria', 2012)",
"INSERT INTO customers(id, name, year_joined) VALUES (2, 'Daniel', 2020)",
"INSERT INTO customers(id, name, year_joined) VALUES (3, 'Peter', 2001)"
};
cursor.execute("sql", insert_statements, "public");
Indexed Parameterized Statement
Indexed parameterized statements use ? as placeholders for parameters. This allows the efficient re-execution of a statement for various sets of parameters. The actual parameter values are provided and assigned in the same order as the placeholders. Their order thus matters. The parameter sets are provided as vectors.
// Prepare the statement with indexed parameters
cursor.prepare("sql", "INSERT INTO customers(id, name, year_joined) VALUES (?, ?, ?)", "public");
// Execute the statement with parameters
std::vector<Types::TypedValue> parameters = { Types::TypedValue(4), Types::TypedValue("Anna"), Types::TypedValue(2004) };
cursor.execute_prepared(parameters);
Batched Indexed Parameterized Statement
Batched indexed parameterized statements are the combination of batched and indexed parameterized statements. They allow executing multiple sets of parameters on a single statement in a single batch. This is useful for inserting or updating multiple rows at once.
// Prepare the statement with indexed parameters
cursor.prepare("sql", "INSERT INTO customers(id, name, year_joined) VALUES (?, ?, ?)", "public");
// Execute the statement with a batch of parameters
std::vector<std::vector<Types::TypedValue>> parameter_batch = {
{ Types::TypedValue(5), Types::TypedValue("Thomas"), Types::TypedValue(2004) },
{ Types::TypedValue(6), Types::TypedValue("Andreas"), Types::TypedValue(2014) }
};
cursor.execute_prepared(parameter_batch);
Named Parameterized Statement
Named parameterized statements use :name as placeholders. The actual parameter values are provided in a map, where keys are parameter names.
// Prepare the statement with named parameters
cursor.prepare("sql", "INSERT INTO customers(id, name, year_joined) VALUES (:id, :name, :year_joined)", "public");
// Execute the statement with named parameters
std::unordered_map<std::string, Types::TypedValue> named_parameters;
named_parameters["id"] = Types::TypedValue(7);
named_parameters["name"] = Types::TypedValue("Michael");
named_parameters["year_joined"] = Types::TypedValue(2010);
cursor.execute_prepared(named_parameters);
