|
|
|
Manage() 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)");
}
