Search the Site

Sponsors

bottom corner

Rollback Oracle transactions in VBScript

By default, VBScript runs Oracle statements in an auto-commit mode, meaning each transaction is immediately committed without offering you the ability to rollback the transaction. This note explains how you can turn off auto-commit, or in other words, allow your VBScript code to commit or rollback based on different situations.

This page is filed under keyword(s): oracle, 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."

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle, vbscript.
Author: C. Peter Chen
Last updated: 17 Mar 2009

bottom corner