|
|
|
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)
{
printf("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)
{
CTSQLRET rc;
if ((rc = ctsqlExecuteDirect(hCmd, "DROP TABLE ordritem")) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(DROP TABLE)");
if ((rc = ctsqlExecuteDirect(hCmd, "DROP TABLE custordr")) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(DROP TABLE)");
if ((rc = ctsqlExecuteDirect(hCmd, "DROP TABLE custmast")) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(DROP TABLE)");
if ((rc = ctsqlExecuteDirect(hCmd, "DROP TABLE itemmast")) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(DROP TABLE)");
}
/*
* Add_CustomerMaster_Records()
*
* This function adds records to table CustomerMaster from an
* array of strings
*/
void Add_CustomerMaster_Records(void)
{
CTSQLRET rc;
INTEGER i;
CTSQLCHAR sCommand[512];
CTSQLCHAR *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')"
};
INTEGER nRecords = sizeof(data) / sizeof(data[0]);
printf("\tAdd records in table CustomerMaster...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
ctsql_strcpy (sCommand, "INSERT INTO custmast VALUES ");
ctsql_strcat (sCommand, data[i]);
if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(INSERT)");
}
if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
Handle_Error("ctsqlCommit()");
}
/*
* Add_ItemMaster_Records()
*
* This function adds records to table ItemMaster from an
* array of strings
*/
void Add_ItemMaster_Records(void)
{
CTSQLRET rc;
INTEGER i;
CTSQLCHAR sCommand[512];
CTSQLCHAR *data[] = {
"(10,19.95,'1','Hammer')",
"(3, 9.99,'2','Wrench')",
"(4, 16.59,'3','Saw')",
"(1, 3.98,'4','Pliers')"
};
INTEGER nRecords = sizeof(data) / sizeof(data[0]);
printf("\tAdd records in table ItemMaster...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
ctsql_strcpy (sCommand, "INSERT INTO itemmast VALUES ");
ctsql_strcat (sCommand, data[i]);
if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(INSERT)");
}
if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
Handle_Error("ctsqlCommit()");
}
/*
* 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.
*/
void Add_Transactions(void)
{
typedef struct {
CTSQLCHAR *ordrdate, *promdate, *ordrnumb, *custnumb;
} ORDER_DATA;
typedef struct {
CTSQLCHAR *ordrnumb;
USMALLINT sequnumb;
USMALLINT quantity;
CTSQLCHAR *itemnumb;
} ORDERITEM_DATA;
ORDER_DATA orders[] = {
{"09/01/2002", "09/05/2002", "1", "1001"},
{"09/02/2002", "09/06/2002", "2", "9999"}, /* bad customer number */
{"09/22/2002", "09/26/2002", "3", "1003"}
};
INTEGER nOrders = sizeof(orders) / sizeof(ORDER_DATA);
ORDERITEM_DATA items[] = {
{"1", 1, 2, "1"},
{"1", 2, 1, "2"},
{"2", 1, 1, "3"},
{"2", 2, 3, "4"},
{"3", 1, 2, "3"},
{"3", 2, 2, "99"} /* bad item number */
};
INTEGER nItems = sizeof(items) / sizeof(ORDERITEM_DATA);
CTSQLRET rc;
INTEGER i, j = 0;
CTSQLCHAR sCommand[512];
printf("\tAdd transaction records... \n");
for (i = 0; i < nOrders; i++)
{
/* add order record */
sprintf(sCommand, "INSERT INTO custordr VALUES ('%s', '%s', '%s', '%s')",
orders[i].ordrdate,
orders[i].promdate,
orders[i].ordrnumb,
orders[i].custnumb);
if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(INSERT)");
/* process order items */
while (!(strcmp(items[j].ordrnumb, orders[i].ordrnumb)))
{
/* add item record */
sprintf(sCommand, "INSERT INTO ordritem VALUES (%d, %d, '%s', '%s')",
items[j].sequnumb,
items[j].quantity,
items[j].ordrnumb,
items[j].itemnumb);
if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(INSERT)");
/* bump to next item */
j++;
/* exit the while loop on last item */
if (j >= nItems)
break;
}
/* commit the transaction */
if ((rc = ctsqlCommit(hConn)) != CTSQLRET_OK)
Handle_Error("ctsqlCommit()");
}
}
/*
* Display_CustomerOrders()
*
* This function displays the contents of CustomerOrders table
*/
void Display_CustomerOrders(void)
{
CTSQLRET rc;
CTSQLCHAR ordrnumb[6+1], custnumb[4+1];
pCTSQLCURSOR hCursor = NULL;
printf("\n\tCustomerOrders Table...\n");
rc = ctsqlPrepare(hCmd, "SELECT * FROM custordr");
if (rc != CTSQLRET_OK)
Handle_Error("ctsqlPrepare(SELECT)");
rc = ctsqlExecute(hCmd, &hCursor);
if (rc != CTSQLRET_OK)
Handle_Error("ctsqlExecute(SELECT)");
/* fetch and display each individual record */
while ((rc = ctsqlNext(hCursor)) == CTSQLRET_OK)
{
ctsqlGetNChar(hCursor, 2, ordrnumb);
ctsqlGetNChar(hCursor, 3, custnumb);
printf("\t %s %s\n", ordrnumb, custnumb);
}
ctsqlFreeCursor(hCursor);
}
/*
* Display_OrderItems()
*
* This function displays the contents of OrderItems table
*/
void Display_OrderItems(void)
{
CTSQLRET rc;
CTSQLCHAR ordrnumb[6+1], itemnumb[5+1];
pCTSQLCURSOR hCursor = NULL;
printf("\n\tOrderItems Table...\n");
rc = ctsqlPrepare(hCmd, "SELECT * FROM ordritem");
if (rc != CTSQLRET_OK)
Handle_Error("ctsqlPrepare(SELECT)");
rc = ctsqlExecute(hCmd, &hCursor);
if (rc != CTSQLRET_OK)
Handle_Error("ctsqlExecute(SELECT)");
/* fetch and display each individual record */
while ((rc = ctsqlNext(hCursor)) == CTSQLRET_OK)
{
ctsqlGetNChar(hCursor, 2, ordrnumb);
ctsqlGetNChar(hCursor, 3, itemnumb);
printf("\t %s %s\n", ordrnumb, itemnumb);
}
ctsqlFreeCursor(hCursor);
}
