Manage a cross server transaction without configuring MSDTC

Usually managing a transaction across two servers would require a distributed transaction controller being setup between the two server.

Below is a scenario where this is managed without a MSTDC configution.

	UserConnection con = new Connection();
	Statement stmt = con.createStatement();
	Statement stmtUpdate = con.createStatement();
	ResultSet r;
	str sql;
	RPSmartParameters parameters;
	RPSmartInvoices rpSmartInvoicesTmp, rpSmartInvoicesInsert;
	Exception sysException;
	str errorText;
	int startLine, currentLine;
	SysInfoLogLevel prevLevel = infolog.infologLevel();
	SqlStatementExecutePermission perm, perm2;
	select firstOnly parameters;
	sql = strfmt('SELECT * FROM %1 where isnull( isAXUpdated, 0 ) = 0', parameters.CustInvoiceTable);
	perm = new SqlStatementExecutePermission(sql);
	perm.assert();
	try //main try block there are 2 try blocks nested below this to ensure all transactions execute consistently
	{
	rpSmartInvoicesTmp.setTmp();
	startLine = Global::infologLine();
	try
	{
	r = stmt.executeQuery(sql);
	while (r.next())
	{
	rpSmartInvoicesTmp.initValue();
	rpSmartInvoicesTmp.InvDetailsID = r.getString(1);
	rpSmartInvoicesTmp.CustCode = r.getString(2);
	rpSmartInvoicesTmp.Currency = r.getString(3);
	rpSmartInvoicesTmp.Amount = r.getReal(4);
	rpSmartInvoicesTmp.InvoiceNo = r.getString(5);
	rpSmartInvoicesTmp.InvoiceDate = r.getDate(6);
	rpSmartInvoicesTmp.Remarks = r.getString(7);
	rpSmartInvoicesTmp.InvoiceStartDate = r.getDate(8);
	rpSmartInvoicesTmp.InvoiceEndDate = r.getDate(9);
	rpSmartInvoicesTmp.DueDate = r.getDate(10);
	rpSmartInvoicesTmp.RevenueCategory = r.getString(11);
	rpSmartInvoicesTmp.Unit = r.getString(12);
	rpSmartInvoicesTmp.Precinct = r.getString(13);
	rpSmartInvoicesTmp.PaymentVoucher = r.getReal(14);
	rpSmartInvoicesTmp.insert();
	}
	}
	catch (exception::Error)
	{
	for (currentLine = startLine + 1; currentLine <= global::infologLine(); currentLine++)
	{
	errorText += infolog.text(currentLine);
	}
	infolog.clear();
	throw Exception::Error;
	}
	CodeAccessPermission::revertAssert();
	//flag the imported records shoudl only happen if there is no error above
	try
	{
	sql = strfmt('update A set A.isAXUpdated = 1, A.IntegrationDate = getdate() FROM %1 A', parameters.CustInvoiceTable);
	perm2 = new SqlStatementExecutePermission(sql);
	perm2.assert();
	stmtUpdate.executeUpdate(sql);
	}
	catch (exception::Error)
	{
	for (currentLine = startLine + 1; currentLine <= global::infologLine(); currentLine++)
	{
	errorText += infolog.text(currentLine);
	}
	infolog.clear();
	throw Exception::Error;
	}
	CodeAccessPermission::revertAssert();
	//move data to live table if the above update was successful
	while select rpSmartInvoicesTmp
	{
	rpSmartInvoicesInsert.data ( rpSmartInvoicesTmp.data() );
	rpSmartInvoicesInsert.insert();
	}
	}
	catch (Exception::Error)
	{
	for (currentLine = startLine + 1; currentLine <= global::infologLine(); currentLine++)
	{
	errorText += infolog.text(currentLine);
	}
	infolog.clear();
	info( errorText );
	throw Exception::Error;

view raw xpo hosted with ❤ by GitHub