Documentation

ODBC Driver Guide

Previous Topic

Next Topic

Manage

The manage step 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)

{

RETCODE rc;

SQLTCHAR custname[47+1];

SQLREAL total;

SDWORD cbData;

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 = SQLExecDirect(hStmt,

"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",

SQL_NTS)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");

/* for each order in the CustomerOrders table */

while ((rc = SQLFetch(hStmt)) == SQL_SUCCESS)

{

if ((rc = SQLGetData(hStmt, 1, SQL_C_CHAR, custname, sizeof(custname), &cbData)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLGetData()");

if ((rc = SQLGetData(hStmt, 2, SQL_C_FLOAT, &total, sizeof(total), &cbData)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLGetData()");

/* output data to stdout */

printf("\t\t%-20s %.2f\n", custname, total);

}

SQLFreeStmt(hStmt, SQL_CLOSE);

}

/*

* 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]);

Delete_Records("custmast");

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)");

}

}

/*

* Add_CustomerOrders_Records()

*

* This function adds records to table CustomerOrders from an

* array of strings

*/

void Add_CustomerOrders_Records(void)

{

RETCODE rc;

SQLINTEGER i;

SQLTCHAR sCommand[512];

SQLTCHAR *data[] = {

"('09/01/2002','09/05/2002','1','1001')",

"('09/02/2002','09/06/2002','2','1002')"

};

SQLINTEGER 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++)

{

strcpy (sCommand, "INSERT INTO custordr VALUES ");

strcat (sCommand, data[i]);

if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");

}

}

/*

* Add_OrderItems_Records()

*

* This function adds records to table OrderItems from an

* array of strings

*/

void Add_OrderItems_Records(void)

{

RETCODE rc;

SQLINTEGER i;

SQLTCHAR sCommand[512];

SQLTCHAR *data[] = {

"(1,2,'1','1')",

"(2,1,'1','2')",

"(3,1,'1','3')",

"(1,3,'2','3')"

};

SQLINTEGER 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++)

{

strcpy (sCommand, "INSERT INTO ordritem VALUES ");

strcat (sCommand, data[i]);

if ((rc = SQLExecDirect(hStmt, sCommand ,SQL_NTS)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(INSERT)");

}

}

/*

* 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]);

Delete_Records("itemmast");

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)");

}

}

/*

* Delete_Records()

*

* This function deletes all the records in a tables

*/

void Delete_Records(SQLTCHAR* table)

{

RETCODE rc;

SQLTCHAR sCommand[512];

printf("\tDelete records...\n");

sprintf (sCommand, "DELETE FROM %s", table);

if ((rc = SQLExecDirect(hStmt, sCommand, SQL_NTS)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(DELETE)");

}

TOCIndex