|
|
|
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
/*
* Manage()
*
* Populates table and performs 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)
{
RETCODE rc;
if ((rc = SQLExecDirect(hStmt, "DROP TABLE ordritem", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");
if ((rc = SQLExecDirect(hStmt, "DROP TABLE custordr", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");
if ((rc = SQLExecDirect(hStmt, "DROP TABLE custmast", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");
if ((rc = SQLExecDirect(hStmt, "DROP TABLE itemmast", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DROP TABLE)");
}
/*
* 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 {
SQLTCHAR *ordrdate, *promdate, *ordrnumb, *custnumb;
} ORDER_DATA;
typedef struct {
SQLTCHAR *ordrnumb;
SQLUSMALLINT sequnumb;
SQLUSMALLINT quantity;
SQLTCHAR *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"}
};
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 */
};
void Add_Transactions(void)
{
RETCODE rc;
SQLINTEGER i, j = 0;
SQLINTEGER nOrders = sizeof(orders) / sizeof(ORDER_DATA);
SQLINTEGER nItems = sizeof(items) / sizeof(ORDERITEM_DATA);
SQLTCHAR 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 = SQLExecDirect(hStmt, sCommand, SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(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 = SQLExecDirect(hStmt, sCommand , SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");
/* bump to next item */
j++;
/* exit the while loop on last item */
if (j >= nItems)
break;
}
/* commit the transaction */
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
}
/*
* 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]);
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)");
}
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
* 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]);
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)");
}
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
* Display_CustomerOrders()
*
* This function displays the contents of CustomerOrders table
*/
void Display_CustomerOrders(void)
{
RETCODE rc;
SQLTCHAR ordrnumb[6+1], custnumb[4+1];
SDWORD cbData;
printf("\n\tCustomerOrders Table...\n");
rc = SQLExecDirect(hStmt, "SELECT * FROM custordr" ,SQL_NTS);
if (rc != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");
/* fetch and display each individual record */
while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)
{
SQLGetData(hStmt, 3, SQL_C_CHAR, ordrnumb, sizeof(ordrnumb), &cbData);
SQLGetData(hStmt, 4, SQL_C_CHAR, custnumb, sizeof(custnumb), &cbData);
printf("\t %s %s\n", ordrnumb, custnumb);
}
SQLFreeStmt(hStmt,SQL_CLOSE);
}
/*
* Display_OrderItems()
*
* This function displays the contents of OrderItems table
*/
void Display_OrderItems(void)
{
RETCODE rc;
SQLTCHAR ordrnumb[6+1], itemnumb[5+1];
SDWORD cbData;
printf("\n\tOrderItems Table...\n");
rc = SQLExecDirect(hStmt, "SELECT * FROM ordritem" ,SQL_NTS);
if (rc != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");
/* fetch and display each individual record */
while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)
{
SQLGetData(hStmt, 3, SQL_C_CHAR, ordrnumb, sizeof(ordrnumb), &cbData);
SQLGetData(hStmt, 4, SQL_C_CHAR, itemnumb, sizeof(itemnumb), &cbData);
printf("\t %s %s\n", ordrnumb, itemnumb);
}
SQLFreeStmt(hStmt,SQL_CLOSE);
}
