Using the SQLite Database

Using the SQLite database system is another solution for saving local persistent data, and it is the preferred solution if your information is somewhat complex, if you want the option to organize it in different ways, or if you want to keep it private.

The AIR runtime contains an SQL database engine to create, organize, retrieve, and manipulate the data, using the open source Structured Query Language Lite (SQLite) database system. It does not use the Android OS SQLite framework.

The SQL classes compose the bulk of the flash.data package. Once again, you have a choice between synchronous and asynchronous mode. For the sake of simplicity, we will use synchronous mode in our examples.

Creating the database file

If the database doesn’t exist yet, create it and save it as a single file in the filesystem:

import flash.filesystem.File;
function createDatabase():void {
var file:File =
File.applicationStorageDirectory.resolvePath(“myData.db”);
if (file.exists) {
trace(“I already exist, ready to be used”);
} else {
trace(“I did not exist, now I am created”);
}
}

It is usually a good idea to keep the database in the ApplicationStorageDirectory directory so that it is not accessible by other applications and it is preserved when the application is updated. If you want to save it to the SD card instead, the path should be:

var file:File = File.documentsDirectory.resolvePath(“myData.db”);

Opening the database file

The SQLConnection class is used to create queries or execute them. It is essential that it is a class variable, not a local variable, so that it doesn’t go out of scope.

import flash.data.SQLConnection;
var connection:SQLConnection;
connection = new SQLConnection();

To open the connection pointing to your database file, call the open method and pass the File reference:

import flash.events.SQLEvent;
import flash.events.SQLErrorEvent;
try {
connection.open(file);
trace(“connection opened”);
} catch(error:Error) {
trace(error.message);
}

Creating the table

An SQL database is organized into tables. Each table consists of columns representing individual attributes and their values. Create the table according to your needs by giving each column a name and a data type. The table will have as many rows as items, or records, created.

You communicate to the database by creating an SQLStatement object and then sending its sqlConnection property to the connection that is open. Next, write the command to its text attribute as a string, and finally, call its execute method.

In this example, we are creating a new table called geography using the statement CREATE TABLE IF NOT EXISTS to guarantee that it is only created once. It has three columns: an id column which self-increments and functions as the primary key, a country column of type Text, and a city column of type Text. The primary key is a unique identifier to
distinguish each row. Figure 6-1 shows the geography table:

import flash.data.SQLStatement;
import flash.data.SQLMode;
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = connection;
var request:String =
“CREATE TABLE IF NOT EXISTS geography (”
+ “id INTEGER PRIMARY KEY AUTOINCREMENT, country TEXT, city TEXT )”;
statement.text = request;
try {
statement.execute();
} catch(error:Error) {
trace(error.message);
}

Adding data

Once the table is created, data is added using an INSERT INTO statement and some values:

var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = connection;
var insert:String =
“INSERT INTO geography (country, city) VALUES (‘France’, ‘Paris’)”;
statement.text = insert;
try {
statement.execute();
} catch(error:Error) {
trace(error.message);
}

If the data is dynamic, you can use the following syntax. Note that unnamed parameters are used, therefore relying on the automatically assigned index value. Figure 6-2 shows the result:

addItem({country:”France”, city:”Paris”});
function addItem(object:Object):void {
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = connection;
var insert:String = “INSERT INTO geography (country, city) VALUES (?, ?)”;
statement.text = insert;
statement.parameters[0] = object.country;
statement.parameters[1] = object.city;
try {
statement.execute();
trace(“item created”);

} catch(error:SQLError) {
trace(error.message);
}
}

As an alternative, you can use the following syntax. Here we assume named parameters that work much like an associate array. Figure 6-3 shows the result:

that work much like an associate array. Figure 6-3 shows the result:
addItem({country:”United States”, city:”New York”});
function addItem(object:Object):void {
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = connection;
var insert:String =
“INSERT INTO geography (country, city) VALUES (:co, :ci)”;
statement.text = insert;
statement.parameters[“:co”] = object.country;
statement.parameters[“:ci”] = object.city;
try {
statement.execute();
trace(“item created”);
} catch(error:SQLError) {
trace(error.message);
}
}

Using either of these two dynamic approaches facilitates re-use of the same SQL statement to add many items, but is also more secure because the parameters are not written in the SQL text. This prevents a possible SQL injection attack.

Requesting data

Data is requested by using the SELECT statement. The result is an SQLResult that you can get as a property of the SQLStatement: statement.getResult(). Each row item is received as an object with property names corresponding to the table column names. Note the use of the * in place of the columns’ name to get the entire table:

import flash.data.SQLResult;
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = connection;
statement.text = “SELECT * FROM geography”;
statement.addEventListener(SQLEvent.RESULT, selectionReceived);
statement.execute();
function selectionReceived(event:SQLEvent):void {
statement.removeEventListener(SQLEvent.RESULT, selectionReceived);
var result:SQLResult = statement.getResult();
if (result != null) {
var rows:int = result.data.length;
for (var i:int = 0; i < rows; i++) {
var row:Object = result.data[i];
trace(row.id + “” + row.country + “” + row.city);
}
}
}

Instead of requesting the entire table, you may want to receive only one item in the table. Let’s request the country that has New York as a city. Execute(1) only returns the item stored under table ID 1:

var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = connection;
statement.text = “SELECT country FROM geography WHERE city = ‘New York'”;
try {
statement.execute(1);
var result:SQLResult = statement.getResult();
if (result.data != null) {
trace(result.data[0].country);
}
} catch(error:Error) {
trace(“item”, error.message);
}

Let’s make the same request again, passing the city as dynamic data:

getCountry(“New York”);
function getCountry(myCity:String):void {
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = connection;
statement.text = “SELECT country FROM geography WHERE city = :ci”;
statement.parameters[“:ci”] = myCity;
try {
statement.execute(1);
var result:SQLResult = statement.getResult();
if (result.data != null) {
trace(result.data[0].country);
}
} catch(error:Error) {
trace(“item”, error.message);
}
}

Editing existing data

Existing data can be modified. In this example, we’re searching for the country United States and changing the city to Washington, DC. Figure 6-4 shows the result:

modifyItem(“United States”, “Washington DC”);
function modifyItem(myCountry:String, myCity:String):void {
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = connection;
var updateMessage:String =
“UPDATE geography SET city = :ci where country = :co”;
statement.text = updateMessage;
statement.parameters[“:co”] = myCountry;
statement.parameters[“:ci”] = myCity;
try {
statement.execute();
trace(“all removed”);
} catch(error:Error) {
trace(“item”, error.message);
}
}

Now let’s look for the country France and delete the row that contains it (see Figure 6-5). We are using the DELETE FROM statement. Note that deleting a row does not modify the IDs of the other items. The ID of the deleted row is no longer usable:

function deleteItem(myCountry:String):void {
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = connection;
var deleteMessage:String = “DELETE FROM geography where country = :co”;
statement.text = deleteMessage;
statement.parameters[“:co”] = myCountry;
try {
statement.execute();
trace(“all removed”);
} catch(error:Error) {
trace(“item”, error.message);

}
}

As you have seen, you can do a lot while working within a structure you create.