Rollback Oracle transactions in VBScript

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."

Leave a Reply

Your email address will not be published.