ADO Transactions

<%@ LANGUAGE = PerlScript%> ADO Transactions

ActiveX Data Objects (ADO) Transactions

This seemingly useless piece of code below is not that useless. It demonstrates the methods used to perform a database session in a transactional state. First, we set the Mode of the Connection object to a read/write mode, then BeginTrans() ignites the transaction. A query is executed and then the transaction is rolled back for fun by calling RollbackTrans(). Lastly, a call to CommitTrans() is made, and it does not do anything because the transaction has been rolled back. Something commonly misunderstood about transactions is that it will not automatically roll back if an error occurs -- you have to do error-checking and then tell the code to roll back on the occurance of an error. No changes made during the transaction are finalized or submitted until CommitTrans() is called, so if you remove the call to RollbackTrans(), you will see that an update occurs provided that you have the database settings configured for a database available on your system.

# Create an instance of the Connection object
#
$conn = $Server->CreateObject('ADODB.Connection');

# Open a SQL Server database
#
$conn->Open(<{Mode} = 3;

# Begin the transaction here
#
$conn->BeginTrans();

# Execute a query
#
$conn->Execute("INSERT into Employees VALUES ('Jdns')");

# Rollback the query
#
$conn->RollbackTrans();

# Transaction was ended with RollbackTrans, so this is not executed
#
$conn->CommitTrans();

$conn->Close(); # Close the connection
undef($conn);   # Destroy the object

Return