Flex / Air sqlite async issue

Question

Hi,

I am writing an app that parses a csv file to an array and then insert the array into a sqlite database. I am having issues with Async connection to the sqlite database, I get a 3106 error...... I think the problem is that it executes the next statement before the previous is finished but I can't find a way to deal with this. Any help would be greatly appreciated.

public function addData(categories:Array):void{
       status = "Adding data to table";
       var insrtStmt:SQLStatement = new SQLStatement();
        insrtStmt.sqlConnection = conn;


         for(var i:int=categories.length-1; i>=0; i--){
             insrtStmt.text = "";
             insrtStmt.text += "INSERT INTO masterlist ";
             insrtStmt.text += "(mainid, transactionDate, tradeId, ccyPair, account, buySell, customer, date,"; 
             insrtStmt.text += " additionalid, dealType, traderName, genericType, owner) ";
               insrtStmt.text += "VALUES(@mainid, @transactionDate, @tradeId, @ccyPair, @account, @buySell, @customer, @date,";
               insrtStmt.text += " @additionalid, @dealType, @traderName, @genericType, @owner);";
               insrtStmt.parameters["@mainid"] = categories[i].mainid;
               insrtStmt.parameters["@transactionDate"] = categories[i].transactionDate;
               insrtStmt.parameters["@tradeId"] = categories[i].tradeId;
               insrtStmt.parameters["@ccyPair"] = categories[i].ccyPair;
               insrtStmt.parameters["@account"] = categories[i].account;
               insrtStmt.parameters["@buySell"] = categories[i].buySell;
               insrtStmt.parameters["@customer"] = categories[i].customer;
               insrtStmt.parameters["@date"] = categories[i].date;
               insrtStmt.parameters["@additionalid"] = categories[i].additionalid;
               insrtStmt.parameters["@dealType"] = categories[i].dealType;
               insrtStmt.parameters["@traderName"] = categories[i].traderName;
               insrtStmt.parameters["@genericType"] = categories[i].genericType;
               insrtStmt.parameters["@owner"] = categories[i].owner;
               insrtStmt.execute();
          }

       }

Answer 1

if you think the problem is that it is still executing, just add an event listener to the statement for it's "result" event, and then fire off the next statements.

public function addDataSet( categories : Array ) : void {
    _categories = categories;
    _loopcounter = categories.length;
    _insrtStmt : SQLStatement = new SQLStatement();
    _insrtStmt.addEventListener( "result", addData );
    addData();
}

public function addData(event : Event = null) : void {
    _loopcounter--;
    // Set up rest of statement
    _insrtStmt.execute();
}

Answer 2

Maybe you should use a synchronous connection instead of an async one? If needed you can open multiple connections to the same db and use the appropriate connection.

Answer 3

For executing multiple commands at once - use transactions.

Simple example from http://www.zedia.net/2009/air-sqlite-optimization-tricks/

_updateStmt.sqlConnection = _conn;
_updateStmt.text = "UPDATE main.myTable SET statusF=@STATUS  WHERE keyId=@ID";

_conn.begin();//_conn is a SQLConnection, I didn't take the time to write the code for it, but this is where the magic happens

for (var i:uint = 0; i < currentArray.length; i++){
  _updateStmt.parameters["@STATUS"] = currentArray[i].status;
  _updateStmt.parameters["@ID"] = currentArray[i].id;
  _updateStmt.execute();
}

_conn.commit();

Also have a look at the adobe flex livedocs chapter 'Improving database performance'