Add multiple parameters to IN clause in SQLAPI c++

Question

I am using SQLAPI to connect to a SQL-Server database from a C++ code. I need to execute a simple select statement with a IN clause, where the string values in the clause is received as a vector of strings. We want to use parameterised queries so did something like this:

std::string getSQLQuery(std::vector<std::string> ids){
    std::stringstream sql;
    sql << "SELECT ID, Name, DOB FROM Employees WHERE ID IN (";
    unsigned int counter = 0;
    for each (auto id in ids)
    {
        sql << ":" << counter + 1;
        if (++counter < ids.size())
        {
            sql << ",";
        }
    }
    sql << ")";
    return sql.str();
}

And then,

    int param_counter = 0;
    for each (auto id in ids) {
        command.Param(++param_counter).setAsString() = id.c_str();
    }

Can anyone please suggest a better way of doing it?

Answer 1

Well, I don't want to undercut your basic question, which I take to be "What's a good way to do this?", but you have some basic syntax problems with your C++. Inferring your intention from the buggy code above, I gather that the goal is to create a command synthesizer with a common SQL select query from an arbitrary set of input id's. Cool. I don't think there's any up-side to first creating a synthesized template command with insertion points and then using their parameter replacement scheme. Might as well do the synthesis all at once. A compile-time-correct version would look something like this (with a few tweaks to make it more reusable) -

std::string getSQLQuery(
    const std::string& columns
  , const std::string& table
  , const std::vector<std::string>& ids
){
  // Check for empty strings/arrays here - leads to illegal SQL,
  // so error out or except on empty ids, columns, or "table".

  std::stringstream sql("SELECT ", std::ios_base::out | std::ios_base::ate);
  sql << columns << " FROM " << table << " WHERE ID IN ( ";

  // Trailing commas are not allowed in SQL, which makes synthesis a little trickier.
  // We checked for empty inputs earlier, so we have at least one ID.
  auto iter = ids.begin();
  sql << *iter++;  // add the first (and possibly only) ID
  for (; iter != ids.end(); ++iter) {  // add the rest (if any) with commas
    sql << ", " << *iter;
  }
  sql << " )";   // <- should this be " );"? Or does SQLAPI++ deal with that?
  return sql.str();  // There's a shrink-to-fit method you may want to use here.
}

Now you can just do something like -

std::vector<std::string> id_array{ "1", "50", "aardvark" };
SACommand basic_command(connection, getSQLQuery("ID, Name, DOB", "Employees", id_array));
basic_command.Execute();

This skips the second substitution phase entirely. The SQLAPI++ parameter substitution is intended for queries with a much more rigid template, but you're doing something more dynamic. You can imagine extending this further with input arrays of columns as well, to avoid syntax errors in the table list (like we do in the id list). Also, since id's are often numerical, you could make the id array std::vector<std::uint64_t> or whatever fits your specific application. In fact, you can handle both cases with the same body of code by making the signature -

template<typename T> std::string getSQLQuery(
    const std::string& columns
  , const std::string& table
  , const std::vector<T>& ids
){
   ... // rest of the implementation is the same
}

I'm a new contributor, but a long-time user, so just a word about questions. When you ask a question like, "Can anyone please suggest a better way of doing it?", the answer is always "Yes.". There are a lot of smart people out there and an infinity of solutions to every high-level problem. In the future, you want to state the problem you're trying to solve (not hard to figure out in this case), and if you show a solution that was tried and failed, you should give specifics about how it failed. In the case of the code you put forth, the most obvious reason it failed is that it is syntactically wrong - the compiler would not accept it. "for each" is from some other languages. In C++, it's something like "for (auto id : ids)". But if you were just trying to show some kind of pseudo-code, it suggests that you don't really know if your existing approach works, because it hasn't been tried. And even in that case, you should say what you don't like about the presented solution (like the unnecessary second step of using the SQLAPI++ substitution scheme), and ask specifically if someone can think of a way to remove that. I'm a talker, and I would have given the same response, but for future reference, try to avoid a question that comes down to, "The following code is broken. Someone fix it for me." Just FWIW.