[odb-users] Set Query timeout

[Boris Kolpackov]

Hi Fredrik,

Fredrik Strand <fredrik.strand at qliro.com> writes:

> I'm currently using a query object to call a stored procedure in SQL server.
> 
> So this approach is only usable with prepared queries?

Yes, that's correct. Note, however, that a non-prepared query is a
special case of the more general prepared query mechanism (i.e, it
behaves as if the query was prepared, executed, and then released).
So anything you can do with a non-prepared query you can do with a
prepared one.


> Would be really nice to have a generic way to set query timeouts
> since it's probably a must have in enterprise applications.

I agree, if there was (1) a natural place to specify it in the ODB
API and (2) uniform support for it across databases.

Specifically, regarding (1), for a simple (i.e., non-prepared) query
there is no notion of a (user-visible) statement. There is just a
query condition and a call to execute the query. Specifying the
timeout as part of the query condition doesn't feel right. So that
leaves us to passing the timeout to the query() call. Which brings
us to (2): what do we do for databases that don't support timeouts
(like SQLite). Also, timeout is just one "tunable" of a query. What
if tomorrow someone else wants something different? Do we add another
overload of query()?

On the other hand, with a prepared query, you get the notion of a
statement which you can resolve all the way down to the underlying
database handle and tune it in any way you want. And if you want
the simple query()-like interface, it is pretty easy to wrap into
a function, for example:

#include <odb/query.hxx>
#include <odb/result.hxx>

#include <odb/mssql/error.hxx>
#include <odb/mssql/statement.hxx>

template <typename T>
odb::result<T>
query_with_timeout (const odb::query<T>& q, 
                    odb::mssql::database& db,
                    unsigned short timeout)
{

  using query = odb::query<T>;
  using prep_query = odb::prepared_query<T>;  

  prep_query pq (db.prepare_query<T> ("temp-query", q));

  // Set timeout.
  //

  // ... as in the previous email.

  return pq.execute ();
}

Then, in client code, instead of:

result<person> r = db.query<person> (query::age > 18);

You do:

result<person> r = query_with_timeout<person> (query::age > 18, db, 10);

Boris