Part A:

Procedure:

1. Be sure to select a Visual C++ Project Type. Also select a Win32 Console Application Template

Enter the name ProQuery for the project

1. Once you have started your source code, be sure to add the include files and the library files as indicated in my Oracle Access in C++ in ECET-450 document.
2. In the Solution Explorer, insert the source code from the ProQueryExample.cpp file into the ProQuery.cpp file to add the needed C++ code. You will want to use most of the classroom code as is. However, you need to modify the code so that you list the sales representativeA????1s last names, each of their customerA????1s balances, and each of their customerA????1s names in this order. Be sure to update the column headings. Update the spacing between columns if necessary.
3. In your ProQuery.cpp source file, modify the source code so that it changes the customer balance of Streaming Direct from $210.40 to $337.26. Rerun the query in step 2 above. Modify the source to return the balance of Streaming Direct to $210.40. Rerun the query in step 3 above. Print out a copy of your output.

Here's the code:

/*
* New Name: ProQueryExample.cpp
*
* New Description
*   This modified source code displays a join of the invoice and
*   customer tables. In addition this source code updates the Streaming
*   Direct customer name to Direct Sports and then returns the name back
*   to Streaming Direct.
*
* Remarks:
*           Author       Date       Comment
*           Mike Lukens   08/08/2008   initial code
*
*/
#include <iostream>
#include <iomanip>
#include <occi.h>
using namespace oracle::occi;
using namespace std;

class occiIntf
{
public:
   //constructor
   occiIntf ( string login, string password, string dbStr )
   {
       //setup the environment which Oracle requires
       environ = Environment::createEnvironment ( Environment::DEFAULT );
       try
       {
           //create a connection to the database
           connect = environ->createConnection ( login, password, dbStr );
       }
       catch ( SQLException excpt )
       {
            cout << "Exception thrown by createConnection" << endl;
            cout << "Error code: "<< excpt.getErrorCode ( ) << endl;
            cout << excpt.getMessage ( ) << endl;
           system ( "pause" );
           exit ( 1 );
       }
   }

   //destructor
   ~occiIntf ( )
   {
       //make sure to clean up the connection and the environment when
       //we are done!
       environ->terminateConnection ( connect );
       Environment::terminateEnvironment ( environ );
   }

   //update a field
   void updateField ( string name, string id )
   {
       //change the customer name to/from Direct Sports in CUSTOMER table
       string sqlStatmt
           = "UPDATE customer SET cust_name = :x WHERE cust_id = :y";
       statmt = connect->createStatement ( sqlStatmt );
       try
       {
           //substitute the customer name and id into the SQL statement
           statmt->setString ( 1, name );
           statmt->setString ( 2, id );
           statmt->executeUpdate ();
           cout << "Update succeeded\n" << endl;
       }
       catch ( SQLException excpt )
       {
           cout << "Exception thrown by updateField" << endl;
           cout << "Error code: "<< excpt.getErrorCode ( ) << endl;
           cout << excpt.getMessage ( ) << endl;
       }
       connect->terminateStatement ( statmt );
   }

   //display the invoice number, date, and customer name
   void displayAllRows ( )
   {
       //create the SQL statement
       string sqlStatmt
           = "SELECT invoice_num, invoice_date, cust_name FROM invoice, customer WHERE invoice.cust_id = customer.cust_id";
       //instantiate the statement object from our connection object using
       //the SQL string to initialize the statement
       statmt = connect->createStatement ( sqlStatmt );
       try
       {
           //execute the query and point to the result set
           //in order to loop through the returned data.
           ResultSet *resSet = statmt->executeQuery ( );
           //display the column headers
           cout << endl
               << setw ( 5 ) << "INV #" << " "
               << setw ( 9 ) << "DATE " << " "
               << setw ( 20 ) << "CUSTOMER NAME" << endl;
           //keep getting results until there are none
           while ( resSet->next ( ) )
           {
               //extract each attribute value which is a string
               cout << setw ( 5 ) << right << resSet->getString ( 1 ) << " "
                   << setw ( 9 ) << resSet->getString ( 2 ) << " "
                   << setw ( 30 ) << left << resSet->getString ( 3 ) << endl;
           }
           cout << endl;
           //close the result set object when done
           statmt->closeResultSet ( resSet );
       }
       catch ( SQLException excpt )
       {
           cout << "Exception thrown by displayAllRows." << endl;
           cout << "Error code: " << excpt.getErrorCode ( ) << endl;
           cout << excpt.getMessage ( ) << endl;
       }
       //done with statement
       connect->terminateStatement ( statmt );
   }

   private:
       //variables required by OCCI
       Environment * environ;
       Connection * connect;
       Statement * statmt;
};   //end of occiIntf class

void main ( )
{
   string dbStr = "ECET450";
   string login;
   string password;

   cout << "Enter your Oracle login: ";
   cin >> login;
   cout << "Enter your Oracle password: ";
   cin >> password;
   system ( "CLS" );       //clear the screen
   system ( "pause" );       //pause before continuing

   cout << "Demonstration of a query using OCCI" << endl;
   // First create an object that is connected with the Oracle database.
   occiIntf * database = new occiIntf ( login, password, dbStr );
  
   cout << "Display invoice number, date, and customer name" << endl;
   database->displayAllRows ( );

   cout << "Update customer Streaming Direct to Direct Sports" << endl;
   database->updateField ( "Direct Sports" , "1193" );

   cout << "Display invoice number, date, and updated customer name" << endl;
   database->displayAllRows ( );

   cout << "Update customer Direct Sports to Streaming Direct" << endl;
   database->updateField ( "Streaming Direct" , "1193" );

   cout << "Display invoice number, date, and updated customer name" << endl;
   database->displayAllRows ( );
  
   delete database;
   cout << "OCCI query and update complete\n" << endl;
   system ( "pause" );
}

Here's the SQL snippet for the customer:

CUST CUST_NAME                      CUST_STREET          CUST_CITY            CU
---- ------------------------------ -------------------- -------------------- --
CUST_ CUST_BALANCE CUST_LIMIT REP
----- ------------ ---------- ---

1193 Streaming Direct               4938 Maple           Grove                GA
23321        210.4      10000 237