Adobe AIR doesn’t include only Flash Player to execute SWF content and only the WebKit engine to load HTML content and JavaScript. AIR also has an embedded version of the SQLite database engine that makes the runtime truly complete; SQLite gives developers the opportunity to store data locally offline and to do it via the same language they use to store data for web applications: SQL.
Widely used across platforms, SQLite is an open source, award-winning database engine that implements the SQL-92 specifications (http://www.sqlite.org/omitted.html).
SQLite in Adobe AIR gives applications the ability to persistently store data and easily manage it. This data can be stored locally in offline situations but then can be synchronized with network data store. With the 1.5 version of Adobe AIR, it isn’t possible, however, to natively connect to remote databases.
To be able to access, store, and manipulate data in a SQLite database
in Adobe AIR, you first must create the database as a local file. Each
database needs its own local file. The AIR SDKs contain the APIs you need to
work with SQLite via ActionScript or JavaScript. For ActionScript code, SQL
database classes are in the flash.data
package, whereas for JavaScript, the classes can be directly instanced by
using AIR aliases (you just have to import the AIRAliases.js file into your HTML
document).
Create a local SQLite database in the same way you create a local file.
The simple operation of creating the database file doesn’t involve
using any specific SQLite class. To create a database without creating a
connection to it, you use the File
class and its resolvePath
method to
create a file with a .db file
extension:
var dbRef:File; dbRef = File.applicationStorageDirectory.resolvePath("myDB.db");
You next have to connect to the database to start working with it. In the following solutions, you will see how to make a connection to the database.
To simply create a database file, you don’t need any
class from the flash.data
package;
all you have to do is work with the File
class.
The following ActionScript class launches the createLocalDB
method in the constructor,
which points to applicationStorageDirectory
, creates the db
folder, and creates the myDBFile.db database file:
package com.oreilly.aircookbook.ch10 { import flash.filesystem.File; public class CreateDB { private var _myDB:File; public function get myDB():File { return _myDB; } public function CreateDB() { createLocalDB(); } private function createLocalDB():void { var folder:File = File.applicationStorageDirectory.resolvePath( "db" ); folder.createDirectory(); _myDB = folder.resolvePath( "myDBFile.db" ); _myDB.createDirectory(); } } }
The resolvePath
method of the
File
object creates a new File
object with a path relating to this
File
object’s path, based on the
path
parameter. The folder is
actually created with the createDirectory
method. This method doesn’t
execute any operation if the directory already exists. The _myDB
private variable uses the getter
method to return the instances of the
File
object you created.
The following example implements the ActionScript class in an application file created with Flex Builder:
<?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" initialize="init()"> <mx:Script> <![CDATA[ import com.oreilly.aircookbook.ch10.CreateDB; private var myDB:File; private function init():void { createBtn.addEventListener(MouseEvent.CLICK, onClick); } private function onClick(evt:MouseEvent):void { var myDBclass:CreateDB = new CreateDB(); myDB = myDBclass.myDB; mx.controls.Alert.show("Database File Was Created : " + myDB.nativePath ); } ]]> </mx:Script> <mx:Button id="createBtn" label="Create DB" /> </mx:WindowedApplication>
At the click of the button, the onClick
event handler creates an instance of
the CreateDB
class, which
creates the database file on the constructor.
The path where the file was created is shown in an Alert
window with the show
method.
Even for JavaScript, you create a database file by
creating a local file through the File
class. To be able to use the AIR
aliases, you have to import the JavaScript library AIRAliases.js into the HTML page.
The following code is created in an external JavaScript file saved as CreateDB.js:
// Constants var DB_NAME = 'db/myDBFile.db'; var myDB; function createDB() { myDB = air.File.applicationStorageDirectory.resolvePath(DB_NAME); myDB.createDirectory(); air.Introspector.Console.log( "Database File was created: " + myDB.nativePath ); }
The following HTML page invokes the createDB
method:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <script type="text/javascript" src="frameworks/AIRAliases.js"></script> <script type="text/javascript" src="frameworks/AIRIntrospector.js"></script> <script type="text/javascript" src="CreateDB.js"></script> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>AIR Cookbook: 10.1 Creating a Database (JavaScript)</title> </head> <body onload="createDB()"> <h1>AIR Cookbook: 10.1 Creating a Database (JavaScript)</h1> <p> Open the AIR Introspector window to see the following message:</p> <p><img src="images/Ch10_Sol1_1.jpg" width="641" height="480" /></p> </body> </html>
The createDB
method is
launched on the onload
event of the
body
tag, which creates the
myDFFile.db database file in
applicationStorageFolder and sends a message to the AIR Introspector
Console tab, as shown in Figure 10-1.
You can create a database by creating a simple local file. To carry out any operations on the database, however, you need to create and open a connection to the file.
With the SQLConnection
class,
you can do more than establish a connection to the database; you can use
it for SQL statements, and it provides a mechanism to execute multiple
statements in a transaction (via the begin
, commit
, and rollback
methods).
A SQLConnection
instance
enables you to carry out operations in synchronous and asynchronous
modes (see Connecting to a Database in Asynchronous Mode).
The synchronous operation prevents users from interacting with the
application until the database has been opened successfully or in the
event of an error. To connect to the database in synchronous mode, use
the open
method of the SQLConnection
class:
open(reference:Object = null, openMode:String = "create", autoCompact:Boolean = false, pageSize:int = 1024, encryptionKey:ByteArray = null):void
The method can accept five parameters:
reference:Object
(the default is null
): Contains the location of the
database file you want to open. This is a File
instance.
openMode:String
(the default is create
): Specifies how the database is
opened. Valid values are constants for the SQLMode
class (CREATE
, READ
, UPDATE
).
autoCompact:Boolean
(the default is false
): Reclaims unused space if set to
true
. You can use this value only
when creating a database or opening an empty database (with no
tables). AIR ignores this parameter if openMode
is set to SQLMode.READ
.
pageSize:int
(the default is 1024
): Indicates the page size for the
database specified in bytes. You can use this value only when
creating a database or opening an empty database (with no
tables).
encryptionKey:ByteArray
(the default is null
): The encryption key used to encrypt
and decrypt the SQLite database file. For more information, see
Creating an Encrypted Database.
If you pass a File
instance
that refers to a nonexistent file location for the reference
parameter (the first parameter), the
open
method creates a database file
at that file location and opens a connection to the newly created
database.
As for the other synchronous operations that AIR offers (such as
for opening and reading a file), you don’t need to register event
listeners to determine when an operation completes or fails. To be able
to manage the errors that occur when opening or creating a database
using the open
method, use the error
handling statements in a try...catch
block.
A crucial aspect of synchronous operations is that the entire
application is frozen when the synchronous operations are being
executed. In fact, because synchronous operations execute in the main
execution thread, all application functionality is paused while the
database operation or operations are performed. If the database file you
are trying to open is large, the open
method can cause a noticeable pause in the application.
To invoke the open
method to
open the database synchronously, you have to create an instance of the
SQLConnection
object. Only after
having created an instance of this class will it be possible to execute
the operations on the database, such as opening the SQLite file:
_myDB = File.applicationStorageDirectory.resolvePath( "db/myDBFile.db" ); _dbConn = new SQLConnection(); _dbConn.open(_myDB);
Being a synchronous execution, the error handling in the opening
of the database is provided by using the error-throwing statements in a
try...catch
block (an example is
given in the next section).
The following ActionScript class continues the class
created in Creating a Database, and as well as
creating a database file, it opens the file in synchronous mode with
the open
method of the SQLConnection
class. An _isOpen
private variable typed as a Boolean
will tell the application that uses
this class whether the database has been successfully opened. With the
isOpen
getter method, it will be
possible to access the property.
Here is the complete code for the ActionScript OpenDB.as class:
package com.oreilly.aircookbook.ch10 { import flash.data.SQLConnection; import flash.errors.SQLError; import flash.events.SQLErrorEvent; import flash.events.SQLEvent; import flash.filesystem.File; import mx.controls.Alert; public class OpenDB { private var _myDB:File; private var _isOpen:Boolean = false; private var _dbConn:SQLConnection; public function get myDB():File { return _myDB; } public function get isOpen():Boolean { return _isOpen; } public function OpenDB() { createLocalDB(); } private function createLocalDB():void { var folder:File= File.applicationStorageDirectory.resolvePath( "db" ); folder.createDirectory(); _myDB = folder.resolvePath( "myDBFile.db" ); } public function openLocalDB(dbFile:File):void { _dbConn = new SQLConnection(); try { _dbConn.open(dbFile); _isOpen = true; mx.controls.Alert.show("The Database File " +_myDB.nativePath + " was opened"); } catch (error:SQLError) { mx.controls.Alert.show("Error message:", event.error.message); mx.controls.Alert.show("Details:", event.error.details); } } } }
This class can be easily instanced in an AIR application that uses Flex SDK’s MXML code such as this:
<?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" initialize="init()"> <mx:Script> <![CDATA[ import com.oreilly.aircookbook.ch10.OpenDB; private var myDB:File; private var myDBclass:OpenDB; private function init():void { createBtn.addEventListener(MouseEvent.CLICK, onClick); openBtn.addEventListener(MouseEvent.CLICK, onClickOpen); } private function onClick(evt:MouseEvent):void { myDBclass = new OpenDB(); myDB = myDBclass.myDB; openBtn.enabled = true; mx.controls.Alert.show("Database File Was Created : " + myDB.nativePath ); } private function onClickOpen(evt:MouseEvent):void { myDBclass.openLocalDB(myDB) if (myDBclass.isOpen) { mx.controls.Alert.show("Database File Was Opened" ); } } ]]> </mx:Script> <mx:Button id="createBtn" label="Create DB" /> <mx:Button label="Open DataBase" id="openBtn" enabled="false" /> </mx:WindowedApplication>
The two buttons launch the methods of the myDBclass
class to create and then connect
to the database. By checking the value of the isOpen
property, you check whether
connecting to and opening the database file have been
successful.
The following is the code of the external OpenDB.js JavaScript file that will be imported in the HTML page:
// Constants var DB_NAME = 'db/myDBFile.db'; var myDB; var dbConn; function createDB() { myDB = air.File.desktopDirectory.resolvePath(DB_NAME); var folder = air.File.applicationStorageDirectory.resolvePath("db"); folder.createDirectory(); myDB = folder.resolvePath(DB_NAME); air.Introspector.Console.log( "Database File was created: " + myDB.nativePath ); } function openDB() { dbConn = new air.SQLConnection(); try { dbConn.open(myDB); air.Introspector.Console.log("Database File Was Opened successfully with a synchronous operation"); } catch (error) { air.Introspector.Console.log("Error message:", error.message); air.Introspector.Console.log("Details:", error.details); } }
The openDB
method creates a
SQLConnection
instance, and then it
uses a try...catch
block to open
the SQL database file contained in the myDB
File
instance with the open
method. Possible error messages are
passed to the AIR Introspector Console.
The following is the HTML page that uses the JavaScript file to make the call:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <script type="text/javascript" src="frameworks/AIRAliases.js"></script> <script type="text/javascript" src="frameworks/AIRIntrospector.js"></script> <script type="text/javascript" src="OpenDB.js"></script> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>AIR Cookbook: 10.2 Creating a Database (JavaScript)</title> </head> <body onload="createDB()"> <h1>AIR Cookbook: 10.2 Creating a Database (JavaScript)</h1> <p> <label>Open Database File <input type="button" name="openDB" id="openDB" value="Open" accesskey="o" tabindex="1" onclick="openDB();document.getElementById('resultDiv').innerText = 'Database was opened successfully';" /> </label> </p> <p><div id="resultDiv"></div></p> </body> </html>
The database is created on the onload
event of the body
, which invokes the createDB
method. When you click the button,
the SQLConnection
is created, and
the database file is opened. Furthermore, the message “Database was
opened successfully” is written in the resultDiv div
using the innerText
property:
<input type="button" name="openDB" id="openDB" value="Open" accesskey="o" tabindex="1" onclick="openDB();document.getElementById('resultDiv').innerText = 'Database was opened successfully';" />
Use the openAsync
method, which opens a database file at the specified location in
the file system with an asynchronous execution.
The openAsync
method performs
the opening operation asynchronously. When the operation completes
successfully, openAsync
dispatches an
open
event; when it fails, the method
dispatches an error
event. When the
error
event is fired, the connection
to the database is closed automatically. These two events are part of
the SQLEvent
and SQLErrorEvent
classes. When opening the database with the openAsync
method, you have to manage the
listeners for these two events:
private var _dbConn:SQLConnection = new SQLConnection(); _dbConn.openAsync(_myDB); _dbConn.addEventListener(SQLEvent.OPEN, openHandler); _dbConn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
If you pass to the method a reference of a File
instance of a file that doesn’t exist,
you’ll automatically create the database file in the specified
location.
A database that is connected using the openAsync
method is automatically assigned the
database name main
; you can use this
name to explicitly qualify table names in SQL statements using the
format [database-name].[table-name]
.
The following class continues and modifies the class you
created in Connecting to a Database in Asynchronous Mode
and adds the isAsync
parameter
typed as a Boolean
to the openLocalDB
method. This parameter tells the
method whether the database has to be opened by using the synchronous
open
method or the openAsync
asynchronous method:
package com.oreilly.aircookbook.ch10 { import flash.data.SQLConnection; import flash.errors.SQLError; import flash.events.SQLErrorEvent; import flash.events.SQLEvent; import flash.filesystem.File; import mx.controls.Alert; public class OpenDBAsync { private var _myDB:File; private var _isOpen:Boolean = false; private var _dbConn:SQLConnection; public function get myDB():File { return _myDB; } public function get isOpen():Boolean { return _isOpen } public function OpenDBAsync() { createLocalDB(); } private function createLocalDB():void { var folder:File= File.applicationStorageDirectory.resolvePath( "db" ); folder.createDirectory(); _myDB = folder.resolvePath( "myDBFile.db" ); } public function openLocalDB(dbFile:File,isAsync:Boolean=true):void { _dbConn = new SQLConnection(); if(isAsync) { _dbConn.openAsync(dbFile); _dbConn.addEventListener(SQLEvent.OPEN, openHandler); _dbConn.addEventListener(SQLErrorEvent.ERROR, errorHandler); }else{ try { _dbConn.open(dbFile); } catch (error:SQLError) { trace("Error message:", error.message); trace("Details:", error.details); } } } private function openHandler(event:SQLEvent):void { trace("The Database File " + _myDB.nativePath + " was opened"); _isOpen = true; } private function errorHandler(event:SQLErrorEvent):void { mx.controls.Alert.show("Error message:", event.error.message); mx.controls.Alert.show("Details:", event.error.details); _isOpen = false; } } }
The class checks whether the condition in the open
method has occurred. If the isAsync
parameter is true
, a connection is
made, and the database file is opened in asynchronous mode.
Two event handlers are created for the OPEN
and ERROR
events, which change the value of the
_isOpen
property. The SQLConnection
class has a connected public
property that returns a Boolean
,
which indicates whether the SQLConnection
instance has an open
connection to a database file.
The following is the entire example in MXML that instances the OpenDBAsync.as ActionScript class:
<?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" initialize="init()"> <mx:Script> <![CDATA[ import com.oreilly.aircookbook.ch10.OpenDBAsync; private var myDB:File; private var myDBclass:OpenDBAsync; private function init():void { createBtn.addEventListener(MouseEvent.CLICK, onClick); openBtn.addEventListener(MouseEvent.CLICK, onClickOpen); } private function onClick(evt:MouseEvent):void { myDBclass = new OpenDBAsync(); myDB = myDBclass.myDB; openBtn.enabled = true; mx.controls.Alert.show("Database File Was Created : " + myDB.nativePath ); } private function onClickOpen(evt:MouseEvent):void { myDBclass.openLocalDB(myDB) if (myDBclass.isOpen) { mx.controls.Alert.show("Database File Was Opened" ); } } ]]> </mx:Script> <mx:Button id="createBtn" label="Create DB" /> <mx:Button label="Open DataBase" id="openBtn" enabled="false" /> </mx:WindowedApplication>
To obtain the same result by using JavaScript, you
modify the JavaScript file you created in Connecting to a Database in Synchronous Mode by adding the
isAsync
parameter to the openDB(isAsync)
method, which indicates
whether to use a synchronous or asynchronous connection:
// Constants var DB_NAME = 'db/myDBFile.db'; var myDB; var dbConn; var isAsync; function createDB() { var folder = air.File.applicationStorageDirectory.resolvePath("db"); folder.createDirectory(); myDB = folder.resolvePath(DB_NAME); myDB = air.File.desktopDirectory.resolvePath(DB_NAME); air.Introspector.Console.log( "Database File was created: " + myDB.nativePath ); } function openDB(isAsync) { dbConn = new air.SQLConnection(); if (isAsync) { dbConn.openAsync(myDB); dbConn.addEventListener(air.SQLEvent.OPEN, onOpenHandler); dbConn.addEventListener(air.SQLErrorEvent.ERROR, onErrorHandler); } else { try { dbConn.open(myDB); air.Introspector.Console.log("Database File Was Opened successfully with a synchronous operation"); } catch (error) { air.Introspector.Console.log("Error message:", error.message); air.Introspector.Console.log("Details:", error.details); } } } function onOpenHandler(event) { air.Introspector.Console.log("Database File Was Opened successfully"); } function onErrorHandler(event) { air.Introspector.Console.log("Error message:", event.error.message); air.Introspector.Console.log("Details:", event.error.details); }
The following is the HTML page that uses this JavaScript file with the calls to the methods:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <script type="text/javascript" src="frameworks/AIRAliases.js"></script> <script type="text/javascript" src="frameworks/AIRIntrospector.js"></script> <script type="text/javascript" src="OpenDB.js"></script> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>AIR Cookbook: 10.2 Creating a Database (JavaScript)</title> </head> <body onload="createDB()"> <h1>AIR Cookbook: 10.3 Connecting to a Database Asynchronously (JavaScript)</h1> <p> <label>Open Database File <input type="button" name="openDB" id="openDB" value="Open" accesskey="o" tabindex="1" onclick="openDB(true);document.getElementById('resultDiv').innerText = 'Database was opened successfully';" /> </label> </p> <div id="resultDiv"></div> </body> </html>
Set the reference
parameter of
the open
and openAsync
methods to null
to create an in-memory database.
For a desktop application, it is often useful to be able to manage the data structurally by using a powerful language such as SQL but without having to save the data locally.
You can create an in-memory database by passing a null
value to the reference
parameter of either the synchronous
open
method or the openAsync
asynchronous method. The temporary
database will exist while your application is open but is not saved to
disk; instead, it’s deleted when your application closes.
You need to ensure that the sensitive information contained in the database for your AIR application cannot be accessed outside the application.
AIR 1.5 introduced encrypted database support within the runtime. This enabled entire database files to be encrypted without using any tools outside AIR. It uses Advanced Encryption Standard (AES) encryption, which provides a tested algorithm to secure your data.
Normal database files within an AIR application can be accessed by any program that can read the SQLite database format. Because of this, sensitive data within an AIR database needs to be encrypted. When encryption is used, the entire database is secured with a 16-byte (128-bit) key. Without this key, the database data cannot be read.
Within AIR, encrypted databases and unencrypted databases are separate. There is no mechanism for encrypting only certain tables. In these cases, you can create multiple database files and set encryption as needed. Also, encryption can be set when the database is created only, and it cannot be removed from a database file.
In this recipe’s example, you will be using AS3Crypto, an open source cryptology library for ActionScript 3 that is released under the BSD license. You can find it at http://code.google.com/p/as3crypto/.
In this example, a single random key will be generated to secure
all the databases within the application. To accomplish this, you can
use the Random
class within
AS3Crypto. The key will then be stored in the encrypted local store with
the stronglyBound
parameter set to
true
(for more information on the
stronglyBound
parameter, see Storing Application-Specific Data in the Encrypted Local
Store). This key will be
retrieved when the application launches. With this method, the key is
never exposed outside the application.
Never store the key that will be used to encrypt the databases on the local file system. This could enable someone to view the contents of the encrypted database.
Because the AS3Crypto project will be used to generate the random encryption key in this exercise, you need to download the as3crypto.swc file from the previous URL. In addition, you must ensure that it is added to your project’s build path.
When this application launches, it checks whether the encryption
key is present inside the encrypted local store. If it is not present
(and it will not be on the first launch), the createRandomKey
method is called, a new key
is generated as a binary 16-byte ByteArray
, and this key is
saved into the encrypted local store. The key generation process uses
the Random
class from the AS3Crypto
library, as shown here:
public function createRandomKey():ByteArray { var encryptionKey:ByteArray = new ByteArray(); var random:Random = new Random(); random.nextBytes(encryptionKey, 16); return encryptionKey; }
When the user clicks the Connect button, the instances of the
File
class and the SQLConnection
class are created. Next, the
needed event listeners are added to detect whether the connection is
successful. Finally, the openAsync
method is called:
connection.openAsync(dbFile,SQLMode.CREATE,null,false,1024,storedKey);
The database encryption key is passed in as the last parameter
in the open
and openAsync
methods.
If the database does not already exist, a new encrypted database
is created, and the SQLEvent.OPEN
event is dispatched when the connection is complete. If the database
exists and the encryption key is correct, the SQLEvent.OPEN
event is dispatched when the
connection is complete. If the key is incorrect or if the referenced
database file is not encrypted, a SQLErrorEvent.ERROR
event is
dispatched.
<?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" horizontalAlign="left" creationComplete="handleCreationComplete()"> <mx:Script> <![CDATA[ import flash.utils.ByteArray; import mx.collections.ArrayCollection; import com.hurlant.crypto.prng.Random; public static const ENCRYPTED_STORE_KEY_NAME:String = "databaseKey"; public static const ENCRYPTED_DB_FILE:String = "encrypted.db"; [Bindable] private var results:ArrayCollection = new ArrayCollection(); private var connection:SQLConnection; private var dbFile:File; private var storedKey:ByteArray; private function handleCreationComplete():void { results.addItem("[ACTION]: Attempting to Retrieve Key from ELS"); storedKey = EncryptedLocalStore.getItem(ENCRYPTED_STORE_KEY_NAME); if(!storedKey) { results.addItem("[ACTION]: Key Not Retrieved - Creating New Key"); storedKey = createRandomKey(); EncryptedLocalStore.setItem(ENCRYPTED_STORE_KEY_NAME, storedKey, true); } else { results.addItem("[ACTION]: Key Retrieved Successfully"); } } private function createRandomKey():ByteArray { var encryptionKey:ByteArray = new ByteArray(); var random:Random = new Random(); random.nextBytes(encryptionKey, 16); results.addItem("[ACTION]: Creating New Key " + encryptionKey.toString()); return encryptionKey; } private function handleConnectClick(event:MouseEvent):void { results.addItem("[ACTION]: Attempting Database Connection"); dbFile = File.applicationStorageDirectory.resolvePath(ENCRYPTED_DB_FILE); connection = new SQLConnection(); connection.addEventListener(SQLEvent.OPEN, handleDatabaseOpen); connection.addEventListener(SQLErrorEvent.ERROR, handleDatabaseError); connection.openAsync(dbFile,SQLMode.CREATE,null,false,1024,storedKey); } private function handleDatabaseOpen(event:SQLEvent):void { results.addItem("[ACTION]: Database Connection Successful"); } private function handleDatabaseError(event:SQLErrorEvent):void { results.addItem("[ACTION]: Could Not Connect to Database " + event.error.detailArguments.toString() ); } ]]> </mx:Script> <mx:Label text="Encrypted Database Connection" fontWeight="bold" fontSize="18"/> <mx:Button label="Connect" click="handleConnectClick(event)" /> <mx:List width="100%" height="100%" dataProvider="{results}" /> </mx:WindowedApplication>
To properly access the AS3Crypto library with a
JavaScript AIR application, you must download as3crypto.swc (http://code.google.com/p/as3crypto/) and rename the
extension of the file to zip
. Next,
extract the library.swf file from
the package. Rename the file as3crypto.swf, and place it into your
project directory. Finally, place the following tag in your
application to include the library:
<script type="application/x-shockwave-flash" src="as3crypto.swf"></script>
When the application launches, checks whether a value exists in
the encrypted local store with the key databaseKey
. If this value does not exist
(and it will not on the first launch), a new key is created with the
createRandomKey
method. This
function uses the Random
class from
the AS3Crypto library to generate a random 16-byte key:
function createRandomKey() { var encryptionKey = new air.ByteArray(); var randomBytes = new window.runtime.com.hurlant.crypto.prng.Random(); randomBytes.nextBytes(encryptionKey, 16); return encryptionKey; }
This new key is then stored into the encrypted local store.
When the user clicks the Connect button, the instances of the
File
class and the SQLConnection
class are created. Next, the
needed event listeners are added to detect whether the connection is
successful. Finally, the openAsync
method is called:
connection.openAsync(dbFile,air.SQLMode.CREATE,null,false,1024,storedKey);
The final parameter is the actual 16-byte binary key used for
the encryption. If the database file does not exist at this point, an
encrypted database is created with the specified key. If the encrypted
database already exists, the connection will be successful only if the
key is correct. When the connection is complete, a SQLEvent.OPEN
is dispatched. If the key is
incorrect, it dispatches a SQLErrorEvent.ERROR
. Additionally, if the
database file exists but it is not encrypted, a SQLErrorEvent.ERROR
is dispatched.
<html>
<head>
<title>Encrypted Database Sample</title>
<script type="text/javascript" src="AIRAliases.js"></script>
<script type="text/javascript" src="AIRIntrospector.js"></script>
<script type="application/x-shockwave-flash" src="as3crypto.swf"></script>
<script type="text/javascript">
var ENCRYPTED_STORE_KEY_NAME = "databaseKey";
var ENCRYPTED_DB_FILE = "encrypted.db";
var storedKey;
var dbFile;
var conn;
function retrieveStoredKey() {
logAction("Attempting to Retrieve Key from ELS");
storedKey = air.EncryptedLocalStore.getItem(ENCRYPTED_STORE_KEY_NAME);
if( !storedKey ) {
logAction("Key Not Retrieved - Creating New Key");
storedKey = createRandomKey();
air.EncryptedLocalStore.setItem(ENCRYPTED_STORE_KEY_NAME,
storedKey, true);
} else {
logAction("Key Was Retrieved from ELS Correctly");
}
}
function createRandomKey() {
var encryptionKey = new air.ByteArray();
var randomBytes = new
window.runtime.com.hurlant.crypto.prng.Random();
randomBytes.nextBytes(encryptionKey, 16);
logAction("Creating New Key " + encryptionKey.toString() );
return encryptionKey;
}
function connectToDatabase(event) {
logAction("Attempting to Connect to Database");
dbFile =
air.File.applicationStorageDirectory.resolvePath(ENCRYPTED_DB_FILE);
conn = new air.SQLConnection();
conn.addEventListener(air.SQLEvent.OPEN, handleDatabaseOpen);
conn.addEventListener(air.SQLErrorEvent.ERROR,
handleDatabaseError);
conn.openAsync(dbFile,air.SQLMode.CREATE,null,false,1024,storedKey);
}
function handleDatabaseOpen(event) {
logAction("Connection to Encrypted Database Successful");
}
function handleDatabaseError(event) {
logAction("Could Not Connect to Database "+
event.error.detailArguments.toString() );
}
function logAction(action) {
var resultsDiv = document.getElementById('results'),
resultsDiv.innerHTML += "[ACTION]: " + action + "<br />";
}
window.onload = retrieveStoredKey;
</script>
<style type="text/css">
body {font-family: Arial; font-size:12px;padding: 15px;}
#results {padding: 5px;border: 1px solid #666;}
</style>
</head>
<body>
<h3>Encrypted Database Connection Test</h3>
<input type="button" value="Connect" onclick="connectToDatabase(event)" />
<div>Results:</div>
<div>
<div id="results"></div>
</div>
</body>
</html>
You need to encrypt a database based on a user’s password. In addition, you need to allow the user to change his password and update the encryption accordingly.
Use the user’s password as the basis for the encryption key, and
use the reencrypt
method of the SQLConnection
class to change the encryption key for a database.
Although creating a random key may work for many situations, in some cases it is ideal to base the key off of user input. This is ideal in situations where you are downloading password-protected data from an online service. This allows the same password that secures the data online to secure the data in the AIR application.
Because the open
and openAsync
methods of the SQLConnection
class are expecting a 16-byte
binary key, you will be creating an MD5 hash of the user’s password by
utilizing the AS3Crypto library. This creates the needed 16-byte
ByteArray
that can then be passed
into the open
or openAsync
methods.
When the user changes his password, the reencrypt
method of the SQLConnection
class will allow the application
to change the encryption key for a specific database. This requires that
the database connection was already opened with the old encryption key.
Once the method has been called, the process runs inside a
transaction-like process. If the process is interrupted before
completion, the database retains the old encryption key. If the process
is completed successfully, a SQLEvent.REENCRYPT
event is dispatched, and if
it fails, a SQLError.ERROR
event is
dispatched.
Remember that a database that is not encrypted cannot be encrypted. To make an unencrypted database encrypted, the data must be imported into a new encrypted database.
In this example, the user must specify a password to connect to
the database. If the database does not exist, a new database is created
using the password hash as the encryption key. While the database is
connected, the user can enter a new password and click the Change
Password button. This triggers the reencrypt
method, which changes the encryption
key for the database.
You will need to include the AS3Crypto library for this project. See Creating an Encrypted Database for instructions on how to include it in your environment.
To create the encryption key, the MD5
class from the AS3Crypto library is used
along with the password entered by the user. The password, which is
passed into the function as a string, is converted into a ByteArray
by using the writeUTFBytes
method. Next, the MD5
class is instantiated, and the hash
method is called on the ByteArray
. This value is returned and used
as the encryption key for the database.
private function createEncryptionKey(password:String):ByteArray { var ba:ByteArray = new ByteArray(); ba.writeUTFBytes(password); var md5:MD5 = new MD5(); var output:ByteArray = md5.hash(ba); return output; }
To allow the changing of the password, the reencrypt
method of the SQLConnection
class is used. This method
must be called while the SQLConnection
is open.
connection.addEventListener(SQLEvent.REENCRYPT, handleDatabaseReencrypt); connection.reencrypt( createEncryptionKey(newPassword.text) );
The completed example, shown here, integrates all this functionality into a single AIR application:
<?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" horizontalAlign="left"> <mx:Script> <![CDATA[ import flash.utils.ByteArray; import mx.collections.ArrayCollection; import com.hurlant.crypto.hash.MD5; public static const ENCRYPTED_DB_FILE:String = "encrypted.db"; [Bindable] private var results:ArrayCollection = new ArrayCollection(); private var connection:SQLConnection; private var dbFile:File; private function createEncryptionKey(password:String):ByteArray { var ba:ByteArray = new ByteArray(); ba.writeUTFBytes(password); var md5:MD5 = new MD5(); var output:ByteArray = md5.hash(ba); results.addItem("[ACTION]: Hash Key Created " + output.toString()); return output; } private function handleConnectClick(event:MouseEvent):void { results.addItem("[ACTION]: Attempting Database Connection"); dbFile = File.applicationStorageDirectory.resolvePath(ENCRYPTED_DB_FILE); connection = new SQLConnection(); connection.addEventListener(SQLEvent.OPEN, handleDatabaseOpen); connection.addEventListener(SQLErrorEvent.ERROR, handleDatabaseError); connection.openAsync(dbFile,SQLMode.CREATE,null,false,1024, createEncryptionKey(password.text)); } private function handleDisconnectClick(event:MouseEvent):void { connection.close(); disconnectButton.enabled = false; password.enabled = true; connectButton.enabled = true; newPassword.enabled = false; reencryptButton.enabled = false; } private function handleReencryptClick(event:MouseEvent):void { connection.addEventListener(SQLEvent.REENCRYPT, handleDatabaseReencrypt); connection.reencrypt(createEncryptionKey(newPassword.text)); } private function handleDatabaseOpen(event:SQLEvent):void { results.addItem("[ACTION]: Database Connection Successful"); disconnectButton.enabled = true; newPassword.enabled = true; reencryptButton.enabled = true; password.enabled = false; connectButton.enabled = false; } private function handleDatabaseReencrypt(event:SQLEvent):void { connection.removeEventListener(SQLEvent.REENCRYPT, handleDatabaseReencrypt); results.addItem("[ACTION]: Database Reencrypted"); } private function handleDatabaseError(event:SQLErrorEvent):void { results.addItem("[ERROR]: Database Error " + event.error.detailArguments.toString() ); } ]]> </mx:Script> <mx:Label text="Encryption By Password" fontWeight="bold" fontSize="18" /> <mx:Label text="Connect To Database" fontWeight="bold"/> <mx:HBox> <mx:Label text="Password" /> <mx:TextInput id="password" /> <mx:Button id="connectButton" label="Connect" click="handleConnectClick(event)" /> <mx:Button id="disconnectButton" label="Disconnect" click="handleDisconnectClick(event)" enabled="false" /> </mx:HBox> <mx:Label text="Change Password / ReEncrypt" fontWeight="bold"/> <mx:HBox> <mx:Label text="New Password" /> <mx:TextInput id="newPassword" enabled="false" /> <mx:Button id="reencryptButton" label="Change Password" click="handleReencryptClick(event)" enabled="false" /> </mx:HBox> <mx:List width="100%" height="100%" dataProvider="{results}" /> </mx:WindowedApplication>
To create the encryption key, the MD5
class from the AS3Crypto library is used
along with the password entered by the user. The password, which is
passed into the function as a string, is converted into a ByteArray
by using the writeUTFBytes
method. Next, the MD5
class is instantiated, and the hash
method is called on the ByteArray
. This value is returned and used
as the encryption key for the database.
function createEncryptionKey(password) { var ba = new air.ByteArray(); ba.writeUTFBytes(password); var md5 = new window.runtime.com.hurlant.crypto.hash.MD5(); var output = md5.hash(ba); logAction("Hash Key Created " + output.toString()); return output; }
To allow the user to change his password, the use of the
reencrypt
method of the SQLConnection
class is used. This method
requires that you pass in the new encryption key and optionally allows
you to pass in a responder method. In this case, the createEncryptionKey
method
is used to generate the new key from the new password field. Also, to
know when the process is complete, an event listener is added for the
SQLEvent.REENCRYPT
event.
The completed application allows for the connection and disconnection of an encrypted database that uses the user-defined password hash for the encryption key. It also allows for the changing of the encryption key:
<html> <head> <title>Encrypted Database Sample</title> <script type="text/javascript" src="AIRAliases.js"></script> <script type="text/javascript" src="AIRIntrospector.js"></script> <script type="application/x-shockwave-flash" src="as3crypto.swf"></script> <script type="text/javascript"> var ENCRYPTED_DB_FILE = "encrypted.db"; var dbFile; var conn; function createEncryptionKey(password) { var ba = new air.ByteArray(); ba.writeUTFBytes(password); var md5 = new window.runtime.com.hurlant.crypto.hash.MD5(); var output = md5.hash(ba); logAction("Hash Key Created " + output.toString()); return output; } function handleConnectClick(event) { logAction("Attempting to Connect to Database"); dbFile = air.File.applicationStorageDirectory.resolvePath(ENCRYPTED_DB_FILE); conn = new air.SQLConnection(); conn.addEventListener(air.SQLEvent.OPEN, handleDatabaseOpen); conn.addEventListener(air.SQLErrorEvent.ERROR, handleDatabaseError); conn.openAsync(dbFile,air.SQLMode.CREATE,null,false,1024, createEncryptionKey(document.getElementById('password').value)); } function handleDisconnectClick(event) { conn.close(); document.getElementById('connectButton').disabled = false; document.getElementById('password').disabled = false; document.getElementById('disconnectButton').disabled = true; document.getElementById('newPassword').disabled = true; document.getElementById('reencryptButton').disabled = true; } function handleReencryptClick(event) { conn.addEventListener(air.SQLEvent.REENCRYPT, handleDatabaseReencrypt); conn.reencrypt(createEncryptionKey(document. getElementById('newPassword').value)); } function handleDatabaseOpen(event) { logAction("Database Connection Successful"); document.getElementById('connectButton').disabled = true; document.getElementById('password').disabled = true; document.getElementById('disconnectButton').disabled = false; document.getElementById('newPassword').disabled = false; document.getElementById('reencryptButton').disabled = false; } function handleDatabaseReencrypt(event) { conn.removeEventListener(air.SQLEvent.REENCRYPT, handleDatabaseReencrypt); logAction("Reencrypt Successful"); } function handleDatabaseError(event) { logAction("Database Error " + event.error.detailArguments.toString() ); } function logAction(action) { var resultsDiv = document.getElementById('results'), resultsDiv.innerHTML += "[ACTION]: " + action + "<br />"; } </script> <style type="text/css"> body {font-family: Arial; font-size:12px;padding: 15px;} #results {padding: 5px;border: 1px solid #666;} </style> </head> <body> <h3>Encrypted By Password</h3> <div> Connect to Database<br /> Password <input id="password" type="text" /> <input id="connectButton" type="button" value="Connect" onclick="handleConnectClick(event)" /> <input id="disconnectButton" type="button" value="Disconnect" onclick="handleDisconnectClick(event)" disabled="true" /> </div> <div> Change Password / Reencrypt<br /> New Password <input id="newPassword" type="text" disabled="true" /> <input id="reencryptButton" type="button" value="Change Password" onclick="handleReencryptClick(event)" disabled="true" /> </div> <br /> <div>Results:</div> <div> <div id="results"></div> </div> </body> </html>
To create a table in your database, create an instance of the
SQLStatement
class. Doing so enables
you to execute SQL statements in an open database by using the
connection established with the SQLConnection
class. The SQLStatement
class tells the sqlConnection
public property which SQL object to accept to create the connection to
the local database file. The SQLStatement
’s text
property, on the other hand, accepts the
SQL string to execute, which will work remotely with the
database.
To execute the SQL text string, you need to invoke the execute
method, which executes the specified
SQL string in the text
property of
the SQLStatement
class:
var SQLStmsqlStatement:SQLStatement = new SQLStatement(); sqlStatementSQLStm.sqlConnection = conn; var sqlText:String = "CREATE TABLE IF NOT EXISTS students (" + " studentId INTEGER PRIMARY KEY AUTOINCREMENT, " + " firstName TEXT, " + " lastName TEXT, " + ")"; sqlStatementSQLStm.text = sqlText; sqlStatementSQLStm.execute();
The execute
method can be
managed in asynchronous mode and will therefore trigger the RESULT
and ERROR
events:
sqlStatementSQLStm.addEventListener(SQLEvent.RESULT, onStatementResult); sqlStatementSQLStm.addEventListener(SQLErrorEvent.ERROR, onStatementError);
In synchronous execution mode, you manage the execution in a
try...catch
block:
try { sqlStatementSQLStm.execute(); } catch (error:SQLError) { Alert.show("Error message:", error.message); Alert.show("Details:", error.details); }
Using the SQL language, you can manipulate data inside a database. Adobe AIR supports SQLite database systems and many standard SQL-92 standard SQL dialects. For retrieving, adding, modifying, and removing data from database tables, the following statements are supported:
SELECT
INSERT
UPDATE
DELETE
For creating, modifying, and removing such database objects as tables, views, indices, and triggers, AIR supports these SQL commands:
Tables:
CREATE TABLE |
ALTER TABLE |
DROP TABLE |
Indices:
CREATE INDEX |
DROP INDEX |
Views:
CREATE VIEW |
DROP VIEW |
Triggers:
CREATE
TRIGGER |
DROP TRIGGER |
In addition, AIR supports clauses and special statements that are extensions to SQL and provided by the runtime, as well as two language elements:
The COLLATE
clause
The EXPLAIN
clause
The ON CONFLICT
clause
and conflict algorithms
The REINDEX
statement
Comments
Expressions
SQL storage classes represent the actual data types used to store values. AIR supports the following:
NULL
: A NULL
value
INTEGER
: A signed
integer
REAL
: A floating-point
number value
TEXT
: A text string
(limited to 256MB)
BLOB
: A raw binary data
(limited to 256MB)
BLOB stands for Binary Large Object.
For a complete and detailed overview of SQL support in local databases, you can refer to http://help.adobe.com/en_US/AS3LCR/Flash_10.0/localDatabaseSQLSupport.html or http://help.adobe.com/en_US/AIR/1.5/jslr/localDatabaseSQLSupport.html.
To create a table in a database, you use the CREATE TABLE
statement:
CREATE TABLE TableName ( column-definition )
Each column definition represents the name of the column to create, followed by the data type.
The following ActionScript class modifies the class from
Connecting to a Database in Asynchronous Mode and adds
an instance of the SQLStatement
class to create a table in the database that was opened with the
SQLConnection
class:
package com.oreilly.aircookbook.ch10 { import flash.data.SQLConnection; import flash.data.SQLStatement; import flash.errors.SQLError; import flash.events.SQLErrorEvent; import flash.events.SQLEvent; import flash.filesystem.File; import mx.controls.Alert; public class CreateTable { private var _myDB:File; private var _isOpen:Boolean = false; private var _dbConn:SQLConnection; private var sqlString:String; public function get myDB():File { return _myDB; } public function get isOpen():Boolean { return _isOpen; } public function CreateTable() { createLocalDB(); sqlString = "CREATE TABLE IF NOT EXISTS Students(" + "stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + "firstName TEXT, " + "lastName TEXT" + ")"; } private function createLocalDB():void { var folder:File= File.applicationStorageDirectory.resolvePath( "db" ); folder.createDirectory(); _myDB = folder.resolvePath( "myDBFile.db" ); } public function createTableDB(dbFile:File,isAsync:Boolean=true):void { _dbConn = new SQLConnection(); if(isAsync) { _dbConn.openAsync(dbFile); _dbConn.addEventListener(SQLEvent.OPEN, openHandler); _dbConn.addEventListener(SQLErrorEvent.ERROR, errorHandler); }else{ try { _dbConn.open(dbFile); var createStm:SQLStatement = new SQLStatement(); createStm.sqlConnection = _dbConn; createStm.text = sqlString; createStm.addEventListener(SQLEvent.RESULT, onStatementResult); createStm.addEventListener(SQLErrorEvent.ERROR, onStatementError); createStm.execute(); } catch (error:SQLError) { trace("Error message:", error.message); trace("Details:", error.details); } } } private function openHandler(event:SQLEvent):void { _isOpen = _dbConn.connected; var createStm:SQLStatement = new SQLStatement(); createStm.sqlConnection = _dbConn; createStm.text = sqlString; createStm.addEventListener(SQLEvent.RESULT, onStatementResult); createStm.addEventListener(SQLErrorEvent.ERROR, onStatementError); createStm.execute(); } private function errorHandler(event:SQLErrorEvent):void { mx.controls.Alert.show("Error message:", event.error.message); mx.controls.Alert.show("Details:", event.error.details); _isOpen = _dbConn.connected } private function onStatementResult(event:SQLEvent):void { mx.controls.Alert.show("Table created"); } private function onStatementError(event:SQLErrorEvent):void { mx.controls.Alert.show("Error message:", event.error.message); mx.controls.Alert.show("Details:", event.error.details); } } }
In the constructor of the class, you assign a value to the
String
that contains the SQL
statement to create a Students
table in the database:
sqlString = "CREATE TABLE IF NOT EXISTS Students(" + "stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + "firstName TEXT, " + "lastName TEXT" + ")";
This string is passed as an argument to the text
property of the SQLStatement
instance that has been created
as a temporary variable in the createTableDB
method. This public method
opens a connection to the database before creating the table. This is
a compulsory step; if you try to create a table in a database without
defining any connection, the SQLErrorEvent.ERROR
class will throw an
error.
The ActionScript code that creates the SQLStatement
class instance passes it the
reference to the database connection, and then the SQL statement is
written in the openHandler
event handler,
managed by the asynchronous connection mode, as well as in the
try...catch
statement for the
synchronous connection to the database. The code is the same for both
connections:
var createStm:SQLStatement = new SQLStatement(); createStm.sqlConnection = _dbConn; createStm.text = sqlString; createStm.addEventListener(SQLEvent.RESULT, onStatementResult); createStm.addEventListener(SQLErrorEvent.ERROR, onStatementError); createStm.execute();
The following Flex code uses the CreateTable.as ActionScript class to create a database and a table at the click of a button:
<?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" initialize="init()"> <mx:Script> <![CDATA[ import com.oreilly.aircookbook.ch10.CreateTable; private var myDB:File; private var myDBclass:CreateTable; private function init():void { createBtn.addEventListener(MouseEvent.CLICK, onClick); openBtn.addEventListener(MouseEvent.CLICK, onClickOpen); } private function onClick(evt:MouseEvent):void { myDBclass = new CreateTable(); myDB = myDBclass.myDB; openBtn.enabled = true; mx.controls.Alert.show("Database File Was Created : " + myDB.nativePath ); } private function onClickOpen(evt:MouseEvent):void { myDBclass.createTableDB(myDB) if (myDBclass.isOpen) { mx.controls.Alert.show("Database File Was Opened and the TABLE was created" ); } } ]]> </mx:Script> <mx:Button id="createBtn" label="Create DB" /> <mx:Button label="Create a Table" id="openBtn" enabled="false" /> </mx:WindowedApplication>
To create a table in the database using the JavaScript code, follow these basic steps:
Create a database.
Open a connection to the database.
Create a SQLStatement
instance.
In the instance, specify the database in which to create the
table by using the sqlConnection
property.
Specify the SQL string to apply on the text
property.
For an asynchronous execution mode, create the event handlers to handle the result of the execute operation.
Execute the execute
method to apply the SQL statements.
The following example creates a database file and executes a
connection to this empty database, which it passes onto the SQLStatement
instance to create a
table:
// Constants var DB_NAME = 'db/myDBFile.db'; var SQL_STRING = "CREATE TABLE IF NOT EXISTS Students (" + "stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + "firstName TEXT, " + "lastName TEXT" + ")"; var myDB; var dbConn; var dbStm; var isAsync; function createDB() { var folder = air.File.applicationStorageDirectory.resolvePath("db"); folder.createDirectory(); myDB = folder.resolvePath(DB_NAME); myDB = air.File.desktopDirectory.resolvePath(DB_NAME); air.Introspector.Console.log( "Database File was created: " + myDB.nativePath ); } function openDB(isAsync) { dbConn = new air.SQLConnection(); if (isAsync) { dbConn.openAsync(myDB); dbConn.addEventListener(air.SQLEvent.OPEN, onOpenHandler); dbConn.addEventListener(air.SQLErrorEvent.ERROR, onErrorHandler); }else { try { dbConn.open(myDB); air.Introspector.Console.log("Database File Was Opened successfully with a synchronous operation"); dbStm = new air.SQLStatement(); dbStm.sqlConnection = dbConn; dbStm.text = SQL_STRING; dbStm.addEventListener(air.SQLEvent.RESULT, onStatementResult); dbStm.addEventListener(air.SQLErrorEvent.ERROR, onStatementError); dbStm.execute(); } catch (error) { air.Introspector.Console.log("Error message:", error.message); air.Introspector.Console.log("Details:", error.details); } } } function onOpenHandler(event) { air.Introspector.Console.log("Database File Was Opened successfully"); dbStm = new air.SQLStatement(); dbStm.sqlConnection = dbConn; dbStm.text = SQL_STRING; dbStm.addEventListener(air.SQLEvent.RESULT, onStatementResult); dbStm.addEventListener(air.SQLErrorEvent.ERROR, onStatementError); dbStm.execute(); } function onErrorHandler(event) { air.Introspector.Console.log("Error message:", event.error.message); air.Introspector.Console.log("Details:", event.error.details); } function onStatementResult(event) { air.Introspector.Console.log("Table created"); } function onStatementError(event) { air.Introspector.Console.log("Error message:", event.error.message); air.Introspector.Console.log("Details:", event.error.details); }
The SQL statement is declared as a constant at the beginning of
the code. It can be changed at will to carry out other operations or
add columns to the table you are creating. Launched when the
asynchronous openAsync
method is
invoked, the onOpenHandler
event handler
creates the instance of the SQLStatement
class. Within this event
handler the sqlConnection
property
is set to the SQLConnection
instance, and then the SQL commands are passed to the text
property that executes the operations
on the database:
dbStm = new air.SQLStatement(); dbStm.sqlConnection = dbConn; dbStm.text = SQL_STRING;
Regardless of whether the database has been opened in
asynchronous or synchronous mode, the JavaScript code calls the
execute
method of the SQLStatement
.
The HTML page loads the content of the external JavaScript file
and invokes the openDB
function at
the click of a button:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <script type="text/javascript" src="frameworks/AIRAliases.js"></script> <script type="text/javascript" src="frameworks/AIRIntrospector.js"></script> <script type="text/javascript" src="CreateTableDB.js"></script> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>AIR Cookbook: 10.4 Creating Tables in a Database (JavaScript)</title> </head> <body onload="createDB()"> <h1>AIR Cookbook: 10.4 Creating Tables in a Database (JavaScript)</h1> <p> <label>Create a Table in a Database File <input type="button" name="openDB" id="openDB" value="Create" accesskey="o" tabindex="1" onclick="openDB(true);document.getElementById('resultDiv').innerText = 'Database was opened successfully';" /> </label> </p> <p><div id="resultDiv"></div></p> </body> </html>
After you create the Button
to create the table, the following messages appear in the AIR
Introspector Console tab when the button is clicked:
[app:/Ch10/createTable.html][19:20:40] Database File was created: C:Documents and SettingsUserDesktopdbmyDBFile.db |
[app:/Ch10/createTable.html][19:20:43] Database File Was Opened successfully |
[app:/Ch10/createTable.html][19:20:43] Table created |
To visualize the content of a SQLite database, you can use a free SQLite Database Browser application (http://sqlitebrowser.sourceforge.net) or the AIR-based SQLite Admin application (http://coenraets.org/blog/2008/02/sqlite-admin-for-air-10/).
Query a database using the sqlConnection
and text
properties of the SQLStatement
class.
When working with synchronous operations, you must understand a few concepts regarding how synchronous operations behave when there are two or more actions that are dependent on each other and how they handle errors.
When you need to execute an operation only if a previous operation has been successful, all you need to do is write the code immediately after the operation on which it depends.
When synchronous operations are executed, you use try...catch...finally
code blocks to handle
errors instead of using event handlers.
Don’t forget that writing a synchronous database operation could penalize the performance and user experience of the application, which will freeze until the entire cycle of executed operations is complete. The user won’t be able to interact with the application in the meantime.
In this solution, you will see how to insert data in a table by
using SQL’s INSERT
statement. By
creating a SQL string, like the one shown here, you can insert static or
dynamic information in the database:
sqlString = "CREATE TABLE IF NOT EXISTS Students(" + "stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + "firstName TEXT, " + "lastName TEXT" + ")"; sqlInsert = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Marco', 'Casario')";
The sqlInsert
string inserts
the values Marco
and Casario
into the firstName
and lastName
columns, respectively.
The database will be opened in synchronous mode with the open
method so that all the following operations on
that database connection will be executed in synchronous mode.
The QueryingTableSynch.as class creates a new
SQLite local database, creates a table, and carries out data
insertion. All these operations are executed in synchronous mode,
because the database has been opened by using the open
method:
package com.oreilly.aircookbook.ch10 { import flash.data.SQLConnection; import flash.data.SQLStatement; import flash.errors.SQLError; import flash.events.SQLErrorEvent; import flash.events.SQLEvent; import flash.filesystem.File; import mx.controls.Alert; public class QueryingTableSynch { private var _myDB:File; private var _isOpen:Boolean = false; private var _dbConn:SQLConnection; private var sqlString:String; private var sqlInsert:String; public function get myDB():File { return _myDB; } public function get isOpen():Boolean { return _isOpen; } public function QueryingTableSynch() { createLocalDB(); sqlString = "CREATE TABLE IF NOT EXISTS Students(" + "stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + "firstName TEXT, " + "lastName TEXT" + ")"; sqlInsert = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Marco', 'Casario')"; } private function createLocalDB():void { var folder:File= File.applicationStorageDirectory.resolvePath( "db" ); folder.createDirectory(); _myDB = folder.resolvePath( "myDBFile.db" ); } public function createTableDB(dbFile:File):void { _dbConn = new SQLConnection(); try { trace("Creating Table ...."); _dbConn.open(dbFile); var createStm:SQLStatement = new SQLStatement(); createStm.sqlConnection = _dbConn; createStm.text = sqlString; createStm.execute(); trace("Table created ...."); } catch (error:SQLError) { trace("Error message:", error.message); trace("Details:", error.details); } } public function insertData(dbFile:File):void { try { trace("Inserting data ...."); var createStm:SQLStatement = new SQLStatement(); createStm.sqlConnection = _dbConn; createStm.text = sqlInsert createStm.execute(); trace("Data inserted!"); } catch (error:SQLError) { trace("Error message:", error.message); trace("Details:", error.details); } } } }
After the database is opened, all the following operations for
that connection are automatically executed in synchronous mode. The
Students
table in the createTableDBB
method, with the firstName
and lastName
columns, is created this way; the
execute
method of the SQLStatement
instance is executed in
synchronous mode. The same applies to the insertion of the values with
the INSERT
SQL statement in the
insertData
method that executes the
following SQL statement:
sqlInsert = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Marco', 'Casario')";
In the synchronous execution mode, the operations are executed
line by line in the code. To manage the errors that the application
could encounter when it is opening the database or when operations are
being executed, the code resides in a try...catch...finally
code block.
To use this ActionScript class, you can use the following MXML code, which is written by using the Flex Framework and invokes the public methods to carry out operations on the database in synchronous execution mode:
<?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" initialize="init()"> <mx:Script> <![CDATA[ import com.oreilly.aircookbook.ch10.QueryingTableSynch; private var myDB:File; private var myDBclass:QueryingTableSynch; private function init():void { createBtn.addEventListener(MouseEvent.CLICK, onClick); openBtn.addEventListener(MouseEvent.CLICK, onClickOpen); insertBtn.addEventListener(MouseEvent.CLICK, onClickInsert); } private function onClick(evt:MouseEvent):void { myDBclass = new QueryingTableSynch(); myDB = myDBclass.myDB; openBtn.enabled = true; mx.controls.Alert.show("Database File Was Created : " + myDB.nativePath ); } private function onClickOpen(evt:MouseEvent):void { myDBclass.createTableDB(myDB); insertBtn.enabled = true; } private function onClickInsert(evt:MouseEvent):void { myDBclass.insertData(myDB); mx.controls.Alert.show("Data was inserted into the database : " + myDB.nativePath ); } ]]> </mx:Script> <mx:Button id="createBtn" label="Create DB" /> <mx:Button label="Open DataBase" id="openBtn" enabled="false" /> <mx:Button label="Insert Data Asynchronously" enabled="false" id="insertBtn" /> </mx:WindowedApplication>
The following JavaScript code creates a new database,
inserts the Students
table, and
with an INSERT
statement inserts a
value in the database. The whole operation is executed in synchronous
mode.
// Constants var DB_NAME = 'db/myDBFile.db'; var SQL_STRING = "CREATE TABLE IF NOT EXISTS Students (" + " stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + " firstName TEXT, " + " lastName TEXT" + ")"; var SQL_INSERT = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Alessio', 'Casario')"; var myDB; var dbConn; var dbStm; var isAsync; function createDB() { var folder = air.File.applicationStorageDirectory.resolvePath("db"); folder.createDirectory(); myDB = folder.resolvePath(DB_NAME); myDB = air.File.desktopDirectory.resolvePath(DB_NAME); air.Introspector.Console.log( "Database File was created: " + myDB.nativePath ) openDB(); } function openDB() { dbConn = new air.SQLConnection(); try { dbConn.open(myDB); air.Introspector.Console.log("Database File Was Opened successfully with a synchronous operation"); dbStm = new air.SQLStatement(); dbStm.sqlConnection = dbConn; dbStm.text = SQL_STRING; dbStm.execute(); } catch (error) { air.Introspector.Console.log("Error message:", error.message); air.Introspector.Console.log("Details:", error.details); } } function insertData() { try { dbStm.text = SQL_INSERT; dbStm.execute(); air.Introspector.Console.log("Data was inserted into the Database"); } catch (error) { air.Introspector.Console.log("Error message:", error.message); air.Introspector.Console.log("Details:", error.details); } }
Both the openDB
function and
the insertData
function manage the
operations on the database in the try...catch
statement so as to intercept
possible errors. The insertData
method uses the same instance of the SQLStatement
class, dbStm
, but the value passed to the text
property has been changed. If the
values are in fact inserted, the SQL text that is executed is the text
contained in the SQL_INSERT
variable, which is specified in the constants at the top of the
file:
var SQL_INSERT = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Alessio', 'Casario')";
Here are the messages that appear in the AIR Introspector Console tab:
[app:/Ch10/insertDataSinch.html][16:25:53] Database File was created: C:Documents and SettingsUserDesktopdbmyDBFile.db |
[app:/Ch10/insertDataSinch.html][16:25:54] Database File Was Opened successfully with a synchronous operation |
[app:/Ch10/insertDataSinch.html][16:26:02] Data was inserted into the Database |
The following is the complete HTML page that uses the previous JavaScript code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <script type="text/javascript" src="frameworks/AIRAliases.js"></script> <script type="text/javascript" src="frameworks/AIRIntrospector.js"></script> <script type="text/javascript" src="InsertDataSinchInsertDataSync.js"></script> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>AIR Cookbook: 10.5 Querying a Database Asynchronously (JavaScript)</title> </head> <body onload="createDB()"> <h1>AIR Cookbook: 10.5 Querying a Database Asynchronously (JavaScript)</h1> <p> <label>Create a Table in a Database File <input type="button" name="openDB" id="openDB" value="Insert Data" accesskey="o" tabindex="1" onclick="insertData();document.getElementById('resultDiv').innerText = 'Data was added to the database';" /> </label> </p> <p><div id="resultDiv"></div></p> </body> </html>.
Use the openAsync
method of the
SQLConnection
class to query a
database asynchronously. Register the event listeners on the RESULT
event of the SQLEvent
class and the ERROR
event of the SQLErrorEvent
class.
The asynchronous operations execute in the background, which
allows the user to continue interacting with the application even if the
results still haven’t been returned. By creating event listeners, which
dispatch the RESULT
event of the
SQLEvent
class and the ERROR
event of the SQLErrorEvent
, the end of each operation is
managed to establish whether it has been successful or whether errors
have occurred.
Before discussing the example, it is important to specify that it
isn’t possible to change the text
property of a SQLStatement
instance
if it has already been assigned. As an alternative, AIR lets you create
several instances of the SQLStatement
class that connect to the same database. This way, it is possible to
manage several SQL statement operations by creating different instances
of the SQLStatement
class for each of
them. For example:
var createText:StringsqlString = "CREATE TABLE IF NOT EXISTS Students(" + "stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + "firstName TEXT, " + "lastName TEXT" + ")"; var insertText:StringsqlInsert = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Marco', 'Casario')"; var sqlCreate:SQLStatement = new SQLStatement(); sqlCreate.sqlConnection = conn; sqlCreate.text = createText; sqlCreate.execute(); var sqlInsert:SQLStatement = new SQLStatement(); sqlInsert.sqlConnection = conn; sqlInsert.text = insertText; sqlInsert.execute();
This code creates two instances of the SQLStatement
class: sqlCreate
and sqlInsert
. Both instances connect to the same
database by using the instance of the SQLConnection
called conn
. However, whereas the first instance
creates the structure of the database (the Students
table with the two columns firstName
and lastName
), the second SQLStatement
instance executes an INSERT
SQL statement by inserting the values
in the newly created table.
With this approach, every SQLStatement
object has its own queue or list
of operations that it is instructed to perform. As soon as the first
operation of the queue list is executed, it will pass on to the
following operations until the queue list is finished.
In the example, you will create nonglobal variables at the method level, and you can assign different text properties to them:
package com.oreilly.aircookbook.ch10 { import flash.data.SQLConnection; import flash.data.SQLStatement; import flash.errors.SQLError; import flash.events.SQLErrorEvent; import flash.events.SQLEvent; import flash.filesystem.File; import mx.controls.Alert; public class QueryingTableAsynch { private var _myDB:File; private var _isOpen:Boolean = false; private var _dbConn:SQLConnection; private var sqlString:String; private var sqlInsert:String; public function get myDB():File { return _myDB; } public function get isOpen():Boolean { return _isOpen; } public function QueryingTableAsynch() { createLocalDB(); sqlString = "CREATE TABLE IF NOT EXISTS Students(" + "stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + "firstName TEXT, " + "lastName TEXT" + ")"; sqlInsert = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Marco', 'Casario')"; } private function createLocalDB():void { var folder:File= File.applicationStorageDirectory.resolvePath( "db" ); folder.createDirectory(); _myDB = folder.resolvePath( "myDBFile.db" ); } public function createTableDB(dbFile:File):void { _dbConn = new SQLConnection(); _dbConn.openAsync(dbFile); _dbConn.addEventListener(SQLEvent.OPEN, openHandler); _dbConn.addEventListener(SQLErrorEvent.ERROR, errorHandler); } public function insertData(dbFile:File):void { var createStm:SQLStatement = new SQLStatement(); createStm.sqlConnection = _dbConn; createStm.text = sqlInsert; createStm.execute(); createStm.addEventListener(SQLEvent.RESULT, onStatementResult); createStm.addEventListener(SQLErrorEvent.ERROR, onStatementError); } private function openHandler(event:SQLEvent):void { trace("The Database File " + _myDB.nativePath + " was opened"); _isOpen = _dbConn.connected; var createStm:SQLStatement = new SQLStatement(); createStm.sqlConnection = _dbConn; createStm.text = sqlString; createStm.addEventListener(SQLEvent.RESULT, onStatementResult); createStm.addEventListener(SQLErrorEvent.ERROR, onStatementError); createStm.execute(); } private function errorHandler(event:SQLErrorEvent):void { mx.controls.Alert.show("Error message:", event.error.message); mx.controls.Alert.show("Details:", event.error.details); _isOpen = _dbConn.connected } private function onStatementResult(event:SQLEvent):void { mx.controls.Alert.show("Table created: " + event.type + event.target.text); } private function onStatementError(event:SQLErrorEvent):void { mx.controls.Alert.show("Error message:", event.error.message); mx.controls.Alert.show("Details:", event.error.details); } } }
The following ActionScript in the openHandler
event handler, which has been
registered on the OPEN
event of the
SQLConnection openAsync
method,
creates the table in the newly created and opened database. In fact,
the temporary variable createStm
executes the SQL string that carries out a CREATE TABLE
statement. Being asynchronous
operations, the event handlers have to be registered on the RESULT
and ERROR
events to manage the result data of
the operation and possible errors.
Data insertion is carried out on the insertData
method using the SQL string
contained in the sqlInsert
variable
as a text
property.
In the onStatementResult
event handler, you access the text
property of the SQLStatement
class that has
generated the event through the event object, as well as the type of
event that has been triggered.
The following is an example of MXML code that uses this ActionScript class to create a local database and to populate a table by executing asynchronous operations:
<?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" initialize="init()"> <mx:Script> <![CDATA[ import com.oreilly.aircookbook.ch10.QueryingTableAsynch; private var myDB:File; private var myDBclass:QueryingTableAsynch; private function init():void { createBtn.addEventListener(MouseEvent.CLICK, onClick); openBtn.addEventListener(MouseEvent.CLICK, onClickOpen); insertBtn.addEventListener(MouseEvent.CLICK, onClickInsert); } private function onClick(evt:MouseEvent):void { myDBclass = new QueryingTableAsynch(); myDB = myDBclass.myDB; openBtn.enabled = true; mx.controls.Alert.show("Database File Was Created : " + myDB.nativePath ); } private function onClickOpen(evt:MouseEvent):void { myDBclass.createTableDB(myDB); insertBtn.enabled = true; } private function onClickInsert(evt:MouseEvent):void { myDBclass.insertData(myDB); mx.controls.Alert.show("Data was inserted into the database : " + myDB.nativePath ); } ]]> </mx:Script> <mx:Button id="createBtn" label="Create DB" /> <mx:Button label="Open DataBase" id="openBtn" enabled="false" /> <mx:Button label="Insert Data Asynchronously" enabled="false" id="insertBtn" /> </mx:WindowedApplication>
In JavaScript, you create two global variables that contain the
instance of the SQLStatement
class. Each
SQLStatement
instance carries out a
connection to the same database and executes a different SQL statement
by loading a different SQL string in its text
property:
// Constants var DB_NAME = "db/myDBFile.db"; var SQL_STRING = "CREATE TABLE IF NOT EXISTS Students (" + " stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + " firstName TEXT, " + " lastName TEXT" + ")"; var SQL_INSERT = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Marco', 'Casario')"; var myDB; var dbConn; var dbStmCreate; var dbStmInsert; function createDB() { var folder = air.File.applicationStorageDirectory.resolvePath("db"); folder.createDirectory(); myDB = folder.resolvePath(DB_NAME); myDB = air.File.desktopDirectory.resolvePath(DB_NAME); air.Introspector.Console.log( "Database File was created: " + myDB.nativePath ); openDB(); } function openDB() { dbConn = new air.SQLConnection(); dbConn.openAsync(myDB); dbConn.addEventListener(air.SQLEvent.OPEN, onOpenHandler); dbConn.addEventListener(air.SQLErrorEvent.ERROR, onErrorHandler); } function populateDB() { air.Introspector.Console.log("Populating the database with data ..... "); dbStmInsert = new air.SQLStatement(); dbStmInsert.sqlConnection = dbConn; dbStmInsert.text = SQL_INSERT; dbStmInsert.execute(); dbStmInsert.addEventListener(air.SQLEvent.RESULT, onStatementResult); dbStmInsert.addEventListener(air.SQLErrorEvent.ERROR, onStatementError); } function onOpenHandler(event) { air.Introspector.Console.log("Database File Was Opened successfully"); dbStmCreate = new air.SQLStatement(); dbStmCreate.sqlConnection = dbConn; dbStmCreate.text = SQL_STRING; dbStmCreate.execute(); dbStmCreate.addEventListener(air.SQLEvent.RESULT, onStatementResult); dbStmCreate.addEventListener(air.SQLErrorEvent.ERROR, onStatementError); } function onErrorHandler(event) { air.Introspector.Console.log("Error message:", event.error.message); air.Introspector.Console.log("Details:", event.error.details); } function onStatementResult(event) { air.Introspector.Console.log("The following SQL statement has been executed:" + event.target.text); } function onStatementError(event) { air.Introspector.Console.log("Error message:", event.error.message); air.Introspector.Console.log("Details:", event.error.details); }
While the creation of the table happens in the event handler
that is triggered on the OPEN
event
of the asynchronous openAsync
method, the data insertion is handled in the second instance of the
SQLStatement
class, dbStmCreate
, which executes the INSERT
SQL statement in the public populateDB
method.
This JavaScript code manages only one event handler: SQLEvent.RESULT
. The SQL statement appears
in this event handler and is executed by the SQLStatement
object, which has triggered the
RESULT
event, by accessing the
target
property of the event
object:
function onStatementResult(event) { air.Introspector.Console.log("The following SQL statement has been executed: " + event.target.text); }
The complete HTML page is as follows:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <script type="text/javascript" src="frameworks/AIRAliases.js"></script> <script type="text/javascript" src="frameworks/AIRIntrospector.js"></script> <script type="text/javascript" src="InsertDataAsynch.js"></script> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>AIR Cookbook: 10.6 Querying a Database Asynchronously (JavaScript)</title> </head> <body onload="createDB()"> <h1>AIR Cookbook: 10.6 Querying a Database Asynchronously (JavaScript)</h1> <p> <label>Create a Table in a Database File <input type="button" name="openDB" id="openDB" value="Insert Data" accesskey="o" tabindex="1" onclick="populateDB();document.getElementById('resultDiv').innerText = 'Data was added to the database';" /> </label> </p> <p><div id="resultDiv"></div></p> </body> </html>
The following are the messages that will appear in the AIR Introspector Console tab after you click the button in the HTML page:
[app:/Ch10/insertDataAsynch.html][20:21:18] Database File was created: C:Documents and SettingsUserDesktopdbmyDBFile.db |
[app:/Ch10/insertDataAsynch.html][20:21:18] Database File Was Opened successfully |
[app:/Ch10/insertDataAsynch.html][20:21:18] The following SQL statement has been executed CREATE TABLE IF NOT EXISTS Students ( stuId INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT); |
[app:/Ch10/insertDataAsynch.html][20:21:21] Populating the database with data ..... |
[app:/Ch10/insertDataAsynch.html][20:21:21] The following SQL statement has been executed: INSERT INTO Students (firstName, lastName) VALUES ('Marco', ‘Casario'), |
Creating a database solves two basic needs. The first is to have a
robust and well-structured place to store data, and the second is to be
able to query the database to retrieve information according to certain
search parameters. The search parameters are written by using the
SELECT
SQL statement that describes
the set of data you want to retrieve and examine or edit.
To retrieve data from a database, you have to work with an
instance of the SQLStatement
class by using
the SELECT
statement to query the
database and make it return zero or more rows of data. The SQL syntax of
the SELECT
statement is as
follows:
SELECT [ALL | DISTINCT] result [FROM table-list] [WHERE expr] [GROUP BY expr-list] [HAVING expr] [compound-op select-statement]* [ORDER BY sort-expr-list] [LIMIT integer [( OFFSET | , ) integer]]
This enables you to write complex and powerful statements to query the database and make it return precise data. For further information, consult the Adobe Help pages regarding local database SQL support (http://help.adobe.com/en_US/AIR/1.5/jslr/localDatabaseSQLSupport.html#select).
The SQLStatement
class provides
the getResult
methods, which can
access the SQLResult
object that
contains the results of the SQL statement execution. By invoking this
method, the data returned by the SQL statement is retrieved and can be
visualized in the application or edited.
With AIR, you can launch the SELECT
SQL statement by creating an instance
of the SQLStatement
class to which
you can pass the database connection:
var selectStmt:SQLStatement = new SQLStatement(); selectStmt.sqlConnection = dbConn; selectStmt.text = "SELECT firstName, lastName FROM Students";
Then you can register the event handlers to handle the result data
or possible errors that could occur, on the RESULT
event of the SQLEvent
class or on the ERROR
event of the SQLErrorEvent
class:
selectStmt.addEventListener(SQLEvent.RESULT, onResultHandler); selectStmt.addEventListener(SQLErrorEvent.ERROR, onErrorHandler);
Finally, you launch the execute
method to execute the statement:
selectStmt.execute();
In the onResultHandler
result
handler, you invoke the getResult
method of the SQLStatement
class to
retrieve the values and visualize them in the AIR application.
If you open the database with the open
method in synchronous mode, you should
insert a try...catch
block instead of the event
handlers registered with the addEventListener
method:
try { selectStmt.execute(); // Invoke the selectStmt.getResult(); } catch (errorsqlError:Error) { // error handling }
Referring to the ActionScript class you created in Encrypting a Database with a Password, you can add a
public method to retrieve the data, as well as a variable that will
contain the SELECT
SQL statement in
the constructor of the class.
Here is the complete SelectDataAsynch.as class, where the new code is highlighted in bold:
package com.oreilly.aircookbook.ch10 { import flash.data.SQLConnection; import flash.data.SQLResult; import flash.data.SQLStatement; import flash.events.SQLErrorEvent; import flash.events.SQLEvent; import flash.filesystem.File; import mx.collections.ArrayCollection; import mx.controls.Alert; public class SelectDataAsynch { private var _myDB:File; private var _isOpen:Boolean = false; private var _dbConn:SQLConnection; private var sqlString:String; private var sqlInsert:String; private var sqlSelect:String; [Bindable] private var _myResultAC:ArrayCollection; public function get myDB():File { return _myDB; } public function get isOpen():Boolean { return _isOpen } public function get myResultAC():ArrayCollection { return _myResultAC; } public function SelectDataAsynch() { this.createLocalDB(); sqlString = "CREATE TABLE IF NOT EXISTS Students(" + "stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + "firstName TEXT, " + "lastName TEXT" + ")"; sqlInsert = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Katia', 'Casario')"; sqlSelect = "SELECT * FROM Students"; } private function createLocalDB():void { _dbConn = new SQLConnection(); var folder:File= File.applicationStorageDirectorydesktopDirectory.resolvePath( "db" ); folder.createDirectory(); _myDB = folder.resolvePath( "myDBFile.db" ); _dbConn.openAsync(_myDB); _dbConn.addEventListener(SQLEvent.OPEN, openHandler); _dbConn.addEventListener(SQLErrorEvent.ERROR, errorHandler); } public function insertData(dbFile:File):void { var insertStm:SQLStatement = new SQLStatement(); insertStm.sqlConnection = _dbConn; insertStm.text = sqlInsert; insertStm.execute(); insertStm.addEventListener(SQLEvent.RESULT, onStatementResult); insertStm.addEventListener(SQLErrorEvent.ERROR, onStatementError); } public function selectData(dbFile:File):void { var selectStm:SQLStatement = new SQLStatement(); selectStm.sqlConnection = _dbConn; selectStm.text = sqlSelect; selectStm.execute(); selectStm.addEventListener(SQLEvent.RESULT, onSelectResult); selectStm.addEventListener(SQLErrorEvent.ERROR, onStatementError); } private function openHandler(event:SQLEvent):void { _isOpen = _dbConn.connected; var createStm:SQLStatement = new SQLStatement(); createStm.sqlConnection = _dbConn; createStm.text = sqlString; createStm.addEventListener(SQLEvent.RESULT, onStatementResult); createStm.addEventListener(SQLErrorEvent.ERROR, errorHandler); createStm.execute(); } private function errorHandler(event:SQLErrorEvent):void { mx.controls.Alert.show("Error message:", event.error.message); mx.controls.Alert.show("Details:", event.error.details); _isOpen = _dbConn.connected } private function onStatementResult(event:SQLEvent):void { mx.controls.Alert.show("Table created:" + event.type + event.target.text); SQLStatement(event.target).removeEventListener(SQLEvent.RESULT, onStatementResult); SQLStatement(event.target).removeEventListener(SQLErrorEvent.ERROR, errorHandler); } private function onStatementError(event:SQLErrorEvent):void { mx.controls.Alert.show("Error message:", event.error.message); mx.controls.Alert.show("Details:", event.error.details); } private function onSelectResult(event:SQLEvent):void { var result:SQLResult = event.target.getResult(); var temp:Array = result.data is Array ? result.data : [{rows: result.rowsAffected}]; _myResultAC = new ArrayCollection(temp); SQLStatement(event.target).removeEventListener(SQLEvent.RESULT, onSelectResult); SQLStatement(event.target).removeEventListener(SQLErrorEvent.ERROR, errorHandler); } } }
The new ActionScript class that implements the SQL operations to retrieve SQL data can be used in an MXML application with the following code:
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" initialize="init()"> <mx:Script> <![CDATA[ import mx.events.CollectionEvent; import com.oreilly.aircookbook.ch10.SelectDataAsynch; private var myDB:File; [Bindable] private var myDBclass:SelectDataAsynch; private function init():void { createBtn.addEventListener(MouseEvent.CLICK, onClick); openBtn.addEventListener(MouseEvent.CLICK, onClickOpen); insertBtn.addEventListener(MouseEvent.CLICK, onClickInsert); selectBtn.addEventListener(MouseEvent.CLICK, onClickSelect); } private function onClick(evt:MouseEvent):void { myDBclass = new SelectDataAsynch(); myDB = myDBclass.myDB; openBtn.enabled = true; mx.controls.Alert.show("Database File Was Created : " + myDB.nativePath ); } private function onClickOpen(evt:MouseEvent):void { insertBtn.enabled = true; selectBtn.enabled = true; } private function onClickInsert(evt:MouseEvent):void { myDBclass.insertData(myDB); mx.controls.Alert.show("Data was inserted into the database : " + myDB.nativePath ); } private function onClickSelect(evt:MouseEvent):void { myDBclass.selectData(myDB); myDG.dataProvider = myDBclass.myResultAC; } ]]> </mx:Script> <mx:HBox> <mx:Button id="createBtn" label="Create DB" /> <mx:Button label="Open DataBase" id="openBtn" enabled="false" /> <mx:Button label="Insert Data Asynchronously" enabled="false" id="insertBtn" /> <mx:Button label="Show Data" id="selectBtn" enabled="false" /> </mx:HBox> <mx:DataGrid id="myDG" width="100%" height="60%"/> </mx:WindowedApplication>
The JavaScript version of this code dynamically creates
a ul
list HTML element, instead of
using a DataGrid
control, which
will create as many list items as the number of data items returned by
the SELECT SQLStatement
in a
for
loop. You can create dynamic
HTML elements in JavaScript by using the createElement
, createTextNode
, and appendChild
methods and by
inserting the for
loop in the event
handler of the air.SQLEvent.RESULT
event
of the SQLStatement
instance.
Here is the complete JavaScript file, saved as RetrieveDataAsynch.js. The new portions of code are highlighted in bold:
// Constants var DB_NAME = 'db/myDBFile.db'; var SQL_STRING = "CREATE TABLE IF NOT EXISTS Students (" + " stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + " firstName TEXT, " + " lastName TEXT" + ")"; var SQL_INSERT = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Marco', 'Casario')"; var SQL_SELECT = "SELECT * FROM Students"; var myDB; var dbConn; var dbStmCreate; var dbStmInsert; var dbStmSelect; function createDB() { var folder = air.File.applicationStorageDirectory.resolvePath("db"); folder.createDirectory(); myDB = folder.resolvePath(DB_NAME); myDB = air.File.desktopDirectory.resolvePath(DB_NAME); air.Introspector.Console.log( "Database File was created: " + myDB.nativePath ); openDB(); } function openDB() { dbConn = new air.SQLConnection(); dbConn.openAsync(myDB); dbConn.addEventListener(air.SQLEvent.OPEN, onOpenHandler); dbConn.addEventListener(air.SQLErrorEvent.ERROR, onErrorHandler); } function populateDB() { air.Introspector.Console.log("Populating the database with data ..... "); dbStmInsert = new air.SQLStatement(); dbStmInsert.sqlConnection = dbConn; dbStmInsert.text = SQL_INSERT; dbStmInsert.execute(); dbStmInsert.addEventListener(air.SQLEvent.RESULT, onStatementResult); dbStmInsert.addEventListener(air.SQLErrorEvent.ERROR, onStatementError); } function selectData() { dbStmSelect = new air.SQLStatement(); dbStmSelect.sqlConnection = dbConn; dbStmSelect.text = SQL_SELECT; dbStmSelect.execute(); dbStmSelect.addEventListener(air.SQLEvent.RESULT, onSelectResult); dbStmSelect.addEventListener(air.SQLErrorEvent.ERROR, onStatementError); } function onOpenHandler(event) { air.Introspector.Console.log("Database File Was Opened successfully"); dbStmCreate = new air.SQLStatement(); dbStmCreate.sqlConnection = dbConn; dbStmCreate.text = SQL_STRING; dbStmCreate.execute(); dbStmCreate.addEventListener(air.SQLEvent.RESULT, onStatementResult); dbStmCreate.addEventListener(air.SQLErrorEvent.ERROR, onStatementError); } function onErrorHandler(event) { air.Introspector.Console.log("Error message:", event.error.message); air.Introspector.Console.log("Details:", event.error.details); } function onStatementResult(event) { air.Introspector.Console.log("The following SQL statement has been executed: " + " " +event.target.text); } function onSelectResult(event) { var result = dbStmSelect.getResult(); var numResults = result.data.length; var ul = document.createElement('ul'), for (i = 0; i < numResults; i++) { var row = result.data[i]; var x = document.createElement('li'), x.appendChild(document.createTextNode("Student #"+ row.stuId + ": " + row.firstName + " " + row.lastName)); ul.appendChild(x); var output = "ID: " + row.stuId; output += "; NAME: " + row.firstName; output += "; LAST NAME: " + row.lastName; air.Introspector.Console.log(output); } document.getElementById('resultDiv').appendChild(ul); } function onStatementError(event) { air.Introspector.Console.log("Error message:", event.error.message); air.Introspector.Console.log("Details:", event.error.details); }
The core of this JavaScript code is in the onSelectResult
event handler, which is triggered when the SQLStatement
has been completed. Inside this
event handler, you create a for
loop, which uses the length of the items returned by the SELECT
statement in the result.data.length
property as the end of
the loop. Create as many li
elements as there are records in the database; then append them to the
ul
element with the appendChild
method of the document
class.
Furthermore, messages are passed onto the AIR Introspector Console tab, showing the content of the database.
Here is the HTML page that includes and uses the methods of this library:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <script type="text/javascript" src="frameworks/AIRAliases.js" /> <script type="text/javascript" src="frameworks/AIRIntrospector.js" /> <script type="text/javascript" src="examples/10/RetrieveDataAsynch.js" /> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>AIR Cookbook: 10.7 Retrieving Results from a Query (JavaScript)</title> </head> <body onload="createDB()"> <h1>AIR Cookbook: 10.7 Retrieving Results from a Query (JavaScript)</h1> <p> <label>Create a Table in a Database File <input type="button" name="openDB" id="openDB" value="Insert Data" accesskey="o" tabindex="1" onclick="populateDB();document.getElementById('resultDiv'). innerText = 'Data was added to the database';" /> </label> </p> <p> <label>Select Data in the Database <input type="button" name="openDB2" id="openDB2" value="Select Data" accesskey="o" tabindex="1" onclick="selectData();document.getElementById('resultDiv').innerText = 'Data selected';" /> </label> </p> <div id="resultDiv"></div> </body> </html>
The following are the text messages that are passed onto the AIR Introspector Console tab (they vary according to the content of the database):
[app:/Ch10/selectDataAsynch.html][18:51:20] Database File was created: C:Documents and SettingsUserDesktopdbmyDBFile.db |
[app:/Ch10/selectDataAsynch.html][18:51:20] Database File Was Opened successfully |
[app:/Ch10/selectDataAsynch.html][18:51:20] The following SQL statement has been executed CREATE TABLE IF NOT EXISTS Students ( stuId INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT); |
[app:/Ch10/selectDataAsynch.html][18:51:22] Populating the database with data ..... |
[app:/Ch10/selectDataAsynch.html][18:51:23] The following SQL statement has been executed: INSERT INTO Students (firstName, lastName) VALUES ('Marco', ‘Casario'), |
[app:/Ch10/selectDataAsynch.html][18:51:23] ID: 1; NAME: Marco; LAST NAME: Casario |
[app:/Ch10/selectDataAsynch.html][18:51:23] ID: 2; NAME: Alessio; LAST NAME: Casario |
You want to use parameters in queries to create a reusable SQL statement and prevent the risk of SQL injection.
Use the parameters
property to
specify named or unnamed parameters in SQL queries and to create
reusable SQL statements.
Parameters enable you to create reusable SQL statements to work
with the same SQLStatement
instance
and carry out multiple SQL operations. For example, you can use an
INSERT
statement several times during
the life cycle of the application to allow the user to insert multiple
values in the database that will populate the database with data. This
is why it is compulsory to use the parameters approach in SQL statements
for the performance of the application itself. Parameters can be
declared as named or unnamed parameters.
Named parameters are declared with a specific name, which the
database uses as a placeholder in the SQL statement. They can be
specified by using the :
or @
character. Here’s an example where :name
and :surname
are two parameters that are inserted
in the SQL text statement:
var statementInstance:SQLStatement = new SQLStatement(); var sqlText:String = "INSERT INTO Students (firstName, lastName) VALUES (:name, :surname)"; statementInstance.parameters[":name"] = "Marco"; statementInstance.parameters[":surname "] = "Casario"; SQL_String = "INSERT INTO Students (firstName, lastName) VALUES (:name, :surname)" statementIstance.parameters[":name"] = "Marco"; statementIstance.parameters[":surname "] = "Casario";
Unnamed parameters, on the other hand, are specified with the
?
character in the SQL statement, and
they are set by using a numerical index in the same order they are
written in the SQL statement:
var statementInstance:SQLStatement = new SQLStatement(); var sqlText:String = "INSERT INTO Students (firstName, lastName) VALUES (?, ?)"; statementInstance.parameters[0] = "Marco"; statementInstance.parameters[1] = "Casario"; SQL_String = "INSERT INTO Students (firstName, lastName) VALUES (?, ?)" statementIstance.parameters[0] = "Marco"; statementIstance.parameters[1] = "Casario";
The parameters
property is an
associative array, and the indices are zero-index based.
Using parameters doesn’t enable you only to reuse the same SQL statement; it also makes the application more robust and secure. It’s more robust because the parameters are typed substitutions of values and they guarantee the storage class for a value passed into the database. It’s more secure because the parameters aren’t written in the SQL text and they don’t link the user input to the SQL text. Therefore, this prevents possible SQL injection attacks. In fact, when you use parameters, the values are treated as substituted values instead of being part of the SQL text.
It will become necessary to use parameters in SQL statements in
most AIR applications. To use parameters, you need to have an instance
of the SQLStatement
class where you
can define the parameters
property as
an associative array. The SQL text will also have to be changed by
defining the placeholder values that will be associated to the
parameters of the SQLStatement
instance.
In this solution, you will add a public method to the ActionScript
and JavaScript class you created in Creating Tables in a Database to create a parameterized
INSERT
SQL operation.
Use the ActionScript class created in Creating Tables in a Database to create an InsertParam.as class, making the following changes:
Add a private String
property called sqlAdd'sqlAdd'
.
Change the constructor by adding a SQL statement that will use parameters:
private var sqlAdd:String; //... public function InsertParam() { this.createLocalDB(); sqlString = "CREATE TABLE IF NOT EXISTS Students(" + "stuId INTEGER PRIMARY KEY AUTOINCREMENT, " + "firstName TEXT, " + "lastName TEXT" + ")"; sqlInsert = "INSERT INTO Students (firstName, lastName) " + "VALUES ('Marco', 'Casario')"; sqlSelect = "SELECT * FROM Students"; sqlAdd = "INSERT INTO Students (firstName, lastName)" + "VALUES (:name, :surname)"; }
Write a new public method that will be invoked by the application and that will be responsible for executing the SQL statement and associating the parameters to the SQL text:
public function insertParameters (paramName:String, paramLast:String):void { var paramStmt:SQLStatement = new SQLStatement(); paramStmt.sqlConnection = _dbConn; paramStmt.text = sqlAdd; paramStmt.parameters[":name"] = paramName; paramStmt.parameters[":surname"] = paramLast; paramStmt.execute(); paramStmt.addEventListener(SQLEvent.RESULT, paramAddHandler); paramStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler); } private function paramAddHandler(event:SQLEvent):void { trace("Data added using parameters"); }
The insertParam
method
accepts two parameters: paramName:String
and paramLast:String
. These are
used by the parameters
property of
the SQLStatement
instance:
paramStmt.parameters[":name"] = paramName; paramStmt.parameters[":surname"] = paramLast;
The MXML page that imports the new ActionScript class has a
Form
container with two TextInput
controls. The text you will insert
into these two controls will be passed onto the insertParam
method, which will use them as
parameters in the SQL statement:
<?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" initialize="init()"> <mx:Script> <![CDATA[ import com.oreilly.aircookbook.ch10.InsertParam; private var myDB:File; [Bindable] private var myDBclass:InsertParam; private function init():void { createBtn.addEventListener(MouseEvent.CLICK, onClick); openBtn.addEventListener(MouseEvent.CLICK, onClickOpen); insertBtn.addEventListener(MouseEvent.CLICK, onClickInsert); selectBtn.addEventListener(MouseEvent.CLICK, onClickSelect); addBtn.addEventListener(MouseEvent.CLICK, onClickAdd); } private function onClick(evt:MouseEvent):void { myDBclass = new InsertParam(); myDB = myDBclass.myDB; openBtn.enabled = true; mx.controls.Alert.show("Database File Was Created : " + myDB.nativePath ); } private function onClickOpen(evt:MouseEvent):void { insertBtn.enabled = true; selectBtn.enabled = true; } private function onClickInsert(evt:MouseEvent):void { myDBclass.insertData(myDB); mx.controls.Alert.show("Data was inserted into the database : " + myDB.nativePath ); } private function onClickSelect(evt:MouseEvent):void { myDBclass.selectData(myDB); myDG.dataProvider = myDBclass.myResultAC; } private function onClickAdd(evt:MouseEvent):void { myDBclass.insertParameters(nameTxt.text, lastTxt.text); } ]]> </mx:Script> <mx:VDividedBox> <mx:HBox> <mx:Button id="createBtn" label="Create DB" /> <mx:Button label="Open DataBase" id="openBtn" enabled="false" /> <mx:Button label="Insert Data Asynchronously" enabled="false" id="insertBtn" /> <mx:Button label="Show Data" id="selectBtn" enabled="false" /> </mx:HBox> <mx:DataGrid id="myDG" width="100%" height="60%"/> <mx:VBox width="100%"> <mx:Label text="Insert Values into the Database" /> <mx:Form width="100%"> <mx:FormHeading label="Heading"/> <mx:FormItem label="Name"> <mx:TextInput id="nameTxt"/> </mx:FormItem> <mx:FormItem label="Surname"> <mx:TextInput id="lastTxt"/> </mx:FormItem> <mx:FormItem > <mx:Button label="Insert Values" id="addBtn"/> </mx:FormItem> </mx:Form> </mx:VBox> </mx:VDividedBox> </mx:WindowedApplication>
In the event handler that is triggered with the click of the
button, the insertParam
method of
the ActionScript class is invoked, and the values inserted in the two
TextInput
controls are
passed onto it.
You can test it by launching the AIR application, inserting
values in the text fields, and clicking the button to send the data.
This data will be inserted in the database and shown in the DataGrid
control that is associated with the
ArrayCollection
that contains the
SELECT
SQL statement.
As far as the JavaScript and HTML version is concerned, you can also add a few finishing touches to the JavaScript and HTML files you created in the previous solution to add parameters to a SQL operation. Create a new file called InsertParam.js based on the RetrieveDataAsynch.js file, and make the following changes:
Insert two new variables in the JavaScript file; one will
contain the SQL text with specified named parameters, and the
other will be the instance of the SQLStatement
with the following
parameters:
var SQL_ADD = "INSERT INTO Students (firstName, lastName)" + "VALUES (:firstName, :lastName)"; // ... var dbStmAddParam; // ... function addDataParam(name, last) { dbStmAddParam = new air.SQLStatement(); dbStmAddParam.sqlConnection = dbConn; dbStmAddParam.text = SQL_ADD; dbStmAddParam.parameters[":firstName"] = name; dbStmAddParam.parameters[":lastName"] = last; dbStmAddParam.addEventListener(air.SQLErrorEvent. RESULT, onStatementResult); dbStmAddParam.addEventListener(air.SQLErrorEvent.ERROR, onStatementError); dbStmAddParam.execute(); } function onSelectResult(event) { selectData(); }
The addDataParam
function
accepts two parameters, which it will pass onto the parameters
property of the SQLStatement
instance. You launch the
selectData
function in the
event handler of the air.SQLEvent.RESULT
event, which has the
role of writing all the records returned by a SELECT
statement in a ul
list.
Add the selectData
function. This is the content of the selectData
function and the RESULT
event handler:
function selectData() { dbStmSelect = new air.SQLStatement(); dbStmSelect.sqlConnection = dbConn; dbStmSelect.text = SQL_SELECT; dbStmSelect.execute(); dbStmSelect.addEventListener(air.SQLEvent.RESULT, onSelectResult); dbStmSelect.addEventListener(air.SQLErrorEvent.ERROR, onStatementError); } function onSelectResult(event) { var result = dbStmSelect.getResult(); var numResults = result.data.length; var ul = document.createElement('ul'), for (i = 0; i < numResults; i++) { var row = result.data[i]; var x = document.createElement('li'), x.appendChild(document.createTextNode("Student #"+ row.stuId + ": " + row.firstName + " " + row.lastName)); ul.appendChild(x); var output = "ID: " + row.stuId; output += "; NAME: " + row.firstName; output += "; LAST NAME: " + row.lastName; air.Introspector.Console.log(output); } document.getElementById('resultDiv').appendChild(ul); }
The HTML page will contain a Form
with two text input controls. The
values inserted by the user will be used as parameters of the addDataParam
function that is defined in the
JavaScript file:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <script type="text/javascript" src="frameworks/AIRAliases.js"></script> <script type="text/javascript" src="frameworks/AIRIntrospector.js"></script> <script type="text/javascript" src="InsertParam.js"></script> <script language="javascript" type="text/javascript"> <!-- function sendParam() { var name = document.simpleForm.firstName.value; var last = document.simpleForm.lastName.value; addDataParam(name,last); } //--> </script> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>AIR Cookbook: 10.8 Using Parameters in Queries (JavaScript)</title> </head> <body onload="createDB()"> <h1>AIR Cookbook: 10.8 Using Parameters in Queries (JavaScript)</h1> <p> <label>Create a Table in a Database File <input type="button" name="openDB" id="openDB" value="Insert Data" accesskey="o" tabindex="1" onclick="populateDB();document.getElementById('resultDiv').innerText = 'Data was added to the database';" /> </label> </p> <p> <label>Select Data in the Database <input type="button" name="openDB2" id="openDB2" value="Select Data" accesskey="o" tabindex="1" onclick="selectData();document.getElementById('resultDiv').innerText = 'Data selected';" /> </label> </p> <p>div> <form name="simpleForm"> Insert Name: <input type="text" name="firstName" size="20" /><br /> Insert Surname:<input type="text" name="lastName" size="20" /> <br /> <input type="button" value="Insert Values" onclick="sendParam()" /> </form> </divp> <p><div id="resultDiv"></div></p> </body> </html>
Contributed by Luca Mezzalira (http://lucamezzalira.wordpress.com/)
Embed an existing SQLite database in the AIR application, and copy it to another folder to interact with it.
Many desktop applications use databases to store data locally on the user’s computer. In some AIR applications, you need to embed an existing SQLite database with the packaged .air file.
Because the .air
file is a package with some files inside, when you install an
AIR application in your computer, you copy those files into the
application folder or in a subfolder. If you want to include other files
in an AIR application, then when you create it, you must package the
other files like images, text files, or database files as well. This
includes an existing SQLite database (created for another application or
with another program). Note that the application folder, File.applicationDirectory
, is read-only. If
you try to work with a database file in this directory, it will fail
with a silent error. To make this work, you must copy database file with
the copyTo
method of the File
class into another folder such as the
Documents folder or the desktop folder. When you copy this file into
this directory, you can work with your database and can create new
records, update records, or delete them.
In this ActionScript example, the file software.db is copied from the application directory of the AIR application to the Documents directory of the user’s computer. After the file is copied, you can then interact with it as needed.
var dbFile:File = File.applicationDirectory.resolvePath("db/software.db"); var dbWorkFile:File = File.documentsDirectory.resolvePath("software.db"); if(!dbWorkFile.exists){ dbFile.copyTo(dbWorkedFile); }
In this JavaScript example, the file software.db is copied from the application directory of the AIR application to the Documents directory of the user’s computer. After the file is copied, you can then interact with it as needed.
var dbFile = air.File.applicationDirectory.resolvePath("db/software.db"); var dbWorkFile = air.File.documentsDirectory.resolvePath("software.db"); if(!dbWorkFile) { dbFile.copyTo(dbWorkFile); }
Contributed by Marin Todorov (http://www.underplot.com/)
You need to easily persist a group of settings for your JavaScript AIR application.
Utilize the embedded SQLite database by reading the application settings from it when the application loads.
Within an application, some settings may need to persist beyond a single session. For example, you might want the user to be able to select the position of the application windows, the color scheme, or the name of the default user profile. In these cases, the values can be stored in the SQLite database and loaded into the application upon instantiation. By doing this, you allow these values to persist, and when the users open the application again, they will see that their settings have been saved.
To understand how the persistence will work, you can
look at the settings database and the JavaScript static class,
Settings
, which performs the
loading and setting of the persistent data.
In the database there will be a name
column and a value
column. In addition, there will be a
namespace
column, just to be able
to group easier similar settings. Finally, there will be a unique
index to ensure that there are not duplicate records. Here are the SQL
queries to create this database:
CREATE TABLE settings (IdSetting INTEGER PRIMARY KEY, name TEXT, namespace TEXT, value TEXT); CREATE UNIQUE INDEX [UNIQ] ON [settings]([name] DESC,[namespace] DESC)
Table 10-1 lists the initial settings populated in the database, settings.db:
IdSetting | Name | Namespace | Value |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
In this example, title
stores
the title of the application. The values x
and y
within the position
namespace
persist the application’s position on the screen. Finally, width
and height
within the size
namespace persist the dimensions of the
application’s window.
In the JavaScript class there are two methods: load
and save
. The load
method
reads everything from the database and loads it into the class itself.
The save
method compares the
setting values at load time with the setting values when the method
was called, and if something was modified, those entries are updated
in the database.
var Settings = { __db: null, __result: null, __load: function(dbconn) { Settings.__db = dbconn; var stmt = new air.SQLStatement(); stmt.sqlConnection = dbconn; stmt.text = "select IdSetting, name, value, namespace from settings"; stmt.execute(); var result = stmt.getResult().data; Settings.__result = result; for( var i=0; i<result.length; i++ ){ if (result[i].namespace!=null) { if (!Settings[result[i].namespace]) { Settings[result[i].namespace] = {}; } Settings[result[i].namespace][result[i].name] = result[i].value; } else { Settings[result[i].name] = result[i].value; } } stmt = null; result = null; }, __save: function() { var stmt = new air.SQLStatement(); stmt.sqlConnection = Settings.__db; for (var i = 0; i < Settings.__result.length; i++) { if (Settings.__result[i].namespace!=null) { if ( Settings[Settings.__result[i].namespace][Settings.__result[i].name] != Settings.__result[i].value ) { stmt.text = "update settings set value= :value where name= :name and namespace= :namespace"; stmt.parameters[":name"] = Settings.__result[i].name; stmt.parameters[":value"] = Settings[Settings.__result[i].namespace][Settings.__result[i].name]; stmt.parameters[":namespace"] = Settings.__result[i].namespace; stmt.execute(); } } else { if (Settings[Settings.__result[i].name] != Settings.__result[i].value) { stmt.text = "update settings set value= :value where name= :name and namespace IS NULL"; stmt.parameters[":name"] = Settings.__result[i].name; stmt.parameters[":value"] = Settings[Settings.__result[i].name]; stmt.execute(); } } } stmt = null; } };
In this class, the method names are prefixed with __
to preserve the class namespace, just in
case you have settings named load
or save
(which would overwrite the
methods if they didn’t have the prefix).
When you call the __load
method and provide it with valid database connection, you can read and
write settings like this:
Settings.namespace.property
For properties that are not designated to a namespace, use this:
Settings.property
For properties that are assigned a namespace, use this:
Settings.position.x = 100;
In this example, the application persists its window position on the screen, the window dimensions, and the window title:
<html> <head> <script type="text/javascript" src="AIRAliases.js"></script> <script type="text/javascript" src="Settings.js"></script> <script type="text/javascript"> var db = null; var stmt = null; //the settings database in the app directory var dbFile = air.File.applicationStorageDirectory.resolvePath("settings.db"); //on application load connect to the db function onApplicationLoad() { //add handler to save the settings window.nativeWindow.addEventListener("closing", onWindowClose); //open connection to settings.db database db = new air.SQLConnection(); db.addEventListener( air.SQLEvent.OPEN, onDatabaseOpen ); db.open( dbFile, air.SQLMode.CREATE ); } //read the settings from db function onDatabaseOpen(e) { Settings.__load(db); //set x, y and window title window.nativeWindow.x = Settings.position.x window.nativeWindow.y = Settings.position.y; window.nativeWindow.width = Settings.size.width; window.nativeWindow.height = Settings.size.height; window.nativeWindow.title = Settings.title; //show the window window.nativeWindow.visible = true; } //method to set window's title function setTitle() { //update window's title window.nativeWindow.title = document.getElementById('titleFld').value; //update the settings object Settings.title = document.getElementById('titleFld').value; } //read the x and y before the window is closed and save them to db function onWindowClose() { Settings.position.x = window.nativeWindow.x; Settings.position.y = window.nativeWindow.y; Settings.size.width = window.nativeWindow.width; Settings.size.height = window.nativeWindow.height; //save the settings Settings.__save(); } </script> <style> body {padding: 10px; color: #ccc; background: #333;} input {width:190px;} </style> </head> <body onload="onApplicationLoad()"> Change the title of the application:<hr /><br /> Window title: <br /> <input type="text" id="titleFld" /><br /> <input type="button" value="Change window title" onClick="setTitle()" /> <br /><br /> Change window position on the screen, resize it and change the title and these settings will be saved for the next time you run the program </body> </html>
Although you can persist application settings in ActionScript in many ways, you can use Adobe’s ActionScript library, as3preferenceslib. This library, along with code samples, are at the project site at http://code.google.com/p/as3preferenceslib/. A sample application is included with the code.