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.<\/p>\n
\noption explicit\ndim dbType, dbHost, dbName, dbUser, dbPass\n\ndbHost = \"hostname\" ' Hostname of the Oracle database server\ndbName = \"database\" ' Name of the database\/SID\ndbUser = \"username\" ' Name of the user\ndbPass = \"password\" ' Password of the above-named user\n\ndim conn\nset conn = CreateObject(\"ADODB.connection\")\nconn.ConnectionTimeout = 30\nconn.CommandTimeout = 30\nconn.open(\"Provider=MSDAORA.1;User ID=\" & dbUser & \";Password=\" & dbPass & \";Data Source=\" & dbName & \";Persist Security Info=False\")\n\nconn.BeginTrans\n\nconn.execute(\"update order set order_total=(select qty * unit_price from order_detail where order_id=123) where order_id=123\")\n\ndim checkOrderTotal\nset checkOrderTotal = conn.execute(\"select order_total from order where order_id=123\")\n\nif (checkOrderTotal(\"order_total\") < 0) then\n\tconn.RollbackTrans\n\t' TODO : Send an email to accounting department to check it out\nelse\n\tconn.CommitTrans\nend if\n\nconn.close\nset conn = nothing\n<\/pre>\nNotice 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.<\/p>\n
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.\"<\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20,11],"tags":[],"class_list":["post-113","post","type-post","status-publish","format-standard","hentry","category-oracle","category-vbscript"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/113","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=113"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/113\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=113"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}