Sunday, May 8, 2011

Creating an AIR application with an SQLite database: Part 3

Today we keep making our contact book.

In the end of the previous tutorial, we've set up a selectItems function that is called when we need to select the items from the database, but we've left that function empty.

Let's fill it with code - we'll need to make another SQLStatement object, feed it a SELECT command and tell it to call onSelected function when it's done.

function selectItems(SQLEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone FROM contacts ORDER BY id";
stat.execute(-1, new Responder(onSelected));
}

The onSelected function needs to turn the results (which is an associative array of data) into the list items of our itemList object on stage. We need to check if the data has something in it - if it does - proceed to take it appart through a loop and add items to the list. We set the data property of the list item to the data object of the results, which is the object with properties fname, lname and phone.

function onSelected(evt:SQLResult):void
{
if (evt.data != null)
{
itemList.dataProvider = new DataProvider();
for (var i:int=0; i<evt.data.length; i++)
{
itemList.addItem({label:(evt.data[i].fname + " " + evt.data[i].lname), data:evt.data[i]});
}
}
}

Now let's add a feature to create new entries in our contacts book.

Here's how it is going to work: the user fills out 3 text fields (first name, last name and phone number) and hits the Creat new item button - a new item is added to the database, the database is re-read and the list is re-filled with items.

Firstly, add a mouse listener for the button:

b_new.addEventListener(MouseEvent.CLICK, createNew);

Next, the function itself. We send a INSERT INTO command, note that it will only work if all the text fields are filled out correctly. For example, nothing will happen if you put "hello" in the phone number field, as we've told the SQLite engine that it is going to be an integer value.

function createNew(MouseEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO contacts (fname, lname, phone) VALUES (@fname, @lname, @phone)";
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.execute(-1, new Responder(selectItems));
}

As you can see, this code calls the already existing function called selectItems.

Now, you can test your code and make a few entries and see them appear in the list. Let's make sure that the user will only type numbers in the phone field:

t_phone.restrict = "0-9";

And the full code:

import flash.filesystem.File;
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.net.Responder;
import flash.data.SQLResult;
import fl.data.DataProvider;

var connection:SQLConnection;

openDatabase();
t_phone.restrict = "0-9";

function openDatabase():void
{
var dbFile:File = File.applicationStorageDirectory.resolvePath("database.db");
connection = new SQLConnection();
connection.addEventListener(SQLEvent.OPEN, onOpen);
connection.openAsync(dbFile, SQLMode.CREATE);
}

function onOpen(SQLEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "CREATE TABLE IF NOT EXISTS contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT, lname TEXT, phone INTEGER)";
stat.execute(-1, new Responder(selectItems));
}

function selectItems(SQLEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone FROM contacts ORDER BY id";
stat.execute(-1, new Responder(onSelected));
}

function onSelected(evt:SQLResult):void
{
if (evt.data != null)
{
itemList.dataProvider = new DataProvider();
for (var i:int=0; i<evt.data.length; i++)
{
itemList.addItem({label:(evt.data[i].fname + " " + evt.data[i].lname), data:evt.data[i]});
}
}
}

b_new.addEventListener(MouseEvent.CLICK, createNew);

function createNew(MouseEvent):void
{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO contacts (fname, lname, phone) VALUES (@fname, @lname, @phone)";
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.execute(-1, new Responder(selectItems));
}

Thanks for reading!

Related:

Creating an AIR application with an SQLite database: Part 1
Creating an AIR application with an SQLite database: Part 2
Creating an AIR application with an SQLite database: Part 4

2 comments:

Xander said...

Does not work.
event.data gives error

1119: Access of possibly undefined property data through a reference with static type flash.events:SQLEvent.

You probably need to do:

var result:SQLResult = stat.getResult();

rponweb said...

Excellent Tutorial .Helped me a lot.. thanks a Ton

Post a Comment