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

}

TOCIndex