I always wanted to have a good personal diary application, but the freeware applications available do not satisfy my requirements. So I decided to write my own using AIR.

However, I haven’t really worked that much with DATE columns in SQLite. And had some problems in inserting dates and retrieving them from the db. So I want to share my research and solutions here.

This is the crucial extract from the documentation:

Explicit data typing

Parameters are used to allow for typed substitution of values that are unknown at the time the SQL statement is constructed. The use of parameters is the only way to guarantee the storage class for a value passed in to the database. When parameters are not used, the runtime attempts to convert all values from their text representation to a storage class based on the associated column’s type affinity.

What this means?

Supposed you have a SQL table which has a "DATE" column, then the right way to insert data is to use parameter substitution SQL prepared statements.

Correct way

var sql:String =
        "INSERT INTO entries (entryText, entryDate, summary,
          entryMood)
         VALUES ( :entryText, :entryDate, :summary,
          :entryMood)";

insertStmt.text = sql;                   
insertStmt.parameters[":entryText"] = inputTxt.text ;
insertStmt.parameters[":entryDate"] = inputDate.selectedDate;
insertStmt.parameters[":summary"] = inputSummary.text;
insertStmt.parameters[":entryMood"] = ‘happy’;

In the above code, "inputDate" is a DateField. Since we are using parameter passing, the Date ActionScript object is not converted to text which preserves the data. If we did not use parameters then the date will be translated into SQL TEXT type and this will be incorrect. So the following is the incorrect way of doing things.

Incorrect way

var sql:String =
          "INSERT INTO entries (entryText, entryDate, summary,
           entryMood)
          VALUES( ‘ +
          + input.text + "’," +
          + inputDate.selectedDate
…

You can also probably use another variation:

1. Set the data type of the date column in the SQL table to TEXT
2. Convert dates to string before inserting them into SQLite, by using the DateField.dateToString() function.
3. You can retrieve the date strings from the database and then use the DateField.stringToDate() to convert strings into date objects.
4. Take care that the format strings in step 2 and 3 should be same.

Also, note that using this method does not preserve the "time" in the date. Hope this helps.

Presentation slides: here
Demo source code: here  

I recently spoke at the Webmaniacs conference in Washington DC.

I spoke about "Client/server data synchronization patterns in occasionally connected clients". I’m uploading the demo files and the ppt files here.

The first part of the talk is pretty general, and I think you can apply it to any kind of occasionally connected client - you may be building it with AIR + LCDS, Flex + LCDS or with AJAX toolkits e.g. Google Gears. The latter half of the ppt & the demo - showcase the features of LiveCycle Data Services (LCDS) that allow users using Flex/AIR to build such apps. It talks about conflict resolution, transactions, batch updates (TODO).

An occasionally connected clients is a software that functions in the same manner while disconnected from the network, as while connected - only with certain restrictions on transactional functionality - the idea is that the user should be least discomforted by disconnections to the network.

I just wanted to get this demo out, it is not polished and some things are not working really but I will keep updating the demo and commenting the source code to make it easier to understand. I’m not using any MVC patterns to avoid adding any complexity to the code. So it is pretty bare bones.

Steps to compile the application:

1. The application uses LCDS 2.6 beta (available from Adobe Labs)
2. The application is compiled as a Flex project using Flex Builder 3. (You dont need the project to be a LCDS project, a simple Flex project will do. I create all connections to LCDS server on the fly.)
3. If you want you can extend it to be an AIR application as well.
4. The application will run as a simple drop-in to LCDS.

Steps to run the application:

1. You can see various usecases by running the application.
2. You will need to run at least two clients at the same time.
3. The clients start disconnected from the server.
4. Click on connect, and then fetch data to get the data in the clients.
5. You can then start playing around with various controls to see how things change.

TODO: I need to put a video demo to show the various functionality.

I will write a more thorough discussion on the topic in a couple of weeks.

This work is licensed under a Creative Commons Attribution 2.5 India License.

A new pricing was announced for Flex Builder 2 which becomes effective 1st November. Flex Builder 2 without charting currently costs $499.99 at Amazon, its new price starting 1st November 2007 will be $249.99 (that is gonna be a saving of $250!!! ) yoo hoo!
Flex Builder 2 with Charting will become $100 cheaper to $699, rather than the $799 sold now.

Besides if you buy a support plan for Flex 2 - which costs $99. Then you will get a free upgrade for Flex Builder 3, whenever it comes out.

Flex Builder 3 will be sold in two versions - Standard ($250) and Professional ($700). The Professional version will contain Charting, Advanced Data Grid and other advanced visualization stuff.

Update on upgrade pricing:

* Flex Builder 2 to Flex Builder 3 Standard Upgrade - $99 US
* Flex Builder 2 with Charting to Flex Builder 3 Professional Upgrade - $299 US
* Trade-up from FB Standard to FB Professional - $499 US

Today (Day 2 of Adobe MAX 2007) FlexBuilder for Linux was announced! Woot! Woot! You can download it from: http://labs.adobe.com/technologies/flex/flexbuilder_linux/

Though its an Alpha version, it is still a great news for a Linux lover like-me! too cool!

It is a stripped down version of FlexBuilder for Windows and Mac. But it will get there I guess.
This release of Flex Builder Linux requires Sun JRE 1.5.x and Eclipse 3.3 and supports the following distributions:

* SuSE Linux Enterprise Server 10 (x86 32-bit version)
* RedHat Enterprise Linux WS 4 (x86 32-bit version)
* Ubuntu 7.0.4 (Feisty) (x86 32-bit version)

I’m trying to write out my max blogging but I’m just too sleepy right now, and then I have to TA a session in the morning. … I just dozed off on the keyboard! I guess I will finish of the max blogging tomorrow! Sorry!