Adobe AIR SQLite Example

20 April 2010

Line Break

Author: Janez Feldin (6 Articles) - Author Website

Janez likes to experiment with flash in his own free time. His other hobbies are playing volleyball, listening to the music, watching movies and above all else, paragliding.

AIR applications that doesn’t need to store data on your computer are extremely rare. That is why I have created a basic application that should help you understand and learn how you can store data in SQLite database. The example is really basic but it should get you an idea on how to do it.

You can download the AIR application right here (view source is enabled).

If you don’t want to download the application, below is the complete source.

SQLiteExample.mxml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009"
xmlns:s="library://ns.adobe.com/flex/spark"
xmlns:mx="library://ns.adobe.com/flex/mx"
creationComplete="start()" width="800" height="600" viewSourceURL="srcview/index.html">
<fx:Declarations>

</fx:Declarations>

<fx:Script source="SQLiteCode.as"/>

<s:Label x="10" y="20" text="First name:"/>
<s:Button x="394" y="10" label="Add" click="addItem()"/>
<s:Label x="202" y="20" text="Last name:"/>
<s:Button label="Remove selected" y="10" x="472" click="remove()" enabled="{dg.selectedIndex != -1}"/>
<s:TextInput x="77" y="10" width="117" id="first_name"/>
<s:TextInput x="268" y="10" width="117" id="last_name"/>
<mx:DataGrid id="dg" left="10" right="10" top="40" bottom="10" dataProvider="{dp}">
<mx:columns>
<mx:DataGridColumn headerText="Index:" dataField="id"/>
<mx:DataGridColumn headerText="First name" dataField="first_name"/>
<mx:DataGridColumn headerText="Last name" dataField="last_name"/>
</mx:columns>
</mx:DataGrid>

</s:WindowedApplication>

SQLiteCode.as

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

import flash.data.SQLStatement;
import flash.errors.SQLError;
import flash.events.Event;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;
import flash.events.TimerEvent;
import flash.filesystem.File;
import flash.utils.Timer;

import mx.collections.ArrayCollection;
import mx.controls.Alert;
import mx.utils.ObjectUtil;

import org.osmf.events.TimeEvent;

// sqlc is a variable we need to define the connection to our database
private var sqlc:SQLConnection = new SQLConnection();
// sqlc is an SQLStatment which we need to execute our sql commands
private var sqls: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 dp:ArrayCollection = new ArrayCollection();

// function we call at the begining when application has finished loading and bulding itself
private function start():void
{
// first we need to set the file class for our database (in this example test.db). If the Database doesn't exists it will be created when we open it.
var db:File = File.applicationStorageDirectory.resolvePath("test.db");
// after we set the file for our database we need to open it with our SQLConnection.
sqlc.openAsync(db);
// we need to set some event listeners so we know if we get an sql error, when the database is fully opened and to know when we recive a resault from an sql statment. The last one is uset to read data out of database.
sqlc.addEventListener(SQLEvent.OPEN, db_opened);
sqlc.addEventListener(SQLErrorEvent.ERROR, error);
sqls.addEventListener(SQLErrorEvent.ERROR, error);
sqls.addEventListener(SQLEvent.RESULT, resault);

}

private function db_opened(e:SQLEvent):void
{
// when the database is opened we need to link the SQLStatment to our SQLConnection, so that sql statments for the right database.
// if you don't set this connection you will get an error when you execute sql statment.
sqls.sqlConnection = sqlc;
// in property text of our SQLStatment we write our sql command. We can also combine sql statments in our text property so that more than one statment can be executed at a time.
// in this sql statment we create table in our database with name "test_table" with three columns (id, first_name and last_name). Id is an integer that is auto incremented when each item is added. First_name and last_name are columns in which we can store text
// If you want to know more about sql statments search the web.
sqls.text = "CREATE TABLE IF NOT EXISTS test_table ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT, last_name TEXT);";
// after we have connected sql statment to our sql connection and writen our sql commands we also need to execute our sql statment.
// nothing will change in database until we execute sql statment.
sqls.execute();
// after we load the database and create the table if it doesn't already exists, we call refresh method which i have created to populate our datagrid
refresh();
}
// function to add item to our database
private function addItem():void
{
// in this sql statment we add item at the end of our table with values first_name.text in column first_name and last_name.text for column last_name
sqls.text = "INSERT INTO test_table (first_name, last_name) VALUES('"+first_name.text+"','"+last_name.text+"');";
sqls.execute();

refresh();
}

// function to call when we want to refresh the data in datagrid
private function refresh(e:TimerEvent = null):void
{
// timer object which we need if sql statment is still executing so that we can try again after 10 milliseconds.
var timer:Timer = new Timer(10,1);
timer.addEventListener(TimerEvent.TIMER, refresh);

if ( !sqls.executing )// we need to check if our sql statment is still executing our last sql command. If so we use Timer to try again in 10 milliseconds. If we wouldn't check we could get an error because SQLStatment can't execute two statments at the same time.
{
// sql statment which returns all the data from our "test_table". To retrive only data from first_name and last_name columns we would use "SELECT first_name,last_name FROM test_table"
sqls.text = "SELECT * FROM test_table"
sqls.execute();
}
else
{
timer.start();
}
}

// method that gets called if we recive some resaults from our sql commands.
//this method would also get called for sql statments to insert item and to create table but in this case sqls.getResault().data would be null
private function resault(e:SQLEvent):void
{
// with sqls.getResault().data we get the array of objects for each row out of our database
var data:Array = sqls.getResult().data;
// we pass the array of objects to our data provider to fill the datagrid
dp = new ArrayCollection(data);
}

// method to remove row from database.
private function remove():void
{
// sql statment to delete from our test_table the row that has the same number in number column as our selected row from datagrid
sqls.text = "DELETE FROM test_table WHERE id="+dp[dg.selectedIndex].id;
sqls.execute();
refresh();
}
// method which gets called when we recive an error from sql connection or sql statment and displays the error in the alert
private function error(e:SQLErrorEvent):void
{
Alert.show(e.toString());
}

Thanks to Janez Feldin for this guest post. Check out this page if you want to see more Adobe AIR Examples.

Written by Janez Feldin · Filed Under Examples

Was this post useful to you?

Please rate this post, follow us @ twitter, or link to this page from your website!

(32 votes, average: 4.81 out of 5)

Loading ...

34cc8ba2d800ed1042be3ab439739d07delicious

Comments

37 Responses to “Adobe AIR SQLite Example”

abdo on October 19th, 2010 10:13 am

welcome
FodorR on November 8th, 2010 6:17 pm

you saved me
thanks a lot for this example
manoj on December 2nd, 2010 5:28 pm

very use full information regarding sqlite db.

Thanks
Manoj
Robert on January 7th, 2011 11:13 pm

Great tutorial.
Can you let me know how to insert the value of a checkbox?
I tried couple of ways but always insert a true value with or without the checkbox selected.

Thanks
Janez Feldin on January 25th, 2011 12:23 pm

For Boolean values I saved 0 or 1 and than just transform that in a boolean value when you read out of database…

You can also save as strings “true” or “false” you just need to make sure to transform what you saved to a Boolean value before you add it to your checkbox…
Flash Builder 4 (AIR) & SQLite: A Simple Alternative to Java/MySQL « The Coding Biologist on January 28th, 2011 11:58 am

[...] AIR app couldn’t be easier, as you write out your SQL commands within ActionScript itself: click here for a sample script. No Java, no Tomcat server, and no BlazeDS required! [...]
Damon on February 19th, 2011 5:24 am

This was a wonderful introduction. So good to read along with the code and your annotations make total sense. Wish there was something like this for every concept I need to grasp!
barecool on March 19th, 2011 1:58 am

is it possible check credentials for login and check them against a local database(sqlite) with out any serverside scripting.

meaning get the username and password and check them against user info in the database in order to login.

is it possible to do in AS3 only?
Janez Feldin on March 21st, 2011 5:57 pm

That’s not possible… Any comunication between server and flash require some sort of server side script…
danbo on April 8th, 2011 8:07 am

do you know how to place the columns in a Air-datagrid in the same order than in the database:
for instance:
table clients : num,name, address, family of products
and I receive that in my datagrid:

address, num, family of products, name ?

Thanks for you explanations.
Ritesh on April 11th, 2011 2:51 pm

Hey I am getting this error..Can u pls help me…

Error: Error #3109: Operation is not permitted when the SQLStatement.sqlConnection property is not set.
at Error$/throwError()
at flash.data::SQLStatement/checkAllowed()
at flash.data::SQLStatement/checkReady()
at flash.data::SQLStatement/execute()
at Main/addItem()[E:\FlashBuilder\SQLite Example\src\SQLiteCode.as:58]
at Main/___Main_Button1_click()[E:\FlashBuilder\SQLite Example\src\Main.mxml:29]
Janez Feldin on April 12th, 2011 4:03 pm

You didn’t set the sqlConnection property on your SQLStatement variable… before you access the database you need to set the connection…
Jose Oviedo on May 5th, 2011 6:09 pm

Hey, thx, but….what is ther SQLite version in use?
Jose Oviedo on May 5th, 2011 6:34 pm

me..again…XD….man, where is the Db created?
Janez Feldin on May 9th, 2011 2:21 pm

About SQLite version:
AIR 2.0 has SQLite 3.6.16
That’s the information I found. I hope it is correct.

About storage path:
I defined the path to the database in line 28 with:
File.applicationStorageDirectory.resolvePath(“test.db”)

File.applicationStorageDirectory is (on Windows 7) something like this:
C:\Users\{windows user}\AppData\Roaming\{app name}

you can find more details about File paths at the following link:
http://help.adobe.com/en_US/AIR/1.5/devappshtml/WS5b3ccc516d4fbf351e63e3d118666ade46-7fe4.html#WS5b3ccc516d4fbf351e63e3d118666ade46-7dbe
rick21 on June 7th, 2011 10:55 am

dear sir..
can you provide similar project like this..which is ‘flex mobile using sqlite’ that using offline database file..because i’m on the research but still got nothing..
thanks hope you can help..
Janez Feldin on June 7th, 2011 11:10 am

I am not familiar with mobile apps development,but as far as I know the same code should work on AIR for mobile. You just need to modify the mxml files to fit AIR for mobile and you should be good.

Try creating a normal app for mobile and than just take the actionscript from this example and paste that to your app.
Giacomo Sam on June 14th, 2011 3:43 pm

Thank very much, we need more AIR app Examples. Slds
David on June 23rd, 2011 12:49 am

To those who are getting the error referred to by Ritesh on April 11 (see below for copy/paste of that post), you need to make sure to include “creationComplete=”start()” at the beginning of your MXML. I ran into the same error and banged my head against the wall on this one, so I hope this helps.

********************
Hey I am getting this error..Can u pls help me…
Error: Error #3109: Operation is not permitted when the SQLStatement.sqlConnection property is not set.
at Error$/throwError()
at flash.data::SQLStatement/checkAllowed()
at flash.data::SQLStatement/checkReady()
at flash.data::SQLStatement/execute()
at Main/addItem()[E:\FlashBuilder\SQLite Example\src\SQLiteCode.as:58]
at Main/___Main_Button1_click()[E:\FlashBuilder\SQLite Example\src\Main.mxml:29]
mesmo on June 27th, 2011 12:39 pm

Hello janez,

I tried add two more label ” username” and “password”, i already create a desktop adobe air chat application and as all chat applications must have username and password labels, well i need to create and later include the database inside the air application to can add users or remove users that who will using the chat application, please if have some examples or how i create the database for users and how include inside the air application code and also ow do the connection between application and database wi will be thanks too much for your help.

Best,
Janez Feldin on June 27th, 2011 1:57 pm

Hello mesmo,

The best way to do this, would be to have an online database which contains your authentication data. Because if you want users to chat they need internet connection anyway. So create an online database, and don’t store usernames and passwords for all users on all computers that have installed this app. It’s not the safest way.

So use some server side language to magane registrations, logins,… (some php script or something like that). You can even use Flex builders built in data services to connect to server, which I think would be the easiest way to do it.(try with researching those features)

If you are familiar with php or some other server side language (just familiar with it, not necessary to know it), you can also find loads of examples on the internet how to connect flash and server. (php is the most commonly used language, so you can find the most examples for that)

I realy wouldn’t recommend storing login data for all the users on all computers because, no one will want to use so unsafe application.

Regards,
Janez
mesmo on June 27th, 2011 5:50 pm

Hello Janez,

Thanks too much for quick reply also thanks for your advice, may if you can path some urls about examples will be too helpful, frankly im not familiar
with php so i believe im gonna other way that you explain by flex bulider also im using in application adobe lccs do you think this give any problem about your suggest, please any ideas will be helpful too me.
Thanks too much

Best,
Janez Feldin on June 27th, 2011 6:44 pm

Here is one:
http://www.flashrealtime.com/flash-builder-4-and-php-data-services/
If it’s not enough, try searching a bit more. Offcorse you will have to modify things for your needs.
mesmo on June 27th, 2011 6:57 pm

Janez, thanks too much for help, you the man, im gonna look to website and later i report you, thanks!!

Best,
Myles on July 14th, 2011 10:28 pm

Hi there Janez.

I was wondering, how would one go about making the rows editable in this example? Like say I spelled the name incorrectly, how could i set it up to where when I click on the name, the data fields at the top fill with the clicked data, and the button changes from “Add” to “Update”

Thanks so much for any help.
Janez Feldin on July 15th, 2011 11:20 am

First you would have to listen for change event on datagrid. When Somone clicks on an item, the change event fires and you can use dp[dg.selectedIndex] to get the selected item.

You can than change the values of text inputs to selected first and second name and you can also change the button label to update, but I would rather add a new button with update label and enable it only when dg.selectedIndex != -1, which means that when you have selected an item this button will be enabled and otherwise not (ofcorse you would have to bind this).

And finally you can execute sql update query. Here is some documentation: http://www.w3schools.com/sql/sql_update.asp
And for this example I would do it like that:

sqls.text = “UPDATE test_table SET first_name=’”+last_name.text+”‘, last_name=’”+last_name.text+”‘”;
sqls.execute();
Myles on July 15th, 2011 6:03 pm

Awesome! Thank you so much!
Tac on August 10th, 2011 3:10 pm

Thanks very the example, very helpful. I think the .as file needs this at the top:

import flash.data.SQLConnection;
Akella on September 21st, 2011 2:20 pm

Hi. I have a question! How can i connect & change created bd on local machnie from another
Janez Feldin on September 21st, 2011 3:26 pm

The only way this would be possible is if you have enabled file sharing over your computers, than you should probably be able to load the database from another computer like you would open a file from that computer (for that try searching for file sharing over local network). Otherwise this is not possible, because SQLite is not meant to run as a server like MySQL or others… If you explain some more details of what you want to do I might suggest you a better way to do it.
supi on October 3rd, 2011 11:24 am

great article! needed nothing more than a copy paste to run this app

B/w i would like your advice on the question that Akella asked. Here are more details on what i wished to do:
I am trying to create an Air App that is supposed to be built for a hotel where they have an intranet in place(4 to 5 systems, with one of them as server).
An ideal scenario would be one where there would be multiple Air client apps trying to access and write onto a single db on the server. From what you have told to Akella, SQLite does not seem a fit.
So would this mean I will have to opt for a regular PHP/MySQL/AMFPHP option?
Can you tell or link me to where I can find how to set up a MySQL for an Intranet?
There is no server software installed as such so that would mean that there will be no addressing like “http:///”. It will just be the regular file:/// i guess.

Any help would highly be appreciated.
Thanks!!
vinumac on October 22nd, 2011 8:36 pm

Hi Janez

I am a beginner at flex.

I am trying to write a simple flex application that gets data via text fields and writes to a sqlite database – very much like what you have shown here.

I have downloaded the flex sdk and am trying to compile your source code from the command line using mxmlc – but have not been successful. Could you provide some guidance as to how
to develop database flex apps using mxmlc command-line?

I also compiled using “amxmlc” compiler and it compiles without warnings or errors into a SWF file but when I open it using IE or Adobe Flash Player it opens up and shows a blank screen.

Any help would be appreciated.

Thanks
Vinnie
Janez Feldin on October 22nd, 2011 9:46 pm

As I understand you are using command prompt to compile the swf file? So you probably went for free flex sdk on Adobe site? If so the problem is, that I am not using the same sdk (I am using sdk for flash builder ). So my SDK probably containes something that yours doesn’t.

Since I never worked with command-lines, I can’t really help you with that but I can recommend some other was to get free software, so you can avoid compiling with command-line.

1) Try this site if you meet the criteria for non-commercial version of Flash builder 4.5
2) You can try some free flash builder replacements like Flash develop: http://www.flashdevelop.org/wikidocs/index.php?title=Main_Page or any other.

Both of this programms shoud help you alot while building your applications for flash.

I hope this helps, if not feel free to ask some more questions.

Regards,
Janez
vinumac on October 22nd, 2011 11:08 pm

Hi Janez

Very much appreciate your answers.

You are right – I downloaded the free Flex SDK from the Adobe site and am trying to use that for my application.

I will try to use your suggestions and will let you know how it goes.

Once again – thanks for the answers.

Regards
yweeen on November 30th, 2011 9:52 pm

Is there anyway i can create two tables in a single database? I tried adding sqls.text = “CREATE TABLE IF NOT EXISTS test_table ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT, last_name TEXT);”;
// after we have connected sql statment to our sql connection and writen our sql commands we also need to execute our sql statment.
// nothing will change in database until we execute sql statment.
sqls.execute();

but it does not work. it gives me an error
Janez Feldin on November 30th, 2011 10:58 pm

This should work, so what kind of error do you get?

Also you cannot execute two sql statements at once so you need to execute one, wait to get the resaults and execute another one. If you put this tho sql statements right by each other that you probably get an error because of that.
Justin Foley on December 9th, 2011 3:32 am

F****ng beautiful!! Thank you very much!