The key to allow transactions lies in the command “.BeginTrans” that you should declare immediately after making the database connection, as seen in the example below.
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass
dbHost = "hostname" ' Hostname of the Oracle database server
dbName = "database" ' Name of the database/SID
dbUser = "username" ' Name of the user
dbPass = "password" ' Password of the above-named user
dim conn
set conn = CreateObject("ADODB.connection")
conn.ConnectionTimeout = 30
conn.CommandTimeout = 30
conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
conn.BeginTrans
conn.execute("update order set order_total=(select qty * unit_price from order_detail where order_id=123) where order_id=123")
dim checkOrderTotal
set checkOrderTotal = conn.execute("select order_total from order where order_id=123")
if (checkOrderTotal("order_total") < 0) then
conn.RollbackTrans
' TODO : Send an email to accounting department to check it out
else
conn.CommitTrans
end if
conn.close
set conn = nothing
Notice the if-else clause near the bottom. If the order total somehow ended up a negative number, we rollback the transaction (and presumably would notify someone to check it out at this point), we roll back the transaction. Otherwise, as seen in the "else" clause, we proceed with committing the Oracle transaction.
It is important that you must issue either ".RollbackTrans" or ".CommitTrans" before the connection is terminated, otherwise you might get the VBScript ADODB connection error 800A0CAE, "Connection object cannot be explicitly closed while in transaction."