AIR Sqlite: SQLEvent.RESULT not firing, but statemet IS executing properly

Question

Ok it looks likes like I have stumbled upon a strange timing issue... I made a quick SQL wrapper class for executing sql statements. However after .execute() is called, the SQLEvent.RESULT event is never fired, but the new entry in the DB is created as it should be. The really really odd part is if I put a setTimeout() just after calling execute() the event fires as expected.. I hope I'm missing something really obvious here... Here is a link to an example air app: http://www.massivepoint.com/airsqltest/AIRSQL.zip

And here is the code to the wrapper class:

if you look down at line 51 in the SQLRequest class, you will see the commented out setTimeout() method. To make everything work, just uncomment that line.. but to me this doesn't make any sense...

anyone have any thoughts? I'm totally stumped here...

package com.jac.sqlite 
{//Package
 import flash.data.SQLConnection;
 import flash.data.SQLStatement;
 import flash.events.EventDispatcher;
 import flash.events.SQLErrorEvent;
 import flash.events.SQLEvent;
 import flash.utils.setTimeout;

 public class SQLRequest extends EventDispatcher
 {//SQLRequest Class

  private var _callback:Function;
  private var _dbConn:SQLConnection;
  private var _query:String;
  private var _params:Object;

  private var _statement:SQLStatement;


  public function SQLRequest(callback:Function, connection:SQLConnection, query:String, parameters:Object=null):void 
  {//SQLRequest
   trace("Creating new SQL Request");
   _callback = callback;
   _dbConn = connection;
   _query = query;
   _params = parameters;


   _statement = new SQLStatement();
   _statement.sqlConnection = _dbConn;
   _statement.text = _query;

   if (_params != null)
   {//assign
    for (var param:String in _params)
    {//params
     trace("Setting Param: " + param + " to: " + _params[param]);
     _statement.parameters[param] = _params[param];
    }//params
   }//assign

   //setup events
   _statement.addEventListener(SQLEvent.RESULT, handleResult, false, 0, true);
   _statement.addEventListener(SQLErrorEvent.ERROR, handleError, false, 0, true);
  }//SQLRequest

  public function startLoad():void
  {//execute
   _statement.execute();
   //setTimeout(handleTimeOut, 10000);
  }//execute

  //TEMP
  private function handleTimeOut():void
  {//handleTimeOut
   trace("Executing: " + _statement.executing + " / " + executing);
  }//handleTimeOut

  private function handleResult(e:SQLEvent):void 
  {//handleResult
   trace("Good SQL Request");
   _callback(e);
   dispatchEvent(e);
  }//handleResult

  private function handleError(e:SQLErrorEvent):void 
  {//handleError
   trace("SQL Error: " + e.errorID + ": " + e.error);
   //dispatchEvent(e);
  }//handleError

  public function get executing():Boolean
  {//get executing
   return _statement.executing;
  }//get executing

  public function get query():String { return _query; }
  public function get statement():SQLStatement { return _statement; }

 }//SQLRequest Class

}//Package

Answer 1

I think what you're missing here is garbage collection.

Haven't tested your code, but this could certainly be the source of the problem.

var sqlReq:SQLRequest = new SQLRequest(handleResult, _dbConn, sql);
sqlReq.startLoad();

The reference sqlReq is local to the function and becomes unreacheable when the function returns. That makes it collectable. I guess there must be some code in the AIR runtime that collects garbage more agressively when there are sql connections involved. Because generally, you'll get away with not storing a ref to your object (at least in a web based environment, in my experience; this is a bug in such code, nevertheless; you just have to be in a bad day to experience it).

The setTimeout masks this problem (or almost solves it, although in an unintended way), because the setTimeout function uses a Timer internally. Running timers are not collected. So, the timer is alive and kicking and has a reference to your SQLRequest instance, which makes it reacheable, and so, not elligible for collection. If your DB call takes longer than the timeout though, you're back in the same situation.

To solve this, store a ref to the object and dispose it properly when you're done.

Edit

Another option, if you don't want to change the way you calling code works, is storing a ref to the instance in a class-scoped (i.e. static) dictionary for the duration of the call (this dictionary shoul not use weak referenced keys for obvious reasons).

You are adding a hidden side effect to your method, which is not a sign of good design in general, but as long as you remove it when the call to the DB is finished (whether it succeded or not), you're safe, so I think the problem is more of style than anything else.

What I mean is something like this:

private static var _dict:Dictionary = new Dictionary();

public function startLoad():void
{//execute
    _statement.execute();
    //  add a self reference to dict so the instance won't be collected
    //  do this in the last line, so if we have an exception in execute, this
    //  code will not run (or add a try/catch if you want, but this is simpler
    //  and cleaner, IMO
    addToDict();
}//execute

private function handleResult(e:SQLEvent):void 
{//handleResult
    //  remove the self reference before running any other code
    //  (again, this is in case the code that follows throws)
    removeFromDict();
    trace("Good SQL Request");
    _callback(e);
    dispatchEvent(e);
}//handleResult

private function handleError(e:SQLErrorEvent):void 
{//handleError
    //  same comment as handleResult
    removeFromDict();
    trace("SQL Error: " + e.errorID + ": " + e.error);
    //dispatchEvent(e);
}//handleError

private function addToDict():void {
    _dict[this] = true;
}

private function removeFromDict():void {
    if(_dict[this]) {
        delete _dict[this];
    }
}