• Choose a category
• All
• Classic-Fiction

By Root 2485 0

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.

NOTE

SQLite stores the expected data types such as Integer, Text, Real, and Null. It also supports the BLOB type, a raw binary data type which stores information exactly as it was input. This is particularly useful for storing a ByteArray. You can read about SQLite at http://www.sqlite.org/datatype3.html.

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:

Figure 6-1. The geography table’s fields

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;