dtl

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;

	}

}

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.