Running queries

There are two query modes: simple query mode and extended query mode. Simple query mode executes arbitrary parameterless SQL scripts. Extended query mode prepares a query and executes the query with (or without) parameters.

Simple queries

A simple query is an arbitrary script. It can have several statements, each statement will generate a resultset callback.

Running a single query with implicit transaction start

When a single query needs to be made, it might be easier not to wrap it into a begin call, but to pass the query the alias of the database to use. Don't forget to commit the transaction after handling the result.

query( "main"_db,       // Database alias
[](transaction_ptr tran, resultset res, bool complete)
{
    // Process the result
    tran->commit();
},
[](db_error const& error)
{
});

See Also

Processing query results

Running several independent queries in one transaction

Independent queries (i.e. queries that do not depend on each others' results can be enqueued in a single begin callback together with the call to commit. The queries will be run one by one in a single transaction, their results callbacks will be called as they will be completed. After all statements finish, the transaction will be committed.

Warning

When the transaction commit or rollback is enqueued, no other queries can be started, neither from the block containing the commit call, nor from the queries' results callbacks.

db_service::begin("main"_db,    // Database alias
[](transaction_ptr tran) {
    query(tran, "statement 1")(
    [](transaction_ptr, resultset r1, bool) {
        // Result 1
    },
    [](db_error const&) {
    });
    query(tran, "statement 2")(
    [](transaction_ptr, resultset r1, bool) {
        // Result 2
    },
    [](db_error const&) {
    });
    query(tran, "statement 3")(
    [](transaction_ptr, resultset r1, bool) {
        // Result 3
    },
    [](db_error const&) {
    });
    query(tran, "statement 4")(
    [](transaction_ptr, resultset r1, bool) {
        // Result 4
    },
    [](db_error const&) {
    });
    // Yes, the commit statement can be issued here.
    tran->commit();
},
[](db_error const&) {
});

See Also

Errors and exceptions

Running a query dependent on the results of another one

query("main"_db, "statement")(
[](transaction_ptr tran, resultset res, bool complete)
{
    for (auto row : res) {
        // Run a prepared statement with a parameter
        query(tran, "statement $1", row["id"].as<bigint>())(
        [](transaction_ptr t, resultset res, bool complete) {
            // Dependent result
        },
        [](db_error const&){
        });
    }
    // All queries has been enqueued, can commit here
    tran->commit();
},
[](db_error const&)
{
});

Prepared statements (extended query mode)

Prepared statements is an efficient way to run repeated queries (the most frequent situation). pg_async uses PostgreSQL extended query mode to parse the statement, bind parameters and execute the query.

// Full syntax
query("main"_db,    // Database alias
    "select * from some_table where amount > $1 and price < $2",    // Statement with placeholders
).bind(
    100, 500        // Query parameters
)(
[](transaction_ptr tran, resultset res, bool complete) {
},
[](db_error const&)
{
});
// Short syntax
query("main"_db,    // Database alias
    "select * from some_table where amount > $1 and price < $2",    // Statement with placeholders
    100, 500        // Query parameters
)(
[](transaction_ptr tran, resultset res, bool complete) {
},
[](db_error const&)
{
});
// Query without parameters
query("main"_db,    // Database alias
    "select * from some_table"  // Statement without parameters
).bind()(           // Call the bind method to mark the query for preparing
    // Handle results and errors
);

See Also

tip::db::pg::query

Values input/output

Transactions

Processing query results

Errors and exceptions

Callback types