[odb-users] Prepared statements and queries

[Boris Kolpackov]

Hi Chris,

Chris Richards <chris.richards at yellowfeather.co.uk> writes:

> Looking at the MySQL logs I can see that the update and insert
> statements are using prepared statements but the select statements are
> not? The select statements are pretty simple, from a single table and
> a where clause on a single column.
> 
> Does odb use prepared statements for queries?

Yes, ODB uses prepared statements for queries (i.e., database::query()
calls), however, these statements are not cached in the connection,
unlike statements corresponding to the persist(), load(), update(),
and erase() calls.

The reason why we don't cache query statements is because they depend
on the passed query condition (and if the query condition has any by-
reference parameters, also on such parameters). So the plan here is
to instead allow the user to prepare and cache query statements
(together with the query condition and any by-reference parameters)
if and when necessary. The difficult part here is to figure out a
nice way to capture all these parts in an object that can be stored
in the connection cache (in a sense this is similar to a closure).

Let's say we have this query which we would like to reuse (based
on the 'hello' example):

typedef odb::query<person> query;
typedef odb::result<person> result;

unsigned short age;
query cond (query::age < query::_ref (age));

A transaction that tries to re-use a cached statement for this query
if available and prepares and caches a news statement if not, could
look like this (function names are not finalized yet):

// Object to capture the parameters, query condition, and the prepared
// statement.
//
struct age_query
{
  unsigned short age;
  query cond;
  statement_ptr stmt;
};

transaction t (db->begin ());

connection& conn (t.connection ());

age_query* q (conn.lookup<age_query> ("age-query"));

if (q == 0)
{
  q = &conn.cache ("age-query", age_query ());
  q->cond = query (query::age < query::_ref (q->age));
  q->stmt = db->query_prepare<person> (q->cond);
}

q->age = 30;
result r (db->query<person> (q->stmt));

...

t.commit ();

Is this something that you are looking for?

Boris