Completely OO C++ SQL Wrapper?

Question

So I'm looking for a SQL wrapper for C++ that completely hides any textual SQL statements. I just can't seem to find any, I'm wondering why all the wrappers out there seem at some point to want you to write a textual SQL statement such as:

SELECT * FROM stock WHERE item = 'Hotdog Buns'

here's MySQL++ for example:

mysqlpp::Query query = conn.query("select * from stock where item = 'Hotdog Buns'");

The most obvious way to do this for me is to create a class that contains properties (columns) with each instance of that class being a row. So to do the above query I would do something like:

// Class defined something like this...
class stock_item : public sql::row
{
public:

   stock_item() : m_name( NULL ), m_amount( 0 ) {};
   ~stock_item() {};

   // Statically define the table
   static void CreateTable( void )
   {
      // Some C++ reflective mechanism
      sql::column( "name",   char[50] );
      sql::column( "amount", u32 );
   }

private:

   const char* m_name;
   u32         m_amount;
}

// Then a table defined like this
sql::table<stock_item> stock;

// Query function defined something like this...
stock GetHotDogBuns( const stock& shopStock )
{
   stock hotDogBuns = shopStock.Select( stock_item::Name(), "Hotdog Buns" );
   return hotDogBuns;
}

Now I'm no SQL expert and I haven't spent very long thinking about the above code but it just seems quite a logical way to deal with a database if your from a C++ background rather than having to be a database expert. What are the problems with this kind of approach?

Is there an open source library that allows you access to a database in a similar fashion?

EDIT The reason why I would like something like this is so that C++ programmers using our code don't have to learn SQL syntax and to provide a much more natural environment for them to code in. I've seen something like this in the SilverStripe CMS written in php.

Answer 1

RogueWave used to (maybe still do) have C++ database access library like this - using it was sheer hell. SQL is a very powerful language, and encapsulating all of it in C++ classes is a very difficult proposition. Also, you haven't made clear, to me at least, what your motivation for doing this is.

Answer 2

Check out hiberlite and litesql.

Answer 3

I have written my own library of Fields and Records.

The Field class has methods such as:

virtual std::string         get_sql_creation_text(void) const = 0;
virtual std::string         get_sql_insert_data(void) const = 0;
virtual std::string         get_sql_where_clause_equals(void) const = 0;
virtual std::string         get_value_as_string(void) const = 0;

My Record class is a container of pointers to strings. I build a SQL statements by iterating over the fields using the above methods.

So one of my queries looks like:

12:37:41: Selecting rows for iterating using:
SELECT  *
 FROM  Ing_Quantified
LEFT JOIN Ing_Processing USING (ID_Processing)
LEFT JOIN Ing_Process_Degrees USING (ID_Process_Degree)
LEFT JOIN Ing_Process_Methods USING (ID_Process_Method)
LEFT JOIN Ingredients USING (ID_Ingredient)
LEFT JOIN Ing_Titles USING (ID_Title)
LEFT JOIN Ing_Varieties USING (ID_Variety)
LEFT JOIN Ing_Categories USING (ID_Category)
LEFT JOIN Ing_Container_Sizes USING (ID_Container_Size)
LEFT JOIN Ing_Container_Types USING (ID_Container_Type)
LEFT JOIN Meas_Fundamentals USING (ID_Measurements)
LEFT JOIN Meas_Systems USING (ID_System)
LEFT JOIN Meas_Types USING (ID_Types)

WHERE (ID_Recipe = 1);

All this done while treating the records and fields as generic. The Fields return their names, which helps in creating WHERE clauses and in the USING clauses above.

I was using wxWidgets wxDbTable, but it doesn't easily support generic fields and records.