AIR Cairngorm 2.0

Sunday, June 22nd, 2008

I have received quite a few emails since the release of AIR 1.0 and Flex 3.0 regarding the AIR Cairngorm API which I developed last year. In the time since I have been working primarily with a modified version of AIR Cairngorm which I used on a number of successful real world AIR applications, however I simply have not had the time to document and refactor for general use until recently.

In case you are not familiar with AIR Cairngorm it is an open source project built on top of Adobe Cairngorm which provides a framework for working with the Adobe AIR SQLite API while building an application with Cairngorm.

AIR Cairngorm is built around the SQLService class which essentially wraps the AIR SQL APIs to provide a uniform interface which can be utilized as a service, much in the same way one would work with RPC services in a typical Cairngorm application. SQLService provides an API for both synchronous and asynchronous implementations.

In the time since the initial development and release of AIR Cairngorm, which was during the early alpha releases of AIR, there has been many changes to the SQL APIs in AIR. In addition I have also developed some new best practices for working with Adobe AIR and Cairngorm, all of which I tried to roll into this latest release where possible.

The following is a brief description of the current AIR Cairngorm API:

AIRServiceLocator: The AIRServiceLocator is a sub class of Cairngorm ServiceLocator, therefore it inherits the same API as ServiceLocator while also adding additional support for working with local databases via the getSQLService and hasSQLService methods.

SQLService: The SQLService class essentially wraps the SQLStatement and SQLConnection classes. SQLService allows developers to create an mxml implementation defined on a Cairngorm ServiceLocator just as one would with typical RPC services (e.g. HTTPServices, WebService etc.)

ISQLResponder: ISQLResponder provides a consistent API from which asynchronous SQLStatement execution results and faults can be handled. ISQLResponder is very similar to IResponder in that it defines both a result and fault handler however with a slightly different signature which is specific to a SQLStatement result / fault, (i.e. strongly typed parameters).

SQLStatementHelper: SQLStatementHelper is an all static utility class which facilitates substituting tokens in a SQL statement with arbitrary values.

I have also created a custom version of my Cairngen project specifically targeting AIR Cairngorm code generation. In addition I will be making some future updates to AIR Cairngorm which will include support for various other AIR APIs in Cairngorm, so stay tuned.

Below I have provided downloads to the source, binary, air.cairngen and asdocs as well as asynchronous and synchronous example projects:
air cairngen
air cairngorm (all)

{ 23 comments to read ... please submit one more! }

  1. Eric,
    Nice Work buddy. You should have added all the cell phone minutes that have been eaten up by me for update requests. Yet again you come through for the community.
    Nice work,

  2. Thanks Eric,
    All of your work effort for Flex / AIR community is amazing. Thanks for posting this one up.


  3. Marcel Overdijk

    Really nice. Just in time as Í’m currently experimenting a little bit with both AIR and Cairngorm.

    One question.
    In your AIRCairngormSyncExample you have specified an userService.
    The example seems to indicatie that a SQL service maps to a database file.

    When you have multiple services (like an productService, orderService, customerService etc.) do they have to map to multiple database files. I currently have 1 database file containing all my tables. But I want multiple service to organise everything.

    How should I do this? Is it just opening referencing to the same database filename? That would also mean multiple opened connections? Will this have some drawbacks?
    How do you do this in your projects?


  4. Marcel Overdijk

    Well I figured out myself. I’m just using 1 SQLService and for the rest some delegates.

    I dived a little bit into the code and have some questions.

    In SQLStatementHelper you are using StringUtil.substitute to create the sql statement. I don’t think this safe related to SQL injection. Shouldn’t you use ? or named parameters instead?

    I also noticed that in the execute method the itemClass is only set when it provided (not null). In case statements are reused (and this is the case for the sych executed method!!) then it could happen that a previously set itemClass is used in the query. I don’t know if this could cause any problem, but why not set always the itemClass?


  5. Hey Marcel,

    Typically for Web Services (WSDL, REST, XML over HTTP, etc) I define a separate Service on the the ServiceLocator as well as a separate Business Delegate for each service.

    For AIR applications utilizing local databases there are two ways to implement this:

    A.) Define one Service (for connecting / accessing the database) on the ServiceLocator and then separate Business Delegates for each table.
    – or –
    B.) Define a separate Service and Business Delegate for each table.

    Personally I suggest having one Service for each database as it simplifies debugging the connections as there will only be one connection to manage per database. As far as organizing is concerned this is best achieved by having separate Business Delegates for each table, these delegates would then define all of the operations which can be performed on the respective table.

    If conceptually it makes more sense to you to define a separate SQLService object for each Service you could always just sub class SQLService and then define an instance of the sub class for each specific service, thus utilizing the same connection and database while separating the SQLService by function.

    Hope that helps,

  6. Hey Marcel.

    In regards to the SQLStatementHelper I have not had any injection issues because of this therefore I haven’t changed the implementation in this release. With that being said I do plan to make some changes to this implementation in a future update.

    In regards to the item class it is a non-issue in the executeAsync method as the SQLStatement object is being re-instantiated in every call. However it is not being re-instantiated in the execute method so I will add an update to this soon.

    – Eric

  7. Hey Eric – I just upgraded from your initial blog post version which I had been tweaking. It was pretty smooth update and I’m happy to be back on the official version. My AbstractAIRDelegate is acting as an adapter, implements ISQLResponder and handles the result/fault events internally, converts them to regular mx.rpc Events and passed those back to the initial command/responder. I find this nice and clean so only the delegate communicates with AIRCairngorm and my command and responder classes only deal with VOs and rpc Events. If felt like the command/responder was tightly coupled with AIRCairngorm otherwise, but maybe it’s just me?!

    Anyway I have one question regarding execute and executeAsync. Since this behavior is set when the connection is opened – do you think it would be a good idea to keep track of the connection type and throw an exception if execute is called when openAsync was specified, or vice versa? I don’t see in the code where this would be handled.

    Thanks for providing this little API, it’s extremely handy.

  8. I get the following error:

    Unable to resolve resource bundle “queries” for locale “en_US”.

    How to fix this.



  9. I have fixed the error.

    I have many tables in the SQlite database , how to define all the table names.



  10. how to use “create table” to define fields type and fields in air caingorm?



  11. Hi Eric,

    I have an application which lets the user create and access multiple SQLite files on the fly. What would be the best way to add identifiable SQLite connections at runtime?

    Thanks a lot in advance and thanks for a great wrapper.

  12. Ah, I found the event dispatcher in I thought it was being manually dispatched, I ran profiling on it to see what classes were being called during spin up and saw SelectUsersCommand in the list which led me to it.
    Thanks for the lib!

  13. Hi,
    the examples show two different apps for handling synchronous and asynchronous requests.
    What is the best way to handle synchronous and aynchronous db requests in the same application?

    Thank you!

  14. Hi, first thanks very much for your air-cairngorm, I’m a big fan of cairngorm and I use it for my every project cause of it simplicity and ability to extend.
    I’m playing with air-cairngorm now and I had some difficulties when mapping data types to SQL cause there are affiliates in air SQLite. I got problems with it, especially with DATE class.
    I modified it a bit to get rid of the string and now in SQLService I simply pass the SQLStatement instead of string so I get rid of the string class and also the speed is better with large statements.
    I now pass the parameters ex.
    SQL statement: INSERT_USER = INSERT INTO users (username, password, some_date, todo) VALUES( :username, :password, :some_date, :todo)

    and sample function in the delegate now looks like this

    public function addUser(user:UserVO) : void
    var statement:SQLStatement = new SQLStatement();

    statement.text = SQLStatementConfiguration.INSERT_USER;
    statement.parameters[‘:username’] = user.username;
    statement.parameters[‘:password’] = user.password;
    statement.parameters[‘:todo’] = user.todo;

    sql.openConnection(statement, responder, UserVO);

    where UserVO is simply class with

    public var username:String;
    public var password:String;
    public var date:Date;
    public var todo:Boolean;

    I hope that it makes sense.
    Maby you got better idea how to map the objects to the table.

  15. Good Day,
    I am also a big fan of cairngorm, like the fact that it is a tight framework. I am tring to get into air apps and just wanted to know what needs to be done to get your examples to work. I added your swc and the normal cairngorm swc, but I am still getting errors when I try to compile. Is there something I am missing in the build paths?


  16. Hey Vane, nice comment, I too had the same problems with errors in the string being passed in Eric’s class for sqlstatement. I simply extended the appropriate class like this and now I pass the sqlstatement object, it works a treat, thx for your work Eric:

    package com.wmanager.control.other.customClasses


    public class SQLStatementService extends SQLService
    public function SQLStatementService()
    public function executeStatementAsync(statement:SQLStatement, responder:ISQLResponder, dataType:Class = null, prefetch:int = -1.0) : void
    _statement = statement;
    _statement.sqlConnection = _connection;
    //_statement.text = statement;

    if ( dataType != null )
    _statement.itemClass = dataType;

    _statement.execute( prefetch, new Responder( responder.result, responder.fault) );


  17. Sorry forgot to add my link, please visit my new blog

  18. Hi Eric,
    great component!
    One question, have you thought about a switch or something for encrypted local databases? As I’m currently implementing such a thing using your component, would the code be of interest to you?

    regards Jonas

  19. HI Jonas,

    I developed this framework about 2 1/2 years ago so it’s been quite awhile since I last updated it. However, that being said I do plan to review it’s currently implementation and update it at some point to reflect the many changes in AIR in the time since this framework was implemented.


  20. Hey Eric,
    nice to hear that!
    I extended your SQLService class to work with encrypted databases, worked like a charm due to the ease in extending it.
    One more question though, as I read both vane and mikeydevs comment on problems with the string statements, especially in the case of vane problems with Date-objects. Is there a problem saving Date-objects with your current implementation, and is their way the way to go (as I understand them, they just removed the String-statements entirely)?
    I’m asking because right now I’m facing problems with Date-objects, too. It seems the Date is converted to a String before passing it into the statement itself, and thus is no longer recognized as a Date when trying to execute the statement, resulting in a “could not convert string value to date” SQLError (Error #3132: Data type mismatch.).

    regards Jonas

  21. Hello everyone! ÁIR Cairngorm It´s a Nice API. How can i get the latest version and documentation?? Thanks for share that. Regards!!

  22. Hi Manuel,

    It has been almost 3 years since any updates have been made to the AIR Cairngorm project. That being said, the latest version can be found in this post and the core implementation can be built upon to suit your needs. You will more than likely need to (at least to some extent) make some changes as many of the original AIR APIs have changed in the time since.


  23. Thank you!Great example!

{ 0 Pingbacks/Trackbacks }