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)
{
os_printf(DH_STRING_LITERAL("MANAGE\n"));
/* populate the tables with data */
Add_CustomerMaster_Records();
Add_ItemMaster_Records();
Add_Transactions();
/* display the orders and their items */
Display_CustomerOrders();
Display_OrderItems();
}
/*
* Delete_Tables()
*
* This function removes all existing tables
*/
void Delete_Tables(void)
{
EXEC SQL BEGIN DECLARE SECTION ;
char sCommand[512];
EXEC SQL END DECLARE SECTION ;
os_sprintf (sCommand, DH_STRING_LITERAL("DROP TABLE ordritem"));
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
os_sprintf (sCommand, DH_STRING_LITERAL("DROP TABLE custordr"));
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
os_sprintf (sCommand, DH_STRING_LITERAL("DROP TABLE itemmast"));
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
os_sprintf (sCommand, DH_STRING_LITERAL("DROP TABLE custmast"));
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
}
/*
* 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]);
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_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]);
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;
}
/*
* Add_Transactions()
*
* Add an Order and associated Items "as a transaction" to their
* respective tables. A transaction is committed or aborted if the
* customer number on the order is confirmed valid. Likewise each
* item in the order is verified to be a valid item.
*/
typedef struct {
dh_char_t *ordrdate, *promdate, *ordrnumb, *custnumb;
} ORDER_DATA;
typedef struct {
dh_char_t* ordrnumb;
short sequnumb;
short quantity;
dh_char_t* itemnumb;
} ORDERITEM_DATA;
ORDER_DATA orders[] = {
{DH_STRING_LITERAL("09/01/2002"), DH_STRING_LITERAL("09/05/2002"), DH_STRING_LITERAL("1"), DH_STRING_LITERAL("1001")},
{DH_STRING_LITERAL("09/02/2002"), DH_STRING_LITERAL("09/06/2002"), DH_STRING_LITERAL("2"), DH_STRING_LITERAL("9999")}, /* bad customer number */
{DH_STRING_LITERAL("09/22/2002"), DH_STRING_LITERAL("09/26/2002"), DH_STRING_LITERAL("3"), DH_STRING_LITERAL("1003")}
};
ORDERITEM_DATA items[] = {
{DH_STRING_LITERAL("1"), 1, 2, DH_STRING_LITERAL("1")},
{DH_STRING_LITERAL("1"), 2, 1, DH_STRING_LITERAL("2")},
{DH_STRING_LITERAL("2"), 1, 1, DH_STRING_LITERAL("3")},
{DH_STRING_LITERAL("2"), 2, 3, DH_STRING_LITERAL("4")},
{DH_STRING_LITERAL("3"), 1, 2, DH_STRING_LITERAL("3")},
{DH_STRING_LITERAL("3"), 2, 2, DH_STRING_LITERAL("99")} /* bad item number */
};
void Add_Transactions(void)
{
EXEC SQL BEGIN DECLARE SECTION ;
CHAR sCommand[512];
EXEC SQL END DECLARE SECTION ;
int i, j = 0;
int nOrders = sizeof(orders) / sizeof(ORDER_DATA);
int nItems = sizeof(items) / sizeof(ORDERITEM_DATA);
os_printf(DH_STRING_LITERAL("\tAdd transaction records... \n"));
for (i = 0; i < nOrders; i++)
{
/* add order record */
os_sprintf(sCommand, DH_STRING_LITERAL("INSERT INTO custordr VALUES ('%s', '%s', '%s', '%s')"),
orders[i].ordrdate,
orders[i].promdate,
orders[i].ordrnumb,
orders[i].custnumb);
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
/* process order items */
while (!(strcmp(items[j].ordrnumb, orders[i].ordrnumb)))
{
/* add item record */
os_sprintf(sCommand, DH_STRING_LITERAL("INSERT INTO ordritem VALUES (%d, %d, '%s', '%s')"),
items[j].sequnumb,
items[j].quantity,
items[j].ordrnumb,
items[j].itemnumb);
EXEC SQL EXECUTE IMMEDIATE :sCommand ;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
/* bump to next item */
j++;
/* exit the while loop on last item */
if (j >= nItems)
break;
}
EXEC SQL COMMIT WORK;
}
}
/*
* Display_CustomerOrders()
*
* This function displays the contents of CustomerOrders table
*/
void Display_CustomerOrders(void)
{
EXEC SQL BEGIN DECLARE SECTION ;
CHAR ordrnumb[6+1];
CHAR custnumb[4+1];
EXEC SQL END DECLARE SECTION ;
os_printf(DH_STRING_LITERAL("\n\tCustomerOrders Table...\n"));
EXEC SQL DECLARE curs CURSOR FOR
SELECT co_ordrnumb, co_custnumb FROM custordr;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
EXEC SQL OPEN curs;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
EXEC SQL FETCH curs INTO :ordrnumb, :custnumb;
/* fetch and display each individual record */
while (!sqlca.sqlcode)
{
os_printf(DH_STRING_LITERAL("\t %s %s\n"), ordrnumb, custnumb);
EXEC SQL FETCH curs INTO :ordrnumb, :custnumb;
}
EXEC SQL CLOSE curs ;
}
/*
* Display_OrderItems()
*
* This function displays the contents of OrderItems table
*/
void Display_OrderItems(void)
{
EXEC SQL BEGIN DECLARE SECTION ;
CHAR ordrnumb[6+1];
CHAR itemnumb[5+1];
EXEC SQL END DECLARE SECTION ;
os_printf(DH_STRING_LITERAL("\n\tOrderItems Table...\n"));
EXEC SQL DECLARE cur2 CURSOR FOR
SELECT oi_ordrnumb, oi_itemnumb FROM ordritem;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
EXEC SQL OPEN cur2;
if (sqlca.sqlcode)
Handle_Error(sqlca.sqlcode, sqlca.sqlerrm);
EXEC SQL FETCH cur2 INTO :ordrnumb, :itemnumb;
/* fetch and display each individual record */
while (!sqlca.sqlcode)
{
os_printf(DH_STRING_LITERAL("\t %s %s\n"), ordrnumb, itemnumb);
EXEC SQL FETCH cur2 INTO :ordrnumb, :itemnumb;
}
EXEC SQL CLOSE cur2 ;
}
|
