|
|
|
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)
{
CTSQLRET rc;
CTSQLCHAR custname[47+1];
NUMERIC ntotal;
SMALLFLOAT ftotal;
pCTSQLCURSOR hCursor = NULL;
printf("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
*/
printf("\n\tQuery Results\n");
if ((rc = ctsqlPrepare(hCmd,
"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"
)) != CTSQLRET_OK)
Handle_Error("ctsqlPrepare(SELECT)");
if ((rc = ctsqlExecute(hCmd, &hCursor)) != CTSQLRET_OK)
Handle_Error("ctsqlExecute(SELECT)");
/* for each order in the CustomerOrders table */
while ((rc = ctsqlNext(hCursor)) == CTSQLRET_OK)
{
if ((rc = ctsqlGetNChar(hCursor, 0, custname)) != CTSQLRET_OK)
Handle_Error("ctsqlGetNChar()");
if ((rc = ctsqlGetMoney(hCursor, 1, &ntotal)) != CTSQLRET_OK)
Handle_Error("ctsqlGetReal()");
if ((rc = ctsqlConvert(CTSQL_MONEY, &ntotal, sizeof(ntotal),
NULL, CTSQL_REAL, &ftotal, sizeof(ftotal))) != CTSQLRET_OK)
Handle_Error("ctsqlConvert()");
/* output data to stdout */
printf("\t\t%-20s %.2f\n", custname, ftotal);
}
ctsqlFreeCursor(hCursor);
}
/*
* 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]);
Delete_Records("custmast");
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)");
}
}
/*
* Add_CustomerOrders_Records()
*
* This function adds records to table CustomerOrders from an
* array of strings
*/
void Add_CustomerOrders_Records(void)
{
CTSQLRET rc;
INTEGER i;
CTSQLCHAR sCommand[512];
CTSQLCHAR *data[] = {
"('09/01/2002','09/05/2002','1','1001')",
"('09/02/2002','09/06/2002','2','1002')"
};
INTEGER nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records("custordr");
printf("\tAdd records in table CustomerOrders...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
ctsql_strcpy (sCommand, "INSERT INTO custordr VALUES ");
ctsql_strcat (sCommand, data[i]);
if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(INSERT)");
}
}
/*
* Add_OrderItems_Records()
*
* This function adds records to table OrderItems from an
* array of strings
*/
void Add_OrderItems_Records(void)
{
CTSQLRET rc;
INTEGER i;
CTSQLCHAR sCommand[512];
CTSQLCHAR *data[] = {
"(1,2,'1','1')",
"(2,1,'1','2')",
"(3,1,'1','3')",
"(1,3,'2','3')"
};
INTEGER nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records("ordritem");
printf("\tAdd records in table OrderItems...\n");
/* add one record at time to table */
for (i = 0; i < nRecords; i++)
{
ctsql_strcpy (sCommand, "INSERT INTO ordritem VALUES ");
ctsql_strcat (sCommand, data[i]);
if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(INSERT)");
}
}
/*
* 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]);
Delete_Records("itemmast");
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)");
}
}
/*
* Delete_Records()
*
* This function deletes all the records in a table
*/
void Delete_Records(CTSQLCHAR* table)
{
CTSQLRET rc;
CTSQLCHAR sCommand[512];
printf("\tDelete records...\n");
ctsql_sprintf (sCommand, "DELETE FROM %s", table);
if ((rc = ctsqlExecuteDirect(hCmd, sCommand)) != CTSQLRET_OK)
Handle_Error("ctsqlExecuteDirect(DELETE)");
}
