SQL::Connection::BeginTransaction()

Syntax

Result_Flag as L = <SQL::Connection>.BeginTransaction()

Argument	Description
Result_Flag	TRUE (.T.) if the operation was successful; otherwise FALSE (.F.).
<SQL::Connection>	A SQL::Connection object.

Description

The BeginTransaction() method begins a batch of SQL updates, inserts and/or deletes that need to succeed or fail as a group.

Supported By

Alpha Five Version 8 Enterprise Edition

Limitations

Desktop applications only.

Example

First, create and define variables. The select_cmd will select the records to copy. The delete_cmd will select the records to delete.

dim conn as sql::connection

dim select_cmd as C

dim delete_cmd as C

select_cmd = "select * from ProductA"

delete_cmd = "delete * from ProductA"

Establish the connection. If there is an error, display a message, and end the script.

if .not. conn.open("{A5API=Access,FileName='C:\Program Files\A5V8\MDBFiles\Alphasports.mdb',UserName='Admin'}") then

ui_msg_box("Error", "Could not establish connection" + crlf()+ conn.CallResult.text)

end

end if

Select the data to copy. If there is an error, display a message, and end the script.

if .not. conn.execute(select_cmd) then

ui_msg_box("Error", "Could not execute query" + crlf()+ conn.CallResult.text)

end

end if

If the records were established, it is time to begin the transaction with the BeginTransaction() method.

conn.BeginTransaction()

Copy records from ProductA to ProductB. If there is an error, roll back the transaction with the RollBackTransaction() method, display a message, and end the script.

if .not. conn.InsertData("", "ProductB", conn.ResultSet)

ui_msg_box("Error", "Could not insert data into ProductB")

conn.RollBackTransaction()

end

end if

Delete the records in ProductA. If there is an error, roll back the transaction, display a message, and end the script.

if .not. conn.execute(delete_cmd)

ui_msg_box("Error", "Could not delete data from ProductA" + crlf()+ conn.CallResult.text)

conn.RollBackTransaction()

end

end if

Finally, commit the transaction and close the connection.

conn.CommitTransaction()

conn.close()