<faircom>\drivers\sql.odbc\tutorials\ODBCTutorial4.c
Now we will discuss transaction processing as it relates to the FairCom DB SQL ODBC Interface.
Transaction processing provides a safe method by which multiple database operations spread across separate tables/files are guaranteed to be atomic. By atomic, we mean that, within a transaction, either all of the operations succeed or none of the operations succeed. This "either all or none" atomicity ensures that the integrity of the data in related tables/files is secure.
Like all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done()!
Tutorial #4: Transaction Processing
Here we demonstrate transaction control.
- Initialize() - Connects to the FairCom Database Engine.
- Define() - Defines and creates our four tables/files.
- Manage() - Adds rows/records to multiple tables/files under transaction control.
- Done() - Disconnects from FairCom Database Engine.
Note our simple Main() function:
/*
* main()
*
* The main() function implements the concept of "init, define, manage
* and you're done..."
*/
int main(int argc, char* argv[])
{
Initialize();
Define();
Manage();
Done();
printf("\nPress <ENTER> key to exit . . .\n");
getchar();
return(0);
}
We suggest opening the source code with your own editor.
Continue now to review these four steps.
Init
First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.
Below is the code for Initialize():
/*
* Initialize()
*
* Perform the minimum requirement of logging onto the c-tree Server
*/
void Initialize(void)
{
RETCODE rc;
printf("INIT\n");
/* allocate environment handle */
if ((rc = SQLAllocEnv(&hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLAllocEnv()");
/* allocate connection handle */
if ((rc = SQLAllocConnect(hEnv, &hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLAllocConnect()");
/* connect to server */
printf("\tLogon to server...\n");
if ((rc = SQLConnect(hDbc, MY_DSN, SQL_NTS, "admin", SQL_NTS, "ADMIN", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLConnect()");
/* disable commit after each single SQL statement */
if ((rc = SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLSetConnectAttr(SQL_AUTOCOMMIT_OFF)");
/* allocate statement handle */
if ((rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");
}
Define
Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.
Below is the code for Define():
/*
* Define()
*
* Create the tables
*/
void Define(void)
{
RETCODE rc;
printf("DEFINE\n");
/* delete tables... */
Delete_Tables();
/* ...and re-create them with constraints */
Create_CustomerMaster_Table();
Create_ItemMaster_Table();
Create_CustomerOrders_Table();
Create_OrderItems_Table();
if ((rc = SQLExecDirect(hStmt, "COMMIT WORK", SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(COMMIT WORK)");
}
/*
* Create_CustomerMaster_Table()
*
* Create the table CustomerMaster
*/
void Create_CustomerMaster_Table(void)
{
RETCODE rc;
/* define table CustomerMaster */
printf("\ttable CustomerMaster\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE custmast ( \
cm_custnumb CHAR(4) PRIMARY KEY, \
cm_custzipc CHAR(9), \
cm_custstat CHAR(2), \
cm_custrtng CHAR(1), \
cm_custname VARCHAR(47), \
cm_custaddr VARCHAR(47), \
cm_custcity VARCHAR(47))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}
/*
* Create_CustomerOrders_Table()
*
* Create the table CustomerOrders
*/
void Create_CustomerOrders_Table(void)
{
RETCODE rc;
/* define table CustomerOrders */
printf("\ttable CustomerOrders\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE custordr ( \
co_ordrdate DATE, \
co_promdate DATE, \
co_ordrnumb CHAR(6) PRIMARY KEY, \
co_custnumb CHAR(4), \
FOREIGN KEY (co_custnumb) REFERENCES custmast)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}
/*
* Create_OrderItems_Table()
*
* Create the table OrderItems
*/
void Create_OrderItems_Table(void)
{
RETCODE rc;
/* define table OrderItems */
printf("\ttable OrderItems\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE ordritem ( \
oi_sequnumb SMALLINT, \
oi_quantity SMALLINT, \
oi_ordrnumb CHAR(6), \
oi_itemnumb CHAR(5), \
FOREIGN KEY (oi_itemnumb) REFERENCES itemmast, \
FOREIGN KEY (oi_ordrnumb) REFERENCES custordr)",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}
/*
* Create_ItemMaster_Table()
*
* Create the table ItemMaster
*/
void Create_ItemMaster_Table(void)
{
RETCODE rc;
/* define table ItemMaster */
printf("\ttable ItemMaster\n");
if ((rc = SQLExecDirect(hStmt,
"CREATE TABLE itemmast ( \
im_itemwght INTEGER, \
im_itempric MONEY, \
im_itemnumb CHAR(5) PRIMARY KEY, \
im_itemdesc VARCHAR(47))",
SQL_NTS)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");
}
Manage
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);
}
Done
When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.
Below is the code for Done():
/*
* Done()
*
* This function handles the housekeeping of closing connection and
* freeing of associated memory
*/
void Done(void)
{
RETCODE rc;
printf("DONE\n");
/* re-enable autocommit */
if ((rc = SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (void *)SQL_AUTOCOMMIT_ON, 0)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLSetConnectAttr(SQL_AUTOCOMMIT_ON)");
Delete_Tables();
/* free statement handles */
if ((rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLFreeHandle(SQL_HANDLE_STMT)");
/* disconnect from server */
printf("\tLogout...\n");
if ((rc = SQLDisconnect(hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLDisconnect()");
/* free connection handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc)) != SQL_SUCCESS)
Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLFreeHandle(SQL_HANDLE_DBC)");
/* free environment handle */
if ((rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv)) != SQL_SUCCESS)
Handle_Error(0, NULL, "SQLFreeHandle(SQL_HANDLE_ENV)");
}
Additional Resources
We encourage you to explore the additional resources listed here:
- Complete source code for this tutorial can be found in ODBCTutorial4.c in your installation directory, within the <faircom>\drivers\sql.odbc\tutorials directory for your platform.
- Additional documentation for other APIs and administering the FairCom DB Database Engine may be found in the Documentation section of the FairCom website.