Share this Site


Subscribe in a reader

Syndicate this blog on your site

Speaking Events


Calendar

June 2007
M T W T F S S
« May   Jul »
 123
45678910
11121314151617
18192021222324
252627282930  

Recent Posts

Recent Comments


« on AIR Bus Tour | Main | AIR Embedded Database Code Exerpts 1 »

AIR Embedded Database Sample Application

By Rich Tretola | June 11, 2007Print This Post Print This Post
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...
39 views

EmployeeDirectory (Download the AIR Installer)

This application utilized the SQLite embedded database and the network connection event listeners which are part of Apollo.

It also uses Cairngorm for interacting with the remote ColdFusion server for backup and restore functionality.

Upon first run, the local SQLIte database and tables are created.

Attempting to add a new Employee record validates the form

The Employee record passes validation and is saved locally.

Selecting the record from the grid will put form in edit mode.

If the application detects a network connection, the server interaction buttons are available. Clicking Back Up sends the data to a remote ColdFusion server.

While the record is highlighted in the grid, hit the delete button which removes the local record from the SQLite database.

Clicking Restore will prompt the user to overwrite any local data.

The record is retrieved from the remote ColdFusion server and saved back into the local SQLite database.

I spent the past few hours patching errors that occurred when the public beta was launched so the source code is kind of ugly. I will post it as soon as I feel that it is in a clean state.

Topics: Adobe AIR (Apollo) |

4 Responses to “AIR Embedded Database Sample Application”

  1. EverythingFlex » Blog Archive » AIR Embedded Database Code Exerpts 1 Says:
    June 12th, 2007 at 11:49 am

    [...] are some excerpts for the AIR Embedded Database [...]

  2. Super-simple SQLite example for Adobe AIR 1 Beta « Flash Enabled - Get Ready With Flash… Says:
    June 15th, 2007 at 4:13 am

    [...] but this code can probably be reused by anyone wanting to get started with AIR+SQLite. There are a couple of other AIR+SQLite examples out there to check out and don’t forget the documentation on the [...]

  3. Tiago Says:
    October 16th, 2007 at 6:41 am

    Hi Rich, how are you synchronizing the data between the local and the remote DB?
    Do you have any down loadable example?
    Thanks, Tiago

  4. everythingflex Says:
    October 16th, 2007 at 7:07 am

    In this case I am simply passing the Objects to a remote ColdFusion server over AMF. I point my local AS3 object to my remote CFC:
    Here is the sample connection:

    1. <mx:RemoteObject
    2.         id="coldfusionService"
    3.         destination="ColdFusion"
    4.         endpoint="http://mydomain.com/flex2gateway/"
    5.         showBusyCursor="true"
    6.         source="com.everythingflex.employeeDirectory.business.Delegate"
    7.         concurrency="last"
    8.         result="event.token.resultHandler( event )"
    9.         fault="event.token.faultHandler( event )"/>

    Employee ActionScript Object

    1. package com.everythingflex.employeeDirectory.vo
    2. {
    3.     [RemoteClass(alias="com.everythingflex.employeeDirectory.vo.Employee")]
    4.     [Bindable]
    5.     public class Employee
    6.     {
    7.         public var employeeId:Number;
    8.         public var firstName:String;
    9.         public var lastName:String;
    10.         public var email:String;
    11.         public var jobTitle:String;
    12.         public var userId:Number;
    13.     }
    14. }

    Employee ColdFusion CFC

    1. <cfcomponent output="false">
    2.  
    3.     <cfproperty name="employeeId" type="numeric" required="true" default="">
    4.     <cfproperty name="firstName" type="string" required="true" default="">
    5.     <cfproperty name="lastName" type="string" required="true" default="">
    6.     <cfproperty name="email" type="string" required="true" default="">
    7.     <cfproperty name="jobTitle" type="string" required="true" default="">
    8.     <cfproperty name="userId" type="numeric" required="true" default="">
    9.  
    10.         <!--- Initialize vars --->
    11.     <cfscript>
    12.         variables.instance["employeeId"]=0;
    13.         variables.instance["firstName"]="";
    14.         variables.instance["lastName"]="";
    15.         variables.instance["email"]="";
    16.         variables.instance["jobTitle"]="";
    17.         variables.instance["userId"]=0;
    18.     </cfscript>
    19.  
    20.     <!--- Populate data --->
    21.     <cffunction name="init" output="false" access="public" returntype="com.everythingflex.employeeDirectory.vo.Employee">
    22.         <cfargument name="employeeId" type="numeric" required="yes">
    23.         <cfargument name="firstName" type="string" required="yes">
    24.         <cfargument name="lastName" type="string" required="yes">
    25.         <cfargument name="email" type="string" required="yes">
    26.         <cfargument name="jobTitle" type="string" required="yes">
    27.         <cfargument name="userId" type="numeric" required="yes">
    28.  
    29.         <!--- Save it all --->
    30.         <cfscript>
    31.             setEmployeeId(arguments.employeeId);
    32.             setFirstName(arguments.firstName);
    33.             setLastName(arguments.lastName);
    34.             setEmail(arguments.email);
    35.             setJobTitle(arguments.jobTitle);
    36.             setUserId(arguments.userId);
    37.         </cfscript>
    38.  
    39.         <cfreturn this>
    40.     </cffunction>
    41.  
    42.     <cffunction name="getEmployeeId" output="false" access="public" returntype="numeric">
    43.         <cfreturn variables.instance.employeeId>
    44.     </cffunction>
    45.     <cffunction name="setEmployeeId" output="false" access="public" returntype="void">
    46.         <cfargument name="val" required="true" type="numeric">
    47.         <cfset variables.instance.employeeId=arguments.val>
    48.     </cffunction>
    49.  
    50.     <cffunction name="getFirstName" output="false" access="public" returntype="string">
    51.         <cfreturn variables.instance.firstName>
    52.     </cffunction>
    53.     <cffunction name="setFirstName" output="false" access="public" returntype="void">
    54.         <cfargument name="val" required="true" type="string">
    55.         <cfset variables.instance.firstName=arguments.val>
    56.     </cffunction>
    57.  
    58.     <cffunction name="getLastName" output="false" access="public" returntype="string">
    59.         <cfreturn variables.instance.lastName>
    60.     </cffunction>
    61.     <cffunction name="setLastName" output="false" access="public" returntype="void">
    62.         <cfargument name="val" required="true" type="string">
    63.         <cfset variables.instance.lastName=arguments.val>
    64.     </cffunction>
    65.  
    66.     <cffunction name="getEmail" output="false" access="public" returntype="string">
    67.         <cfreturn variables.instance.email>
    68.     </cffunction>
    69.     <cffunction name="setEmail" output="false" access="public" returntype="void">
    70.         <cfargument name="val" required="true" type="string">
    71.         <cfset variables.instance.email=arguments.val>
    72.     </cffunction>
    73.  
    74.     <cffunction name="getJobTitle" output="false" access="public" returntype="string">
    75.         <cfreturn variables.instance.jobTitle>
    76.     </cffunction>
    77.     <cffunction name="setJobTitle" output="false" access="public" returntype="void">
    78.         <cfargument name="val" required="true" type="string">
    79.         <cfset variables.instance.jobTitle=arguments.val>
    80.     </cffunction>
    81.  
    82.     <cffunction name="getUserId" output="false" access="public" returntype="numeric">
    83.         <cfreturn variables.instance.userId>
    84.     </cffunction>
    85.     <cffunction name="setUserId" output="false" access="public" returntype="void">
    86.         <cfargument name="val" required="true" type="numeric">
    87.         <cfset variables.instance.userId=arguments.val>
    88.     </cffunction>
    89.  
    90. </cfcomponent>

    I do also have a central delegate but it simply calls this DAO to manage the remote data:

    1. <cfcomponent>
    2.     <!--- Define properties --->
    3.     <cfproperty name="datasource" type="string">
    4.  
    5.     <!--- Initialize vars --->
    6.     <cfset datasource="EmployeeDirectory">
    7.  
    8.     <cffunction name="getEmployees" returntype="Array">
    9.         <cfargument name="userId" default="true" required="false">
    10.         <cfset var getEmployees = "">
    11.         <cfset var employees = ArrayNew(1)>
    12.         <cfset var args = StructNew()>
    13.         <cfquery name="getEmployees" datasource="#datasource#">
    14.             SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
    15.                    JOB_TITLE, USER_ID
    16.             FROM Employees
    17.             WHERE USER_ID = #arguments.userId#
    18.         </cfquery>
    19.         <cfloop query="getEmployees">
    20.             <cfscript>
    21.              args = StructNew();
    22.              StructInsert(args, "employeeId", getEmployees.EMPLOYEE_ID);
    23.              StructInsert(args, "firstName", getEmployees.FIRST_NAME);
    24.              StructInsert(args, "lastName", getEmployees.LAST_NAME);
    25.              StructInsert(args, "email", getEmployees.EMAIL);
    26.              StructInsert(args, "jobTitle", getEmployees.JOB_TITLE);
    27.              StructInsert(args, "userId", getEmployees.USER_ID);
    28.             </cfscript>
    29.             <cfinvoke component="com.everythingflex.employeeDirectory.vo.Employee" method="init"
    30.                     argumentcollection="#args#"
    31.                     returnvariable="employee">
    32.             <cfset ArrayAppend(employees, employee)>
    33.         </cfloop>
    34.         <cfreturn employees/>
    35.     </cffunction>
    36.  
    37.     <cffunction name="deleteEmployees" returntype="Boolean">
    38.         <cfargument name="userId" default="0" required="false">
    39.         <cftry>
    40.             <cfquery name="deleteEmployees" datasource="#datasource#">
    41.                 DELETE
    42.                 FROM Employees
    43.                 WHERE USER_ID = #arguments.userId#
    44.             </cfquery>
    45.             <cfreturn true>
    46.         <cfcatch>
    47.             <cfreturn false>
    48.         </cfcatch>
    49.         </cftry>
    50.     </cffunction>
    51.  
    52.     <cffunction name="saveEmployees" returntype="numeric">
    53.         <cfargument name="employees" type="Array" required="true">
    54.         <cfset var returnCount = 0>
    55.         <cfset var args = "">
    56.         <cfscript>
    57.             deleteEmployees(arguments.employees[1].getUserId());
    58.         </cfscript>
    59.  
    60.         <cfloop from="1" to="#ArrayLen(employees)#" index="i">
    61.             <cfscript>
    62.              args = StructNew();
    63.              StructInsert(args, "employee", employees[i]);
    64.             </cfscript>
    65.             <cfinvoke component="com.everythingflex.employeeDirectory.business.Delegate" method="saveEmployee"
    66.                     argumentcollection="#args#"
    67.                     returnvariable="result">
    68.             <cfif result>
    69.                 <cfset returnCount = returnCount + 1>
    70.             </cfif>
    71.         </cfloop>
    72.         <cfreturn returnCount/>
    73.     </cffunction>
    74.  
    75.     <cffunction name="saveEmployee" returntype="Boolean">
    76.         <cfargument name="employee" type="com.everythingflex.employeeDirectory.vo.Employee">
    77.         <cfset var returnVar = true>
    78.         <cfset var saveEmployee = "">
    79.         <cftry>
    80.             <cfquery name="saveEmployee" datasource="#datasource#">
    81.                 INSERT INTO Employees
    82.                     (FIRST_NAME,
    83.                      LAST_NAME,
    84.                      EMAIL,
    85.                      JOB_TITLE,
    86.                      USER_ID)
    87.                 VALUES
    88.                     ('#employee.getFirstName()#',
    89.                      '#employee.getLastName()#',
    90.                      '#employee.getEmail()#',
    91.                      '#employee.getJobTitle()#',
    92.                      #employee.getUserId()#)
    93.             </cfquery>
    94.         <cfcatch>
    95.             <cfset returnVar = false>
    96.         </cfcatch>
    97.         </cftry>
    98.         <cfreturn returnVar/>
    99.     </cffunction>
    100.  
    101. </cfcomponent>

Comments