Manage

The manage step provides data management functionality for your application and/or process.

Below is the code for Manage():

/*

 * Manage()

 *

 * Populates table and perform a simple query

 *

 */

void Manage(void)

{

   RETCODE  rc;

   SQLTCHAR custname[47+1];

   SQLREAL  total;

   SDWORD   cbData;

   printf("MANAGE\n");

   /* populate the tables with data */

   Add_CustomerMaster_Records();

   Add_CustomerOrders_Records();

   Add_OrderItems_Records();

   Add_ItemMaster_Records();

   /* perform a query:

      list customer name and total amount per order

      name               total

      @@@@@@@@@@@@@      $xx.xx

      for each order in the CustomerOrders table

         fetch order number

         fetch customer number

         fetch name from CustomerMaster table based on customer number

         for each order item in OrderItems table

            fetch item quantity

            fetch item number

            fetch item price from ItemMaster table based on item number

         next

      next

   */

   printf("\n\tQuery Results\n");

   if ((rc = SQLExecDirect(hStmt,

         "SELECT cm_custname, SUM(im_itempric * oi_quantity) "

         "FROM custmast, custordr, ordritem, itemmast "

         "WHERE co_custnumb = cm_custnumb AND co_ordrnumb = oi_ordrnumb AND oi_itemnumb = im_itemnumb "

         "GROUP BY cm_custnumb, cm_custname",

      SQL_NTS)) != SQL_SUCCESS)

      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");

   /* for each order in the CustomerOrders table */

   while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)

   {

      if ((rc = SQLGetData(hStmt, 1, SQL_C_CHAR, custname, sizeof(custname), &cbData)) != SQL_SUCCESS)

         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLGetData()");

      if ((rc = SQLGetData(hStmt, 2, SQL_C_FLOAT, &total, sizeof(total), &cbData)) != SQL_SUCCESS)

         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLGetData()");

      /* output data to stdout */

      printf("\t\t%-20s %.2f\n", custname, total);

   }

   SQLFreeStmt(hStmt, SQL_CLOSE);

}

/*

 * Add_CustomerMaster_Records()

 *

 * This function adds records to table CustomerMaster from an

 * array of strings

 */

void Add_CustomerMaster_Records(void)

{

   RETCODE     rc;

   SQLINTEGER  i;

   SQLTCHAR    sCommand[512];

   SQLTCHAR    *data[] = {

      "('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",

      "('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",

      "('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",

      "('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"

   };

   SQLINTEGER  nRecords = sizeof(data) / sizeof(data[0]);

   Delete_Records("custmast");

   printf("\tAdd records in table CustomerMaster...\n");

   /* add one record at time to table */

   for (i = 0; i < nRecords; i++)

   {

      strcpy (sCommand, "INSERT INTO custmast VALUES ");

      strcat (sCommand, data[i]);

      if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)

         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");

   }

}

/*

 * Add_CustomerOrders_Records()

 *

 * This function adds records to table CustomerOrders from an

 * array of strings

 */

void Add_CustomerOrders_Records(void)

{

   RETCODE     rc;

   SQLINTEGER  i;

   SQLTCHAR    sCommand[512];

   SQLTCHAR    *data[] = {

      "('09/01/2002','09/05/2002','1','1001')",

      "('09/02/2002','09/06/2002','2','1002')"

   };

   SQLINTEGER  nRecords = sizeof(data) / sizeof(data[0]);

   Delete_Records("custordr");

   printf("\tAdd records in table CustomerOrders...\n");

   /* add one record at time to table */

   for (i = 0; i < nRecords; i++)

   {

      strcpy (sCommand, "INSERT INTO custordr VALUES ");

      strcat (sCommand, data[i]);

      if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)

         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");

   }

}

/*

 * Add_OrderItems_Records()

 *

 * This function adds records to table OrderItems from an

 * array of strings

 */

void Add_OrderItems_Records(void)

{

   RETCODE     rc;

   SQLINTEGER  i;

   SQLTCHAR    sCommand[512];

   SQLTCHAR    *data[] = {

      "(1,2,'1','1')",

      "(2,1,'1','2')",

      "(3,1,'1','3')",

      "(1,3,'2','3')"

   };

   SQLINTEGER  nRecords = sizeof(data) / sizeof(data[0]);

   Delete_Records("ordritem");

   printf("\tAdd records in table OrderItems...\n");

   /* add one record at time to table */

   for (i = 0; i < nRecords; i++)

   {

      strcpy (sCommand, "INSERT INTO ordritem VALUES ");

      strcat (sCommand, data[i]);

      if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)

         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");

   }

}

/*

 * Add_ItemMaster_Records()

 *

 * This function adds records to table ItemMaster from an

 * array of strings

 */

void Add_ItemMaster_Records(void)

{

   RETCODE     rc;

   SQLINTEGER  i;

   SQLTCHAR    sCommand[512];

   SQLTCHAR    *data[] = {

      "(10,19.95,'1','Hammer')",

      "(3,  9.99,'2','Wrench')",

      "(4, 16.59,'3','Saw')",

      "(1,  3.98,'4','Pliers')"

   };

   SQLINTEGER  nRecords = sizeof(data) / sizeof(data[0]);

   Delete_Records("itemmast");

   printf("\tAdd records in table ItemMaster...\n");

   /* add one record at time to table */

   for (i = 0; i < nRecords; i++)

   {

      strcpy (sCommand, "INSERT INTO itemmast VALUES ");

      strcat (sCommand, data[i]);

      if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)

         Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");

   }

}

/*

 * Delete_Records()

 *

 * This function deletes all the records in a tables

 */

void Delete_Records(SQLTCHAR* table)

{

   RETCODE  rc;

   SQLTCHAR sCommand[512];

   printf("\tDelete records...\n");

   sprintf (sCommand, "DELETE FROM %s", table);

   if ((rc = SQLExecDirect(hStmt, sCommand, SQL_NTS)) != SQL_SUCCESS)

      Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DELETE)");

}