[odb-users] Set Query timeout

[Fredrik Strand]

Hi,

I'm currently using a query object to call a stored procedure in SQL server.

So this approach is only usable with prepared queries? Would be really nice to have a generic way to set query timeouts since it's probably a must have in enterprise applications.

/Fredrik

-----Original Message-----
From: Boris Kolpackov [mailto:boris at codesynthesis.com] 
Sent: Monday, September 14, 2015 4:11 PM
To: Fredrik Strand
Cc: odb-users at codesynthesis.com
Subject: Re: [odb-users] Set Query timeout

Hi Fredrik,

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

> It's possible to set query timeout using raw odbc calls. However I 
> don't find a way to set query timeout using ODB. Nor do I see any 
> support for this in the source code.

I assume you are interesting in the SQL_ATTR_QUERY_TIMEOUT statement attribute. While ODB normally hides underlying database statements, you can get hold of it for a prepared query (Section 4.5 in the manual). Here is an outline based on the example from this section:

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

using query = odb::query<person>;
using prep_query = odb::prepared_query<person>; using result = odb::result<person>;

transaction t (db.begin ());

prep_query pq (db.prepare_query<person> ("age-query", query::age > 18));

// Set timeout.
//
{
  auto& s (static_cast<odb::mssql::statement&> (pq.statement ()));

  SQLHSTMT h (s.handle ());
  SQLULEN t (10); // In seconds.
  SQLRETURN r (SQLSetStmtAttr (h, 
                               SQL_ATTR_QUERY_TIMEOUT, 
                               (SQLPOINTER) t,
                               nullptr));

  // Translate errors to exceptions using ODB mechanisms.
  //
  if (!SQL_SUCCEEDED (r))
    odb::mssql::translate_error (r, s.connection (), s); }

// Run the query.
//
result r (pq.execute ());
...

t.commit ();

Let me know if there are any problems with this approach.

Boris