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.

This is a tad late, but this is the most exciting news since launch of AIR 1.0 and Flex 3.0.

Adobe Creative Suite public betas are the next version of the next release of its collection of graphic design, Web development and video editing applications. The public beta versions of Adobe Dreamweaver® CS4, Adobe Fireworks® CS4 and Adobe Soundbooth® CS4 will be free downloads on Adobe labs. Try betas for Dreamweaver CS4, Fireworks CS4, and Soundbooth CS4  and see how the right web tools increase productivity and help our creative professionals create rich, relevant web experiences.

Try out the betas, and get a chance to peek into the future.

Plus, you can also give a holler on enhancements and issues to the dev team. How cool is that?