example.cpp

The goal of this library is to make ODBC recordsets look just like an STL container. As a user, you

// Example client code for a single table
// Initial: 9/5/2000 - MG
// Reviewed: 11/12/2000 - CJ
// Edited: 12/20/2000 - MG - added namespace support

#include "Example.h"
using namespace dtl;


// set parameters function for Example ... used by IndexedDBView<Example>
void SetParamsExample(DefaultParamObj<Example> &params)
{
	// set parameter values
	params.lowIntValue = 2;
	params.highIntValue = 8;
	params.strValue = "Example";
	
	TIMESTAMP_STRUCT paramDate = {2000, 1, 1, 0, 0, 0, 0};
	params.dateValue = paramDate;
}

// read the contents of a single table and return a vector the resulting rows
vector<Example> SimpleRead() {
	vector<Example> results;
	DBView<Example> view("DB_EXAMPLE");

	back_insert_iterator<vector<Example> > i(results);
	copy(view.begin(), view.end(), i);
	return results;
}

// Read the contents of a table and print the resulting rows
void SimpleDynamicRead() {

	// Our query will be "SELECT * FROM DB_EXAMPLE"
	DynamicDBView<> view("DB_EXAMPLE", "*");

	// NOTE: We need to construct r from the view itself since we
	// don't know what fields the table will contain.
	// We therefore make a call to the DataObj() function to have the
	// table return us a template row with the correct number of fields
	// and field types.
	// We use this construction since we can't be guaranteed that the table
	// is non-empty & we want to still display column names in this case.
	variant_row s(view.DataObj());

	// Print out the column names
	vector<string> colNames = s.GetNames();
	for (vector<string>::iterator name_it = colNames.begin(); name_it != colNames.end(); name_it++)
	{
		cout << (*name_it) << " ";
	}
	cout << endl;

	// Print out all rows and columns from our query
	DynamicDBView<>::select_iterator print_it;
	for (print_it = view.begin(); print_it != view.end(); print_it++)
	{
		variant_row r = *print_it;
#if 0
		for (size_t i = 0; i < r.size(); i++)
		{
			cout << r[i] << " ";
		}
#endif
		cout << r << endl;
	}
}

// Insert two rows into a table with unknown fields
void SimpleDynamicWrite() {
	TIMESTAMP_STRUCT paramDate = {2012, 12, 23, 0, 0, 0, 0}; 
	// Mayan DOOMSDAY! End of the Mayan 5126 year long calendar cycle starting from May 1, 3094 B.C.
	// Date is 13.13.13.0.0.0.0  4 Ahaw, 3 K'ank'in
	
	DynamicDBView<> view("DB_EXAMPLE", "*");

	DynamicDBView<>::insert_iterator write_it;
	write_it = view;

	// NOTE: We need to construct r from the view itself since we
	// don't know what fields the table will contain.
	// We therefore make a call to the DataObj() function to have the
	// table return us a template row with the correct number of fields
	// and field types.
	variant_row r(view.DataObj());

	// Prepare the number of the beast!
	// Set all fields to the value 6,
	// except for the last column which is a date and cannot
	// currently accept numeric values

  size_t i = 0;

	for (i = 0; i < r.size()-1; i++)
	{
		 r[i] = 6;
	}
	r[i] = paramDate;  // set the Doomsdate

	// insert the number
	*write_it = r;
	write_it++;

	// Prepare the number of angels who stand before
	// the throne of God!
	// Set all fields to the value 7,
	// except for the last column which is a date and cannot
	// currently accept numeric values
	for (i = 0; i < r.size()-1; i++)
	{
		 r[i] = 7;
	}
	r[i] = paramDate;

	// insert the number
	*write_it = r;
	write_it++;
}


// read some Example objects from the database and return a vector of
// the results
vector<Example> ReadData()
{
	vector<Example> results;

	// construct view
	// DBView<Example> is actually DBView<Example, 
    // DefaultParamObj<Example> > thanks to the default argument to the DBView
	// template

	DBView<Example>
		view("DB_EXAMPLE", BCAExampleObj(),
		"WHERE INT_VALUE BETWEEN (?) AND (?) AND "
		"STRING_VALUE = (?) OR EXAMPLE_DATE < (?) ORDER BY EXAMPLE_LONG",
		BPAExampleObj());

	// loop through query results and add them to our vector
	// in this loop, read_it.GetLastCount() records read from DB

	DBView<Example>::select_iterator read_it = view.begin();

	// set parameter values for the WHERE clause in our SQL query
	read_it.Params().lowIntValue = 2;
	read_it.Params().highIntValue = 8;
	read_it.Params().strValue = "Example";
	
	TIMESTAMP_STRUCT paramDate = {2000, 1, 1, 0, 0, 0, 0};
	read_it.Params().dateValue = paramDate;

	for ( ; read_it != view.end();  read_it++)
	{
		// note that the read_iterator::GetLastCount()  is incremented in operator++()
		// remember that the record is fetched and thus the count incremented
		// before operator*() is applied to the read_iterator

		cout << "Reading element #" << read_it.GetLastCount() << endl;
		results.push_back(*read_it);

		cout << "read_it->exampleInt = " << read_it->exampleInt << endl;
		cout << "read_it->exampleStr = " << read_it->exampleStr << endl;
		
	}
	
	return results;
}

// print out Example objects directly from a DBView
void PrintExamplesFromView()
{
    DBView<Example> view("DB_EXAMPLE");
	copy(view.begin(), view.end(), ostream_iterator<Example>(cout, "\n"));
}

// write some Example objects to the database
void WriteData(const vector<Example> &examples)
{
	// construct view ... note: no BPA as there is no postfix clause
	DBView<Example> view("DB_EXAMPLE",
		BCAExampleObj());

	// loop through vector and write Example objects  to DB
	// write_it.GetLastCount() records written in loop

	DBView<Example>::insert_iterator write_it = view;

	// note initialization of query parameters done in BPA for BoundParams
	// and in operator*() for BoundCols
	for (vector<Example>::const_iterator ex_it = examples.begin(); ex_it != examples.end(); ex_it++, write_it++)
	{
		*write_it = *ex_it;
		
	    // note that write_iterator::GetLastCount() is the # of records actually written 
	    // so far ... which is not registered until after operator++()
	    // hence the need to add 1 to the result of write_it.GetLastCount()
	    cout << "Writing element #" << write_it.GetLastCount() + 1<< endl;
	}


}

// update Example object (with new values)  meeting a query in the database
void UpdateData()
{	
	// construct view
	DBView<Example> view("DB_EXAMPLE", 
		BCAExampleObj(), "WHERE INT_VALUE BETWEEN (?) AND (?) AND "
		"STRING_VALUE = (?) OR EXAMPLE_DATE = (?)", BPAExampleObj());
		
	// build an updater for the view
	DBView<Example>::update_iterator 
		exampleUpdater;
	
	exampleUpdater = view;

	// set data fields we want to update to their desired values
	// exampleStr to "Updated" and sampleLong to 0
	Example updateMe;

	updateMe.exampleStr = "Updated";
	updateMe.exampleLong = 25;

	TIMESTAMP_STRUCT today = {2000, 9, 29, 0, 0, 0, 0};

	updateMe = Example(2121, "Dereferenced", 99.99, 25, today);

	*exampleUpdater = updateMe;
    
	// now set the parameters indicating which rows we want the update applied
	exampleUpdater.Params().lowIntValue = 5;
	exampleUpdater.Params().highIntValue = 13;
	exampleUpdater.Params().strValue = "Find Me";

	TIMESTAMP_STRUCT paramDate = {1999, 11, 11, 0, 0, 0, 0};
	exampleUpdater.Params().dateValue = paramDate;

	// execute the update
	exampleUpdater++;

	cout << exampleUpdater.GetLastCount() << " rows updated!" << endl;

	// now can perform other updates using the same updater object
	// make sure to put in your new values for both the data and parameter values
	// for the update
	// set data fields we want to update to their desired values
	// exampleStr to "Second Update" and exampleLong to 66
	TIMESTAMP_STRUCT tomorrow = {2000, 9, 30, 0, 0, 0, 0};
	updateMe = Example(2222, "Second Update", 0.11111, 66, tomorrow);
	updateMe.exampleLong = 66;

	*exampleUpdater = updateMe;

	// now set the parameters indicating which rows we want the update applied
	exampleUpdater.Params().lowIntValue = 21;
	exampleUpdater.Params().highIntValue = 30;
	exampleUpdater.Params().strValue = "To find";

	TIMESTAMP_STRUCT otherParamDate = {2001, 10, 31, 0, 0, 0, 0};
    exampleUpdater.Params().dateValue = otherParamDate;

	// execute the update
	exampleUpdater++;

	cout << exampleUpdater.GetLastCount() << " rows updated!" << endl;
}
 
// delete some Examples from the database
void DeleteData()
{
	// construct view
	DBView<Example>
		view("DB_EXAMPLE", 
		DelBCAExampleObj(), "OR STRING_VALUE = (?) OR INT_VALUE = (?)", DelBPAExampleObj());
		
	// build a deleter for the view
	DBView<Example>::delete_iterator exampleDeleter;
	
	exampleDeleter = view;

	// now set the parameters indicating which rows we want to delete
	TIMESTAMP_STRUCT y2k = {2000, 1, 1, 0, 0, 0, 0};
	
	*exampleDeleter = Example(0, "Example", 0.0, 0, y2k);

	exampleDeleter.Params().strValue = "Find Me";
	exampleDeleter.Params().lowIntValue = 18;
	// execute the delete
	exampleDeleter++;

	cout << exampleDeleter.GetLastCount() << " rows deleted!" << endl;

	// now can perform other deletes using the same deleter object
	// make sure to put in your new values for the parameters
	// for the delete
	
	// now set the parameters indicating which rows we want to delete

	TIMESTAMP_STRUCT today = {1999, 9, 29, 0, 0, 0, 0};
	*exampleDeleter = Example(3, "operator->() works", 0.0, 0, today);

	// execute the delete
	exampleDeleter++;

	cout << exampleDeleter.GetLastCount() << " rows deleted!" << endl;
}

// Dynamic IndexedDBView example
void DynamicIndexedViewExample()
{
	DynamicDBView<> dynamic_view("DB_EXAMPLE", "INT_VALUE, STRING_VALUE, DOUBLE_VALUE, EXAMPLE_LONG, EXAMPLE_DATE");
	
	DynamicIndexedDBView< DynamicDBView<> > indexed_view(dynamic_view, 
		"IndexString; STRING_VALUE;"
		"Unique IndexLongDate; EXAMPLE_LONG, EXAMPLE_DATE", BOUND, USE_ALL_FIELDS);

	// Find the item where the STRING_VALUE matches the string "Foozle"
	DynamicIndexedDBView< DynamicDBView<> >::indexed_iterator idxview_it = indexed_view.find(string("Foozle"));;
	

	// Update the item with the key of "Foozle", to read "Fizzle" instead
	if (idxview_it != indexed_view.end()) {
		variant_row replacement;
		replacement = *idxview_it;
		replacement["STRING_VALUE"] = string("Fizzle");
		replacement["DOUBLE_VALUE"] = string("44.5");
		cout << "Replacing: " << endl;
		cout << *idxview_it << endl;
		cout << "with: " << endl;
		cout << replacement << endl;
		indexed_view.replace(idxview_it, replacement);
	}

	// Now find a second set of items using the alternate index, IndexLongDate
	// The STL convention for equal_range is that the return value consists of a pair:  
	// 1. an iterator referring to the beginning of the list of found items
	// and 2. an iterator pointing to the end of the list of found items 
	// We will remove all DataObj's in this range
	const TIMESTAMP_STRUCT date_criteria = {2000, 1, 1, 0, 0, 0, 0};
	long long_criteria = 33;
	DynamicIndexedDBView< DynamicDBView<> >::indexed_pair 
		pr = indexed_view.equal_range_AK("IndexLongDate", long_criteria, date_criteria);

	idxview_it = pr.first;

	cout << "*** Size before erase calls: " << indexed_view.size() << " ***"
		<< endl;
	
	// Remove all DataObj that matched the criteria in our equal_range_AK lookup
	while (idxview_it != pr.second)
	{
		// as iterator is invalidated upon an erase(), use a temporary iterator
		// to point to DataObj to erase
		// increment idxview_it before we erase so it will still be valid
		// when we erase the DataObj
		DynamicIndexedDBView< DynamicDBView<> >::indexed_iterator deleteMe = idxview_it;

		idxview_it++;

		indexed_view.erase(deleteMe);