Re: [boost] Interest in an ODBC library?

From: Caleb Epstein (caleb.epstein_at_[hidden])
Date: 2005-08-18 10:22:07

On 8/18/05, Caleb Epstein <caleb.epstein_at_[hidden]> wrote:

> On 8/18/05, Brock Peabody <brock.peabody_at_[hidden]> wrote:
> 
> > We could make abstract_row_set::get return an optional<field> instead of
> > a field.  Then row::get_field could return an optional as well.
> >
> > I'd suggest that row::get, however, continue its current behavior which
> > is to return a default constructed value in the presence of null fields.

I have a couple of comments/questions about your implementation so
far, which is quite nice:

* Iterators.  I don't think these should be random access.  Of the
database APIs I have experience with (Sybase, SQLite), none allows you
to navigate to an arbitrary row in a result set.  You must process all
results in sequence.

* The transaction begin/commit/rollback methods should be moved to the
abstract_database class as virtual methods.  There is just too much
variation in the way transactions are handled to implement this at the
top level.  For example, the syntax "START TRANSACTION" is not
portable (isn't it just "BEGIN"?), and I believe that in general one
must match each "BEGIN" with an explicit  "COMMIT" or "ROLLBACK"; its
not enough to just "COMMIT" or "ROLLBACK" once the nesting depth hits
1 unless you name your transactions.  Additionally, some
implementations (e.g. SQLite) don't support nested transactions, so
the underlying impl ought to be able to throw in the case that the
user requests such an operation.

* I'd recommend a scoped_lock-like class for the transaction user
interface.  For the same reasons that it is not advisable to manually
lock/unlock mutexes, it is not adviseable to manually
begin/commit/rollback transactions.  I'd suggest something like

class transaction : boost::noncopyable {
    database& db_;
    bool committed_;

public:
    transaction (database& d) : db_ (d) { db_.start_transaction (); }
    ~transaction () { if (!committed_) db_.rollback_transaction (); }
    void commit () { db_.commit_transaction (); committed_ = true; }
};

* Prepared Statements.  Sending data to a database is frequently done
by preparing an SQL statement with placeholder arguments (e.g. "INSERT
INTO foo ( col1, col2, ... ) VALUES ( ?, ?, ... )" and then binding
program variables to those arguments and executing the prepared
statement.  Do you think it would be good to add this ability?

* Binding.  Some vendors provide APIs whereby query results can be
directly bound to program variables.  The general workflow would be
something like:

    double price;
    std::string name;

    row_set results = database.execute (query);

    results.bind ("name", &name);
    results.bind ("price", &price);

    for (row_set::iterator i = results.begin (), e = results.end ();
          i != e && results.fetch (); ++i) {
        std::cout << "name=" << name << ", price=" << price << std::endl;
    }

This saves the variant<> conversion overhead and can be extended to
include user-defined types and conversions.  Would you be open to
including this in the implementation?

-- 
Caleb Epstein
caleb dot epstein at gmail dot com