[odb-users] Prepared statement feature

[Boris Kolpackov]

Hi Paul,

Again, thanks for the feedback. See my comments below.

Stath, Paul <pstath at axxcelera.com> writes:

> In your simple scenario, the prepared query would be compiled when the
> pq() object is constructed, and would be cleaned up when the pq object
> goes out of scope.

Yes. More precisely, the query is prepared by the db->prepare_query()
call.


> While it can be done, why not use the current obd::query<> with 
> query::_ref(age) in the simple scenario?

I am not sure what you mean by this. The simple scenario uses odb::query
to construct the query predicate.


> To make a prepared query operate over multiple transactions, the
> programmer would simply need to construct the prepared_query<> in
> such a way that it has an extended life-cycle. (i.e. member variable
> of a long-lived class, or as part of a shared_ptr.)

The API that I described does not prevent you from doing this, provided
you use the same connection (i.e., your database access is single-
threaded). With multi-threaded applications things are more complicated
since prepared statements (which is what the prepared_query<> will
really be, underneath) are connection-specific. I.e., you cannot
prepare a statement using one database connection and then execute
it on another.

So if a multi-threaded application wants to repeatedly execute the
same prepared statement from multiple treads, then it will have to
create a separate one for each connection.


> For processing a prepared query that contains by-reference arguments,
> the execute() method should accept the parameter structure (POD)
> constructed as an auto_ptr (or unique_ptr).

The by-reference parameters are already "bound" to the query during
preparation. It is not possible (well, not cheap, to be precisely)
to re-bind them for each execution.


> Doesn't your approach require the developer to provide their own
> thread safety to protect the data elements of the parameter POD?

No. In my approach each connection gets its own prepared query and
parameter object. And since each connection can only be accessed by
a single thread at a time, there is no need for access synchronization.

I guess I should have explained this whole connection/multi-threading
issue from the beginning (to me it sounds obvious since I know how
things work underneath). So let's say we have 10 threads executing
the same transaction in a loop, in parallel. Underneath there will
be a connection pool with 10 connections. When each thread starts
a transaction, it gets one of the connections from this pool. Now
it needs to get a prepared query that corresponds to this connection.
That's why a thread cannot store a prepared query that it created.
And that's where this whole caching business comes into play. A
thread looks up the prepared query in the per-connection cache,
creating one if it doesn't exist. And, in case of the by-ref
parameters, this cache will also store the user-provided parameter
object.

But, if the application is single-threaded (and thus only uses
one connection), then there is no need to use the cache or the
parameter object (as shown in the simple scenario). Instead,
the prepared query and the by-ref parameters can be stored as
local variable, members of some class, or even as global
variables.

Boris