CODEWITHSUNDEEP
c++sqllinuxinsertlibpqxx
gispathfinder
gispathfinder

Reputation: 1

How to exec the sql with dynamic params in pqxx7

I want to exec the sql with dynamci params

I can find the codes like the following in pqxx4

work txn(*conn);
pqxx::result r = txn.prepared("insert into mytable (a,b,c,d,e) values (1,2,$1,$2,$3)")(person_name)(age)(sex).exec();
txn.commit();

but I use the pqxx of version 7, the code can't support.

so How to exec the sql with dynamic params in pqxx7?

using FieldValue = std::variant<int32_t, int64_t, float, double, std::string, bool>

std::string tableName = "mytable";
std::vector<std::string> columNames{"a", "b", "c", "d", "e", ...};
std::vector<FieldValue >{1, "", 2, "", 5L ...};

std::string sql = "INSERT INTO \"" + tableName + "\"(";
for (int i = 0; i < colums.size(); i++)
{
  if (i > 0)
  {
    sql += ", ";
  }
  sql += colums[i];
}
sql += " ) VALUES ( ";
for (int i = 0; i < colums.size(); i++)
{
  if (i > 0)
  {
    sql += ", ";
  }
  sql += "$" + std::to_string(i + 1);
}
sql += " )";

pqxx::connection conn{...};
conn.prepare("test", sql);
pqxx::work work(conn);

how to do ?

Upvotes: 0

Views: 902

Answers (2)

Shawnz99
Shawnz99

Reputation: 23

This is a little late but for people looking at this now and in the future. I think now you would want to do something like this Documentation

Basically instead of prepared statements you would still do the $1 , $2, ... stuff in a query string and then you would build a pqxx::params and then pass that with the query.

int values[] = {1,2,3};
std::string query = "INSERT into mytable (a, b, c) VALUES ($1, $2, $3)";
pqxx::params p;

for (int i = 0; i < num_values; i++) {
  p.append(values[i])
}
pqxx::nontransaction ntxn(c);
ntxn.exec(query, p);

and your c would be your pqxx::connection. Note: This code my not be exactly syntatically correct but this is the general idea I think.

Upvotes: 1

Karlson
Karlson

Reputation: 3048

I think what you're looking for is in the Documentation section on Prepared Statements (sorry no link as it keeps changing)

pqxx::connection conn;
conn.prepare("insert_statement", "insert into mytable(a, b, c, d, e) values (1, 2, $1, $2, $3)");

Then at some later point:

pqxx::transaction txn(conn);
txn.exec_prepared("insert_statement", field_value3, field_value4, field_value5);

If you're going to be inserting strings use:

txn.esc(field_value)

There are various different ways of executing prepared statements but this is something similar to what I currently use with libpqxx 7.x.

Upvotes: 0

Related Questions