[odb-users] Executing native selects for no mapped type

[Roland Kloeters]

Hi Boris,

of course there is a way I could use.

I would prefer to use

#pragma db view query("SELECT nextval('sequence')")
struct sequence_value
{
  unsigned long long value;
};
 
result r (db->query<sequence_value> ());

Or you could specify the actual sequence name at the query execution
time:

#pragma db view
struct sequence_value
{
  unsigned long long value;
};

string seq_name = ...;

result r (
  db->query<sequence_value> (
    "SELECT nextval('" + query::_val (seq_name) + "')");

Unfortunately my ODB Compiler is not willing to know the pragma "view".

I get an error like "Sequence.h:16:19: error: unknown db pragma 'view'"

What now?

Kind regards
Roland


Am Mittwoch, den 17.08.2011, 12:51 +0200 schrieb Boris Kolpackov:
> Hi Roland,
> 
> Roland Kloeters <roland.kloeters at xcom.de> writes:
> 
> > As I cannot set the value by default using #pragma db id auto I have to
> > select the value from the sequence directly.
> > 
> > I am using postgresql as RDMS so my select would be: "select
> > nextval('sequence')".
> > 
> > [...]
> > 
> > Is there any chance of executing native select statements and retrieving
> > the results?
> 
> One way to do it would be to get the native Postgres connection handle
> from odb::pgsql::connection and execute the query using the low-level
> C API (libpq). This is not the most convenient way but it is always an
> option.
> 
> Also we have been thinking about supporting what we call 'view' objects.
> A view is a read-only object that can only be loaded using a query. They
> could be useful for retrieving only a porting of an object, supporting
> ad-hoc table joins, or performing stored procedure calls. Here is an
> example:
> 
> #pragma db view query("SELECT person.name, employer.name FROM person LEFT JOIN employer ON person.employer = employer.id")
> struct name_and_employer
> {
>   string name;
>   string employer;
> };
> 
> typedef query<name_and_employer> query;
> typedef result<name_and_employer> result;
> 
> result r (
>   db->query<name_and_employer> (
>     "WHERE person.age < " + query::_val(30));
> 
> Your case would have been handled like this:
> 
> #pragma db view query("SELECT nextval('sequence')")
> struct sequence_value
> {
>   unsigned long long value;
> };
> 
> result r (db->query<sequence_value> ());
> 
> Or you could specify the actual sequence name at the query execution time:
> 
> #pragma db view
> struct sequence_value
> {
>   unsigned long long value;
> };
> 
> string seq_name = ...;
> 
> result r (
>   db->query<sequence_value> (
>     "SELECT nextval('" + query::_val (seq_name) + "')");
> 
> Do you think something like this will work well for your use case?
> 
> In fact, it is possible to kind of emulate views using the existing
> objects functionality. First we define the object (it must contain
> the dummy object id):
> 
> #pragma db object
> struct sequence_value
> {
>   #pragma db id
>   unsigned int dummy;
> 
>   unsigned long long value;
> };
> 
> Then the transaction that gets the next value would look like this
> (PostgreSQL-specific code):
> 
> #include <odb/pgsql/result.hxx>
> #include <odb/pgsql/connection.hxx>
> #include <odb/pgsql/transaction-impl.hxx>
> 
> namespace pgsql = odb::pgsql;
> 
> transaction t (db->begin ());
> 
> odb::result<sequence_value> r;
> 
> {
>   pgsql::connection& conn (pgsql::transaction::current ().connection ());
> 
>   odb::query<sequence_value> q;
> 
>   odb::details::shared_ptr<pgsql::select_statement> st (
>     new (odb::details::shared) pgsql::select_statement (
>       conn,
>       "sequence_value_query" // Statement name.
>       "select 0, nextval('sequence')",
>       q.parameter_types (),
>       q.parameter_count (),
>       q.parameters_binding (),
>       conn.statement_cache ().find<sequence_value> ().out_image_binding ()));
> 
>   st->execute ();
>   st->deallocate ();
> 
>   odb::details::shared_ptr<odb::result_impl<sequence_value> > ri (
>     new (odb::details::shared) pgsql::result_impl<sequence_value> (
>       q, st, conn.statement_cache ().find<sequence_value> ()));
> 
>   r = odb::result<sequence_value> (ri);
> }
> 
> // Iterate over the query result as usual. For example, r->begin ()->value
> // is the returned sequence value.
> 
> t.commit ();
> 
> This could be a temporary solution until views are supported natively by
> ODB.
> 
> Boris