On 2006-10-04, Nicola Musatti <Nicola.Musatti@gmail.com> wrote:
Missing the TR2 deadline was unfortunate, but probably putting together a valid proposal deserved more time than was available, even with such an impressive starting point such as SOCI.
Indeed that's probably the case...
Lately I've been trying to put together a few ideas on how I think such a library should look like and I'd be happy to discuss them with anybody who is interested. To get the ball rolling, here is an example of how a program using my hypothetical Boost.Dbi (for DataBase Interface) library might look like:
Ok, I'll reply below with the SOCI equivalent of your example code, mainly because I don't think it differs too much in substance.
// Execute a parameterized query
long a; std::string b; boost::optional<double> c; // a value that may be null boost::dbi::null_value<long> d; // a value that is always null
// prepare the statement and bind input parameters, so as to // be able to execute it in a loop with different values
st.prepare("insert into t ( a, b, c, d ) values ( ?, ?, ?, ? )", a, b, c, d);
SOCI supports something very similar, with either positional binding like you show, or binding by name. sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)", use(personId), use(firstName), use(lastName); sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)", use(firstName, "fn"), use(lastName, "ln"), use(personId, "id"); Prepared statements are also supported, e.g. Statement st = (sql.prepare << "insert into numbers(value) values(:val)", use(valsIn)); for (int i = 0; i != 4; ++i) { st.execute(true); } One difference is your use of boost::optional to express nulls, versus SOCI's use of embedded-SQL-style indicator variables. SOCI currently doesn't use any code from boost so as to minimize dependencies, but I think boost::optional would be something we might want to leverage in a boostified version of SOCI.
// Execute a query that returns a result set
A Resultset object is something which we are adding to SOCI for the upcoming 2.2 releases...
st.exec("select * from t"); boost::dbi::result_set rs(st);
// Explore the structure of the result set
std::cout << "No. of columns: " << rs.column_count() << '\n'; for ( int i = 0; i < rs.column_count(); ++i ) { std::cout << rs[i].name() << "\t " << rs[i].type().name() << '(' << rs[i].size() << ", " << rs[i].precision() << ')'; if ( rs[i].nullable() ) std::cout << "\t null"; std::cout << '\n'; }
SOCI currenly has support for describing columns via the Row and ColumnProperties classes - this will be soon be accesible via Rowset iterators as well... Row r; sql << "select * from ex2", into(r);
// Columns may be accessed by position... std::cout << r[0].as<long>() << "\t ";
SOCI equivalent: r.get<long>(0)
std::cout << r["b"].as<std::string>() << "\t ";
SOCI equivalent: r.get<std::string>("b")
boost::optional<double> c = r[2].as<boost::optional<double> >(); if ( ! c ) std::cout << "(null)\t "; else std::cout << *c << "\t ";
SOCI has a way to specify a default to be used in case a value is Null: double c = r.get<double>(2, -1); Or you can test explicitly: eIndicator ind = r.indicator(2); if (ind == eNUll) { //etc...} In summary I think that our 2.2 release with iterator support will be very close to what you have sketched out here, with the exception of boost::optional integraton, which I think we can address in a boostified version of SOCI, post 2.2. Of course, there are also some additional features in SOCI that you didn't touch on here, e.g. support for custom types and basic O/R mapping... http://soci.sourceforge.net/doc/index.html Steve