|
|
|
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();
}
/*
* 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[] = {
CT_STRING_LITERAL("('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')"),
CT_STRING_LITERAL("('1001','61434','CT','1','Michael Jordan','13 Main','Harford')"),
CT_STRING_LITERAL("('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')"),
CT_STRING_LITERAL("('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_snprintf (sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO custmast VALUES %") CT_PRINTF_STR, 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[] = {
CT_STRING_LITERAL("(10,19.95,'1','Hammer')"),
CT_STRING_LITERAL("(3, 9.99,'2','Wrench')"),
CT_STRING_LITERAL("(4, 16.59,'3','Saw')"),
CT_STRING_LITERAL("(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_snprintf (sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO itemmast VALUES %") CT_PRINTF_STR, 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[] = {
{CT_STRING_LITERAL("09/01/2002"), CT_STRING_LITERAL("09/05/2002"), CT_STRING_LITERAL("1"), CT_STRING_LITERAL("1001")},
{CT_STRING_LITERAL("09/02/2002"), CT_STRING_LITERAL("09/06/2002"), CT_STRING_LITERAL("2"), CT_STRING_LITERAL("9999")}, /* bad customer number */
{CT_STRING_LITERAL("09/22/2002"), CT_STRING_LITERAL("09/26/2002"), CT_STRING_LITERAL("3"), CT_STRING_LITERAL("1003")}
};
INTEGER nOrders = sizeof(orders) / sizeof(ORDER_DATA);
ORDERITEM_DATA items[] = {
{CT_STRING_LITERAL("1"), 1, 2, CT_STRING_LITERAL("1")},
{CT_STRING_LITERAL("1"), 2, 1, CT_STRING_LITERAL("2")},
{CT_STRING_LITERAL("2"), 1, 1, CT_STRING_LITERAL("3")},
{CT_STRING_LITERAL("2"), 2, 3, CT_STRING_LITERAL("4")},
{CT_STRING_LITERAL("3"), 1, 2, CT_STRING_LITERAL("3")},
{CT_STRING_LITERAL("3"), 2, 2, CT_STRING_LITERAL("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 */
ctsql_snprintf(sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO custordr VALUES ('%" CT_PRINTF_STR "', '%" CT_PRINTF_STR "', '%" CT_PRINTF_STR "', '%" CT_PRINTF_STR "')"),
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 (!(ctsql_strcmp(items[j].ordrnumb, orders[i].ordrnumb)))
{
/* add item record */
ctsql_snprintf(sCommand, sizeof(sCommand)/sizeof(*sCommand), CT_STRING_LITERAL("INSERT INTO ordritem VALUES (%d, %d, '%" CT_PRINTF_STR "', '%" CT_PRINTF_STR "')"),
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;
char ordrnumb[6+1], custnumb[4+1];
pCTSQLCURSOR hCursor = NULL;
printf("\n\tCustomerOrders Table...\n");
rc = ctsqlPrepare(hCmd, CT_STRING_LITERAL("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)
{
ctsqlGetChar(hCursor, 2, ordrnumb);
ctsqlGetChar(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;
char ordrnumb[6+1], itemnumb[5+1];
pCTSQLCURSOR hCursor = NULL;
printf("\n\tOrderItems Table...\n");
rc = ctsqlPrepare(hCmd, CT_STRING_LITERAL("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)
{
ctsqlGetChar(hCursor, 2, ordrnumb);
ctsqlGetChar(hCursor, 3, itemnumb);
printf("\t %s %s\n", ordrnumb, itemnumb);
}
ctsqlFreeCursor(hCursor);
}
