On 13/11/2013 18:24, Quoth Roland Bock:
On 2013-11-13 00:48, Gavin Lambert wrote:
Provided that you do value insertion via actual parameters (either named or positional, depending on the target SQL dialect) and not via directly inserting constants into the SQL query itself, it should be fairly straightforward to extend to a dialect that requires explicit preparing; you'd just need to provide a syntax that allows you to cache a query object in a variable and then execute it multiple times with different actual values. (Using parameters is also preferred over direct insertion because it lessens the risk of script injection.)
That should be relatively easy to do (and if you encapsulate the query generation in a parametrized function it is already there).
I'm not sure I'm reading that last part right, but what I am talking about is very different from having a C++ function that accepts parameters and internally generates and runs the query. I haven't looked at your code enough to formulate a sensible example using its syntax, but if I can offer an analogy using Boost.Format's syntax (don't take it too literally): boost::format GenerateQuery() { return boost::format("select * from T where a=%1% and b=%2%"); // not intended to be fully safe SQL, it's just for show // also I'm not implying you should use strings to construct } std::string IndirectCall(const boost::format& query, int a, const std::string& b) { return (query % a % b).str(); // imagine this actually runs SQL } std::string DirectCall(int a, const std::string& b) { IndirectCall(GenerateQuery(), a, b); } The GenerateQuery method returns a "query object" that defines the action to be done but not the specific parameter values on which it acts. It is expensive to create (because this is where all the parsing happens) but is (fairly) cheap to copy, and reusable without copying. This is analogous to preparing an SQL query. Outside code could run GenerateQuery once (perhaps at program startup), cache the result somewhere, and then use it repeatedly via IndirectCall, thereby only paying the parsing cost once. (Boost.Format allows the same object to be reused once str() is invoked on it, or if explicitly cleared; or you can copy it prior to adding actual values to the copy.) This is analogous to running a prepared query with different actual parameters. (The db connector backend usually has specific separation between the SQL statement and the parameters -- you wouldn't actually insert them into the string the way that this example implies. This also typically means you don't have to escape values to prevent injection, as they're already known to be data.) The DirectCall method is the least performant case; it explicitly regenerates the query each time. This is analogous to running an unprepared query *or* actually inserting the real values directly into the SQL instead of using the db-connector's parameter mechanism. (But this is the most common way that SQL gets used, because it's "easier". I haven't looked too closely but I get the impression that this is the only way that sqlpp11 works at present too.) The key point being that for prepared statements to work (whether explicit as in Oracle or automatic as in SQL Server), you need to be able to define the SQL statement itself using only constants and placeholders where variables go, but not provide the actual values of the variables. Then when you actually go to execute the query you pass the constant SQL statement along with the collection of actual values to fill in the placeholders for this particular invocation. Different database backends support different syntaxes for placeholders; most support positional placeholders and a few offer named placeholders. (In your case I would recommend using only named placeholders on the user side, because it will be very unobvious to the user what the "real" positional order in the underlying SQL query will be. When interfacing to a connector for a DB that only supports positional placeholders you would have to convert them appropriately.)