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

From: Nicola Musatti (Nicola.Musatti_at_[hidden])
Date: 2006-10-04 17:39:12

Jeff Garland wrote:
> Nicola Musatti wrote:
>> Hallo,
>> a few months ago there was a discussion on how a database interface library
>> would be a welcome addition to TR2 and the SOCI developers suggested that SOCI
>> could be a valid candidate for that role
>> (http://article.gmane.org/gmane.comp.lib.boost.devel/140816). They also
>> mentioned their intention to make SOCI ready for submission to Boost.
>>
>> Was any progress made on this issue? Is there any interest in reopening the
>> discussion on such a library, SOCI submission notwithstanding?
> 
> I'm not sure what there is to discuss.  It didn't happen :-( And TR2 
> submission time is over.  I still want to see SOCI or the equivalent in Boost, 
> but the authors have to bring it forward.  Last I knew they were waiting to 
> have an ODBC driver first.  I didn't see the need for that, but they thought 
> it was essential...

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.

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:

#include <iostream>
#include <ostream>

#include <boost/optional.hpp>
#include <boost/none.hpp>

#include <boost/dbi.hpp>

int main()
{
     try
     {

// Connect to a database, specifying the underlying technology. Unless
// specific SQL syntax is used, this should be the only place where the
// actual DBMS product / technology is evident

         boost::dbi::connection conn("ODBC");
         conn.connect("PVAL", "dba", "sql");

// Execute a couple of simple queries

         boost::dbi::statement st(conn);
         try
         {
             st.exec("drop table t");
             conn.commit();
         }
         catch ( boost::dbi::error & e )
         {
             std::cerr << e.what() << '\n';
         }
         st.exec("create table t ( a int, b varchar(10), c double, "
                "d int )");

// 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);

         char * nums[] = { "zero", "one", "two", "three", "four", "five",
                "six", "seven", "eight", "nine" };
         for ( a = 0; a < 10; ++a )
         {
             b = nums[a];
             if ( a % 2 )
                 c = a * 1.1;
             else
                 c = boost::none;
             st.exec();
         }
         conn.commit();

// Execute a query that returns a result set

         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';
         }

         // Print the actual results

         std::cout << "\nRows:\n";
         for ( boost::dbi::result_set::iterator i = rs.begin();
                i != rs.end(); ++i )
         {
             boost::dbi::row const & r = *i;

             // Columns may be accessed by position...

             std::cout << r[0].as<long>() << "\t ";

             // ...or by name

             std::cout << r["b"].as<std::string>() << "\t ";
             boost::optional<double> c =
                    r[2].as<boost::optional<double> >();
             if ( ! c )
                 std::cout << "(null)\t ";
             else
                 std::cout << *c << "\t ";
             boost::optional<long> d =
                    r["d"].as<boost::optional<long> >();
             if ( ! d )
                 std::cout << "(null)\n";
             else
                 std::cout << *d << '\n';
         }
         conn.disconnect();
     }
     catch ( boost::dbi::error & e )
     {
         std::cerr << e.what() << '\n';
         std::cout << "Failure!\n";
         return 1;
     }
     std::cout << "Success!\n";
}

Cheers,
Nicola Musatti