statement_execution.md

< Index

Statement execution

Using sqlpp23, SQL statements can be executed by calling operator() on a connection object. The argument has to be

• a std::string containing a valid SQL statement for the given connection,
• a sqlpp::statement_t which passes sqlpp23's validity checks, or
• a prepared statement

String execution

// Select pretty much nothing
const size_t affected_rows = db("SELECT NULL");

Executing SQL statements in string form can be useful for statement types that are not supported by sqlpp23, like CREATE TABLE, for instance.

Statement execution

// delete unwanted rows
size_t affected_rows = db(delete_from(tab).where(tab.id == 17));

// insert a row
const int64_t last_insert_id = db(insert_into(tab).default_values());

// update rows
affected_rows = db(update(tab).set(tab.intN = 7));

// select some columns of some rows
for (const auto& row : db(select(tab.id, tab.intN)
                          .from(tab)
                          .where(tab.textN.is_null()))) {
  // do something with row.id and row.intN
}

If a statement does not pass the library's compile time checks, a static_assert will prevent the execution of that statement. Depending on the compiler and the surrounding code, the respective error messages might look a bit ugly, but should contain the static_assert's message, e.g.

// Ooops, table mismatch in select and from.
for (const auto& row : db(select(all_of(foo)).from(bar))) {
  // Something
}

This would result in an error message like this (depending on the compiler and flags and probably a few more things):

.../Select.cpp:81:28:   required from here
   81 |   for (const auto& row : db(select(all_of(foo)).from(bar))) {
      |                          ~~^~~~~~~~~~~~~~~~~~~~~~~~~~~
.../include/sqlpp23/core/wrapped_static_assert.h:43:42: error: static assertion failed:
at least one selected column requires a table which is otherwise not known in the statement

Prepared statement execution

Statements can also be prepared before execution. This makes sense for instance if you want to run a parameterized statement more than once, e.g.

SQLPP_CREATE_NAME_TAG(cheese); // outside of function
// ...

// Prepare an insert statement with two parameters
auto prepared_insert = db.prepare(
    insert_into(tab).set(
                tab.alpha = parameter(tab.alpha),
                tab.beta = parameter(sqlpp::text(), cheese)
    ));

Parameters can be constructed in two fashions:

parameter(const DataType&, const AliasProvider&);

parameter(const NamedExpression&)

• DataType has to be an sqlpp23 data type.
• AliasProviders can be generated by using the SQLPP_CREATE_NAME_TAG macro (at namespace level).
• Named expressions are expressions with an sqlpp23 data type and a name, e.g. a table or a column.

You can now set the parameters and execute the prepared statement multiple times, e.g.

// insert multiple rows based on a range of input values
for (const auto& input : input_values)
{
  // Set parameters
  prepared_insert.parameters.alpha = input.alpa;
  prepared_insert.parameters.cheese = input.beta;

  // Execute prepared statement with the current set of parameters
  db(prepared_insert);
}

< Index