// Using dynamic parameters to join two tables


// For purposes of illustration we introduce a table called DB_SAMPLE 

SQL> desc db_sample;
Name				Type
------------------------------- -------- 
SAMPLE_LONG			LONG INTEGER
SAMPLE_INT			INTEGER
SAMPLE_STR			STRING
EXTRA_FLOAT			FLOAT

class JoinExample
{
private:
	                                //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

friend class BCAJoinExample;
friend class BPAJoinParamObj;
};

// 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 &boundIOs, 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<JoinExample> ReadJoinedData()
{
	vector<JoinExample> results;

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

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

	// loop through query results and add them to our vector
	DBView<JoinExample, JoinParamObj>::select_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;
}