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)
{
EXEC SQL BEGIN DECLARE SECTION;
CHAR custname[47+1];
FLOAT total;
EXEC SQL END DECLARE SECTION;
os_printf(DH_STRING_LITERAL("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
*/
os_printf(DH_STRING_LITERAL("\n\tQuery Results\n"));
/* declare cursor for fetching orders */
EXEC SQL DECLARE curs CURSOR FOR
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;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
EXEC SQL OPEN curs;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
while (1)
{
/* for each order in the CustomerOrders table */
EXEC SQL FETCH curs INTO :custname, :total;
if (sqlca.sqlcode == SQL_NOT_FOUND)
break;
else if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
/* output data to stdout */
os_printf(DH_STRING_LITERAL("\t\t%-20s %.2f\n"), custname, total);
}
EXEC SQL CLOSE curs ;
}
/*
* Delete_Records()
*
* This function deletes all the records in the table
*/
void Delete_Records(dh_char_t* table)
{
EXEC SQL BEGIN DECLARE SECTION ;
char sCommand[512];
EXEC SQL END DECLARE SECTION ;
os_printf(DH_STRING_LITERAL("\tDelete records…\n"));
os_sprintf (sCommand, DH_STRING_LITERAL("DELETE FROM %s"), table);
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode < 0)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
EXEC SQL COMMIT WORK ;
}
/*
* Add_CustomerMaster_Records()
*
* This function adds records to table CustomerMaster from an
* array of strings
*/
void Add_CustomerMaster_Records(void)
{
EXEC SQL BEGIN DECLARE SECTION ;
char sCommand[512];
EXEC SQL END DECLARE SECTION ;
dh_char_t *data[] = {
DH_STRING_LITERAL("('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')"),
DH_STRING_LITERAL("('1001','61434','CT','1','Michael Jordan','13 Main','Harford')"),
DH_STRING_LITERAL("('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')"),
DH_STRING_LITERAL("('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')")
};
int i;
int nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records(DH_STRING_LITERAL("custmast"));
os_printf(DH_STRING_LITERAL("\tAdd records in table CustomerMaster…\n"));
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
os_sprintf (sCommand, DH_STRING_LITERAL("INSERT INTO custmast VALUES %s"), data[i]);
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
}
EXEC SQL COMMIT WORK;
}
/*
* Add_CustomerOrders_Records()
*
* This function adds records to table CustomerOrders from an
* array of strings
*/
void Add_CustomerOrders_Records(void)
{
EXEC SQL BEGIN DECLARE SECTION ;
char sCommand[512];
EXEC SQL END DECLARE SECTION ;
dh_char_t *data[] = {
DH_STRING_LITERAL("('09/01/2002','09/05/2002','1','1001')"),
DH_STRING_LITERAL("('09/02/2002','09/06/2002','2','1002')")
};
int i;
int nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records(DH_STRING_LITERAL("custordr"));
os_printf(DH_STRING_LITERAL("\tAdd records in table CustomerOrders…\n"));
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
os_sprintf (sCommand, DH_STRING_LITERAL("INSERT INTO custordr VALUES %s"), data[i]);
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
}
EXEC SQL COMMIT WORK;
}
/*
* Add_OrderItems_Records()
*
* This function adds records to table OrderItems from an
* array of strings
*/
void Add_OrderItems_Records(void)
{
EXEC SQL BEGIN DECLARE SECTION ;
char sCommand[512];
EXEC SQL END DECLARE SECTION ;
dh_char_t *data[] = {
DH_STRING_LITERAL("(1,2,'1','1')"),
DH_STRING_LITERAL("(2,1,'1','2')"),
DH_STRING_LITERAL("(3,1,'1','3')"),
DH_STRING_LITERAL("(1,3,'2','3')")
};
int i;
int nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records(DH_STRING_LITERAL("ordritem"));
os_printf(DH_STRING_LITERAL("\tAdd records in table OrderItems…\n"));
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
os_sprintf (sCommand, DH_STRING_LITERAL("INSERT INTO ordritem VALUES %s"), data[i]);
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
}
EXEC SQL COMMIT WORK;
}
/*
* Add_ItemMaster_Records()
*
* This function adds records to table ItemMaster from an
* array of strings
*/
void Add_ItemMaster_Records(void)
{
EXEC SQL BEGIN DECLARE SECTION ;
char sCommand[512];
EXEC SQL END DECLARE SECTION ;
dh_char_t *data[] = {
DH_STRING_LITERAL("(10,19.95,'1','Hammer')"),
DH_STRING_LITERAL("(3, 9.99,'2','Wrench')"),
DH_STRING_LITERAL("(4, 16.59,'3','Saw')"),
DH_STRING_LITERAL("(1, 3.98,'4','Pliers')")
};
int i;
int nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records(DH_STRING_LITERAL("itemmast"));
os_printf(DH_STRING_LITERAL("\tAdd records in table ItemMaster...\n"));
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
os_sprintf (sCommand, DH_STRING_LITERAL("INSERT INTO itemmast VALUES %s"), data[i]);
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
}
EXEC SQL COMMIT WORK;
}
|
