42
205
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
• For any application, a database can be used to store individual users’ application settings, such as user options or
application information like window size and position.
More Help topics
Christophe Coenraets: Employee Directory on AIR for Android
Raymond Camden: jQuery and AIR - Moving from web page to application
About AIR databases and database files
Adobe AIR 1.0 and later
An individual Adobe AIR local SQL database is stored as a single file in the computer’s file system. The runtime
includes the SQL database engine that manages creation and structuring of database files and manipulation and
retrieval of data from a database file. The runtime does not specify how or where database data is stored on the file
system; rather, each database is stored completely within a single file. You specify the location in the file system where
the database file is stored. A single AIR application can access one or many separate databases (that is, separate
database files). Because the runtime stores each database as a single file on the file system, you can locate your database
as needed by the design of your application and file access constraints of the operating system. Each user can have a
separate database file for their specific data, or a database file can be accessed by all application users on a single
computer for shared data. Because the data is local to a single computer, data is not automatically shared among users
on different computers. The local SQL database engine doesn’t provide any capability to execute SQL statements
against a remote or server-based database.
About relational databases
Adobe AIR 1.0 and later
A relational database is a mechanism for storing (and retrieving) data on a computer. Data is organized into tables:
rows represent records or items, and columns (sometimes called “fields”) divide each record into individual values.
For example, an address book application could contain a “friends” table. Each row in the table would represent a
single friend stored in the database. The table’s columns would represent data such as first name, last name, birth date,
and so forth. For each friend row in the table, the database stores a separate value for each column.
Relational databases are designed to store complex data, where one item is associated with or related to items of
another type. In a relational database, any data that has a one-to-many relationship—where a single record can be
related to multiple records of a different type—should be divided among different tables. For example, suppose you
want your address book application to store multiple phone numbers for each friend; this is a one-to-many
relationship. The “friends” table would contain all the personal information for each friend. A separate “phone
numbers” table would contain all the phone numbers for all the friends.
In addition to storing the data about friends and phone numbers, each table would need a piece of data to keep track
of the relationship between the two tables—to match individual friend records with their phone numbers. This data is
known as a primary key—a unique identifier that distinguishes each row in a table from other rows in that table. The
primary key can be a “natural key,” meaning it’s one of the items of data that naturally distinguishes each record in a
table. In the “friends” table, if you knew that none of your friends share a birth date, you could use the birth date
column as the primary key (a natural key) of the “friends” table. If there isn’t a natural key, you would create a separate
primary key column such as a “friend id” —an artificial value that the application uses to distinguish between rows.
47
206
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
Using a primary key, you can set up relationships between multiple tables. For example, suppose the “friends” table
has a column “friend id” that contains a unique number for each row (each friend). The related “phone numbers” table
can be structured with two columns: one with the “friend id” of the friend to whom the phone number belongs, and
one with the actual phone number. That way, no matter how many phone numbers a single friend has, they can all be
stored in the “phone numbers” table and can be linked to the related friend using the “friend id” primary key. When a
primary key from one table is used in a related table to specify the connection between the records, the value in the
related table is known as a foreign key. Unlike many databases, the AIR local database engine does not allow you to
create foreign key constraints, which are constraints that automatically check that an inserted or updated foreign key
value has a corresponding row in the primary key table. Nevertheless, foreign key relationships are an important part
of the structure of a relational database, and foreign keys should be used when creating relationships between tables in
your database.
About SQL
Adobe AIR 1.0 and later
Structured Query Language (SQL) is used with relational databases to manipulate and retrieve data. SQL is a descriptive
language rather than a procedural language. Instead of giving the computer instructions on how it should retrieve data,
a SQL statement describes the set of data you want. The database engine determines how to retrieve that data.
The SQL language has been standardized by the American National Standards Institute (ANSI). The Adobe AIR local
SQL database supports most of the SQL-92 standard.
For specific descriptions of the SQL language supported in Adobe AIR, see “SQL support in local databases” on
page 341.
About SQL database classes
Adobe AIR 1.0 and later
To work with local SQL databases in JavaScript, you use instances of the following classes. (Note that you need to load
the file AIRAliases.js in your HTML document in order to use the air.* aliases for these classes):
To obtain schema information describing the structure of a database, you use these classes:
Class
Description
air.SQLConnection
Provides the means to create and open databases (database files), as well as methods for performing
database-level operations and for controlling database transactions.
air.SQLStatement
Represents a single SQL statement (a single query or command) that is executed on a database, including
defining the statement text and setting parameter values.
air.SQLResult
Provides a way to get information about or results from executing a statement, such as the result rows from
a SELECT statement, the number of rows affected by an UPDATE or DELETE statement, and so forth.
Class
Description
air.SQLSchemaResult
Serves as a container for database schema results generated by calling the
SQLConnection.loadSchema() method.
air.SQLTableSchema
Provides information describing a single table in a database.
54
207
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
The following classes provide constants that are used with the SQLConnection class:
In addition, the following classes represent the events (and supporting constants) that you use:
Finally, the following classes provide information about database operation errors:
About synchronous and asynchronous execution modes
Adobe AIR 1.0 and later
When you’re writing code to work with a local SQL database, you specify that database operations execution in one of
two execution modes: asynchronous or synchronous execution mode. In general, the code examples show how to
perform each operation in both ways, so that you can use the example that’s most appropriate for your needs.
air.SQLViewSchema
Provides information describing a single view in a database.
air.SQLIndexSchema
Provides information describing a single column of a table or view in a database.
air.SQLTriggerSchema
Provides information describing a single trigger in a database.
Class
Description
air.SQLMode
Defines a set of constants representing the possible values for the openMode parameter of the
SQLConnection.open() and SQLConnection.openAsync() methods.
air.SQLColumnNameStyle
Defines a set of constants representing the possible values for the SQLConnection.columnNameStyle
property.
air.SQLTransactionLockType
Defines a set of constants representing the possible values for the option parameter of the
SQLConnection.begin() method.
air.SQLCollationType
Defines a set of constants representing the possible values for the
SQLColumnSchema.defaultCollationType property and the defaultCollationType parameter
of the SQLColumnSchema() constructor.
Class
Description
air.SQLEvent
Defines the events that a SQLConnection or SQLStatement instance dispatches when any of its operations
execute successfully. Each operation has an associated event type constant defined in the SQLEvent class.
air.SQLErrorEvent
Defines the event that a SQLConnection or SQLStatement instance dispatches when any of its operations
results in an error.
air.SQLUpdateEvent
Defines the event that a SQLConnection instances dispatches when table data in one of its connected
databases changes as a result of an INSERT, UPDATE, or DELETE SQL statement being executed.
Class
Description
air.SQLError
Provides information about a database operation error, including the operation that was being attempted
and the cause of the failure.
air.SQLErrorOperation
Defines a set of constants representing the possible values for the SQLError class’s operation property,
which indicates the database operation that resulted in an error.
Class
Description
44
208
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
In asynchronous execution mode, you give the runtime an instruction and the runtime dispatches an event when your
requested operation completes or fails. First you tell the database engine to perform an operation. The database engine
does its work in the background while the application continues running. Finally, when the operation is completed (or
when it fails) the database engine dispatches an event. Your code, triggered by the event, carries out subsequent
operations. This approach has a significant benefit: the runtime performs the database operations in the background
while the main application code continues executing. If the database operation takes a notable amount of time, the
application continues to run. Most importantly, the user can continue to interact with it without the screen freezing.
Nevertheless, asynchronous operation code can be more complex to write than other code. This complexity is usually
in cases where multiple dependent operations must be divided up among various event listener methods.
Conceptually, it is simpler to code operations as a single sequence of steps—a set of synchronous operations—rather
than a set of operations split into several event listener methods. In addition to asynchronous database operations,
Adobe AIR also allows you to execute database operations synchronously. In synchronous execution mode, operations
don’t run in the background. Instead they run in the same execution sequence as all other application code. You tell
the database engine to perform an operation. The code then pauses at that point while the database engine does its
work. When the operation completes, execution continues with the next line of your code.
Whether operations execute asynchronously or synchronously is set at the SQLConnection level. Using a single
database connection, you can’t execute some operations or statements synchronously and others asynchronously. You
specify whether a SQLConnection operates in synchronous or asynchronous execution mode by calling a
SQLConnection method to open the database. If you call
SQLConnection.open()
the connection operates in
synchronous execution mode, and if you call
SQLConnection.openAsync()
the connection operates in
asynchronous execution mode. Once a SQLConnection instance is connected to a database using
open()
or
openAsync()
, it is fixed to synchronous or asynchronous execution mode unless you close and reopen the connection
to the database.
Each execution mode has benefits. While most aspects of each mode are similar, there are some differences you’ll want
to keep in mind when working in each mode. For more information on these topics, and suggestions for working in
each mode, see “Using synchronous and asynchronous database operations” on page 232.
Creating and modifying a database
Adobe AIR 1.0 and later
Before your application can add or retrieve data, there must be a database with tables defined in it that your application
can access. Described here are the tasks of creating a database and creating the data structure within a database. While
these tasks are less frequently used than data insertion and retrieval, they are necessary for most applications.
More Help topics
Mind the Flex: Updating an existing AIR database
72
209
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
Creating a database
Adobe AIR 1.0 and later
To create a database file, you first create a SQLConnection instance. You call its
open()
method to open it in
synchronous execution mode, or its
openAsync()
method to open it in asynchronous execution mode. The
open()
and
openAsync()
methods are used to open a connection to a database. If you pass a File instance that refers to a non-
existent file location for the
reference
parameter (the first parameter), the
open()
or
openAsync()
method creates
a database file at that file location and open a connection to the newly created database.
Whether you call the
open()
method or the
openAsync()
method to create a database, the database file’s name can
be any valid filename, with any filename extension. If you call the
open()
or
openAsync()
method with
null
for the
reference
parameter, a new in-memory database is created rather than a database file on disk.
The following code listing shows the process of creating a database file (a new database) using asynchronous execution
mode. In this case, the database file is saved in the “Pointing to the application storage directory” on page 149, with the
filename “DBSample.db”:
// Include AIRAliases.js to use air.* shortcuts
var conn = new air.SQLConnection();
conn.addEventListener(air.SQLEvent.OPEN, openHandler);
conn.addEventListener(air.SQLErrorEvent.ERROR, errorHandler);
// The database file is in the application storage directory
var folder = air.File.applicationStorageDirectory;
var dbFile = folder.resolvePath("DBSample.db");
conn.openAsync(dbFile);
function openHandler(event)
{
air.trace("the database was created successfully");
}
function errorHandler(event)
{
air.trace("Error message:", event.error.message);
air.trace("Details:", event.error.details);
}
Note: Although the File class lets you point to a specific native file path, doing so can lead to applications that will not
work across platforms. For example, the path C:\Documents and Settings\joe\test.db only works on Windows. For these
reasons, it is best to use the static properties of the File class such as
File.applicationStorageDirectory
, as well as
the
resolvePath()
method (as shown in the previous example). For more information, see “Paths of File objects” on
pag e147.
To execute operations synchronously, when you open a database connection with the SQLConnection instance, call
the
open()
method. The following example shows how to create and open a SQLConnection instance that executes its
operations synchronously:
C# Word - Convert Word to HTML in C#.NET VB.NET How-to, VB.NET PDF, VB.NET Word, VB toolkit SDK, preserves all the original anchors, links, bookmarks and font C#: Convert Word document to HTML5 files.
bookmarks pdf reader; create bookmark pdf
35
210
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
// Include AIRAliases.js to use air.* shortcuts
var conn = new air.SQLConnection();
// The database file is in the application storage directory
var folder = air.File.applicationStorageDirectory;
var dbFile = folder.resolvePath("DBSample.db");
try
{
conn.open(dbFile);
air.trace("the database was created successfully");
}
catch (error)
{
air.trace("Error message:", error.message);
air.trace("Details:", error.details);
}
Creating database tables
Adobe AIR 1.0 and later
Creating a table in a database involves executing a SQL statement on that database, using the same process that you
use to execute a SQL statement such as
SELECT
,
INSERT
, and so forth. To create a table, you use a
CREATE TABLE
statement, which includes definitions of columns and constraints for the new table. For more information about
executing SQL statements, see “Working with SQL statements” on page 214.
The following example demonstrates creating a table named “employees” in an existing database file, using
asynchronous execution mode. Note that this code assumes there is a SQLConnection instance named
conn
that is
already instantiated and is already connected to a database.
37
211
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
// Include AIRAliases.js to use air.* shortcuts
// ... create and open the SQLConnection instance named conn ...
var createStmt = new air.SQLStatement();
createStmt.sqlConnection = conn;
var sql =
"CREATE TABLE IF NOT EXISTS employees (" +
"
empId INTEGER PRIMARY KEY AUTOINCREMENT, " +
"
firstName TEXT, " +
"
lastName TEXT, " +
"
salary NUMERIC CHECK (salary > 0)" +
")";
createStmt.text = sql;
createStmt.addEventListener(air.SQLEvent.RESULT, createResult);
createStmt.addEventListener(air.SQLErrorEvent.ERROR, createError);
createStmt.execute();
function createResult(event)
{
air.trace("Table created");
}
function createError(event)
{
air.trace("Error message:", event.error.message);
air.trace("Details:", event.error.details);
}
The following example demonstrates how to create a table named “employees” in an existing database file, using
synchronous execution mode. Note that this code assumes there is a SQLConnection instance named
conn
that is
already instantiated and is already connected to a database.
45
212
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
// Include AIRAliases.js to use air.* shortcuts
// ... create and open the SQLConnection instance named conn ...
var createStmt = new air.SQLStatement();
createStmt.sqlConnection = conn;
var sql =
"CREATE TABLE IF NOT EXISTS employees (" +
"
empId INTEGER PRIMARY KEY AUTOINCREMENT, " +
"
firstName TEXT, " +
"
lastName TEXT, " +
"
salary NUMERIC CHECK (salary > 0)" +
")";
createStmt.text = sql;
try
{
createStmt.execute();
air.trace("Table created");
}
catch (error)
{
air.trace("Error message:", error.message);
air.trace("Details:", error.details);
}
Manipulating SQL database data
Adobe AIR 1.0 and later
There are some common tasks that you perform when you’re working with local SQL databases. These tasks include
connecting to a database, adding data to tables, and retrieving data from tables in a database. There are also several
issues you’ll want to keep in mind while performing these tasks, such as working with data types and handling errors.
Note that there are also several database tasks that are things you’ll deal with less frequently, but will often need to do
before you can perform these more common tasks. For example, before you can connect to a database and retrieve data
from a table, you’ll need to create the database and create the table structure in the database. Those less-frequent initial
setup tasks are discussed in “Creating and modifying a database” on page 208.
You can choose to perform database operations asynchronously, meaning the database engine runs in the background
and notifies you when the operation succeeds or fails by dispatching an event. You can also perform these operations
synchronously. In that case the database operations are performed one after another and the entire application
(including updates to the screen) waits for the operations to complete before executing other code. For more
information on working in asynchronous or synchronous execution mode, see “Using synchronous and asynchronous
database operations” on page 232.
44
213
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
Connecting to a database
Adobe AIR 1.0 and later
Before you can perform any database operations, first open a connection to the database file. A SQLConnection
instance is used to represent a connection to one or more databases. The first database that is connected using a
SQLConnection instance is known as the “main” database. This database is connected using the
open()
method (for
synchronous execution mode) or the
openAsync()
method (for asynchronous execution mode).
If you open a database using the asynchronous
openAsync()
operation, register for the SQLConnection instance’s
open
event in order to know when the
openAsync()
operation completes. Register for the SQLConnection instance’s
error
event to determine if the operation fails.
The following example shows how to open an existing database file for asynchronous execution. The database file is
named “DBSample.db” and is located in the user’s “Pointing to the application storage directory” on page 149.
// Include AIRAliases.js to use air.* shortcuts
var conn = new air.SQLConnection();
conn.addEventListener(air.SQLEvent.OPEN, openHandler);
conn.addEventListener(air.SQLErrorEvent.ERROR, errorHandler);
// The database file is in the application storage directory
var folder = air.File.applicationStorageDirectory;
var dbFile = folder.resolvePath("DBSample.db");
conn.openAsync(dbFile, air.SQLMode.UPDATE);
function openHandler(event)
{
air.trace("the database opened successfully");
}
function errorHandler(event)
{
air.trace("Error message:", event.error.message);
air.trace("Details:", event.error.details);
}
The following example shows how to open an existing database file for synchronous execution. The database file is
named “DBSample.db” and is located in the user’s “Pointing to the application storage directory” on page 149.
74
214
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
// Include AIRAliases.js to use air.* shortcuts
var conn = new air.SQLConnection();
// The database file is in the application storage directory
var folder = air.File.applicationStorageDirectory;
var dbFile = folder.resolvePath("DBSample.db");
try
{
conn.open(dbFile, air.SQLMode.UPDATE);
air.trace("the database opened successfully");
}
catch (error)
{
air.trace("Error message:", error.message);
air.trace("Details:", error.details);
}
Notice that in the
openAsync()
method call in the asynchronous example, and the
open()
method call in the
synchronous example, the second argument is the constant
SQLMode.UPDATE
. Specifying
SQLMode.UPDATE
for the
second parameter (
openMode
) causes the runtime to dispatch an error if the specified file doesn’t exist. If you pass
SQLMode.CREATE
for the
openMode
parameter (or if you leave the
openMode
parameter off), the runtime attempts to
create a database file if the specified file doesn’t exist. However, if the file exists it is opened, which is the same as if you
use
SQLMode.Update
. You can also specify
SQLMode.READ
for the
openMode
parameter to open an existing database
in a read-only mode. In that case data can be retrieved from the database but no data can be added, deleted, or changed.
Working with SQL statements
Adobe AIR 1.0 and later
An individual SQL statement (a query or command) is represented in the runtime as a SQLStatement object. Follow
these steps to create and execute a SQL statement:
Create a SQLStatement instance.
The SQLStatement object represents the SQL statement in your application.
var selectData = new air.SQLStatement();
Specify which database the query runs against.
To do this, set the SQLStatement object’s
sqlConnection
property to the SQLConnection instance that’s connected
with the desired database.
// A SQLConnection named "conn" has been created previously
selectData.sqlConnection = conn;
Specify the actual SQL statement.
Create the statement text as a String and assign it to the SQLStatement instance’s
text
property.
selectData.text = "SELECT col1, col2 FROM my_table WHERE col1 = :param1";
Define functions to handle the result of the execute operation (asynchronous execution mode only).
Use the
addEventListener()
method to register functions as listeners for the SQLStatement instance’s
result
and
error
events.
Documents you may be interested
Documents you may be interested
