Dbo Query bind multiple strings

I would like to use the "WHERE FIELD IN list" construct with Dbo .bind(), but could not achieved a working solution.

What I want in SQL something like: SELECT name, phone FROM phonebook WHERE name IN ('Susan', 'Mary');

I could make it work with a single name:
session.query<Result>("SELECT name, phone FROM phonebook WHERE name IN (?)").bind("Susan");
This works.
But how could I bind "'Susan', 'Mary'" as a list?

Could somebody show me the right way?

Regars:
Tibor

Replies (4)

RE: Dbo Query bind multiple strings - Added by Stefan Bn 11 months ago

You could create a separate WString variable, build that up with the names list and put that WString variable into the query.

Something like (pseudo-code, not tested):

WString namesQuery;
for (WString name : namesArray)
{
  if (!namesQuery.empty())
    namesQuery += ",";

  namesQuery += "'" + name + "'";
}

session.query<Result>("SELECT name, phone FROM phonebook WHERE name IN (?)").bind(namesQuery);

Best,
Stefan

RE: Dbo Query bind multiple strings - Added by Tibor Miseta 11 months ago

Dear Stefan,

Thank you for the hint, but that doesn't work either.
Digging a bit into it, Wt::Dbo does not make literal string substitution for parameter binding, so this approach is not working.
It uses instead the 'natural' bind method of the SQL backend (logical), so the behavior may be backend specific. Currently there is no general solution for this, as I see. (The Query.bind() method doesn't accept Wt::WString just std::string.)

I just realized that I have posted in the wrong forum (not in the Help), sorry for that.
Tibor

RE: Dbo Query bind multiple strings - Added by Matthias Van Ceulebroeck 11 months ago

Hello Tibor,

I find the easiest way to do this is to have a helper like this:

std::string createBindString(size_t length)
{
  if (size == 0) {
    return "";
  }

  std::string bindString = "(";
  for (std::size_t i = 0; i < length - 1; ++i) {
    bindString += "?, ";
  }

  return bindString + "?)";
}

auto query = session.query<Result>("SELECT name ... WHERE name in " +  createBindString(names.size());
for (const auto& name : names) {
  query.bind(name);
}

Note that the case where the size is 0 is not really correct here. You may want to handle that before the query construction, or make the helper throw.

Best,
Matthias

RE: Dbo Query bind multiple strings - Added by Tibor Miseta 11 months ago

Yes! This is a nice idea! Thanks for it, Matthias!
It could be further generalized, using a named parameter, that could alter the sql statement too accordingly. Could be something like:
auto q = Wt::Dbo::Query<Result>("SELECT name ... WHERE name in @names"); std::vector<std::string>> params; // the parameters q.bind("@names", params);
Although it is questionable if it's worth the efforts, how frequent this need is.
However supporting named parameters would not be a bad idea in general. Less error prone for complex queries than counting the question marks in case of a bit more complex sql statement/stored procedure when you need to rearrange it in the future.
Thanks again for the solution!
Best regards:
Tibor

(1-4/4)

Project

General

Profile

Wt