MySQL C++ connector: symbolic names for parameters?

Question

I need symbolic names for MySQL query parameters because the query uses a really complex expression in its WHERE clause. Unfortunately, the C++ connector does not support named parameters. I had an idea to use two statements, one to set the variables and the other to use them, like below:

const char* req =
    " SET @id=?, @from=?, @to=?;"
    " SELECT ..., creation_date, ... "
    " FROM ... "
    " WHERE ... AND (@from is null OR @from is not null AND creation_date >= @from) AND (@to is null OR @to is not null AND creation_date <= @to)";
// in practice, the WHERE condition is even more complex than the above

std::unique_ptr<sql::PreparedStatement>stmt(con->prepareStatement(req));
....

but this does not work, the connector cannot execute multiple statements.

In addition, from what I read it is not clear if the variables will still exist after the first statement is complete.

How can I use symbolic names in a query?

Answer 1

I will not accept my own answer in the hope that someone comes up with a better solution.

The poor man's symbolic variables are implemented via string replacement:

const char* req_cstr =
    " SET @id=?, @from=?, @to=?;"
    " SELECT ..., creation_date, ... "
    " FROM ... "
    " WHERE ... AND (@from is null OR @from is not null AND creation_date >= @from) AND (@to is null OR @to is not null AND creation_date <= @to)";

std::string req(req_cstr);
std::string to = std::to_string(timeTo) + " ";
replaceAll(req,"@to",to);
//replaceAll(req,"@from",...);

and then that modified request is executed.

You will have to pay attention to the SQL variable names, it is too easy to confuse them with C++ variable names, for example replaceAll(req,"@after",after); is wrong for the above query because the variable is called "@from".

The replaceAll function is (origin):

void replaceAll(std::string& str, const std::string& from, const std::string& to) {
    if(from.empty())
    return;
    size_t start_pos = 0;
    while((start_pos = str.find(from, start_pos)) != std::string::npos) {
    str.replace(start_pos, from.length(), to);
    start_pos += to.length(); // In case 'to' contains 'from', like replacing 'x' with 'yx'
    }
}