C++/Database Lab

Part A:

Purpose:

This laboratory provides some experience working with the C++ programming language that has an SQL query and SQL update embedded in it.

Procedure:

Follow the instructions below.

Be sure to load the C++ to ProQueryExample code presented in the Appendix A below. 

1. In your ProQueryExample.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 3 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.

1. Submit a cover sheet, a copy of your output, and the source code that you used to obtain it.  Follow any additional instructions that your instructor may give you.

Appendix A

/*

 * Name: ProQueryExample.cpp

 *

 * Description

 *    This 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

      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" );

}

Part B:

Procedure:

Be sure to have a copy of the C++ to ProInsertExample code presented in the Appendix B below. 

1. Modify the member function in the ProInsertExample.cpp file below to insert a new row in the Line table.  Add the following row: Invoice number: 42447, product ID: KW114, number ordered: 1, and price: $595.00.  Be sure to update the column headings.  Update the spacing between columns if necessary.

1. Modify the member function that deletes this new row inserted in step 3 above.  Display the contents of the Line table again.  Print out a copy of your output that displays the Line table before the insertion, of the Line table after the insertion, and the Line table after the deletion.

1. Submit a cover sheet, a copy of your output, and the source code that you used to obtain it.  Follow any additional instructions that your instructor may give you.

Appendix B

/*

 * Name: ProInsertExample.cpp

 *

 * Description:

 *    This source file inserts and deletes a row in the product table.

 *    In addition the displayAllRows member function is modified to display

 *    the contents of the product table.

 *

 * 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 )

      {

            //create an 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 the customer name field

      void updateField ( string name, string id )

      {

            //change the customer name 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 updateRow" << endl;

                  cout << "Error code: "<< excpt.getErrorCode ( ) << endl;

                  cout << excpt.getMessage ( ) << endl;

            }

            connect->terminateStatement ( statmt );

      }

      //display the product table

      void displayAllRows ( )

      {

            //generate the SQL string

            string sqlStatmt = "SELECT * FROM product";

            //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 ( 7 ) << "PROD ID" << "  "

                        << setw ( 20 ) << "PROD DESC" << "  "

                        << setw ( 3 ) << "#" << "  "

                        << setw ( 4 ) << "TYPE" << "  "

                        << setw ( 9 ) << "WAREHOUSE" << "  "

                        << setw ( 8 ) << "PRICE" << endl;

                  //keep processing the records until there are none

                  while ( resSet->next ( ) )

                  {

                        //get the attribute values either as strings or as integers

                        cout << setw ( 7 ) << right << resSet->getString ( 1 ) << "  "

                              << setw ( 20 ) << resSet->getString ( 2 ) << "  "

                              << setw ( 3 ) << resSet->getInt ( 3 ) << "  "

                              << setw ( 4 ) << resSet->getString ( 4 ) << "  "

                              << setw ( 9 ) << resSet->getString ( 5 ) << "  "

                              << setw ( 8 ) << resSet->getFloat ( 6 ) << "  "

                              << endl;

                  }

                  cout << endl;

                  //close the result set object

                  statmt->closeResultSet ( resSet );

            }

            catch ( SQLException excpt )

            {

                  cout << "Exception thrown by displayAllRows" << endl;

                  cout << "Error code: " << excpt.getErrorCode ( ) << endl;

                  cout << excpt.getMessage ( ) << endl;

            }

            //close the statement

            connect->terminateStatement ( statmt );

      }

      //insert a row into the product table

      void insertRow ( )

      {

            //generate a string containing the SQL statement

            string sqlStatmt =

                  "INSERT INTO PRODUCT VALUES ( 'MK140', 'Hair Trimmer', 23, 'AP', 'B', 19.95 )";

            //instantiate a statement object from our connection object using

            //the SQL string to initialize the statement

            statmt = connect->createStatement ( sqlStatmt );

            try{

                  //execute the SQL statement

                  statmt->executeUpdate ( );

                  cout << "Inserting a row into the PRODUCT table was successful\n" << endl;

            }

            catch ( SQLException excpt )

            {

                  cout << "Exception thrown by insertRow" << endl;

                  cout << "Error code: "<< excpt.getErrorCode ( ) << endl;

                  cout << excpt.getMessage ( ) << endl;

            }

            //close this statement object

            connect->terminateStatement ( statmt );

      }

      //delete a row

      void deleteRow ( string product_id )

      {

            //generate the SQL statement to delete a product row

            string sqlStatmt = "DELETE FROM PRODUCT WHERE PROD_ID = :x";

            statmt = connect->createStatement ( sqlStatmt );

            try

            {

                  // Substitute the product id into the prepared statement

                  statmt->setString ( 1, product_id );

                  statmt->executeUpdate ( );

                  cout << "Delete a product row\n" << endl;

            }

            catch ( SQLException excpt )

            {

                  cout << "Exception thrown by deleteRow" << endl;

                  cout << "Error code: "<< excpt.getErrorCode ( ) << endl;

                  cout << excpt.getMessage ( ) << endl;

            }

            connect->terminateStatement ( statmt );

      }

      private:

            // Variables required to interact with Oracle

            Environment * environ;

            Connection * connect;

            Statement * statmt;

}; // end of class  occiIntf

int main (void)

{

      string dbStr = "ECET450";

      string login;

      string password;

      cout << "Enter your Oracle login: ";

      cin >> login;

      cout << "Enter your Oracle password: ";

      cin >> password;

      system ( "CLS" );

      cout << "Perform an insertion and a deletion using OCCI" << endl;

      //instantiate an occiIntf object

      occiIntf * database = new occiIntf ( login, password, dbStr );

      cout << "Display PRODUCT table" << endl;

      database->displayAllRows ( );

      cout << "Insert a new row into the PRODUCT table" << endl;

      database->insertRow ( );

      cout << "Display product table with the new row" << endl;

      database->displayAllRows ( );

      cout << "Delete the new row from the PRODUCT table" << endl;

      database->deleteRow ( "MK140" );

      cout << "Display product table without the new row" << endl;

      database->displayAllRows ( );

      delete database;

      cout << "OCCI insertion and deletion complete\n" << endl;

      system ( "pause" );

}