Suriya Narayanan
Principal Consultant
Microsoft Consulting Services
Updated: May 1996
Distributed applications are becoming a reality. Transactions are the fundamental units of processing in applications, and just about any operation in an application can be considered as a transaction. Therefore, in a distributed applications environment, it is necessary to have the tools and techniques to create, destroy, monitor, and manage transactions. In addition, having these tools and techniques available within a unifying architecture such as OLE and on commodity hardware and software enables developing distributed applications using the component object model and using commodity software components.
This white paper explains writing applications using the Microsoft Distributed Transaction Coordinator Technology. A rudimentary knowledge of C, C++ programming, and transactions is assumed.
The word transaction is said to be derived from the phrase "transformation action." A transaction is an action or series of actions that transform the system from one consistent state to another. For example, if I go the bank and transfer $50 from my checking account to my savings account, then I had performed a transaction; in that process, I had transformed (changed) the balances of my checking and savings accounts. Transactions are characterized by their ACID (atomicity, consistency, isolation, durability) properties:
A Transaction Processing System can be viewed (see Figure 1) as consisting of three distinct components—application programs, the transaction manager, and resource managers. These different components are not necessarily confined to independent nodes on the network. All these components, including the transaction manager, can be distributed across multiple nodes on the network. Resource managers own the objects affected by the transactions and are responsible for the persistent storage of the resource objects. Resource managers also guarantee the ACID properties of the transactions within themselves. Examples of resource managers include database systems such as Microsoft® SQL Server™ and ORACLE, and file systems such as Object File System in Windows NT®. An application can be an independent, self contained component by itself (with every piece of functionality, business rules and logic), or it can be a collection of cooperating components.
Figure 1. Generalized view of a transaction processing system
Guaranteeing the ACID properties of the transaction is a relatively straightforward process when the actions of a transaction are confined to a single resource manager. Facilities such as logging and locking are examples of features provided by resource managers to guarantee the ACID properties. However, when more than one resource manager is involved in a transaction, guaranteeing the ACID properties is more involved and requires an additional protocol—the two-phase commit protocol. In the absence of a transaction manager, the application program must implement the two-phase commit protocol within itself. The protocol has two distinct phases—the Prepare phase and the Commit phase. The application program chooses a controlling agent—the Commit Agent. The Commit Agent can be one of the participating resource managers or another resource manager. The application program performs the actions on the resource managers normally. After completing the actions, the application enters the Prepare phase by sending a Prepare message to the resource managers. The resource managers record the fact that they are in Prepare phase and respond to the application program with Prepared messages. If, for some reason, any of the resource managers are not able to complete the Prepare phase, then the application must abort the transaction. After receiving Prepared messages from each of the participating resource managers, the application enters the Commit phase, by sending a Commit message to the Commit Agent. The Commit Agent records this fact. Then the application program must send the Commit message to each of the participating resource managers. If any of the participating resource managers fail to commit, then the application must abort the transaction in all the resource managers.
Figure 2. Two-phase commit protocol
The two-phase commit protocol is a complex sequence of operations, and as the number of participating resource managers increase, the complexity of the two-phase commit protocol increases exponentially. Therefore, it is very difficult to write good application programs obeying the two-phase commit protocol rules, when several resource managers are involved. Having the transaction manager implement the two-phase commit protocol and handle all the relevant coordination issues enables simpler and easier to implement applications.
The Microsoft Distributed Transaction Coordinator (MSDTC) is an exciting new technology from Microsoft that provides one of the main foundations for transaction processing. The Microsoft Distributed Transaction Coordinator is characterized by the following key capabilities:
Let us briefly examine the evolution of the application models. While the following treatise on the application models is not exact, it is a sufficient approximation to understand the application model when involving Distributed Transaction Coordinator.
In the beginning, there were (and still are) these single-tier, highly monolithic programs containing everything from the user interface definitions, the business logic, business rules, and database operations. These monolithic programs have very limited structure, except the programming structures offered by the programming language of their implementation. Some of the very early COBOL programs doing extremely mission critical operations fall under this category.
Figure 3. Evolution of the Application Model
As these early applications increased in number, size, and complexity, the connections to the databases from the application programs became too many and the connection management became expensive. A model involving a TP monitor evolved in order to optimally allocate the connections to the database and multiplex them across many application programs. This model assumed a central TP monitor such as CICS and IMS and divided the application into client and server parts. The server parts of the applications handled the database operations, business logic, and so forth, while the client parts of the applications handled the user interface using the dumb-terminal commands.
The arrival of intelligent personal computers and relational database systems propelled this application model into another variation—the client-server two-tier model—where parts of the application residing on the client PC control the user interfaces and interact with the other parts of the applications running on the database server. Usually, these parts of the application running on the database servers performed all the database operations, as well as some limited set of business logic and rules. Using products such as Tuxedo, transaction processing principles were applied to this model as well.
Three-tier models where the user interface, business logic, and database operations are confined to distinct layers of hardware and software evolved during this time as well. A good example of such an application model is the SAP business application software.
The object-oriented methods of design and development matured, and the concept of the application as a collection of cooperating objects was born. The maturity of the object model combined with the maturity of technologies, such as remote procedure calls, facilitated true distributed applications, where the cooperating objects of the application can be distributed across nodes in the network. The object-oriented model of the application does not confine the application objects to specific tiers. However, it is very convenient to classify the application objects into three different categories—user services, business services, and data services.
The Distributed Transaction Coordinator extends the object-oriented model of the application with objects required for transaction processing. DTC provides objects to create, destroy, manage, and monitor transaction objects. These transaction objects can in turn be operated upon by the application programs and resource managers. The application objects can use the services provided by the DTC objects, when transaction processing is desired by the application. Transactions are characterized by their ACID properties. The DTC objects guarantee the atomicity of the transactions when their actions span multiple resource managers using the two-phase commit protocol. The other characteristics—consistency, isolation, and durability—of the transaction are guaranteed by the resource managers participating in the transaction.
As discussed in the application model, the role of the Distributed Transaction Coordinator in an application is to guarantee the atomicity of the transactions. The transactions must be initiated by some other component of the application. The component that initiates the transaction will receive a pointer to the transaction; the component cannot pass this pointer to another component. Therefore, DTC requires that the transaction be completed by the same component that initiated it.
The two-tier model refers to the classical client-server model of programming, where a client PC provides the user interfaces for an application and interacts with other components on the database server. The business rules can be in the client application program, in the database server components such as stored procedures, or in both. The client application program uses APIs such as DBLIB or ODBC to communicate with the database server. While this is not the modern object-oriented model of the application, there are numerous applications that were written around this model. The Distributed Transaction Coordinator can be used in this model very effectively. When introducing DTC into this programming model, the transaction can be initiated and committed in two different places—the client application program and the database server stored procedure.
There are four general steps involved in processing a transaction when the client application program initiates the transaction:
In order to use DTC, the client application program must establish a connection to the DTC service. The DTC service can be running on either the same node as the application program or on another node on the network. Once the connection to the DTC is established, this instance of DTC becomes the primary DTC for this transaction. Other instances of DTC running on resource managers participating in the transaction become subordinate DTCs. As the application program begins and commits transactions, the primary and subordinate DTC exchange messages and cooperate to guarantee atomicity.
Opening a connection to the Primary DTC is done using the DtcGetTransactionManager helper API call. Using this helper API, the application program can obtain a pointer to the ITransactionDispenser interface. After opening a connection to the Primary DTC, the application program can then proceed with usual API calls to open ODBC or DBLIB connections to the resource managers. The application program can initiate the transaction using the BeginTransaction member function in the ITransactionDispenser interface, obtaining a pointer to the ITransaction interface. The pointer to the ITransaction interface represents the transaction object. The application program must enlist the participating resource managers to the transaction object in order to tie the transaction object and the resource managers together. The enlistment is done via the function dbenlisttrans for DBLIB and the SQLSetConnectOption function call for ODBC. After the enlistment of the participating resource managers is complete, the application can continue to send commands to the resource managers for actions within the transaction. When the application is ready to commit the transaction, it calls the Commit member function of the transaction object. Or, the Abort member function of the transaction object can be called to roll back or abort the transaction. The transaction and the transaction dispenser objects can be released at this time.
The programming model is simpler for stored procedure initiated transactions. The Microsoft SQL Server 6.5 Transasct SQL syntax has been enhanced to support the BEGIN DISTRIBUTED TRANSACTION statement. Microsoft SQL server recognizes this statement and performs the appropriate transaction initiation and enlistment steps on behalf of the application program. Any TRANSACT SQL commands enclosed by the BEGIN DISTRIBUTED TRANSACTION and the COMMIT TRANSACTION statements constitute the transaction. When it is necessary to involve another SQL Server (resource manager) in the transaction, remote stored procedure calls are used. When a remote stored procedure call to another SQL server is performed within a stored procedure, the SQL Server will automatically enlist the remote SQL server on the application program's behalf. SQL server will use the DTC services to commit the transaction when it executes COMMIT TRANSACTION.
In addition to using the BEGIN DISTRIBUTED TRANSACTION statement, the SQL server can be configured to use DTC services for the transaction every time a BEGIN TRANSACTION statement is encountered. All the remote stored procedure calls within a transaction will use DTC transaction services. The new server configuration option remote proc trans is introduced in SQL Server. Turning this option on by using the sp_configure system procedure will enable SQL Server to use DTC transaction services server-wide.
Application programs can also temporarily turn on using the DTC transaction services by using the SET REMOTE_PROCEDURE_TRANSACTIONS ON statement.
Having the database server or the resource manager stored procedures initiate the transactions reduce the likelihood of "in doubt" transactions. In doubt transactions result when the participating resource managers completed the Prepare phase, but they never hear back from the Commit Agent because either the Commit Agent itself may have crashed or the network might have disconnected. Since the resource managers can neither commit nor abort the transaction, the transaction status is in question; therefore, they are called in doubt transactions. Since a resource manager node is more likely to be running a DTC instance as well (as the resource managers usually run on more powerful hardware), it is very likely that the DTC instance running on the resource manager acts as the primary transaction coordinator. Having a local DTC instance as the primary coordinator reduces the number of DTC-DTC messages across the network and therefore can increase the performance throughput as well.
As discussed earlier, the Distributed Transaction Coordinator fits very well within the object-oriented model, where the application is viewed as a collection of cooperating objects, distributed across the nodes in the network. Using the convenient classification of these objects into user services, business services, and data services, the DTC objects are very likely to fit within the business services layer, although they provide supporting services such as creation, management, and coordination of transaction objects. Any application component can initiate a transaction, although it is very likely to be the business services object. The above discussions and programming models on client-initiated and server-initiated transactions are valid in the three-tier context as well.
A sample application using the Distributed Transaction Services can be implemented using two SQL servers, one managing a database of orders and the other managing a database of customers. The business problem is that whenever an order is entered, the order gets approved only if the customer has adequate credit limit and the credit limit should be updated after the order is approved. The business transaction and the application are shown in Figure 4.
Figure 4. Example application and transaction
This sample application is implemented by using the Distributed Transaction Coordinator Services using all the different programming models discussed earlier.
#define INITGUID
#include <txdtc.h>
#include <xolehlp.h>
#define DBNTWIN32
#include <stdio.h>
#include <windows.h>
#include <sqlfront.h>
#include <sqldb.h>
#include "OrderEntry.h"
// Forward declarations of the error handler and message handler.
int err_handler(PDBPROCESS, INT, INT, INT, LPCSTR, LPCSTR);
int msg_handler(PDBPROCESS, DBINT, INT, INT, LPCSTR, LPCSTR,
LPCSTR, DBUSMALLINT);
main(int argc, char **argv)
{
// Transaction Dispenser and Transaction Objects
ITransactionDispenser *pTransactionDispenser;
ITransaction *pTransaction;
HRESULT hr;
RETCODE rc;
PDBPROCESS pdb1, pdb2;
PLOGINREC login; // The login information.
ORDER_HEADER OrderHeader;
ORDER_LINE OrderLine[100];
long NumberLines = 0;
long CustomerNumber;
long OrderTotal;
// Open a connection to the Distributed Transaction Coordinator.
hr = DtcGetTransactionManager(
NULL, // DTC Host name - this host
NULL, // Transaction Manager Name
IID_ITransactionDispenser, // Interface needed
0, // Reserved
0, // Reserved
(void *)NULL, // Also Reserved
(void **)&pTransactionDispenser);
// Ptr to Interface
if (FAILED(hr))
{
fprintf (stderr, "Can't init Transaction Manager\n");
return (0);
}
// Initialize DB-Library.
dbinit ();
// Set up Error and Message handlers
dberrhandle (err_handler);
dbmsghandle (msg_handler);
login = dblogin (); // Get a LOGINREC.
DBSETLUSER (login, SQL_USER_ID);
DBSETLPWD (login, SQL_PASSWORD);
DBSETLAPP (login, "DTCApp");
// Open Connections to the Database servers
pdb1 = dbopen (login, SQL_SERVER_1);
pdb2 = dbopen (login, SQL_SERVER_2);
// Get the Order Data from the user. This is where the
// Order Entry UI will get involved. For the time being, it is
// a simple function which returns a set of pre-defined records
GetOrderRecords(&CustomerNumber, &OrderHeader, OrderLine, &NumberLines);
// Compute the Order totals
GetOrderTotals(OrderLine, NumberLines, &OrderTotal);
// Create a Transaction object using
// the BeginTransaction call.
hr = pTransactionDispenser->BeginTransaction (
NULL, // Controlling Iunknown Interface
ISOLATIONLEVEL_BROWSE, // Isolation level
0, // Isolation Flags
NULL, // Reserved
&pTransaction); // Ptr to Transaction Object
if (FAILED(hr))
{
fprintf (stderr, "Can't BeginTrans\n");
return(0);
}
// Enlist the SQL servers in the transaction
rc = dbenlisttrans(pdb1, pTransaction);
rc = dbenlisttrans(pdb2, pTransaction);
// Complete the Order Entry process
if (InsertSaleOrder(pdb1, pdb2, CustomerNumber, &OrderHeader,
OrderLine, NumberLines, OrderTotal) == FAIL)
{
// If the Order Entry does not succeed, then abort
// the transaction
pTransaction->Abort(FALSE, FALSE, NULL);
fprintf (stderr, "Transaction Aborted\n");
}
else
{
// Order entry is successful, Commit the transaction
pTransaction->Commit(0,0,0);
fprintf(stderr, "Transaction Committed\n");
}
// Close the Resource Manager connections
dbclose (pdb1);
dbclose (pdb2);
return (0);
}
// Get order data from possibly the user interface.
void GetOrderRecords (long *pCustomerNumber, ORDER_HEADER *pOrderHeader,
ORDER_LINE *pOrderLine, long *pNumberLines)
{
// Hard code the Order data for now.
*pCustomerNumber = 100;
pOrderHeader->OrderNumber = 102;
strcpy (pOrderHeader->OrderDate, "11/22/1995");
strcpy (pOrderHeader->ShipAddress1, "100 Main Street");
strcpy (pOrderHeader->ShipAddress2, "Bellevue");
strcpy (pOrderHeader->ShipState, "WA");
strcpy (pOrderHeader->ShipZip, "12345-111");
*pNumberLines = 5;
for (long i = 1; i <= *pNumberLines; i++)
{
pOrderLine->OrderNumber = 102;
pOrderLine->LineNumber = i;
pOrderLine->ProductNumber = 2 * i;
pOrderLine->Quantity = i;
pOrderLine->UnitPrice = 10 + i;
pOrderLine++;
}
}
// Compute the Order Total
void GetOrderTotals(ORDER_LINE *pOrderLine, long NumberLines, long *pOrderTotal)
{
*pOrderTotal = 0;
for (long i = 0 ; i < NumberLines ;i++)
{
*pOrderTotal += pOrderLine->Quantity *
pOrderLine->UnitPrice;
pOrderLine++;
}
}
// Query the Customer database server and retrieve the available
// credit
void GetAvailableCredit(long CustomerNumber, PDBPROCESS pDB, long *pAvailCredit)
{
RETCODE rc;
// Assemble the command line to retrieve the credit limit.
rc = dbfcmd (pDB,
"select AvailableCredit from Customer where CustomerNumber = %ld",
CustomerNumber);
// Run the command on the Customer database server
rc = dbsqlexec(pDB);
// Process the results and retrieve the credit limit
rc = dbresults(pDB);
// Bind column data to program variables
rc = dbbind(pDB, 1, INTBIND, (DBINT) 0, (BYTE *) pAvailCredit);
// Process rows. There should be only one row for this query.
while (dbnextrow(pDB) != NO_MORE_ROWS)
{
rc = dbnextrow(pDB);
}
rc = dbresults(pDB); // Should return NO_MORE_RESULTS
}
// Insert the Order Lines into the Orders database server and update
// the credit limit on the customer database server
RETCODE InsertSaleOrder(PDBPROCESS pdb1, PDBPROCESS pdb2,
long CustomerNumber, ORDER_HEADER *pOrderHeader,
ORDER_LINE *pOrderLine, long NumberLines, long OrderTotal)
{
RETCODE rc;
long AvailCredit;
// Reduce the available credit in the customer database server
// Assemble the command and send it.
dbfcmd (pdb2,
"update Customer set AvailableCredit = AvailableCredit - %ld where\
CustomerNumber = %ld\n",
OrderTotal, CustomerNumber);
// Run the command in the customer database server
rc = dbsqlexec(pdb2);
if (rc == FAIL)
{
return (FAIL);
}
// No results are expected, again.
while (dbresults(pdb2) != NO_MORE_RESULTS)
{
while (dbnextrow(pdb2) != NO_MORE_ROWS)
{
}
}
// To ensure that the customer has the credit
// read the credit limit and make sure it is
// greater than 0
GetAvailableCredit(CustomerNumber, pdb2, &AvailCredit);
if (AvailCredit <= 0L)
{
fprintf (stderr, "Not Enough Credit For the Order\n");
return (FAIL);
}
// Insert Header - Assemble the command line and send it to
// the server
rc = dbfcmd (pdb1,
"insert into OrderHeader(OrderNumber, OrderDate, \
CustomerNumber,ShipAddress1, ShipAddress2,\
ShipState, ShipZip) values(%ld, \'%s\',\
%ld,\'%s\',\'%s\',\'%s\',\'%s\')\n",
pOrderHeader->OrderNumber, pOrderHeader->OrderDate,
CustomerNumber, pOrderHeader->ShipAddress1,
pOrderHeader->ShipAddress2, pOrderHeader->ShipState,
pOrderHeader->ShipZip) ;
// Insert the detail lines. Assemble the command lines and
// send them
for (long i = 0; i <NumberLines ; i++)
{
rc = dbfcmd(pdb1,
"Insert into OrderLine(OrderNumber, LineNumber, \
ProductNumber, Quantity, UnitPrice) values(\
%ld, %ld, %ld, %ld, %ld)\n",
pOrderLine->OrderNumber,
pOrderLine->LineNumber,
pOrderLine->ProductNumber,
pOrderLine->Quantity,
pOrderLine->UnitPrice) ;
pOrderLine++;
}
// All the SQL command lines to enter the order has been
// sent. Run them.
rc = dbsqlexec(pdb1);
if (rc == FAIL)
{
return (FAIL);
}
// No results are expected from the server
while (dbresults(pdb1) != NO_MORE_RESULTS)
{
while (dbnextrow(pdb1) != NO_MORE_ROWS)
{
}
}
return (SUCCEED);
}
// Error and Message handler procedures for the DB Library
int err_handler (PDBPROCESS dbproc, INT severity,
INT dberr, INT oserr, LPCSTR dberrstr, LPCSTR oserrstr)
{
printf ("DB-Library Error %i: %s\n", dberr, dberrstr);
if (oserr != DBNOERR)
{
printf ("Operating System Error %i: %s\n", oserr, oserrstr);
}
return (INT_CANCEL);
}
int msg_handler (PDBPROCESS dbproc, DBINT msgno, INT msgstate,
INT severity, LPCSTR msgtext, LPCSTR server,
LPCSTR procedure, DBUSMALLINT line)
{
printf ("SQL Server Message %ld: %s\n", msgno, msgtext);
return (0);
}
Client Initiated Transactions Using ODBC
#define INITGUID
#include <txdtc.h>
#include <xolehlp.h>
#define DBNTWIN32
#include <stdio.h>
#include <windows.h>
#include <SQL.h>
#include <SQLEXT.h>
#include "odbcss.h"
#include <ODBCINST.h>
#include "OrderEntry.h"
void ProcessRC(LPTSTR pszFuncName, RETCODE rc, HDBC hDbc, HSTMT hStmt);
void DoSQLError(HDBC hDbc, HSTMT hStmt);
HENV hEnv = SQL_NULL_HENV;
main(int argc, char **argv)
{
// Declare the Transaction Dispenser and Transaction Objects
ITransactionDispenser *pTransactionDispenser;
ITransaction *pTransaction;
HRESULT hr;
HDBC hDbc1, hDbc2;
ORDER_HEADER OrderHeader;
ORDER_LINE OrderLine[100];
long NumberLines = 0;
long CustomerNumber,OrderTotal;
RETCODE rc = 0;
// Open a connection to the Distributed Transaction Coordinator
hr = DtcGetTransactionManager(
NULL, // DTC Host name - This Host
NULL, // DTC Name
IID_ITransactionDispenser, // Interface needed
0, // Reserved Params
0,
(void *)NULL,
(void **)&pTransactionDispenser);
// Ptr to Interface
if (FAILED(hr))
{
fprintf (stderr, "Can't init Transaction Manager\n");
return (0);
}
// Initialize ODBC
rc = SQLAllocEnv(&hEnv);
ProcessRC("SQLAllocEnv", rc, SQL_NULL_HDBC, SQL_NULL_HSTMT);
// Allocate Connections to the Database Servers
rc = SQLAllocConnect(hEnv, &hDbc1);
ProcessRC("SQLAllocConnect-1", rc, SQL_NULL_HDBC, SQL_NULL_HSTMT);
rc = SQLAllocConnect(hEnv, &hDbc2);
ProcessRC("SQLAllocConnect-2", rc, SQL_NULL_HDBC, SQL_NULL_HSTMT);
// Establish Connections to Database Servers
rc = SQLConnect(hDbc1, (unsigned char*)SQL_SERVER_1,
SQL_NTS,
(unsigned char *)SQL_USER_ID,
SQL_NTS,
(unsigned char *)SQL_PASSWORD,
SQL_NTS
);
ProcessRC("SQLConnect-1",rc, hDbc1, SQL_NULL_HSTMT);
rc = SQLConnect(hDbc2, (unsigned char *)SQL_SERVER_2,
SQL_NTS,
(unsigned char *)SQL_USER_ID,
SQL_NTS,
(unsigned char *)SQL_PASSWORD,
SQL_NTS
);
ProcessRC("SQLConnect-2",rc, hDbc2, SQL_NULL_HSTMT);
// Get the Order detail. This is the place for the UI to get
// involved. For the time being, hard code some generic
// order data
GetOrderRecords(&CustomerNumber, &OrderHeader, OrderLine,
&NumberLines);
// Compute Order total
GetOrderTotals(OrderLine, NumberLines, &OrderTotal);
// Create a Transaction Object using the BeginTransaction Call
hr = pTransactionDispenser->BeginTransaction (
NULL, // Controlling IUnknown
ISOLATIONLEVEL_BROWSE, // Isolation level
0, // Isolation Flags
NULL, // Reserved
&pTransaction); // Ptr to the Transaction Object
if (FAILED(hr))
{
fprintf (stderr, "Can't BeginTrans\n");
return(0);
}
// Enlist the SQL servers in the transaction
// Note the syntax difference from DBLIB
rc = SQLSetConnectOption(hDbc1, SQL_COPT_SS_ENLIST_IN_DTC,
UDWORD(pTransaction));
rc = SQLSetConnectOption(hDbc2, SQL_COPT_SS_ENLIST_IN_DTC,
UDWORD(pTransaction));
// Process the Order
if (InsertSaleOrder(hDbc1, hDbc2, CustomerNumber, &OrderHeader,
OrderLine, NumberLines, OrderTotal) == FALSE)
{
// If the Order entry failed for some reason, abort
// the transaction
pTransaction->Abort(FALSE, FALSE, NULL);
fprintf (stderr, "Transaction Aborted\n");
}
else
{
// Order entry was successful, commit the transaction.
pTransaction->Commit(0,0,0);
fprintf(stderr, "Transaction Committed\n");
}
// Free the ODBC connections to the databases
rc = SQLDisconnect(hDbc1);
rc = SQLDisconnect(hDbc2);
rc = SQLFreeConnect(hDbc1);
rc = SQLFreeConnect(hDbc2);
rc = SQLFreeEnv(hEnv);
return (0);
}
// Get the Order records. This is the place to involve the UI, but
// for the time being hard code the data in the code itself
void GetOrderRecords (long *pCustomerNumber, ORDER_HEADER *pOrderHeader, ORDER_LINE *pOrderLine, long *pNumberLines)
{
*pCustomerNumber = 100;
pOrderHeader->OrderNumber = 102;
strcpy (pOrderHeader->OrderDate, "11/22/1995");
strcpy (pOrderHeader->ShipAddress1, "100 Main Street");
strcpy (pOrderHeader->ShipAddress2, "Bellevue");
strcpy (pOrderHeader->ShipState, "WA");
strcpy (pOrderHeader->ShipZip, "12345-111");
*pNumberLines = 5;
for (long i = 1; i <= *pNumberLines; i++)
{
pOrderLine->OrderNumber = 102;
pOrderLine->LineNumber = i;
pOrderLine->ProductNumber = 2 * i;
pOrderLine->Quantity = i;
pOrderLine->UnitPrice = 10 + i;
pOrderLine++;
}
}
// Compute the Order totals
void GetOrderTotals(ORDER_LINE *pOrderLine, long NumberLines, long *pOrderTotal)
{
*pOrderTotal = 0;
for (long i = 0 ; i < NumberLines ;i++)
{
*pOrderTotal += pOrderLine->Quantity *
pOrderLine->UnitPrice;
pOrderLine++;
}
}
// Query the Customer Database Server for the customer's avialable
// credit limit
void GetAvailableCredit(long CustomerNumber, HDBC hDbc,
long *pAvailCredit)
{
RETCODE rc;
HSTMT hStmt;
char cmdBuf[500];
SDWORD cbAvailCredit;
rc = SQLAllocStmt(hDbc, &hStmt);
ProcessRC("SQLAllocStmt", rc, hDbc, hStmt);
// Assemble the command line and send it to the server
sprintf (cmdBuf,
"select AvailableCredit from Customer where CustomerNumber = %ld",
CustomerNumber );
rc = SQLExecDirect(hStmt, (UCHAR *)cmdBuf, SQL_NTS);
ProcessRC("SQLExecDirect", rc, hDbc, hStmt);
// Prepare to process the results from the server
rc = SQLBindCol(hStmt, 1, SQL_C_SLONG, pAvailCredit,0,
&cbAvailCredit);
ProcessRC("SQLBindCol", rc, hDbc, hStmt);
rc = SQLFetch(hStmt);
ProcessRC("SQLFetch", rc, hDbc, hStmt);
rc = SQLFreeStmt(hStmt, SQL_DROP);
}
// Perform the Order Entry transaction. Insert the Order data into
// the Order Database tables and reduce the customer's credit limit
// on the customer database server.
BOOL InsertSaleOrder(HDBC hDbc1, HDBC hDbc2, long CustomerNumber, ORDER_HEADER *pOrderHeader, ORDER_LINE *pOrderLine, long NumberLines, long OrderTotal)
{
RETCODE rc;
char cmdBuf[500];
HSTMT hStmt1, hStmt2;
long AvailCredit;
// Reduce the credit limit - assemble the SQL command for this
// and send it
rc = SQLAllocStmt(hDbc2, &hStmt2);
ProcessRC("SQLAllocStmt", rc, hDbc2, hStmt2);
sprintf (cmdBuf,
"update Customer set AvailableCredit = AvailableCredit - %ld where \
CustomerNumber = %ld\n",
OrderTotal, CustomerNumber);
rc = SQLExecDirect(hStmt2, (UCHAR *)cmdBuf, SQL_NTS);
if (rc == SQL_ERROR || rc == SQL_SUCCESS_WITH_INFO)
{
return (FALSE);
}
// To ensure that the customer has the
// credit, read the credit limit and make sure it is
// greater than 0
GetAvailableCredit(CustomerNumber, hDbc2, &AvailCredit);
if (AvailCredit <= 0L)
{
fprintf (stderr, "Not Enough Credit for the Order\n");
return (FALSE);
}
SQLFreeStmt(hStmt2, SQL_DROP);
rc = SQLAllocStmt(hDbc1, &hStmt1);
ProcessRC("SQLAllocStmt", rc, hDbc1, hStmt1);
// Insert Header - Assemble the commands and send it.
sprintf (
cmdBuf,
"insert into OrderHeader(OrderNumber, OrderDate, \
CustomerNumber,ShipAddress1, ShipAddress2,\
ShipState, ShipZip) values(%ld, \'%s\',\
%ld,\'%s\',\'%s\',\'%s\',\'%s\')\n",
pOrderHeader->OrderNumber, pOrderHeader->OrderDate,
CustomerNumber, pOrderHeader->ShipAddress1,
pOrderHeader->ShipAddress2, pOrderHeader->ShipState,
pOrderHeader->ShipZip) ;
rc = SQLExecDirect(hStmt1, (UCHAR *)cmdBuf, SQL_NTS);
if (rc == SQL_ERROR || rc == SQL_SUCCESS_WITH_INFO)
{
return (FALSE);
}
// Insert the line items - assemble the SQL commands and send it.
for (long i = 0; i <NumberLines ; i++)
{
sprintf (cmdBuf,
"Insert into OrderLine(OrderNumber, LineNumber, \
ProductNumber, Quantity, UnitPrice) values(\
%ld, %ld, %ld, %ld, %ld)\n",
pOrderLine->OrderNumber,
pOrderLine->LineNumber,
pOrderLine->ProductNumber,
pOrderLine->Quantity,
pOrderLine->UnitPrice) ;
rc = SQLExecDirect(hStmt1, (UCHAR *)cmdBuf, SQL_NTS);
if (rc == SQL_ERROR || rc == SQL_SUCCESS_WITH_INFO)
{
return (FALSE);
}
pOrderLine++;
}
SQLFreeStmt(hStmt1, SQL_DROP);
return (TRUE);
}
// Process the return code from the ODBC API functions
void ProcessRC(LPTSTR pszFuncName, RETCODE rc, HDBC hDbc, HSTMT hStmt)
{
switch (rc)
{
case SQL_SUCCESS:
printf("%s succeeded\n",pszFuncName);
break;
case SQL_SUCCESS_WITH_INFO:
printf("%s succeeded with info\n",pszFuncName);
DoSQLError(hDbc, hStmt);
break;
case SQL_ERROR:
printf("%s Failed - see more info\n",pszFuncName);
DoSQLError(hDbc, hStmt);
exit(-1); // need better cleanup mechanism later....
break;
case SQL_INVALID_HANDLE:
printf("%s Failed - SQL_INVALID_HANDLE\n",pszFuncName);
exit(-1); // need better cleanup mechanism later....
break;
case SQL_NO_DATA_FOUND:
printf("%s Failed - SQL_NO_DATA_FOUND\n",pszFuncName);
break;
case SQL_STILL_EXECUTING:
printf("%s Failed - SQL_STILL_EXECUTING\n",pszFuncName);
break;
case SQL_NEED_DATA:
printf("%s Failed - SQL_NEED_DATA\n",pszFuncName);
break;
default:
printf("%s Failed - unexpected error, rc = %x\n",pszFuncName,rc);
DoSQLError(hDbc, hStmt);
exit(-1); // need better cleanup mechanism later....
break;
}
}
void DoSQLError(HDBC hDbc, HSTMT hStmt)
{
const int MSG_BUF_SIZE = 300;
unsigned char szSqlState[MSG_BUF_SIZE];
unsigned char szErrorMsg[MSG_BUF_SIZE];
SQLINTEGER /* SWORD */ fNativeError = 0;
SWORD cbErrorMsg = MSG_BUF_SIZE;
RETCODE rc;
rc = SQLError(hEnv,hDbc,hStmt,szSqlState,&fNativeError,szErrorMsg,MSG_BUF_SIZE,&cbErrorMsg);
if (rc != SQL_NO_DATA_FOUND || rc != SQL_ERROR)
{
printf("SQLError info:\n");
printf("SqlState: %s, fNativeError: %x\n",szSqlState,fNativeError);
printf("Error Message: %s\n",szErrorMsg);
}
else
{
printf("SQLError() failed: %x, NO_DATA_FOUND OR SQL_ERROR\n",rc);
}
}
The client application program runs stored procedures on the Order Entry SQL Server. The stored procedures, in turn, will run other remote stored procedures on the Customer database SQL server as and when required. The client application program need not know anything about Distributed Transaction Coordinator at all. Since SQL Server stored procedures do not take arrays as parameters, it is necessary to hold the multiple order lines temporarily on the Order Entry SQL Server before processing the order. The order lines are held in a table temporarily. Each order line record is passed to the Order Entry SQL server using a stored procedure, which in turn loads them into the holding table.
#define INITGUID
#define DBNTWIN32
#include <stdio.h>
#include <windows.h>
#include <SQL.h>
#include <SQLEXT.h>
#include "odbcss.h"
#include <ODBCINST.h>
#include "OrderEntry.h"
void ProcessRC(LPTSTR pszFuncName, RETCODE rc, HDBC hDbc, HSTMT hStmt);
void DoSQLError(HDBC hDbc, HSTMT hStmt);
HENV hEnv = SQL_NULL_HENV;
main(int argc, char **argv)
{
HDBC hDbc;
ORDER_HEADER OrderHeader;
ORDER_LINE OrderLine[100];
long NumberLines = 0;
long CustomerNumber;
RETCODE rc = 0;
rc = SQLAllocEnv(&hEnv);
ProcessRC("SQLAllocEnv", rc, SQL_NULL_HDBC, SQL_NULL_HSTMT);
rc = SQLAllocConnect(hEnv, &hDbc);
ProcessRC("SQLAllocConnect-1", rc, SQL_NULL_HDBC, SQL_NULL_HSTMT);
// The client application program needs to open a connection
// only to to the Order Entry database server. This program
// does not even know that the order entry server updates
// the customer's credit limit on the customer database server
rc = SQLConnect(hDbc, (unsigned char*)SQL_SERVER_1,
SQL_NTS,
(unsigned char *)SQL_USER_ID,
SQL_NTS,
(unsigned char *)SQL_PASSWORD,
SQL_NTS
);
ProcessRC("SQLConnect-1",rc, hDbc, SQL_NULL_HSTMT);
// Get the Order data records. This is the place to have the
// UI involved
GetOrderRecords(&CustomerNumber, &OrderHeader, OrderLine, &NumberLines);
// Process the Order Entry transaction.
InsertSaleOrder(hDbc, CustomerNumber, &OrderHeader, OrderLine, NumberLines);
// Close connections to the database and exit
rc = SQLDisconnect(hDbc);
rc = SQLFreeConnect(hDbc);
rc = SQLFreeEnv(hEnv);
return (0);
}
// Process an order entry transaction by running appropriate
// stored procedures
void InsertSaleOrder(HDBC hDbc, long CustomerNumber, ORDER_HEADER *pOrderHeader, ORDER_LINE *pOrderLine, long NumberLines)
{
RETCODE rc;
char cmdBuf[500];
HSTMT hStmt;
rc = SQLAllocStmt(hDbc, &hStmt);
ProcessRC("SQLAllocStmt", rc, hDbc, hStmt);
// Insert the Order Lines into the work table in the database
// Pass the line items to the database server using a
// stored procedure call.
// Assemble the SQL command line to run the stored procedure
// and run it.
for (long i = 0; i <NumberLines ; i++)
{
sprintf (
cmdBuf,
"execute InsertOrderLineWorkTable %ld, %ld, %ld, %ld\n",
pOrderLine->LineNumber,
pOrderLine->ProductNumber,
pOrderLine->Quantity,
pOrderLine->UnitPrice
) ;
rc = SQLExecDirect(hStmt, (UCHAR *)cmdBuf, SQL_NTS);
ProcessRC("SQLExecDirect", rc, hDbc, hStmt);
if (rc == SQL_ERROR)
{
fprintf (stderr, "Error saving the Order Lines\n");
exit(0);
}
pOrderLine++;
}
// Run a stored procedure to process the order.
// Assemble the command line and run it.
sprintf (
cmdBuf,
"execute SaveOrder %ld, \'%s\', %ld, \'%s\',\'%s\',\'%s\',\'%s\'\n",
pOrderHeader->OrderNumber, pOrderHeader->OrderDate,
CustomerNumber, pOrderHeader->ShipAddress1,
pOrderHeader->ShipAddress2, pOrderHeader->ShipState,
pOrderHeader->ShipZip) ;
rc = SQLExecDirect(hStmt, (UCHAR *)cmdBuf, SQL_NTS);
ProcessRC("SQLExecDirect", rc, hDbc, hStmt);
if (rc == SQL_ERROR)
{
fprintf (stderr, "Error saving the Order Lines\n");
exit(0);
}
SQLFreeStmt(hStmt, SQL_DROP);
printf ("Transaction Committed");
}
/*
* Procedure InsertOrderLineWorkTable inserts an Order Line Item
* record into a work table for future use by the SaveOrder Procedure
*/
CREATE PROCEDURE InsertOrderLineWorkTable (
@LineNumber int,
@ProductNumber int,
@Quantity int,
@UnitPrice int
) AS
insert into TempOrderLine
(SPID, LineNumber, ProductNumber, Quantity, UnitPrice)
values (@@SPID, @LineNumber, @ProductNumber, @Quantity,
@UnitPrice)
GO
/*
*
* SaveOrder - Order Transaction Stored Procedure. Order Header elements * are passed as parameters while the line items are in the
* TempOrderLines Table identified by the Server process Id
*
*/
CREATE PROCEDURE SaveOrder
(
@OrderNumber int,
@OrderDate datetime,
@CustomerNumber int,
@ShipAddress1 char(40),
@ShipAddress2 char(40),
@ShipState char(2),
@ShipZip char(10)
) AS
declare @OrderTotal int
declare @AvailCredit int
declare @NewCredit int
/*
* BEGIN Distributed Transaction automatically initiates the
* transaction using the distributed transaction coordinator
*/
BEGIN DISTRIBUTED TRANSACTION
/* Compute the Order Total */
select @OrderTotal = SUM(Quantity * UnitPrice)
from TempOrderLine
where
SPID = @@SPID
/*
* Reduce the available credit for this customer by running
* a remote stored procedure on the Customer Database SQL server.
* MOUNTROAD is the name of the Customer Database SQL Server.
* Ensure that the Updated Credit Limit is > 0. Other transactions may
* have reduced the credit limit while this order was being processed.
* Since we had begun a DISTRIBUTED TRANSACTION, this stored
* procedure call and the update performed by the stored procedure
* will be part of the same transaction using two phase commit
* protocol.
*/
execute MOUNTROAD.TESTDB2.dbo.ReduceAvailableCredit
@CustomerNumber, @OrderTotal,
@NewCreditLimit = @NewCredit OUTPUT
if (@@ERROR != 0)
begin
ROLLBACK TRANSACTION
RAISERROR 32752 "Error updating credit limt"
RETURN
end
if (@NewCredit <= 0)
begin
ROLLBACK TRANSACTION
RAISERROR 32750 "Not enough credit for the order"
RETURN
end
/*
* Insert the Order header and detail records
*/
insert into OrderHeader
(OrderNumber, OrderDate, CustomerNumber, ShipAddress1,
ShipAddress2, ShipState, ShipZip)
values( @OrderNumber, @OrderDate, @CustomerNumber, @ShipAddress1,
@ShipAddress2, @ShipState, @ShipZip)
insert into OrderLine
select @OrderNumber, LineNumber, ProductNumber,
Quantity, UnitPrice
from TempOrderLine
where
SPID = @@SPID
if (@@ROWCOUNT = 0)
begin
ROLLBACK TRANSACTION
RAISERROR 32751 "No Line Items for this Order"
RETURN
end
delete from TempOrderLine where SPID = @@SPID
COMMIT TRANSACTION
RETURN
GO
/*
* Reduce the Available Credit for a given customer by a given amount
* and return the new credit limit
*/
CREATE PROCEDURE ReduceAvailableCredit (
@CustomerNumber int,
@ByAmount int,
@NewCreditLimit int OUTPUT
) AS
update Customer set AvailableCredit = AvailableCredit - @ByAmount
where CustomerNumber = @CustomerNumber
select @NewCreditLimit = AvailableCredit
from Customer
where CustomerNumber = @CustomerNumber
GO
CREATE TABLE dbo.Customer (
CustomerNumber int NOT NULL ,
CustomerName char (100) NOT NULL ,
CreditLimit money NOT NULL ,
AvailableCredit money NOT NULL
)
GO
CREATE TABLE dbo.OrderHeader (
OrderNumber int NOT NULL ,
OrderDate datetime NOT NULL ,
CustomerNumber int NOT NULL ,
ShipAddress1 char (40) NOT NULL ,
ShipAddress2 char (40) NOT NULL ,
ShipState char (2) NOT NULL ,
ShipZip char (10) NOT NULL
)
GO
CREATE TABLE dbo.OrderLine (
OrderNumber int NOT NULL ,
LineNumber int NOT NULL ,
ProductNumber int NOT NULL ,
Quantity int NOT NULL ,
UnitPrice int NULL
)
GO
CREATE TABLE dbo.TempOrderLine (
SPID int NOT NULL ,
LineNumber int NOT NULL ,
ProductNumber int NOT NULL ,
Quantity int NOT NULL ,
UnitPrice int NULL
)
GO
Gray, Jim, and Andreas Reuter. Transaction Processing. San Francisco, California: Morgan Kaufmann Publishers, 1993.
Microsoft Distributed Transaction Coordinator, version 0.1. Resource Manager Implementation Guide.