Android SQLite App Development with Flash Builder and Flex

Source

Adobe Flash Builder with either Flex (Adobe or Apache) is very fast and is optimized for mobile application development. When you create an Adobe Mobile Application, you are actually using the Adobe AIR API 3.x which has undergone several mega transformations since version 1. The new Air API will actually compile mxml/Actionscript into native code, which in the case of Android, is Java.

Android Apps created with Flash Builder using Adobe Air and Adobe Flex (4.6 and earlier) or Apache Flex (4.8 and later) can be distributed as is through the Google Play Store and the Amazon App Store.

The IDE also has utilities which allow you to sign and bundle your application for distribution in the Google Play Store. The whole process from project creation in the Eclipse based Flash Builder IDE to packaging your app for distribution is very easy and fast depending on the complexity of your app.

Project Synopsis

To demonstrate the process of creating an Android application with a SQLite database. I will create a simple contact management app that we allow a user to enter contacts and save the information to a SQlite database. The app will also allow a use to view the stored contacts in a list. The app will feature a tab based design; one of the contact editing and the other to view the contacts.

The project will be built using Adobe Flash Builder 4.6 and corresponding SDK. Once the app is built and tested, I will provide the steps to do to package the app for deployment to the Google Play web Android web store.

Figure 1: Creating a new mobile project in Flash Builder 4.6
Figure 1: Creating a new mobile project in Flash Builder 4.6 | Source
Figure 2: Selecting the Android template in the Flash Builder 4.6 IDE
Figure 2: Selecting the Android template in the Flash Builder 4.6 IDE | Source

Create Project

  1. The first step is to create a project in Flash Builder 4.6
  2. note: you can download a trial version; cloud based or desktop
  3. Select “New Flex mobile Project” from the File menu.
  4. Under “Project Location”, provide a Project name in the project creation utility. See corresponding screenshot in figure 1 (opposite).
  5. Select the 4.6 SDK
  6. Under “Mobile Settings” select only “Google Android” as target platform
  7. Select the Tabbed application project template. This will allow us to create a tab based Android app. Take a look at figure 2 in the adjacent screenshot for a visual guide.
  8. Under Server Settings and Build Paths accept the defaults and click “Finish” to create the initial project.

Figure 3: The Flash Builder 4.6 IDE offers  a wide assortment of Android based devices to allow you to test on many configurations.
Figure 3: The Flash Builder 4.6 IDE offers a wide assortment of Android based devices to allow you to test on many configurations. | Source

Developing the Application

Once the project is created and the project‘s main application file, AndroidSQLite.mxml, view code listing 1 below, is opened in the Visual Designer, select the target device from the drop down list. You work area of your screen should automatically adjust to the screen resolution of the selected device. You can also use this list to test your app on several different devices. The Flash Builder 4.6 offers all the current device version on the market at the present time. You can add new device configurations as new models are introduced to the market. To add a new device , locate the Device configuration section in Preferences.

The first part of this project involves developing the SQLite database interface, the Model and the Controller in the MVC design pattern. The code will include functions to setup and initialize the database, to insert new records and to select existing records. Once this code is complete, we will connect the ActionScript code to the mxml views in order to make the application complete.


Code Listing 1: AndroidSQlite.mxml

<?xml version="1.0" encoding="utf-8"?>
<s:TabbedViewNavigatorApplication xmlns:fx="http://ns.adobe.com/mxml/2009" 
								  xmlns:s="library://ns.adobe.com/flex/spark" applicationDPI="160" applicationComplete="dbinit(event)">
	<fx:Declarations>
		<!-- Place non-visual elements (e.g., services, value objects) here -->
	</fx:Declarations>
	<s:ViewNavigator width="100%" height="100%" label="Add Contact" firstView="views.AddContactView"
					 icon="assets/contact.png" title="Add To List"/>
	<s:ViewNavigator width="100%" height="100%" label="List" firstView="views.ListView"
					 icon="assets/docList.png" title="View Contact List"/>
	
</s:TabbedViewNavigatorApplication>

Developing Database Interface

To start create a new folder inside of the Views folder. You right click on the View folder in the Project Explorer and select the new folder option in the context menu. For this example I named the folder "scripts".

Right click the scripts folder and select New->Actionscript File to create a new script file in the folder. You can name it whatever you like. In the current example, I named it dbOps.as and I will refer to this name when speaking about the database interface.

The first order of business is to define the import statements that we will use in our code. Import the following classes:

import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;
import flash.events.Event;
import flash.filesystem.File;
import mx.collections.ArrayCollection;
import mx.utils.ObjectUtil;

These classes include the SQLConnection which handles connections with the database. The SQLStatement as you may have guessed represents the query statements. The three following classes are to handle SQL success and failure events and the Events class handles any interaction with the components.

The File class is used to interface with the database file. The ArrayCollection is a class to handle multi-dimensional arrays.

Next define the global variables for the connection, statement and ArrayCollection. To define global variables we use the [Bindable] directive which will maintain state between calls to the variables.

Once the variables are define, we can start work on the functions which include:

  1. dbinit
  2. isDbConnected
  3. dbLoaded
  4. sqlError
  5. sqlResult
  6. insertContact
  7. selectContacts
  8. displayFunc

dbinit

This function is called from the applicationComplete event of the TabbedViewNavigatorApplication main application element. The function creates the database using the File class and we use the OpenSync to open the database.

Next we define the event listeners and callback function to handle database initialization once the database is opened. We also define a callback function to handle any result from a query and we also define a callback to capture and handle any errors.

The first callback function to be executed once the database is opened is the "dbLoaded" function.

dbLoaded

The dbLoaded function will define a SQL statement to create the table, contacts_t, in the database if it doesn't already exist. This SQL statement is only executed when the application launched.

At the point the database and table is created, opened and its table is created. The other functions will be used from the two views.

insertContact

This function is used with the Save button in AddContactView.mxml subview. It inserts any new records into the database. The first line in the body of the function re sets the statement and re connects and opens that database using the isDbConnected function.

After which the SQL statement executes the INSERT statement using the values from the parameters which will be set using the UITextInput fields in the AddContact screen.

isDbConnected

This function is almost a copy of the dbinit function except that is uses the SQLConnection parameter to check to see if the database is opened and if not the function re sets the event listeners.

selectContacts

The selectContacts function is called from the contentCreationComplete event of the ListView. The function mimics the insertContacts function is that it creates a new SQL statement using the isDbConnected function. Next it executes a SELECT SQL query on the SQLite database. Finally is set a listener to handle the query results if any and also any errors. The callback functions are sqlResult and sqlError respectifully.

sqlResult

The sqlResult function is triggered by a callback from the selectContacts. It sets an Array object using the results of the query. It then initializes the contactList ArrayCollection and loops through the items in the Array and assigns them to the contactList. See the section on List component on how to set the DataProvider.

sqlError

The sqlError callback will write any errors to the console using the trace function for the sake of simplicity.

displayFunc

The last function that we will implement is used with the List component to layout the data in columns. It is assigned to the labelFunction

Code Listing 2: The ActionScript Code (dbOps.as)

// ActionScript file
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.events.Event;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;
import flash.filesystem.File;
import mx.collections.ArrayCollection;

[Bindable]
public var conn:SQLConnection = new SQLConnection();

[Bindable]
private var stmt:SQLStatement = new SQLStatement();

// ArrayCollection used as a data provider for the datagrid. It has to be bindable so that data in datagrid changes automatically when we change the ArrayCollection

[Bindable]
private var contactList:ArrayCollection = new ArrayCollection();

// function we call at the beginning when application has finished loading and bulding itself
private function dbinit(event:Event):void
{
	var dir:File = File.applicationDirectory;
	//
	var db:File = dir.resolvePath("contacts.db");
	// after we set the file for our database we need to open it with our SQLConnection.
	conn.openAsync(db);
	//We set event listeners to check that the database is opened
	//The second event listener is to catch any processing errors
	//The last is handle the results from our queries since
	//Actionscript is an event based language.
	conn.addEventListener(SQLEvent.OPEN, dbLoaded);
	conn.addEventListener(SQLErrorEvent.ERROR, sqlError);
	conn.addEventListener(SQLEvent.RESULT, sqlResult);
}

//Function to check is the database is connected and that the contacts.db
//actually exist.
public function isDbConnected(conDb:SQLConnection):SQLConnection{
	var dir:File = File.applicationDirectory;
	var db:File = dir.resolvePath("contacts.db");
	if(!conDb.connected){
		conDb.open(db);
		conn.addEventListener(SQLEvent.OPEN, dbLoaded);
		conn.addEventListener(SQLErrorEvent.ERROR, sqlError);
		conn.addEventListener(SQLEvent.RESULT, sqlResult);
	}
	return conDb;
}


private function dbLoaded(op:SQLEvent):void{
	
	stmt.sqlConnection = conn;

	stmt.text = "CREATE TABLE IF NOT EXISTS contacts_t ( id INTEGER PRIMARY KEY AUTOINCREMENT, contactName TEXT, cellPhone TEXT, email TEXT);";
	
	stmt.execute();
	
}



private function sqlError(err:SQLErrorEvent):void{
	trace(err.error.message);
}
private function sqlResult(res:SQLEvent):void{
	
	var data:Array = stmt.getResult().data;
	contactList = new ArrayCollection();
	for(var d:int=0;d<=data.length-1;d++)
	{
		contactList.addItem({contactName:data[d].contactName,cellPhone:data[d].cellPhone,email:data[d].email});
	}
	// we pass the array of objects to our data provider to fill the datagrid
	
}

private function insertContact(contact:String, cell:String, email:String):void
{
	stmt.sqlConnection = this.isDbConnected(conn);
	
	stmt.text = "INSERT INTO contacts_t (contactName, cellPhone, email) VALUES('"+contact+"','"+cell+"','"+email+"');";
	stmt.execute();
	
}

private function selectContacts():void
{
	stmt.sqlConnection = this.isDbConnected(conn);
	stmt.text = "SELECT * FROM contacts_t";
	
	stmt.addEventListener(SQLErrorEvent.ERROR, sqlError);
	stmt.addEventListener(SQLEvent.RESULT, sqlResult);
	stmt.execute();
}

private function displayFunc(item:Object):String {
	return item.contactName + "\t" + item.cellPhone + "\t" +item.email;
}

To create a folder, right click on the project root and select New Folder from the context menu. Likewise to import the icons, I right clicked on the folder and selected the Import command. In the import utility, I selected from the File System and browsed to the files. To import you need to select the folder where they are located. Then select the files in the provided field in the IDE.

Figure 4: The two options for adding icons to the Tab control
Figure 4: The two options for adding icons to the Tab control | Source
Figure 5: Setting up the Tab in the TabNavigator
Figure 5: Setting up the Tab in the TabNavigator | Source
Figure 6: Icon for Add Contact tab
Figure 6: Icon for Add Contact tab | Source
Figure 7: Icon for List Contacts tab
Figure 7: Icon for List Contacts tab | Source

Configuring Tabs


Since we are building a tab based application we need to add some tab navigators to our project before actually building the UI. When you select (click) the TabNavigator in the Design view of the IDE, there will appear a “+” and “-” symbol on the bottom left side of the component you either add or delete tab views in the component. Since we don’t have tabs, let us add two. For the first view “Insert ViewNavigator” popover, enter the title and label of the contact entry view. the adjacent screenshot on adding ViewNavigators is provided as a visual aid.
Add Contact (AddToList)

  • Title: AddToList
  • Label: Add Contact
  • icon:contact.png

The clipart icons were created using PowerPoint and were saved to the assets folder in the project. They are available for download from Wiki Commons or by right clicking on the icon images and electing to save them to your computer. For this example, I opted to simply add the icons to the ViewNavigators as is. You can however elect to “Embed” them and this will allow to add custom attributes like resizing or you can resize the icons in the software application that you used to created them to the size needed for your app.

List (ViewContactList)

  • Title: ViewContactList
  • Label: List
  • icon: assets/docList.png

One last bit of work to do before working on the individual views, is to change the titles of the ViewNavigators in the MXML code. See the AndroidSQlite.mxml code listing 1 above. The changes will immediately be reflected in the Visual Designer once you save the mxml file.

Switch to the Source Code view in the IDE
Locate the Title attribute in each of the ViewNavigator elements and change the title values to:

  • “Add Contact”
  • “List Contact List”

Save your changes was done.The initial UI layout, see screenshot below, features the various rough components and icons (oversized) the the two tabs. The “placeholder” section will embed the individual subviews that is associated with each tab. We will work on the individual views next. The individual subviews will be inserted automatically for you when the user clicks on one of the tabs. No additional coding is required for this.

Figure 8: Changing the view titles in the main MXML file AndroidSQLite.mxml
Figure 8: Changing the view titles in the main MXML file AndroidSQLite.mxml | Source
Figure 9: The main screen of the Android SQLite app. You can't add components here. These must be added in the individual MXML files.
Figure 9: The main screen of the Android SQLite app. You can't add components here. These must be added in the individual MXML files. | Source

Testing the App

Before getting to the subviews, you should run the app, thus far, to see if all the pieces are well connected. There is a couple of ways to run the app which requires a Run Configuration, if you running the app, and/or Debug Configuration if you need to debug your app. If you haven’t already created a Run or Debug configuration you can elect to click on the “Run” or “Debug” icon in toolbar and the appropriate utility will open for you to set the launch device if you are using the Simulator or if you want to run the app from the desktop. For this example, I chose the HTC Droid Eris. There are “Tens of Dozens” to choose from, refer to the screenshot below. Another way to setup the "Run" and "Debug" configurations is to create them via the "Run" menu in the Flash Builder IDE. Once you are satisfied that the app is running fine, shut it down and I will walk you through the steps in the next section to lay out the UI and add an ActionScript file to the project that will contain the code to interface with SQLite.

Figure 10: Creating a Run Configuration in Flash Builder 4.6 with the targeted device.
Figure 10: Creating a Run Configuration in Flash Builder 4.6 with the targeted device. | Source

Develop the App UI Layout

The next task in developing the Android SQLite app is laying out the various screens. As with this sample app, each screen is contained in its own MXML file which are located under the \views folder (see the Explorer) in the project. To lay out your screens you will need to open the individual subviews. In this example app, I have two subviews under the views folder:

  • ListView.mxml
  • AddContactView.mxml

Let’s start with the Add Contact view. These are the tasks that we will accomplish:

  1. Add input fields and
  2. Save button
  3. Define the dbOps.as ActionScript file
  4. Add ActionScript code to insert contact information from the Contact screen into the SQLite database

The code for the AddContactView.mxml is provided below. However we will lay out the Add Contact screen in the Visual Designer since this is easier and much faster than typing all the necessary code. The only additions to the MXML code that can't be added through the Designer view is a script element and adding the ActionScript function to the click event for the button. You will need to set the source attribute to "scripts/dbOps.as". Create the scripts folder as a sub-folder of the views parent folder otherwise you will get compile time errors. Since we didn't create the dbOps.as file yet, you will get errors. That is ok. We will create the file in a few steps. Here are the steps required to layout the "Add Contact" screen:

Switch to the "Design" view and locate the UITextInput component in the control palette in the IDE (you can also open it by selecting the "Window->Show View" in the IDE menu and selecting the "Components" view. Drag three UITextInput Components onto the screen, selecting each in kind and adding a name in the "id" property of each of the fields; one for the contact name, cell and e-mail address.

Also add three labels (Label) components and assign the following values to correspond to the fields:

  • Name
  • Cell
  • E-Mail

Finally drag a button component onto the visual designer. Change the name value "Save". Switch back to the Source view and the "insertContact" function to the button's click event. View the AddContact.mxml code listing for the proper signature and list of parameters.

Figure 4 below is the screenshot of the Add Contact screen once all the components have been added. We will work on the List Contacts next before attacking the ActionScript code.

Code Listing 3: The AddContact View (AddContactView.mxml)

<?xml version="1.0" encoding="utf-8"?>
<s:View xmlns:fx="http://ns.adobe.com/mxml/2009" 
		xmlns:s="library://ns.adobe.com/flex/spark" title="Add Contact">
	<fx:Declarations>
		<!-- Place non-visual elements (e.g., services, value objects) here -->
	</fx:Declarations>
	<fx:Script source="scripts/dbOps.as"/>
	<s:TextInput id="contactName" x="10" y="22"/>
	<s:TextInput id="cellPhone" x="9" y="82"/>
	<s:TextInput id="eMailAddress" x="9" y="146"/>
	<s:Label x="10" y="6" text="Name"/>
	<s:Label x="10" y="63" text="Cell"/>
	<s:Label x="10" y="124" text="E-Mail"/>
	<s:Button x="204" y="187" height="34" label="Save" click="insertContact(contactName.text, cellPhone.text, eMailAddress.text)"/>
</s:View>
Figure 11: The Add Contacts screen in the Android running app.
Figure 11: The Add Contacts screen in the Android running app. | Source

The List View

To display all the stored contacts in the SQLite database, we will need to implement either a List component or a SpinnerList, which is a similar implementation as the UIPickerView in the iOS SDK. Figure 5 provides a view of the List View in the IDE.

To setup the List component, drag a List component from the Component palette to the List View work area. With the List component selected in the work area, set the constraints (bottom right view- figure 5) to make the List component centered in the View component of the ListView.mxml file.

Provide an name to the List component by entering a unique name in the “id” property
To display the data, we will need to define a DataProvider. For this project, I am using the defined ArrayCollection variable, “contactList” which assigned to the DataProvider property either through the Design view or the Source view. The contactList variable is populated through the “selectContacts()” function. I will add the “selectContacts()” function to the contentCreationComplete attribute in the view element to load the contacts when the user select the List tab. The contentCreationComplete executes a function once the view has finished loading. Refer yourself to code listing 4 “The List View MXML Code (ListView.mxml)” on how to properly set both these properties.

Now we are ready to run the application again and start entering and viewing the data. The last screenshot shows the List view of the running app. I also have included a video of the running app to allow you to see how the app runs.

Code Listing 4: The List View MXML Code (ListView.mxml)

<?xml version="1.0" encoding="utf-8"?>
<s:View xmlns:fx="http://ns.adobe.com/mxml/2009" 
		xmlns:s="library://ns.adobe.com/flex/spark" title="List" contentCreationComplete="{selectContacts()}">
	<fx:Declarations>
		<!-- Place non-visual elements (e.g., services, value objects) here -->
	</fx:Declarations>
	<fx:Script source="scripts/dbOps.as"/>
	<s:List width="300" height="211" 
			horizontalCenter="0" 
			verticalCenter="0" 
			dataProvider="{contactList}"
			labelFunction="displayFunc"
			fontSize="9">
	</s:List>
</s:View>
Figure 12: Add the List component to the ViewList.mxml file
Figure 12: Add the List component to the ViewList.mxml file | Source
Figure 13 : The List view in the running Android App
Figure 13 : The List view in the running Android App | Source

More by this Author


Comments 24 comments

KDuBarry03 4 years ago

Wow, even though I'm not a computer wizard, I was able to follow through your information. Great article, Klanguedoc!


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Thanks Keith


NanoinUSA 3 years ago

Hi Klanguedoc.

Even though I am following all your indications, I get error 1180.

It says

1180: Call to a possibly undefined method dbinit.

How can I fix that error?


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

Did you add the script element and link to actionscript file?


NanoinUSA 3 years ago

Hi Klangue,

Yes I did. Both Views have the Script Source. Is there any other place I should add it?


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

NanoinUSA,

I know this might sound lame, but is there any typos in the name of the dbinit and the dbinit you are calling in your code?

Kevin


muhammad aasem 3 years ago

you need to enter the following line after declaration block (don't forget angle brakets):

fx:Script source="views/scripts/dbOps.as"/

so that the entire code becomes:


NanoinUSA 3 years ago

Hi klangedoc and Mohamad,

The code shows: (No brackets to avoid errors in the message

s:TabbedViewNavigatorApplication xmlns:fx="http://ns.adobe.com/mxml/2009"

xmlns:s="library://ns.adobe.com/flex/spark" applicationDPI="160" applicationComplete="dbinit(event)"

It doesn't matter if I try adding

fx:Script source="views/scripts/dbOps.as"/

as explained by Mohammad.

In the actionscript file:

public function dbinit(event:Event):void

No typos in the name.

Thank you for your help.


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

Hi muhammad aasem,

The code is actually there. Please see code listing 4. As NanoinUSA, I can send you the source code if you send me an e-mail for the project.

Hi NanoinUSA,

If you want I can send you the source code. Send me an e-mail (see my profile for the address).


TK429 3 years ago

Great post!! Exactly what I was looking for. Any chance of showing us how to add a 'delete record' function.

Thanks again!!


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

the delete is just the other function except use :

delete from table

where column='"value"'


Ochko 3 years ago

hi? First of all it is very good tutorial to me. But i have a following error

SQLError: 'Error #3115: SQL Error.', details:'no such table: 'contacts_t'', operation:'execute', detailID:'2013'

how do i fix it?


Ochko 3 years ago

Hi? It's me again. Now i have a following error:

1180: Call to a possibly undefined method dbinit.

Hope you can help me


Ochko 3 years ago

Hi? It's me again. I solved the problem. I added following code to my AndroidSQlite.mxml

fx:Script source="views/scripts/dbOps.as"/

Cheers! Thank you so much!


Ochko 3 years ago

Hi? Thank you so much! I added Delete and Update actions. It's all working now. I'm so happy. Thank you again!


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

Hey Ochko,

I am glad everything worked out for you. It is often some little thing that does wrong that frustrate us


Fady Rashwan profile image

Fady Rashwan 3 years ago

hi

i have a following error

SQLError: 'Error #3115: SQL Error.', details:'no such table: 'contacts_t'', operation:'execute', detailID:'2013'

how do i fix it?


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

does your table exist in the database ? I know it is a stupid question but sometimes a simple typo can throw you off. What the double quotes for after contact_t" for?


Kurt 2 years ago

Hey, i got the same error. Flash builder gives an error that he can't find the database. How did you create your table? with which program. I tried using acces and even Firefox SQL manager but all those files are names different and it's never a .db file.

Any feedback will be appreciated


Klanguedoc 2 years ago

I built the db through an inline query. However you can use SQL manager in Firefox


Pilar 2 years ago

Hey i got same error, I create table, but I do not know where I put db?


klanguedoc profile image

klanguedoc 2 years ago from Canada Author

in the main folder


Asim 6 months ago

hi

i have a following error

SQLError: 'Error #3115: SQL Error.', details:'no such table: 'contacts_t'', operation:'execute', detailID:'2013'

how do i fix it plz solve it?


klanguedoc profile image

klanguedoc 6 months ago from Canada Author

First can you see that the db is open. Second, can u open the db using SQLite Mgr in Firefox and visually verify that the table was created. Basically the message is saying that the table doesn't exist

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working