Working with Flex, AIR, and SQL (2)

This post is more than 2 years old.

It's been a little while, but I've gotten around to working more on my Time Tracker AIR application, first discussed here. As a recap, my intention was to build a simple time tracker that would let me enter hours for projects and clients. The real goal was to learn more about SQLite and the AIR connection to it.

I've made some updates to the application and learned a bit on the way.

The first issue I ran into was my database setup code. My original code made one table, and when the table was done being created (if it needed to be), the application entered it's normal operating mode. This became a problem when I wanted to make two tables. If you remember, I had created an asynchronous database connection. I wasn't able to run both SQL statements in one execution so I had to two executions. How would I know when to start my program? I could use a bunch of flags, but that seemed like a mess.

So what I did was compromise. I kept one database connection in asynchronous mode. This would be my main connector to the database. In my startup code though I created another connection - one that was synchronous. This let me setup my tables and begin the application proper when done.

I also discovered that you want to be sure you think about your table structure. While it is possibly to modify tables, I just simply wrote a temp "drop table" code segment that I keep commented out most of the time.

I ran into this issue again on the new Projects page. This lets me create projects that are tied to clients. On loading the panel though we need to get both clients and projects. The issue was this though - what if projects loaded very quickly, but clients didn't? If you clicked to edit a project, I wouldn't be able to display the client drop down. So for this page I used a flag. I only allow you to edit projects once clients have finished loading. (As a quick release note, the Client filter on this page doesn't work, but the Active filter does.)

I added the Hour Entry page yesterday. It lets you add hours to a project. (Again though, no validation, so be sure to pick everything.) I loved how Flex had a built in numeric stepper control. It was perfect for this. Ditto for the calendar.

I ran into one more database issue here. I wanted to show the hours you entered today. (The reports page will let you view all hours, when I get to coding it.) But I'm having issues with date comparisons and SQLite. For now it will show everything.

I've include the AIR installer and full source code in the package. Enjoy. Again, assume I wrote horrible Flex code. I'm going to blame the Saints for not making it to the playoffs for my lack of Flex skills.

Download attached file.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Raul Riera posted on 1/10/2008 at 2:54 AM

Do you know if the lack of remote SQL connection is part of the "BETANESS" of AIR or it will never happen without the need of a remote web language?
It will be nice to create / connect to sqlite databases on a remote central machine.

Comment 2 by Raymond Camden posted on 1/10/2008 at 3:13 AM

I honestly don't know. I know AIR has network abilities - so in theory someone could write the connectors one time and share it with the world.

Comment 3 by Raul Riera posted on 1/10/2008 at 3:32 AM

Hmmm I will have to dig on that, it could be the thing I need. Thanks

Comment 4 by Gareth posted on 1/10/2008 at 6:59 AM

Hey Ray,
Thanks for putting up your code. I know that whenever anyone puts code up, everyone's a critic :)

In my flex/AIR travels I came across a couple of things that may make creating multiple SQL statements a little easier. You'll need to start adding event handling to your code in order to do the asynchronous stuff without throwing an error because A has not finished when B runs (once A is done, go B, once B done, go C, etc).

Here's something I came across that I thought was a clever way of doing things that I've modified slightly to fit your model:

private var statements:Array = [];

public function init():void {
statements.push( "CREATE TABLE IF NOT EXISTS clients(" +
" id INTEGER PRIMARY KEY AUTOINCREMENT, " +
" name TEXT, " +
" description TEXT " +
");" );
statements.push( "CREATE TABLE IF NOT EXISTS projects(" +
" id INTEGER PRIMARY KEY AUTOINCREMENT, " +
" name TEXT, " +
" description TEXT, " +
" clientidfk INTEGER, " +
" active INTEGER " +
");" );
statements.push( "CREATE TABLE IF NOT EXISTS hours(" +
" id INTEGER PRIMARY KEY AUTOINCREMENT, " +
" description TEXT, " +
" clientidfk INTEGER, " +
" projectidfk INTEGER, " +
" hours NUMERIC, " +
" date DATE " +
");" );
conn.openAsync(dbFile);
executeNext();
}

private function executeNext( e:SQLEvent=null ) {
if (statements.length > 0) {
createStmt = new SQLStatement;
createStmt.sqlConnection = conn;
createStmt.text = statements.shift();
createStmt.execute( -1, new Responder( executeNext, errorDBHandler ));
} else {
conn.close();
dbReady();
}
}

The Responder class takes 2 functions, the first is for a successful SQL call, the second is for an unsuccessful call. It takes the place of the 2 listener events that you initially placed. Then it recursively calls the "executeNext()" function and shifts each statement off of the statements array, executing each one as it goes. Once it gets a result event, it calls the next one, until all statements have been called. Once all are done, it goes to dbReady().

This was borrowed from the employeeDirectory code at Adobe.

Comment 5 by Raymond Camden posted on 1/10/2008 at 7:34 AM

Wow. I got to say - thats nice. I'm not going to use it for now - as I want to focus on other parts of the app - but next time I will.

Comment 6 by Gareth posted on 1/10/2008 at 7:41 AM

One other thing I found,
you forgot to add a clickHandler to the projects.clientFilter combobox, so it doesn't matter if you switch clients, it still shows all.

For the date problem you were encountering, have you tried using Date.parse(recent date) - Date.parse(older date) or is it a dB issue? I've found Date.parse will parse just about anything (all textual dates as well), and do a good job of returning values. You just have to convert it back to a date after that to find the elapsed time difference.

Sorry for all of the "and don't forget this" posts :)

Comment 7 by Raymond Camden posted on 1/10/2008 at 7:43 AM

Ah yes - I forgot to release note the client filter. I knew about that - my issue was (and why I didn't make it work) was that I need a simple way to clear that filter.

For the date issue - all I want to do is mod my 'get hours' sql to get records where date == sometime today. Nothing I tried worked.

Comment 8 by Gareth posted on 1/10/2008 at 4:54 PM

One last thing,
I don't know if you've seen it or not, but Firefox now has a SQLite Manager extension which allows you to view data, edit tables/data/indicies, and run queries. Kinda nice and makes it easier to check queries rather than having to rebuild your app each time with the modified query.

(Oh yes, I found 2 errors with my previous code. It should be e:SQLResult that is passed into executeNext and executeNext needs a return datatype of "void")

Comment 9 by Raymond Camden posted on 1/10/2008 at 5:06 PM

Actually, I'm running another AIR app to do my queries. I forget the name (I'm downstairs with my laptop), but I'm going to blog about it later today. I'll find the FF plugin as well to let folks know about it.

Comment 10 by dc posted on 1/10/2008 at 6:00 PM

a bit unrelated, but this is a fairly basic ORM for AIR which uses annotations in AS VO's to maintain the database.

http://coenraets.org/blog/2...

Comment 11 by Gareth posted on 1/10/2008 at 9:01 PM

@dc,
Now that is *really* nice. I'll have to give that a try. Only modification I may make is to prevent all columns from being uppercased, but there are quite a few things in there I haven't yet played around with (in Flex or AIR). Nice!

Comment 12 by CG posted on 4/16/2008 at 1:02 AM

Gareth - THANK YOU! Not quite sure why it is that Air doesn't support BEGIN/END transaction...

Comment 13 by Stefan@JustRIA posted on 7/31/2008 at 2:28 AM

I prefer to create SQL in an embedded file - each statement postfixed by ';'. Then, this code splits the SQL into single statements and executes all step by step (error handling for clarity not included):

try {

// Separate all statements
var parts:Array = createSQL.split( ');' );

for( var i:int; i<parts.length; i++ ) {

// Only, if we really have an SQL statement
if ( '' != parts[i] ) {
var realStatement:String = parts[i] + ');';
createStmt.text = realStatement;
createStmt.execute();
}
}

} catch( error:SQLError ) {

Alert.show( 'Sorry, DB error: ' + error.details );

}

Comment 14 by Raymond Camden posted on 7/31/2008 at 5:24 AM

That's pretty slick. Thanks for sharing.

Comment 15 by Stefan@JustRIA posted on 7/31/2008 at 6:42 AM

Additionally, here's another small utility I created: CPreferencesManager manages application preferences by means of a key/value set:

Here is how to use it to make window positions persist after relaunch of an application:

1. Create an instance:

<tracker:CPreferencesManager id="thePrefs" />

2. Attach windowMove events to your Window:

windowMove="thePrefs.setPreference('main.window.x', this.nativeWindow.x.toString() ); thePrefs.setPreference('main.window.y', this.nativeWindow.y.toString() );"

3. After creationComplete, move the window to its position of the one but last launch:

var posX:int = int( thePrefs.getPreference('main.window.x' ) );
var posY:int = int( thePrefs.getPreference('main.window.y' ) );
move( posX, posY );

The implementation:

package com.your.company
{
import flash.filesystem.File;
import flash.filesystem.FileMode;
import flash.filesystem.FileStream;
import flash.utils.ByteArray;
import flash.utils.Dictionary;
import flash.utils.IDataInput;
import flash.utils.IDataOutput;
import flash.utils.IExternalizable;

import mx.controls.Alert;

public class CPreferencesManager implements IExternalizable
{

private var _prefValues:Dictionary;
private var _prefsStream:FileStream;

public function CPreferencesManager()
{

_prefValues = new Dictionary();
loadPreferences();

}

public function loadPreferences():void {

var fp: File = File.applicationStorageDirectory;

fp = fp.resolvePath( 'prefs.xml' );

if ( fp.exists ) {

_prefsStream = new FileStream();
_prefsStream.open( fp, FileMode.READ);

readExternal( _prefsStream );
_prefsStream.close();

} else {
_prefValues[ 'appName' ] = 'tracker'; // test-code
savePreferences();
}

}

public function savePreferences():void {

var fp: File = File.applicationStorageDirectory;

fp = fp.resolvePath( 'prefs.xml' );

_prefsStream = new FileStream();
_prefsStream.open( fp, FileMode.WRITE );

writeExternal( _prefsStream );
_prefsStream.close();

}

public function setPreference( name:String, value:Object ):void {

_prefValues[ name ] = value;
savePreferences();

}

public function getPreference( name:String ):Object {

return _prefValues[ name ];

}

public function readExternal(input:IDataInput):void
{

var bytes:ByteArray = new ByteArray();
input.readBytes( bytes, 0, input.bytesAvailable );

var q:Object = bytes.readObject();

_prefValues = new Dictionary();

for( var p:Object in q ) {
_prefValues[ p ] = q[p];
}

// Alert.show( _prefValues.toString() );

}

public function writeExternal(output:IDataOutput):void
{

var bytes:ByteArray = new ByteArray();
bytes.writeObject( _prefValues );

output.writeBytes( bytes );
}

}
}

Comment 16 by Raymond Camden posted on 7/31/2008 at 6:20 PM

Is there any kind of AIR cookbook? Solutions like this need to be aggregated. I know there is a Flex cookbook, but does it allow you to filter by AIR solutions?

Comment 17 by Gareth Arch posted on 7/31/2008 at 9:36 PM

Perhaps a nice co-conspirator to cflib.org?
airlib.org?
flexairlib.org?
:)

Comment 18 by Raymond Camden posted on 7/31/2008 at 11:16 PM

Be my guest. :)

Comment 19 by Stefan@JustRIA posted on 7/31/2008 at 11:28 PM

I just posted my solution to Flex Cookbook - but neet to fix it soon:

http://www.adobe.com/cfusio...

Why? The code

output.writeObject( _prefValues );

writes an object, but does not save it's class, therefore, one has to register its class first:

registerClassAlias( "flash.utils.Dictionary", Dictionary );

Likewise, one has to register the class before

registerClassAlias( "flash.utils.Dictionary", Dictionary );
_prefValues = input.readObject() as Dictionary;

first.

I wonder, why the lib doesn't do this itself. Now, it tends to be hard, to write an universal serializer, which maintains all class, of all linked objects.

Hm, too bad.

Comment 20 by Gareth Arch posted on 8/1/2008 at 12:11 AM

@Ray,
wouldn't want to take away from the fun you seem to have of building/hosting all of these sites :)

Comment 21 by Stefan@JustRIA posted on 8/2/2008 at 11:53 PM

Actually, Adobe has an AIR cookbook here:

http://www.adobe.com/cfusio...