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:

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 

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 &PARAMS) COUT } PROCOUTBPA PROCOUTPARAMS // 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++; // force the statement to execute 

	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 << COUT   // Print out all rows and columns from our query
 DBView<variant_row>::sql_iterator print_it = view.begin();
 variant_row r;

 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." << COUT }   

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)	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
const DataObj &operator*() const	Input Iterator	Dereferencing operator. Returns the DataObj pointed to in the DBView. This operator forbids assigning to the iterator's DataObj.
DataObj &operator*()	Output Iterator	Dereferencing operator. Returns the DataObj pointed to in the DBView. This operator will be called in non-const situations.
CountedPtr<DataObj> operator->()	Input Iterator	Dereferencing operator. Returns a pointer to the DataObj read from the DBView.
DBView::select_iterator& operator++()	Input Iterator, Output Iterator	Preincrement. Reads/writes an DataObj from/to the DBView. See Note [1].
const DBView::select_iterator operator++(int)	Input Iterator, Output Iterator	Postincrement Reads/writes an DataObj fron/to the DBView. 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)	Creates an sql_iterator which refers to view. 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.

Notes

[1] This is the operation that actually reads/writes the DataObj from/to the database via the DBView. 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.

See also

DB_iterator, Output Iterator, Input Iterator.

---

Copyright © 2002, Michael Gradman and Corwin Joy.

Permission to use, copy, modify, distribute and sell this software and its documentation for any purpose is hereby granted without fee, provided that the above copyright notice appears in all copies and that both that copyright notice and this permission notice appear in supporting documentation. Corwin Joy and Michael Gradman make no representations about the suitability of this software for any purpose. It is provided "as is" without express or implied warranty.

This site written using the ORB.