Example: Selecting records from a view using a sql_iterator


// Define an object to hold our row data -- used for joined table examples
class JoinExample
{
public:                    // tablename.columnname:
 int exampleInt;           // DB_EXAMPLE.INT_VALUE
 string exampleStr;        // DB_EXAMPLE.STRING_VALUE
 double exampleDouble;     // DB_EXAMPLE.DOUBLE_VALUE
 unsigned long sampleLong; // DB_SAMPLE.SAMPLE_LONG
 double extraDouble;       // DB_SAMPLE.EXTRA_FLOAT
};

// Here we define a custom parameter object for use with our JoinExample 
class JoinParamObj
{
public:
 int intValue;
 string strValue;
 int sampleInt;
 string sampleStr;
};

// BCA for JoinExample ... needed to store bindings between
// query fields and members in JoinExample objects
class BCAJoinExample
{
public:
 void operator()(BoundIOs &cols, JoinExample &row)
 {
  cols["INT_VALUE"]    >> row.exampleInt;
  cols["STRING_VALUE"] >> row.exampleStr;
  cols["DOUBLE_VALUE"] >> row.exampleDouble;
  cols["SAMPLE_LONG"]  >> row.sampleLong;
  cols["EXTRA_FLOAT"]  >> row.extraDouble;
 }
};

// BPA for JoinParamObj ... set SQL Query parameters from object
class BPAJoinParamObj
{
public:
 void operator()(BoundIOs &params, JoinParamObj &paramObj)
 {
  params[0] << paramObj.intValue;
  params[1] << paramObj.strValue;
  params[2] << paramObj.sampleInt;
  params[3] << paramObj.sampleStr;
 }
};

// Read JoinExample objects from the database using a query that
// joins the DB_EXAMPLE and DB_SAMPLE tables
vector ReadJoinedData()
{
 vector results;

 // construct view
 // note here that we use a custom parameter class for JoinExample
 // rather than DefaultParamObj

 DBView<JoinExample, ParamObj> view("SELECT INT_VALUE, STRING_VALUE, DOUBLE_VALUE, "
    "SAMPLE_LONG, EXTRA_FLOAT FROM DB_EXAMPLE, DB_SAMPLE WHERE (INT_VALUE = (?) AND STRING_VALUE = (?)) AND "
    "(SAMPLE_INT = (?) OR SAMPLE_STR = (?)) "
    "ORDER BY SAMPLE_LONG", BCAJoinExample(), "",
    BPAJoinParamObj());


 // loop through query results and add them to our vector
 DBView<JoinExample, JoinParamObj>::sql_iterator read_it  = view.begin();

 // assign paramteter values as represented by the (?) placeholders
 // in the where clause for our view
 read_it.Params().intValue = 3;
 read_it.Params().strValue = "Join Example";
 read_it.Params().sampleInt = 1;
 read_it.Params().sampleStr = "Joined Tables";

 for ( ; read_it != view.end(); ++read_it)
 { 
  results.push_back(*read_it);
 }

 return results;
}