How I load data from SQLite using CF9 Air Integration
ColdFusion 9's Air integration is much more than just a syncManager, but also a client side ORM. Though some have argued that it is not, Rakshith and Terrence from Adobe say that it is, and they are smarter than me so I'll agree with them! Besides, it has Obects that Relationally Map to persisted storage, with methods to Load, Save, Remove, etc... So, in my very little knowledge of ORM, it's ORM enough for me!
Problem is, where the heck is the documentation? Short answer: Right here. (no link intended)
I've spent the past couple of months searching the web for all the information I can find on the topic as well as learning by trial and error (mostly error). Today I had a breakthrough, so figured I'd post it before I forget about it. It has to do with loading data from SQLite using the coldfusion.air.session's many load methods. As with Hibernate and other ORM's, using the cfair.swc integration we now have several methods to access data in AS3: load(), loadByPk(), loadAll() and loadByQuery().
Below I'll provide an example of how I'm using this in my login routine. This is how it can work, but not necessarily how it should be done. I'm far from being smart enough to know how to best handle things in Flex. If you anyone reads this, and has a better way, please correct me!
First, just as with any ORM you need your entity class or value object (or whatever you call those thingers). The below is a scaled down version of my Users class. Note that it uses the metadata [RemoteClass(alias="serverside.users")] this tells the coldfusion.air.syncmanager what ORM CFC on the server that this class maps to. The "serverside.users" is the url to the CFC, or the url to the alias. The url is relative to the "cfServer" property set in the syncManager (will show this below).
Users.as (Entity class)
package com.myproject.model.vo
{
[]
[(alias="serverside.users")]
[]
public class Users
{
[]
public var ID:int;
public var user_name:String;
public var password:String;
public var first_name:String;
public var last_name:String;
public function Users()
{
}
}
}
Ok, that gives us the object to contain our data. Now we load the data from SQLite. To do this we need to setup a session. For simplicity in this example I'll put this in the onCreationComplete of my main application file (note that these code snippets wont work if you copy/paste them because I'm not including all the import stuff):
Main.mxml (main application)
public function onCreationComplete() : void
{
/*
Initialize Global variable model
Singleton that holds my global stuff
(I don't think smart people like this, dunno why though)
*/
global = Global.getInstance();
//create a global sqlite session
global.dbFile = File.userDirectory.resolvePath("the_local.db");
//the ColdFusion server
global.syncManager.cfServer = "myserver";
//Path (relative to cfServer) to my CFC that manages sync/fetch
global.syncManager.syncCFC = "extensions.components.dataManager";
global.syncManager.cfPort = 80;
//open the session
var sessiontoken:SessionToken = global.syncManager.openSession(global.dbFile,017915);
sessiontoken.addResponder(new mx.rpc.Responder(
function(e:SessionResultEvent):void
{
// this handle will be how we interact with the local db later.
global.session = e.sessionToken.session;
},
function():void
{
Alert.show("Could not connect to the SQLite Database");
}
));
// This is how I show my login screen
appContainer.addChild(new LoginScreen());
}
Ok, so we have our Users.as class that defines the data object. We also open a global connection/session to our SQLite db (global.session). Now we can use that anywhere in our app that has access to our global singleton (which is any file that imports the singleton and invokes the getInstance() method. If that doesn't make any sense, google AS3 Singleton and look at how people do it. There's lots of documentation on that subject. To retrieve data we use one of the load methods provided in the cfair.swc (or coldfusion.air.session package). Here's my authentication routine (sample snippet of it anyways):
LoginController.as
public function authenticate(userName:String, password:String) : void
{
//load the data using a filter.
var loadToken:SessionToken = global.session.load(Users,{user_name:userName,password:password});
loadToken.addResponder(new mx.rpc.Responder(loadSuccess,loadFault));
}
The code of interest here is the global.session.load(Users,{user_name:userName,password:password});. The first parameter of the "load" method is the entity class (in this case Users). The second parameter is an object containing key:value pairs that will be used to filter the results. The "key" is the property name (i.e. table column name) and the value is what you wish to compare it with. So in the above example the traditional SQL would look like: SELECT * FROM users WHERE user_name = userName AND password = password.
The load method returns an array of objects (in this case an array of Users objects). However, since db calls are asynchronous, we need to add a responder in order to retrieve the data. That's the loadToken.addResponder(....) bit. The loadSuccess method looks like this:
private function loadSuccess(event:SessionResultEvent) : void
{
var users:ArrayCollection = event.result as ArrayCollection;
if (users.source.length != 0){
Alert.show("woohooo you logged in!");
}else{
Alert.show("Login Failed!");
}
}
Notice it takes a parameter of type SessionResultEvent. This is a custom event provided by the cfair.swc that wil contain the results. Also, notice that I check users.source.length != 0. This property is always passed in this event.result object and is simply the count of array items it contains. If zero, then we know that there were no matches.
I'm pretty sure I butchered some of the terminology, and the design pattern experts would cringe at this, but it works. For me at least, your mileage may vary.
Dilip Shah wrote on 06/24/11 11:37 PM
the article came in quite handy as I'm converting my web application written in Flex to AIR desktop application... thanks!