Boost logo

Boost :

Subject: [boost] [rdb] 0.2.00 - bind parameter and result + dynamic sql
From: Jean-Louis Leroy (jl_at_[hidden])
Date: 2009-11-05 15:09:39


I have uploaded version 0.2.01 to the vault. It contains two new major
features.

The first is the ability to bind parameters to variables allocated
outside the prepared statement, e.g.:

  // in the following examples the database contains:
  // id first_name name age
  // 1 Homer Simpson 37
  // 2 Marge Simpson 34

  BOOST_FIXTURE_TEST_CASE(prepared_select_bind_integer_param,
springfield_fixture) {
    person p;
    BOOST_AUTO(st, db.prepare(select(p.first_name).from(p).where(p.id ==
_)));
   
    integer id_param;
    st.bind_parameters(id_param);
   
    id_param = 1;
    BOOST_RDB_CHECK_SELECT_RESULTS(st.execute(), "((Homer))");
   
    id_param = 2;
    BOOST_RDB_CHECK_SELECT_RESULTS(st.execute(), "((Marge))");
  }

The possibility of passing parameters to execute() remains but this is
more efficient, as execute(params) binds the parameters each time it is
called.

Likewise, it is now possible to bind results, e.g.:

  BOOST_FIXTURE_TEST_CASE(prepared_select_bind_results,
springfield_fixture) {
    person p;
    BOOST_AUTO(st, db.prepare(select(p.id, p.first_name).from(p)));
    integer id;
    varchar<30> first_name;
    st.bind_results(id, first_name);
    BOOST_AUTO(results, st.execute());

    results.fetch();
    BOOST_CHECK(!id.is_null());
    BOOST_CHECK_EQUAL(id.value(), 1);
    BOOST_CHECK(!first_name.is_null());
    BOOST_CHECK_EQUAL(string(first_name), "Homer");

    results.fetch();
    BOOST_CHECK(!id.is_null());
    BOOST_CHECK_EQUAL(id.value(), 2);
    BOOST_CHECK(!first_name.is_null());
    BOOST_CHECK_EQUAL(string(first_name), "Marge");
  }

The second feature is dynamic creation of statements. A fully static
system doesn't make it possible e.g. to create a search form, where you
enter (optional) values and the program returns a list of matching values.

Dynamic statement parts (lists of expressions, columns, tables) are
created via the make_dynamic() function. It returns a "stable" type,
which does not incorporate the full type information for the
encapsulated sql code. This makes it possible to create dynamic
collections of sql constructs and pass them to a statement. Static and
dynamic parts can be miexed freely.

For example:

  BOOST_FIXTURE_TEST_CASE(prepared_select_bind_dynamic_integer_param,
springfield_fixture) {

    person p;

    dynamic_boolean predicate = make_dynamic(p.id == _);
   
    BOOST_AUTO(st,
db.prepare(select(p.first_name).from(p).where(predicate)));
   
    integer id_param;
    dynamic_values params;
    params.push_back(make_dynamic(id_param));
    st.bind_parameters(params);
   
    id_param = 1;
    BOOST_RDB_CHECK_SELECT_RESULTS(st.execute(), "((Homer))");
   
    id_param = 2;
    BOOST_RDB_CHECK_SELECT_RESULTS(st.execute(), "((Marge))");
  }

Here a dynamic boolean expression is used in the where clause. It
contains a parameter so it is necessary to pass a collection of dynamic
values when executing the statement.

Here is a more complex example:

  BOOST_FIXTURE_TEST_CASE(prepared_select_dynamic_tables,
springfield_fixture) {

    person p("husband");
    person spouse("wife");
    partner::qualified link;
   
    dynamic_expressions exprs;
    exprs.push_back(make_dynamic(spouse.first_name));
   
    dynamic_tables tables;
    tables.push_back(make_dynamic(spouse));
    tables.push_back(make_dynamic(link));
   
    dynamic_boolean predicate = make_dynamic(p.id == link.husband &&
link.wife == spouse.id);
   
    BOOST_AUTO(st, db.prepare(select(p.first_name, exprs).from(p,
tables).where(predicate)));
   
    varchar<30> him;

    dynamic_values results;
    varchar<30> her;
    results.push_back(make_dynamic(her));

    st.bind_results(him, results);
   
    BOOST_AUTO(cursor, st.execute());

    cursor.fetch();
    BOOST_CHECK(!him.is_null());
    BOOST_CHECK_EQUAL(string(him), "Homer");
    BOOST_CHECK(!her.is_null());
    BOOST_CHECK_EQUAL(string(her), "Marge");
  }

Here the projection is partly dynamic, and so is the table list and the
where clause. Static entities are checked at compile-time as usual, and
dynamic entities at run-time - and thus may fail with an exception.
However, the system is designed to (attempt to) minimize the risk of
type mismatch in the dynamic parts - no casts are required in client
code (nor in the implementation ftm).

The test suite contains several other examples of dynamic statements
(see test_odbc_dynamic.cpp).

At this point I feel that all the major features in the scope of this
project have been addressed. Okay a lot is still missing - e.g. more
types (currently only integer and varchar are supported), constraints,
count() and such functions, group/order by... - but all these should be
easy to implement in the existing framework. And the thing needs
cleaning up and documentation, plus testing on Unix.

J-L


Boost list run by bdawes at acm.org, gregod at cs.rpi.edu, cpdaniel at pacbell.net, john at johnmaddock.co.uk