2

I am creating a transaction where I want to update a user in one table and delete some data in another that belongs to that user. But only the first query is executed, not the second one. In the delete statement in the second query code is a comma-separated std::string.

pqxx::connection c(connectionString);

try {
    pqxx::work w(c);
    pqxx::result r;

    c.prepare("user", "update user set started = null, finished = null, task = $1 where id = $2");
    r = w.prepared("user")(task)(email).exec();

    c.prepare("belongings", "delete from belongings where id in " \
        "(select id from info where code in ($1) and id = $2)");
    r = w.prepared("belongings")(code)(id).exec();

    w.commit();
}

I read this SO-thread that explain how to run multiple queries before commit(). So I must be making a mistake in the second delete statement but can't find the reason.

kometen
  • 6,536
  • 6
  • 41
  • 51
  • What's the type of `belongings.code?` Also show assignment to `code` variable. – klin Nov 17 '17 at 11:40
  • This doesn't answer your question, but you could wrap all of that in a function and execute the function. This would have the added benefit that if the first statement failed it wouldn't execute the second (although a transaction could accomplish the same thing). – Hambone Nov 17 '17 at 11:42
  • @klin belongings.code is varchar in the table. And code is a std::string like 'abc','def','ghi'. I first thought there was a limit to the IN clause but that appears not to be the case. – kometen Nov 17 '17 at 11:50

1 Answers1

1

The code parameter is interpreted as a single literal. You can try to use the alternative syntax of any(array expression), e.g.:

code = "{abc,def}";     // instead of code = "'abc','def'"
...

c.prepare("belongings", "delete from belongings where id in " \
    "(select id from info where code = any ($1) and id = $2)");
r = w.prepared("belongings")(code)(id).exec();
klin
  • 112,967
  • 15
  • 204
  • 232