Re: [boost] SQL library with full embedded SQL-syntax

From: Phil Endecott (spam_from_boost_dev_at_[hidden])
Date: 2007-03-15 10:26:10

> Choosing C++ for a projects where databases are involved is probably 
> rare

Well it's what I spend quite a lot of my time doing, and I find it a 
good combination.  But then I often seem to be doing things differently 
from everyone else...

In my case I have written a C++ binding for PostgreSQL's libpq which 
allows me to define queries as functors:

Query<string,int> insert_thing(db, "insert into things(name,qty) values ($1,$2)");
SingletonQuery<int, string> count_things(db, "select sum(qty) from 
things where name=$1");

insert_thing("table",1);
insert_thing("chair",4);
int n_pens = count_things("pen");

Note the template type parameters in the query declarations.  These 
give the C++ types corresponding to the $n parameter placeholders in 
the SQL, and are mapped to the "oids" (in postgresql-speak) of the 
corresponding SQL types.  The queries are prepared, and when they are 
run the parameters are passed to the database server in binary format.  
You get compile-time checking that the types in the query invocations 
match those in the declarations and run-time checking that the backend 
is happy with those types.

I like this approach as it gives about as much type safety as is 
possible and lets you do database accesses with essentially the same 
syntax that you would use to access C++ data.  I prefer this to the way 
that SOCI is used, where you always know that you're using SQL (though 
SOCI no doubt has other features that my library is missing).

There is more detail here:

   http://svn.chezphil.org/libpbe/trunk/doc/Database

I would be happy to describe my experiences further if anyone is 
considering adding something along these lines to Boost.  My code is 
currently GPL but I am flexible about that.

Regards,

Phil.