|
|
|
Manage() provides data management functionality for your application and/or process.
/*
* 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]);
Delete_Records(CT_STRING_LITERAL("custmast"));
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)");
}
}
/*
* 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[] = {
CT_STRING_LITERAL("('09/01/2002','09/05/2002','1','1001')"),
CT_STRING_LITERAL("('09/02/2002','09/06/2002','2','1002')")
};
INTEGER nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records(CT_STRING_LITERAL("custordr"));
printf("\tAdd records in table CustomerOrders...\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 custordr VALUES %") CT_PRINTF_STR, 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[] = {
CT_STRING_LITERAL("(1,2,'1','1')"),
CT_STRING_LITERAL("(2,1,'1','2')"),
CT_STRING_LITERAL("(3,1,'1','3')"),
CT_STRING_LITERAL("(1,3,'2','3')")
};
INTEGER nRecords = sizeof(data) / sizeof(data[0]);
Delete_Records(CT_STRING_LITERAL("ordritem"));
printf("\tAdd records in table OrderItems...\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 ordritem VALUES %") CT_PRINTF_STR, 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[] = {
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]);
Delete_Records(CT_STRING_LITERAL("itemmast"));
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)");
}
}
Below is the code for Manage():
/*
* Manage()
*
* Populates table and perform a simple query
*
*/
#ifdef PROTOTYPE
VOID Manage(VOID)
#else
VOID Manage()
#endif
{
CTDBRET retval;
CTSIGNED quantity;
CTFLOAT price, total;
TEXT itemnumb[5+1], custnumb[4+1], ordrnumb[6+1], custname[47+1];
CTBOOL isOrderFound, isItemFound;
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");
/* get the first order */
if (ctdbFirstRecord(hRecordCustOrdr))
Handle_Error("Manage(): ctdbFirstRecord()");
isOrderFound = YES;
while (isOrderFound) /* for each order in the CustomerOrders table */
{
/* fetch order number */
retval = ctdbGetFieldAsString(hRecordCustOrdr, 2, ordrnumb, sizeof(ordrnumb));
/* fetch customer number */
retval |= ctdbGetFieldAsString(hRecordCustOrdr, 3, custnumb, sizeof(custnumb));
if (retval)
Handle_Error("Manage(): ctdbGetFieldAsString()");
/* fetch name from CustomerMaster table based on customer number */
if (ctdbClearRecord(hRecordCustMast))
Handle_Error("Manage(): ctdbClearRecord()");
if (ctdbSetFieldAsString(hRecordCustMast, 0, custnumb))
Handle_Error("Manage(): ctdbSetFieldAsString()");
if (ctdbFindRecord(hRecordCustMast, CTFIND_EQ))
Handle_Error("Manage(): ctdbFindRecord()");
if (ctdbGetFieldAsString(hRecordCustMast, 4, custname, sizeof(custname)))
Handle_Error("Manage(): ctdbGetFieldAsString()");
/* fetch item price from OrderItems table */
if (ctdbClearRecord(hRecordOrdrItem))
Handle_Error("Manage(): ctdbClearRecord()");
if (ctdbSetFieldAsString(hRecordOrdrItem, 2, ordrnumb))
Handle_Error("Manage(): ctdbSetFieldAsString()");
/* define a recordset to scan only items applicable to this order */
if (ctdbRecordSetOn(hRecordOrdrItem, 6))
Handle_Error("Manage(): ctdbRecordSetOn()");
if (ctdbFirstRecord(hRecordOrdrItem))
Handle_Error("Manage(): ctdbFirstRecord()");
isItemFound = YES;
total = 0;
while (isItemFound) /* for each order item in OrderItems table */
{
/* fetch item quantity */
if (ctdbGetFieldAsSigned(hRecordOrdrItem, 1, &quantity))
Handle_Error("Manage(): ctdbGetFieldAsSigned()");
/* fetch item number */
if (ctdbGetFieldAsString(hRecordOrdrItem, 3, itemnumb, sizeof(itemnumb)))
Handle_Error("Manage(): ctdbGetFieldAsString()");
/* fetch item price from ItemMaster table based on item number */
if (ctdbClearRecord(hRecordItemMast))
Handle_Error("Manage(): ctdbClearRecord()");
if (ctdbSetFieldAsString(hRecordItemMast, 2, itemnumb))
Handle_Error("Manage(): ctdbSetFieldAsString()");
if (ctdbFindRecord(hRecordItemMast, CTFIND_EQ))
Handle_Error("Manage(): ctdbFindRecord()");
if (ctdbGetFieldAsFloat(hRecordItemMast, 1, &price))
Handle_Error("Manage(): ctdbGetFieldAsFloat()");
/* calculate order total */
total += (price * quantity);
/* read next record */
retval = ctdbNextRecord(hRecordOrdrItem);
if (retval != CTDBRET_OK)
{
if (retval == END_OF_FILE)
isItemFound = NO;
else
Handle_Error("Manage(): ctdbNextRecord()");
}
}
ctdbRecordSetOff(hRecordOrdrItem);
/* output data to stdout */
printf("\t\t%-20s %.2f\n", custname, total);
/* read next order */
retval = ctdbNextRecord(hRecordCustOrdr);
if (retval != CTDBRET_OK)
{
if (retval == END_OF_FILE)
isOrderFound = NO;
else
Handle_Error("Manage(): ctdbNextRecord()");
}
}
}
/*
* Add_CustomerMaster_Records()
*
* This function adds records to table CustomerMaster from an
* array of strings
*/
typedef struct {
CTSTRING number, zipcode, state, rating, name, address, city;
} CUSTOMER_DATA;
CUSTOMER_DATA data4[] = {
"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"
};
#ifdef PROTOTYPE
VOID Add_CustomerMaster_Records(VOID)
#else
VOID Add_CustomerMaster_Records()
#endif
{
CTDBRET retval;
CTSIGNED i;
CTSIGNED nRecords = sizeof(data4) / sizeof(CUSTOMER_DATA);
if ((hRecordCustMast = ctdbAllocRecord(hTableCustMast)) == NULL)
Handle_Error("Add_Customer_Records(): ctdbAllocRecord()");
Delete_Records(hRecordCustMast);
printf("\tAdd records in table CustomerMaster...\n");
/* add records to table */
for (i = 0; i < nRecords; i++)
{
/* clear record buffer */
ctdbClearRecord(hRecordCustMast);
retval = 0;
/* populate record buffer with data */
retval |= ctdbSetFieldAsString(hRecordCustMast, 0, data4[i].number);
retval |= ctdbSetFieldAsString(hRecordCustMast, 1, data4[i].zipcode);
retval |= ctdbSetFieldAsString(hRecordCustMast, 2, data4[i].state);
retval |= ctdbSetFieldAsString(hRecordCustMast, 3, data4[i].rating);
retval |= ctdbSetFieldAsString(hRecordCustMast, 4, data4[i].name);
retval |= ctdbSetFieldAsString(hRecordCustMast, 5, data4[i].address);
retval |= ctdbSetFieldAsString(hRecordCustMast, 6, data4[i].city);
if(retval)
Handle_Error("Add_Customer_Records(): ctdbSetFieldAsString()");
/* add record */
if (ctdbWriteRecord(hRecordCustMast))
Handle_Error("Add_Customer_Records(): ctdbWriteRecord()");
}
}
/*
* Add_CustomerOrders_Records()
*
* This function adds records to table CustomerOrders from an
* array of strings
*/
typedef struct {
CTSTRING orderdate, promisedate, ordernum, customernum;
} ORDER_DATA;
ORDER_DATA data1[] = {
{"09/01/2002", "09/05/2002", "1", "1001"},
{"09/02/2002", "09/06/2002", "2", "1002"}
};
#ifdef PROTOTYPE
VOID Add_CustomerOrders_Records(VOID)
#else
VOID Add_CustomerOrders_Records()
#endif
{
CTDBRET retval;
CTSIGNED i;
CTSIGNED nRecords = sizeof(data1) / sizeof(ORDER_DATA);
CTDATE orderdate;
CTDATE promisedate;
if ((hRecordCustOrdr = ctdbAllocRecord(hTableCustOrdr)) == NULL)
Handle_Error("Add_CustomerOrders_Records(): ctdbAllocRecord()");
Delete_Records(hRecordCustOrdr);
printf("\tAdd records in table CustomerOrders...\n");
/* add records to table */
for (i = 0; i < nRecords; i++)
{
/* clear record buffer */
ctdbClearRecord(hRecordCustOrdr);
retval = 0;
retval |= ctdbStringToDate(data1[i].orderdate, CTDATE_MDCY ,&orderdate);
retval |= ctdbStringToDate(data1[i].promisedate, CTDATE_MDCY, &promisedate);
/* populate record buffer with data */
retval |= ctdbSetFieldAsDate(hRecordCustOrdr, 0, orderdate);
retval |= ctdbSetFieldAsDate(hRecordCustOrdr, 1, promisedate);
retval |= ctdbSetFieldAsString(hRecordCustOrdr, 2, data1[i].ordernum);
retval |= ctdbSetFieldAsString(hRecordCustOrdr, 3, data1[i].customernum);
if (retval)
Handle_Error("Add_CustomerOrders_Records(): ctdbSetFieldAsString()|ctdbSetFieldAsDate()");
/* add record */
if (ctdbWriteRecord(hRecordCustOrdr))
Handle_Error("Add_CustomerOrders_Records(): ctdbWriteRecord()");
}
}
/*
* Add_OrderItems_Records()
*
* This function adds records to table OrderItems from an
* array of strings
*/
typedef struct {
COUNT sequencenum, quantity;
CTSTRING ordernum, itemnum;
} ORDERITEM_DATA;
ORDERITEM_DATA data2[] = {
{1, 2, "1", "1"},
{2, 1, "1", "2"},
{3, 1, "1", "3"},
{1, 3, "2", "3"}
};
#ifdef PROTOTYPE
VOID Add_OrderItems_Records(VOID)
#else
VOID Add_OrderItems_Records()
#endif
{
CTDBRET retval;
CTSIGNED i;
CTSIGNED nRecords = sizeof(data2) / sizeof(ORDERITEM_DATA);
if ((hRecordOrdrItem = ctdbAllocRecord(hTableOrdrItem)) == NULL)
Handle_Error("Add_OrderItems_Records(): ctdbAllocRecord()");
Delete_Records(hRecordOrdrItem);
printf("\tAdd records in table OrderItems...\n");
/* add records to table */
for (i = 0; i < nRecords; i++)
{
/* clear record buffer */
ctdbClearRecord(hRecordOrdrItem);
retval = 0;
/* populate record buffer with data */
retval |= ctdbSetFieldAsSigned(hRecordOrdrItem, 0, data2[i].sequencenum);
retval |= ctdbSetFieldAsSigned(hRecordOrdrItem, 1, data2[i].quantity);
retval |= ctdbSetFieldAsString(hRecordOrdrItem, 2, data2[i].ordernum);
retval |= ctdbSetFieldAsString(hRecordOrdrItem, 3, data2[i].itemnum);
if(retval)
Handle_Error("Add_OrderItems_Records(): ctdbSetFieldAsString()");
/* add record */
if (ctdbWriteRecord(hRecordOrdrItem))
Handle_Error("Add_OrderItems_Records(): ctdbWriteRecord()");
}
}
/*
* Add_ItemMaster_Records()
*
* This function adds records to table ItemMaster from an
* array of strings
*/
typedef struct {
CTSIGNED weight;
CTMONEY price;
CTSTRING itemnum, description;
} ITEM_DATA;
ITEM_DATA data3[] = {
{10, 1995, "1", "Hammer"},
{3, 999, "2", "Wrench"},
{4, 1659, "3", "Saw"},
{1, 398, "4", "Pliers"}
};
#ifdef PROTOTYPE
VOID Add_ItemMaster_Records(VOID)
#else
VOID Add_ItemMaster_Records()
#endif
{
CTDBRET retval;
CTSIGNED i;
CTSIGNED nRecords = sizeof(data3) / sizeof(ITEM_DATA);
if ((hRecordItemMast = ctdbAllocRecord(hTableItemMast)) == NULL)
Handle_Error("Add_ItemMaster_Records(): ctdbAllocRecord()");
Delete_Records(hRecordItemMast);
printf("\tAdd records in table ItemMaster...\n");
/* add records to table */
for (i = 0; i < nRecords; i++)
{
/* clear record buffer */
ctdbClearRecord(hRecordItemMast);
retval = 0;
/* populate record buffer with data */
retval |= ctdbSetFieldAsSigned(hRecordItemMast, 0, data3[i].weight);
retval |= ctdbSetFieldAsMoney(hRecordItemMast, 1, data3[i].price);
retval |= ctdbSetFieldAsString(hRecordItemMast, 2, data3[i].itemnum);
retval |= ctdbSetFieldAsString(hRecordItemMast, 3, data3[i].description);
if(retval)
Handle_Error("Add_ItemMaster_Records(): ctdbSetFieldAsString()|ctdbSetFieldAsSigned()");
/* add record */
if (ctdbWriteRecord(hRecordItemMast))
Handle_Error("Add_ItemMaster_Records(): ctdbWriteRecord()");
}
}
/*
* Delete_Records()
*
* This function deletes all the records in the table based
* on the input parameter
*/
#ifdef PROTOTYPE
VOID Delete_Records(CTHANDLE hRecord)
#else
VOID Delete_Records(hRecord)
CTHANDLE hRecord;
#endif
{
CTDBRET retval;
CTBOOL empty;
printf("\tDelete records...\n");
if (ctdbClearRecord(hRecord))
Handle_Error("Delete_Records(): ctdbClearRecord()");
empty = NO;
retval = ctdbFirstRecord(hRecord);
if (retval != CTDBRET_OK)
{
if (retval == END_OF_FILE)
empty = YES;
else
Handle_Error("Delete_Records(): ctdbFirstRecord()");
}
while (empty == NO) /* while table is not empty */
{
/* delete record */
if (ctdbDeleteRecord(hRecord))
Handle_Error("Delete_Records(): ctdbDeleteRecord()");
/* read next record */
retval = ctdbNextRecord(hRecord);
if (retval != CTDBRET_OK)
{
if (retval == END_OF_FILE)
empty = YES;
else
Handle_Error("Delete_Records(): ctdbNextRecord()");
}
}
}
