Chapter 10. Embedded SQL Database Programming

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

Creating a Database

Problem

You need to create a local database using SQLite.

Solution

Create a local SQLite database in the same way you create a local file.

Discussion

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.

ActionScript/Flex

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.

JavaScript

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.

The database has been created, and the message is shown in the AIR Introspector Console tab.
Figure 10-1. The database has been created, and the message is shown in the AIR Introspector Console tab.

Connecting to a Database in Synchronous Mode

Problem

You need to open a database and create a synchronous connection to it.

Solution

Create a connection to an existing database file using the SQLConnection class.

Discussion

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.

    Note

    Note that the autoCompact operation can decrease the performance of database access and can also cause the database data to become fragmented over time.

  • 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).

ActionScript/Flex

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.

JavaScript/HTML

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';" />

Connecting to a Database in Asynchronous Mode

Problem

You need to open a database and connect to it in asynchronous mode.

Solution

Use the openAsync method, which opens a database file at the specified location in the file system with an asynchronous execution.

Discussion

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].

ActionScript/Flex

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>

JavaScript/HTML

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>

Creating an In-Memory Database

Problem

You need to create an in-memory database without creating a local file.

Solution

Set the reference parameter of the open and openAsync methods to null to create an in-memory database.

Discussion

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.

ActionScript

The reference parameter is an Object that specifies the location of the database file that is being opened. Set it to null to create an in-memory database:

var _dbConn:SQLConnection = new SQLConnection();
_dbConn.openAsync(null);

JavaScript

To create an in-memory database with JavaScript, use the following syntax:

var dbConn = new air.SQLConnection();
dbConn.openAsync(null);

Creating an Encrypted Database

Problem

You need to ensure that the sensitive information contained in the database for your AIR application cannot be accessed outside the application.

Solution

Encrypt the database with the Advanced Encryption Standard (AES) encryption inside AIR 1.5.

Discussion

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.

Note

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.

Warning

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.

ActionScript/Flex

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>

JavaScript

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>

Encrypting a Database with a Password

Problem

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.

Solution

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.

Discussion

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.

Note

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.

ActionScript/Flex

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>

JavaScript

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>

Creating Tables in a Database

Problem

You want to create a table in the SQLite local database.

Solution

Create tables in a database using the SQLStatement class.

Discussion

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

Supported SQL Dialects

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)

Note

BLOB stands for Binary Large Object.

Note

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.

ActionScript/Flex

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>

JavaScript

To create a table in the database using the JavaScript code, follow these basic steps:

  1. Create a database.

  2. Open a connection to the database.

  3. Create a SQLStatement instance.

  4. In the instance, specify the database in which to create the table by using the sqlConnection property.

  5. Specify the SQL string to apply on the text property.

  6. For an asynchronous execution mode, create the event handlers to handle the result of the execute operation.

  7. 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

Note

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/).

Querying a Database Synchronously

Problem

You want to establish synchronous database operations

Solution

Query a database using the sqlConnection and text properties of the SQLStatement class.

Discussion

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.

Flex/ActionScript

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>

JavaScript

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>.

Querying a Database Asynchronously

Problem

You want to execute database operations asynchronously.

Solution

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.

Discussion

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.

Flex/ActionScript

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>

JavaScript

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'),

Retrieving Results from a Query

Problem

You want to retrieve data from a database with a SELECT statement.

Solution

Use the SELECT statement with the SQLStatement class to query a database and retrieve data.

Discussion

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
}

ActionScript/Flex

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>

JavaScript

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

Using Parameters in Queries

Problem

You want to use parameters in queries to create a reusable SQL statement and prevent the risk of SQL injection.

Solution

Use the parameters property to specify named or unnamed parameters in SQL queries and to create reusable SQL statements.

Discussion

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.

ActionScript/Flex

Use the ActionScript class created in Creating Tables in a Database to create an InsertParam.as class, making the following changes:

  1. Add a private String property called sqlAdd'sqlAdd'.

  2. 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)";
}

  3. 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.

JavaScript

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:

  1. 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.

  2. 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>

Including a Database in an Application

Contributed by Luca Mezzalira (http://lucamezzalira.wordpress.com/)

Problem

You want to include an existing SQLite database with Adobe AIR.

Solution

Embed an existing SQLite database in the AIR application, and copy it to another folder to interact with it.

Discussion

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.

ActionScript/Flex

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

JavaScript

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

Persisting Application Settings

Contributed by Marin Todorov (http://www.underplot.com/)

Problem

You need to easily persist a group of settings for your JavaScript AIR application.

Solution

Utilize the embedded SQLite database by reading the application settings from it when the application loads.

Discussion

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.

JavaScript

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:

Table 10-1. Initial Database Settings

IdSetting

Name

Namespace

Value

1

title

NULL

Set Example

2

x

position

79

3

y

position

305

4

width

size

220

5

height

size

320

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>

ActionScript/Flex

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.188.152.162