Array

Using the SQLite Database

Must Read

Unsecured Consolidation Loans – Weigh Your Pros And Cons Before Making Up Your Mind

Unsecured consolidation loans are the best way to repay your loans and get a control over your debts. People...

Debt Consolidation Loan Consequences – Discover the Consequences of Debt Consolidation

Debt consolidation is a very widely known idea and is therefore one of the first things that come to...

Unsecured Debt Consolidation Loan – Combine Various Payments Into One

If you have small balance payments to make towards old loans, and as a result of which your monthly...

Camera inspired 180 degrees, infinite depth of field

Technologists accept been cartoon afflatus from the insect apple for a continued time. And association alive on robotics absolutely...

How Much Traffic Are Law Firms Missing?

Patience may be a virtue, but it is new cases that keep your law firm growing; waiting for someone...

Wells Fargo Home Refinance Or Modification With the Stimulus Plan

Being one of only a few selected lenders to offer President Obamas stimulus plan, Wells Fargo, is going all...
Admin
test

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:

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;
statement.parameters[1] = object.city;
try {
statement.execute();
trace(“item created”);

The geography table with some dynamic data added

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

The geography table with dynamic data and named parameters added

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

The geography table with existing data modified

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

}
}

The geography table with row 0 deleted

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

 

Previous articleThe Filesystem
Next articleEmbedding a Database

1 COMMENT

Comments are closed.

Latest News

Digital Marketing for Beginners

Digital marketing for starter, Let to basic learning about connecting with your audience in the right place...

What are 7 things poor people do that the rich don’t?

1. poor people watch TV in which people read books how many hours you spend in front of the TV and when...

Top 18 best small business ideas for beginners starting

A small business can be frightening and requires plenty of careful planning there are many small business ideas which can be beneficial...

Summer that makes you happy

We saw were already here I've been thinking about some of the things. I used to do with my husband even though...

4 Point to helpful tips specifically for caregivers

What you need to take a vacation. I know it sounds impossible creative and try to make it work for you almost...

More Articles Like This