Saturday, May 7, 2011

Creating an AIR application with an SQLite database: Part 2

Today we will start writing the code for our SQLite database powered AIR application.

Now that we have all the visual elements of our application set up, we can get to the scripting.

First of all, we need to create the connection variable.

var connection:SQLConnection;

This is the variable we're going to need to 'communicate' with our database.

Call a function called openDatabase().

openDatabase();

This is the initial function that will handle establishing connection to the database and create it if it doesn't exist.

Firstly, we set up a File object that points to the database file that is (or should be) in the application storage directory. After that, we add an SQLEvent.OPEN listener for our connection, which will call a function called onOpen when the file is successfully opened. We open the file using the openAsync() method.

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

Next - the onOpen function. Here's where we create a SQLStatement object and use it to create the contacts table in our database if it doesnt exist yet. To do that, we first need to set the sqlConnection parameter of the SQLStatement object to our connection variable, then we pass the statement string and execute the statement. We add a function that is called once the execution has got results in the execute() function.

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));
}

Because we've set the Responder object to point to a function called selectItems, this function will be called once the command was successfully executed. For now, the selectItems is empty. I added a trace to test the code:

function selectItems(SQLEvent):void
{
trace("Select items!");
}

Now, if you test your code, you can see that everything works alright if you get "Select items!" outputted in the debug window.

Full code:

import flash.filesystem.File;
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.net.Responder;

var connection:SQLConnection;

openDatabase();

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
{
trace("Select items!");
}

Thanks for reading!

Related:

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

2 comments:

Anonymous said...

1120: Access of undefined property SQLMode. :(

Anonymous said...

I know now is because SQLMode is not imported
import flash.data.SQLMode;

Post a Comment