How to prepare statements and bind parameters in Postgresql for C++

Question

I'm quite new to C++ and know a little bit about pqxx library. What I want to implement is to prepare statements and bind parameters. In PHP I'm used to doing this in such a nice and concise manner:

$s = $db->prepare("SELECT id FROM mytable WHERE id = :id");
$s->bindParam(':id', $id);
$s->execute();

or using tokens:

$data = array();
$data[] = 1;
$data[] = 2;
$s = $db->prepare("SELECT id FROM mytable WHERE id = ? or id = ?");
$s->execute($data);

I tried to fugure out from pqxx documentation how to implement this, but to me documentation looks like a mess and lacks short and simple examples (like I provided above). I hope someone can also provide such simple examples (or of comparable simplicity - without having to write some behemoth code) when dealing with Postgresql in C++.

Thundering

For those who are struggling with new version of pqxx which has no pqxx::prepare::invocation class in the library, here is the solution: https://mcmap.net/q/1476308/-libpqxx-v12-dynamically-bind-vector-values-to-prepared-statement-alternative-to-invocation – Iorgo

Answer 1

A simple example. This just prints the number of entries with id value 0.

#include<pqxx/pqxx>
#include<iostream>

int main()
{
    std::string name = "name";
    int id = 0;
    try {
        //established connection to data base
        pqxx::connection c("dbname=mydb user=keutoi");
        pqxx::work w(c);
        //statement template
        c.prepare("example", "SELECT id  FROM mytable WHERE id = $1");
        //invocation as in varible binding
        pqxx::result r = w.prepared("example")(id).exec();
        
        w.commit();
        //result handling for accessing arrays and conversions look at docs
        std::cout << r.size() << std::endl;
    }
    catch(const std::exception &e)
    {
        std::cerr << e.what() << std::endl;
        return 1;
    }
    return 0;
}

The function w.prepared() is a bit convoluted. It's similar to a curried(curry) function in haskell, as in it takes a parameter and returns another function which in turn takes another parameter. That kind of thing.

Documentation says:

How do you pass those parameters? C++ has no good way to let you pass an unlimited, variable number of arguments to a function call, and the compiler does not know how many you are going to pass. There's a trick for that: you can treat the value you get back from prepared as a function, which you call to pass a parameter. What you get back from that call is the same again, so you can call it again to pass another parameter and so on.

Once you've passed all parameters in this way, you invoke the statement with the parameters by calling exec on the invocation

If there are more parameters use $1 $2 and so on in the prepare function.

c.prepare("SELECT id name FROM mytable WHERE id = $1 AND name = $2")

and give the varibles as

w.prepared("example")(dollar1_var)(dollar2_var).exec()

An Example for dynamic preparation

#include<pqxx/pqxx>
#include<iostream>
#include<vector>

//Just give a vector of data you can change the template<int> to any data type
pqxx::prepare::invocation& prep_dynamic(std::vector<int> data, pqxx::prepare::invocation& inv)
{
    for(auto data_val : data)
        inv(data_val);
    return inv;
}

int main()
{
    std::string name = "name";

    //a data array to be used.
    std::vector<int> ids;
    ids.push_back(0);
    ids.push_back(1);

    try {
        pqxx::connection c("dbname=mydb user=keutoi");
        pqxx::work w(c);

        c.prepare("example", "SELECT id  FROM mytable WHERE id = $1 or id = $2");
        pqxx::prepare::invocation w_invocation = w.prepared("example");

        //dynamic array preparation
        prep_dynamic(ids, w_invocation);
        //executing prepared invocation.
        pqxx::result r = w_invocation.exec();

        w.commit();

        std::cout << r.size() << std::endl;
    }
    catch(const std::exception &e)
    {
        std::cerr << e.what() << std::endl;
        return 1;
    }
    return 0;
}

if you want to handle other data types use this function definition

template<class T> pqxx::prepare::invocation& prep_dynamic(std::vector<T> data, pqxx::prepare::invocation& inv)
{
    for(auto data_val : data)
        inv(data_val);
    return inv;
}

Attraction

That is very useful! Thank you, sir! And can you, please, elaborate a little bit on this and explain where quote method is used. It seems like I've seen somewhere this method, but I'm not sure whether it is used to prepare statements or do some other job. As for this construct - w.prepared("example")(dollar1_var)(dollar2_var).exec() - it is useful indeed, but I do not know if anybody can build this dynamically, based on some arbitrary number of parameters to prepare - something like I showed in PHP - $s->execute($data); Is something like this possible to implement in C++? – Thundering

@Thundering I've added a simple example with dynamic preparation. I don't know what you are refereing to by quote method. – Attraction

Thank you so much! You made a great answer! – Thundering

Answer 2

Use pqxx::prepare::invocation where you can, and bind more values before execution, because it's more stable and error preventative, but there is a faster way as I describe it below.

I. With invocation:

pqxx::nontransaction W(C);
std::string m_insertCommand = "INSERT INTO tableforperftest(column1, column2) VALUES";


unsigned  int m_nCurrentRow = 32767;

for (size_t i = 0; i < m_nCurrentRow; i++)
{
    unsigned int countOf$ = i * 2;
    for (unsigned int i = 0; i < 2; ++i)
    {
        if (i == 0)
        {
            m_insertCommand += "(";
        }
        else
        {
            m_insertCommand += ", ";
        }
        m_insertCommand += "$";
        std::stringstream ss;
        ss << countOf$ + i + 1;
        m_insertCommand += ss.str();
    }
   if(i < m_nCurrentRow - 1)
    m_insertCommand += ") ,";
}
m_insertCommand += ")";

C.prepare("insert_into_db", m_insertCommand);
pqxx::prepare::invocation inv = W.prepared("insert_into_db");

for (size_t i = 0; i < m_nCurrentRow; i++)
{
    inv(i)(i);
}

inv.exec();

II. With stored procedure which gets more values for parameters:

CREATE OR REPLACE FUNCTION insertintoboosted(valuesforinsert TEXT) RETURNS VOID AS
$$ 
BEGIN
     EXECUTE 'INSERT INTO tableforperftestproof(column1, column2) VALUES (' || valuesforinsert || ')';
END;
$$
LANGUAGE plpgsql;

Code:

for (size_t i = 0; i < m_nCurrentRow; i++)
{

    if (i == 0)
        ss  << i << "," << i;
    else
        ss << "(" << i << "," << i;

    if (i < m_nCurrentRow - 1)
        ss << "),";
}

C.prepare("prep2", "select insertintoboosted($1::text)");

W.prepared("prep2")(ss).exec();

III. With parameter bindings and execution for each time:

std::string m_insertCommand3 = "INSERT INTO tableforperftest(column1, column2) VALUES ($1, $2)";
C.prepare("insert_into_db3", m_insertCommand3);
for (size_t i = 0; i < m_nCurrentRow; i++)
{
    W.prepared("insert_into_db3")(i)(i).exec();
}

To compare the solutions with 32767 inserts:

Invocation:                              --> Elapsed:    0.250292s
Stored Proc:                             --> Elapsed:    0.154507s 
Parameter binding + execution each time: --> Elapsed:    29.5566s

Absalom