DBView<DataObj,ParamObj>::select_iterator (original) (raw)

DBView<DataObj, ParamObj>::select_iterator is an Input Iterator that performs the reading of objects of type DataObj from a particular DBView (and thus the database). The select_iteratorgenerates the following SQL statement to read records from the database: "SELECT " + "<field1_fromBCA>, <field2_fromBCA>, ... " + "FROM " + "<tablename1_from_view>, <tablename2_from_view>, ... " + posfix_clause_from_view. (But see BuildSpecialQry for how to override this.) Note that all of the restrictions of an Input Iterator must be obeyed, including the restrictions on the ordering of operator* and operator++operations.

Defined in the select_iterator.hheader file.

1. Define an object to hold the rows from your query.

2. Define an association between fields in your query and fields in your object. This is what we call a 'BCA', which is short for Bind Column Addresses. In the example below, this is done via the functor "BCAExample". The job of the BCA is to equate SQL fields with object fields via the '==' operator which will then establish ODBC bindings to move data to or from a user query.

3. Create a view to select records from. This view is built from the template DBView and establishes which table(s) you want to access, what fields you want to look at (via the BCA), and an optional where clause to further limit the set of records that you are working with.

4. Use the DBView container to obtain an iterator to SELECT, INSERT, UPDATE or DELETE records from your view. These iterators may be used to either populate STL containers or apply algorithms from the Standard Template library.



In all the examples that follow we will assume that our database contains a table called DB_EXAMPLE of the form


SQL> desc db_example;

Name                            Type

------------------------------- --------

INT_VALUE                       INTEGER

STRING_VALUE                    VARCHAR

DOUBLE_VALUE                    FLOAT

EXAMPLE_LONG                    INTEGER

EXAMPLE_DATE                    DATE


// STEP 1 ////

// "Example" structure to hold rows from our database table

struct Example

{

                                        // tablename.columnname:

    int exampleInt;                 // DB_EXAMPLE.INT_VALUE

    string exampleStr;              // DB_EXAMPLE.STRING_VALUE

    double exampleDouble;           // DB_EXAMPLE.DOUBLE_VALUE

    long exampleLong;               // DB_EXAMPLE.EXAMPLE_LONG

    TIMESTAMP_STRUCT exampleDate;   // DB_EXAMPLE.EXAMPLE_DATE

};


// STEP 2 ////

// Create an association between table columns and fields in our object

template<> class dtl::DefaultBCA<Example>
{

public:

    void operator()(BoundIOs &cols, Example &rowbuf)

        {

       cols["INT_VALUE"] == rowbuf.exampleInt;

       cols["STRING_VALUE"] == rowbuf.exampleStr;

       cols["DOUBLE_VALUE"] == rowbuf.exampleDouble;

       cols["EXAMPLE_LONG"] == rowbuf.exampleLong;

       cols["EXAMPLE_DATE"] == rowbuf.exampleDate;

    }

};


// STEP 3 & 4

// Read the contents of the DB_EXAMPLE table and return a vector of the

// resulting rows

vector<Example> ReadData() {

    // Read the data

    vector<Example> results;

    DBView<Example> view("DB_EXAMPLE");


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

    for ( ; read_it != view.end();  ++read_it)

    {

        results.push_back(*read_it);

    }

    return results;

}

Template parameters

Parameter	Description	Default
DataObj	The type of object that will be written to the DBView. This object will be bound through use of the BCA to the appropriate columns in the database. The set of value types of an DBView::select_iterator consists of a single type, DataObj.
ParamObj	The type of object that will be used to specify the postfix parameters to the DBView.	DefaultParamObj

Model of

Input Iterator

Type requirements

DataObj and ParamObj must each fulfill the following requirements:.

Be of a type that is not primitive or of type string.
Have a publicly accessible copy constructor (the default is OK).
Have a publicly accessible assignment operator (the default is OK).

Public base classes

DB_iterator<DataObj, ParamObj>, iterator<input_iterator_tag, DataObj>

Members

Member	Where defined	Description
DBView::select_iterator()	select_iterator	Default constructor.
DBView::select_iterator(DBView<DataObj, ParamObj> &view, bool bPrepare = false)	select_iterator	See below.
DBView::select_iterator(const DBView::select_iterator&)	Input Iterator	The copy constructor. See Note [2].
DBView::select_iterator& operator=(const DBView select_iterator&)	Input Iterator	The assignment operator See Note [5].
const DataObj &operator*()	Input Iterator	Dereferencing operator. Returns the DataObj pointed to in the DBView. This operator forbids assigning to the iterator's DataObj.
CountedPtr operator->()	Input Iterator, DB_iterator	Dereferencing operator. Returns a pointer to the DataObj read from the DBView.
DBView::select_iterator& operator++()	Input Iterator	Preincrement. Reads a DataObj from the DBView. See Note [1].
DataObjPtr operator++(int)	Input Iterator	Postincrement Reads a DataObj from the DBView. Returns a proxy in order to remember the DataObj for future dereference operations in the same expression. See Note [1].
friend bool operator==(const DBView::select_iterator &i1, const DBView::select_iterator &i2)	Input Iterator	Returns whether the two iterators are equal, that is, do they refer to the same DataObj? See Note [3].
friend bool operator!=(const DBView::select_iterator &i1, const DBView::select_iterator &i2)	Input Iterator	Returns whether the two iterators are notequal. Equivalent to !(i1 == i2).

New members

These members are not defined in the Input Iterator requirements or in DB_iterator<DataObj, ParamObj>, but are specific to DBView::select_iterator.

Function	Description
DBView::select_iterator(DBView<DataObj, ParamObj> &view, bool bPrepare = false)	Creates an select_iterator which refers to view. Set bPrepare to true if you want the iterator to use SQLPrepare() and SQLExecute() in the event you are executing the statement many times. Otherwise, the iterator will use SQLExecDirect(). See Note [2].
void swap(DBView::select_iterator &other)	Swap *this with other.

Notes

[1] This is the operation that actually reads the DataObj from the database via the DBView. Each DBView::select_iterator internally owns a DBStmt object which is allocated and prepared when the underlying ODBC statement handle is first needed and not before. The handle is not opened until absolutely needed in order to make copying and assigning these iterators an inexpensive operation. The DBStmt is executed on each call to operator++(), whether the prefix or postfix version.

[2] There is also a variant of this constructor which takes a second argument specifying a dummy whether the iterator is at the beginning or end of the DBView. It is used internally and should never be called by the end user.

[3] According to the C++ standard, Table 72, Section 24.1.1, page 511, certain invariants must be maintained for an Input Iterator. The select_iterator implementation both adheres to the following invariants as well as taking advantage of them (assume x and y are both select_iterators):

Identity: x == y if and only if *x is equivalent to *y. This doesn't necessarily mean that you will always get the same answer from each, as is the case for the standard library's std::istream_iterator template.
Assignable and Equality Comparable: If you perform x = y, then x == y. See the comments below for how this invariant is preserved.
Increment Equality Not Guaranteed: x == y does not imply ++x == ++y.

We use a reference counted pointer to the data and copy this pointer on assignment of the iterator to obtain the Identity invariant. However, this does not work when we copy DBView::begin() because a new recordset may be opened. In this case, we may not have *x == *y although *x and *y are equivalent in the conceptual sense as the first element of a recordset.

[4] This function is a bit peculiar for select_iterators. For the notion of a DBView's begin() and end() iterators, begin() must point to the first DataObj in the view and end()to one past the last DataObj in the view. To provide this conceptual behavior, the dereferencing operators must be able to grab the first record from the database if the iterator hasn't done so already. So the ReadData() function in the above example code will say its reading elements 0, 2, 3, 4, etc., rather than the 1, 2, 3, 4, etc., you would expect. This behavior occurs as the following happens:

The program enters the first time through the loop. The iterator hasn't needed to access the database, so no elements have been read yet. Thus GetLastCount() == 0. Thus, the program prints "Reading element #0".
operator*() executes, reading the first DataObj from the database. So far then, the iterator has grabbed one DataObj so far.
Then, after the next few statements work with the fetched DataObj,the program prepares for the next iteration of the loop by incrementing the select_iterator, thus fetching another DataObj. Now two DataObj's have been read and the next invocation of GetLastCount() returns 2. The program now displays "Reading element #2".
From there in this and all future iterations of the loop: operator*() just returns the value of the already fetched DataObj. It does not read another DataObj at all from the database. Only one object will be read overall per iteration in the loop, that done by operator++().

[5] It is more efficient to reset your iterator to DBView::begin() if you are iterating over a view several times. When you perform the assigment select_iterator = view.begin(), the code will automatically reset the underlying recordset for you. This can represent a significant performance gain because then the database cursor does not have to be reparsed & reconstructed every time we wish to revisit the view (possibly with different selection parameters).