Re: [boost] Boost.SQL, SOCI and beyond

From: Steve Hutton (shutton_at_[hidden])
Date: 2006-10-04 21:32:33

On 2006-10-04, Nicola Musatti <Nicola.Musatti_at_[hidden]> 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