dtl

DBView<DataObj, ParamObj>::sql_iterator


Category: iterators	Component type: type

Description

DBView<DataObj, ParamObj>::sql_iterator is both an Input Iterator and Output Iterator that repeatedly executes a SQL string to read or write data against a particular DBView . The SQL string that is used by this iterator is controlled by the DBView constructor. Because the SQL string can be arbitrary (SELECT, INSERT, UPDATE, DELETE or other SQL commands) sql_iterator can do anything the other DTL iterators do and much more. In practice, the primary application for sql_iterator is for use in calling stored procedures.

As with the other subclasses of DB_iterator, parameters and columns are bound respectively through the use of BPAs and BCAs. However, for sql_iterator, it is possible to specify parameter bindings either in the BCA or BPA. This flexiblity is necessary because a user defined SQL statement may contain all parameters e.g. "INSERT INTO TABLENAME VALUES(?, ?)" or "{call InsertProcedure(?, ?)}" and we want to allow these parameters to be written through a DataObj for compatibitility with the other DBView iterator types. Conceptually, we recommend that you place information that you wish to read or write into the DataObj class and information that you want to use for WHERE clause conditions into the ParamObj class. One important note is that the BCA and BPA will determine how sql_iterator behaves for operator++. If your BCA and BPA contain only parameters, then operator++ will simply call SQLExecute(). This matches the behavior given by insert_iterator, update_iterator, and delete_iterator. Therefore, your SQL statement will not get executed until the first call to operator++ if it contains only parameters. If your BCA and BPA have at least one bound column (i.e. BoundIO["ColumnName"] == row.ColumnName) then operator++ will use SQLFetch() logic. This matches the behavior given by select_iterator which means the SQL statement will get executed only once when the iterator is initialized and subseqent calls to operator++ will read additional values through the use of SQLFetch(). (If your SQL statement exposes multiple result sets these may be retrieved through the use of the MoreResults() function described below).

Because sql_iterator can run against arbitrary SQL statements it must distinguish what kinds of parameters are being bound. The three possible types of parameters are INPUT, OUTPUT and INPUT/OUTPUT. To make this distinction, sql_iterator introduces directional binding syntax for its parameters.

Each possible form of information flow between boundIO and DataObj/ParamObj corresponds to whether the parameter passed in to the query is an input, output, or input/output parameter. The user must be able to define the direction of information flow for parameters in cases such as stored procedure calls. The new binding syntax DTL provides shows the flow of information very clearly:

Expressions	Parameter Type	Description
boundIOs[colName] << dataObj.member boundIOs[paramNum] << paramObj.member	INPUT	The DataObj/ParamObj member supplies data needed by the SQL query.
boundIOs[colName] >> dataObj.member boundIOs[paramNum] >> paramObj.member	OUTPUT	The DataObj/ParamObj member receives data back from the SQL query.
boundIOs[colName] == dataObj.member boundIOs[paramNum] == paramObj.member	INPUT/OUTPUT	The DataObj/ParamObj member both supplies data to and receives back data from the SQL query.

You should always use this new syntax with any BCA's and BPA's you define. To maintain backwards compatibility with BCA's and BPA's for select_iterator's, insert_iterator's, delete_iterator's, and update_iterator's, the use of "boundIOs[colName] == dataObj.member" and "boundIOs[paramNum] == paramObj.member" is still legal. The direction of information flow can be inferred from the type of iterator used in those cases. However, the directional syntax is mandatory for sql_iterators as no information flow semantics can be inferred from a general SQL query. Examples in this documentation may use both forms of syntax where legal.

Definition

Defined in the sql_iterator.h header file.

Examples:

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;

}

Example: Reading a set of records from an Oracle stored procedure.



// Oracle stored procedure we wish to test

// for more information on this example, see the following Oracle documentation

#if 0

 Create or replace package ExampleInfo as



   Type ExampleRec is record



   (

   INT_VALUE     integer,



   STRING_VALUE   varchar2(50)



   );



   Type ExampleCursor is ref cursor return 



   ExampleRec;



   End ExampleInfo;



   



   Create or replace procedure ExampleInfoProc



   (LONG_CRITERIA IN integer, empcursor IN OUT 



   ExampleInfo.ExampleCursor)



   As



   Begin



   Open empcursor For



   select INT_VALUE, STRING_VALUE 



   from db_example



   where EXAMPLE_LONG = LONG_CRITERIA;



   End;

#endif



class ProcBCA {

public:

 void operator()(BoundIOs &cols, variant_row &row)

 {

  cols["INT_VALUE"] >> row._int();

  cols["STRING_VALUE"] >> row._string();

  

  cols.BindVariantRow(row);

 }

};



class ProcParams {

public:

	long long_criteria;

};



class ProcBPA {

public:

 void operator()(BoundIOs &cols, ProcParams &row)

 {

  cols[0] << row.long_criteria;

 }

};



// Read the contents of a table and print the resulting rows

// *** you must have Oracle ODBC driver version 8.1.5.3.0 for this to work ***

// The reason why is that the stored procedure shown above returns a "cursor"

// to pass back multiple rows which is only supported in Oracle ODBC driver

// version 8.1.5.3.0 or higher.  (Merant drivers for Oracle also support this syntax.)

void StoredProcReadData() {



        // Note: ExampleInfoProc takes an integer as an input parameter to determine

        // what records to return, and returns a cursor to a set of rows as an

        // output parameter. The output cursor is bound implicitly as a set of columns.

	DBView<variant_row, ProcParams> view("{call ExampleInfoProc(?)}", 

		ProcBCA(), "", ProcBPA());



	variant_row s(view.GetDataObj());



	// 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

	DBView<variant_row, ProcParams>::sql_iterator print_it = view.begin();

	print_it.Params().long_criteria = 22;



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

	{

		 variant_row r = *print_it;

		 for (size_t i = 0; i < r.size(); ++i)

		 	cout << r[i] << " ";

		 cout << endl;

	}

}

Example: A count query using a stored procedure. This illustrates the use of input and output parameters.

class EmptyDataObj

{



};



class ProcOutBCA

{

public:

   void operator()(BoundIOs &boundIOs, EmptyDataObj &rowbuf)

   {



   }

};



class ProcOutParams {

public:

	long long_criteria;

	int numRecords;

	

	friend ostream &operator<<(ostream &o, const ProcOutParams &params)

	{

       cout << "ProcOutParams(" << params.long_criteria << ", " << params.numRecords << ")";

	   return o;

	}

};



class ProcOutBPA {

public:

 void operator()(BoundIOs &cols, ProcOutParams &params)

 {

  cols[0] << params.long_criteria;

  cols[1] >> params.numRecords;

 }

};



// Oracle stored procedure we wish to test

#if 0

   Create or replace procedure ExampleInfoProcOutParams



   (LONG_CRITERIA IN integer, NUM_RECORDS OUT integer)



   As

  

   Begin



   select count(*)



   into NUM_RECORDS



   from db_example



   where EXAMPLE_LONG = LONG_CRITERIA;



   End;

#endif



// simply does a select count(*) from db_example where example_long = 22

void StoredProcCountRecords() {



	DBView<EmptyDataObj, ProcOutParams> view("{call ExampleInfoProcOutParams(?, ?)}", 

		ProcOutBCA(), "", ProcOutBPA());



	// execute our stored procedure

	DBView<EmptyDataObj, ProcOutParams>::sql_iterator print_it = view.begin();



	print_it.Params().long_criteria = 22;



	*print_it = EmptyDataObj(); // force the statement to execute 

    ++print_it;



	cout << "number of records with EXAMPLE_LONG = 22 is " 

		 << print_it.Params().numRecords << endl;

}

Example: Use of MoreResults() with a stored procedure



// *** Note: This example is specific to SQL Server ***

// for more information on processing return values from

// SQL Server stored procedures, see this documentation



// stored procedure being called in this example

#if 0

DROP PROCEDURE TestParm 



-- Example procedure returns three things:

-- 1. A set of records from the select statement: "SELECT STRING_VALUE FROM DB_EXAMPLE"

-- After all records have been retrieved, output paramenters are returned:

-- 2. OutParm

-- 3. Return value for function

CREATE PROCEDURE TestParm @OutParm int OUTPUT AS

SELECT STRING_VALUE FROM db_example

SELECT @OutParm = 66

RETURN 99





DECLARE @RetVal INT



DECLARE @Param INT



  



-- Execute the procedure, which returns



-- the result set from the first SELECT.



EXEC @RetVal = TestParm @OutParm = @Param OUTPUT



  



-- Use the return code and output parameter.



PRINT 'The return value from the procedure was: ' +



                 CONVERT(CHAR(6), @RetVal)



PRINT 'The value of the output parameter in the procedure was: ' +



                 CONVERT(CHAR(6), @Param)



#endif





class TestParmBCA {

public:

 void operator()(BoundIOs &cols, variant_row &row)

 {

  cols["STRING_VALUE"] == row._string();

  cols[0] >> row._int();

  cols[1] >> row._int();



  cols.BindVariantRow(row);

 }

};



// Read the contents of a table and print the resulting rows

void StoredProcReadTestParm() {



 DBView<variant_row> view("{? = call TestParm(?)}",

  TestParmBCA());





 // 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.GetDataObj());



 // 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

 DBView<variant_row>::sql_iterator print_it = view;





 // By default DTL uses server side cursors for SQL Server so that more than

 // one iterator can be active at a time.  This is set in the constructor

 // for DBStmt.

 // Here we require a client side cursor because our stored procedure returns

 // multiple result sets.  Therfore we clear out the setting to use server

 // side cursors in which case SQL server will default to a client side cursor.

 // For details on server side versus client side cursors

 // see Rowsets and SQL Server Cursors

 

 print_it.GetStmt().ClearStmtAttrs();



 variant_row r = view.GetDataObj();

 r[0] = 0;

 r[1] = 0;



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

 {

  r = *print_it;

  for (size_t i = 0; i < r.size(); ++i)

    cout << r[i] << " ";

  cout << endl;

 }



 cout << endl;

 cout << "After call to MoreResults(), "

  "SQL-Server gives results in output parameters & return code." << endl;

 print_it.MoreResults();

 r = *print_it;

 for (size_t i = 0; i < r.size(); ++i)

    cout << r[i] << " ";

 cout << endl;



}

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::insert_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<DataObj>

Model of

Input Iterator, Output 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::sql_iterator()`	`sql_iterator`	Default constructor.
`DBView::sql_iterator(DBView<DataObj, ParamObj> &view, bool bPrepare = true)`	`sql_iterator`	See below.
`DBView::sql_iterator(DBView<DataObj, ParamObj>::select_iterator&)`	`sql_iterator`	See below.
`DBView::sql_iterator()`	`sql_iterator`	Default constructor.
`DBView::sql_iterator(const DBView::sql_iterator&)`	Input Iterator	The copy constructor. See Note [2].
`DBView::sql_iterator& operator=(const DBView sql_iterator&)`	Input Iterator	The assignment operator
DBView::sql_iterator &operator*()	Input Iterator, Output Iterator	Proxy operators necessary to emulate it = data. Return this.
`operator DataObj()`	Input Iterator	Conversion operator necessary to emulate data = it. Note that if you need to access a field of it, you must either assign the result to a DataObj or use a static_cast<DataObj>(*it). Or better yet, use operator->(). Only should get used as an input operation.
`DBView::sql_iterator& operator=(const DataObj &dataObj)`	Output Iterator	Proxy operators necessary to emulate it = data. Return this. Executes the query with the DataObj to the DBView. Note that this version is only used in output operations. See Note [1].
`CountedPtr<DataObj> operator->()`	Input Iterator	Dereferencing operator. Returns a pointer to the DataObj read from the DBView.
`DBView::sql_iterator& operator++()`	Input Iterator, Output Iterator	Preincrement. Reads an DataObj from the DBView if an input operation. Return *this. See Note [1].
`DataObjPtr DBView::sql_iterator operator++(int)`	Input Iterator, Output Iterator	Postincrement Reads an DataObj from the DBView if an input operation. Returns a proxy in order to remember the DataObj for future dereference operations in the same expression. See Note [1].
`friend bool operator==(const select_iterator &i1, const select_iterator &i2)`	Input Iterator	Returns whether the two iterators are equal, that is, do they refer to the same DataObj?
`friend bool operator!=(const select_iterator &i1, const select_iterator &i2)`	Input Iterator	Returns whether the two iterators are not equal. Equivalent to !(i1 == i2).
`bool MoreResults()`	Input Iterator	See below.
`void swap(DBView::sql_iterator &other)`	`sql_iterator`	See below.

New members

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

Function Description

DBView::sql_iterator(DBView<DataObj, ParamObj> &view, bool bPrepare = true) Creates an sql_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].

DBView::sql_iterator(DBView<DataObj, ParamObj>::select_iterator&) Constructs a sql_iterator from the select_iterator passed in. This new iterator will behave like a select_iterator.

void swap(DBView::sql_iterator &other) Swap *this with other.

bool MoreResults() Gets the next result set if any. Returns true if another result set is available, false otherwise. This is primarly useful if the SQL string contains multiple SELECT statements or if the SQL string calls a stored procedure that returns multiple result sets. For details, see the example above and the SQLMoreResults() statement in the ODBC documentation.

Function	Description
`DBView::sql_iterator(DBView<DataObj, ParamObj> &view, bool bPrepare = true)`	Creates an sql_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].
`DBView::sql_iterator(DBView<DataObj, ParamObj>::select_iterator&)`	Constructs a `sql_iterator` from the s`elect_iterator passed in. This new iterator will behave like a select_iterator`.
`void swap(DBView::sql_iterator &other)`	Swap *this with other.
`bool MoreResults()`	Gets the next result set if any. Returns true if another result set is available, false otherwise. This is primarly useful if the SQL string contains multiple SELECT statements or if the SQL string calls a stored procedure that returns multiple result sets. For details, see the example above and the SQLMoreResults() statement in the ODBC documentation.

Notes

[1] operator++() actually reads the DataObj from database via the DBView if the iterator is performing an input operation. However, if the iterator is outputting to the database, it actually writes to the database on operator=(). Note that previous versions of DTL performed their output operations to the DB on operator++(). Each DBView::sql_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.

[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.