package
{
import flash.data.SQLConnection;
import flash.data.SQLResult;
import flash.data.SQLStatement;
import flash.display.Sprite;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;
import flash.filesystem.File;
public class MultiInsertTransactionExample extends Sprite
{
private var conn:SQLConnection;
private var insertEmployee:SQLStatement;
private var insertPhoneNumber:SQLStatement;
public function MultiInsertTransactionExample():void
{
// define where to find the database file
var appStorage:File = File.applicationStorageDirectory;
var dbFile:File = appStorage.resolvePath("ExampleDatabase.db");
// open the database connection
conn = new SQLConnection();
conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
conn.addEventListener(SQLEvent.OPEN, openHandler);
conn.openAsync(dbFile);
}
// Called when the database is connected
private function openHandler(event:SQLEvent):void
{
conn.removeEventListener(SQLEvent.OPEN, openHandler);
// start a transaction
conn.addEventListener(SQLEvent.BEGIN, beginHandler);
conn.begin();
}
// Called when the transaction begins
private function beginHandler(event:SQLEvent):void
{
conn.removeEventListener(SQLEvent.BEGIN, beginHandler);
// create and execute the first SQL statement:
// insert an employee record
insertEmployee = new SQLStatement();
insertEmployee.sqlConnection = conn;
insertEmployee.text =
"INSERT INTO employees (lastName, firstName, email) " +
"VALUES (:lastName, :firstName, :email, :birthday)";
insertEmployee.parameters[":lastName"] = "Smith";
insertEmployee.parameters[":firstName"] = "Bob";
insertEmployee.parameters[":email"] = "[email protected]";
insertEmployee.parameters[":birthday"] = new Date(1971, 8, 12);
insertEmployee.addEventListener(SQLEvent.RESULT, insertEmployeeHandler);
insertEmployee.addEventListener(SQLErrorEvent.ERROR, errorHandler);
insertEmployee.execute();
}
// Called after the employee record is inserted
private function insertEmployeeHandler(event:SQLEvent):void
{
insertEmployee.removeEventListener(SQLEvent.RESULT, insertEmployeeHandler);
insertEmployee.removeEventListener(SQLErrorEvent.ERROR, errorHandler);
// Get the employee id of the newly created employee row
var result:SQLResult = insertEmployee.getResult();
var employeeId:Number = result.lastInsertRowID;
// Add a phone number to the related phoneNumbers table
insertPhoneNumber = new SQLStatement();
insertPhoneNumber.sqlConnection = conn;
insertPhoneNumber.text =
"INSERT INTO phoneNumbers (employeeId, type, number) " +
"VALUES (:employeeId, :type, :number)";
insertPhoneNumber.parameters[":employeeId"] = employeeId;
insertPhoneNumber.parameters[":type"] = "Home";
insertPhoneNumber.parameters[":number"] = "(555) 555-1234";
insertPhoneNumber.addEventListener(SQLEvent.RESULT, insertPhoneNumberHandler);
insertPhoneNumber.addEventListener(SQLErrorEvent.ERROR, errorHandler);
insertPhoneNumber.execute();
}
// Called after the phone number record is inserted
private function insertPhoneNumberHandler(event:SQLEvent):void
{
insertPhoneNumber.removeEventListener(SQLEvent.RESULT, insertPhoneNumberHandler);
insertPhoneNumber.removeEventListener(SQLErrorEvent.ERROR, errorHandler);
// No errors so far, so commit the transaction
conn.addEventListener(SQLEvent.COMMIT, commitHandler);
conn.commit();
}
// Called after the transaction is committed
private function commitHandler(event:SQLEvent):void
{
conn.removeEventListener(SQLEvent.COMMIT, commitHandler);
trace("Transaction complete");
}
// Called whenever an error occurs
private function errorHandler(event:SQLErrorEvent):void
{
// If a transaction is happening, roll it back
if (conn.inTransaction)
{
conn.addEventListener(SQLEvent.ROLLBACK, rollbackHandler);
conn.rollback();
}
trace(event.error.message);
trace(event.error.details);
}
// Called when the transaction is rolled back
private function rollbackHandler(event:SQLEvent):void
{
conn.removeEventListener(SQLEvent.ROLLBACK, rollbackHandler);
// add additional error handling, close the database, etc.
}
}
}
