|
Work with Local SQL Database Using AIR
Adobe integrated runtime (AIR) allows developers to built rich Internet applications. Here is a demo of using AIR's integrated SQL database engine
Sandeep Koul
Sunday, February 01, 2009
One good feature in Adobe's AIR, the platform for developing rich Internet
apps is a database engine that runs within AIR's runtime environment. Since all
the database files are stored locally, there is no need of an external database
connection. This also implies that persistent application data can be stored
conveniently on the local drive. The local database is stored as a single file
in the computer's file system. The runtime is responsible for creation,
retrieval and manipulation of data in this database file. In the following
implementation, we will be using synchronous mode of execution to create RIAs
with AIR.
Implementation
Let's start with installation of AIR SDK. This can be downloaded from
'www.adobe.com or you can also get it from this issue's DVD. Once extracted to a
folder, set the PATH environment variable. This can be done by adding reference
of extracted folder to it. In our case, we added the following path in the end
of PATH environment variable 'C:\AdobeAIRSDK\bin'. The bin folder present in SDK
contains ADL and ADT tools.AIR debug launcher or ADL is used to run applications
without packaging them, whereas AIR developer tool is for packaging your
application as an AIR file for distribution. One more important file that we
would be using in this implementation is 'AIRaliases.js' present in 'C:\AdobeAIRSDK
\frameworks\libs\air' folder. This file provides alias definition that allows
one to run 'ActionScript' runtime class i.e. air.FileStream, air.SQLDatabase
etc. To use this file, one needs to add following script reference in HTML page:
<script src="AIRAliases.js" type="text/javascript"></script>
Start with creating a folder that contains three files: XML file (pcquest.xml),
HTML file (pcquest.html) and AIRaliases.js file as explained above. We will
start with the applications descriptor file (pcquest.xml). Here is the content
of this file along with explanation:
|
Direct Hit! |
Applies To: Web Developers
USP: SQL like engine within AIR
Primary Link:
www.adobe.com
Keywords: AIR |
<?xml version="1.0" encoding="utf-8"?>
Add the attribute that defines the namespace for AIR as shown. Last part
shows the version of AIR used:
<application xmlns="http://ns.adobe.com/air/application/1.5">
Next line shows attribute to uniquely identify application:
<id>sandeep.com</id>
One can also define version of the application as :
<version>version 1.0</version>
<filename>sample</filename>
<name>AIR Example</name>
To define properties of the application window:
<initialWindow>
<title>synchronous local database example</title>
<content>pcquest.html</content>
<transparent>false</transparent>
<visible>true</visible>
<minimizable>true</minimizable>
<maximizable>true</maximizable>
<resizable>true</resizable>
<width>640</width>
<height>480</height>
<x>150</x>
<y>150</y>
</initialWindow>
</application>
Now we have created the required descriptive file. Let's create a HTML page
that contains code to create database and then display that database. Here is
the code snippet showing the content of 'pcquest.html':
 |
| Before packaging, one can test
the result of application using ADL. Simply move to application folder and
type 'adl pcquest.xml' in command prompt. |
<html>
<head>
<title>pcquest database example</title>
<style type="text/css">..........</style>
<script src="AIRAliases.js" type="text/javascript"></script>
<script>
var conn = null;
var createStmt = null;
var insertStmt = null;
var insertStmt2 = null;
var selectStmt = null;
The 'init()' method is defined in '<body>' tag's 'onload' event handler, this
is called when application starts loading. Instance of 'SQLConnection()' method
is created in it for connection to the database. After connection is
established, it is opened using 'open()' and parameter 'null' signifies that
instead of disk, database would be created in computer memory as shown:
function init()
{
conn = new air.SQLConnection();
conn.open(null);
createTable();
}
Next step is to create the table. This is done by creating an instance of
'SQLStatem ent()' and passing connection 'conn' to it:
function createTable()
{
createStmt = new air.SQLStatement();
createStmt.sqlConnection = conn;
var sql = "";
var sql = "CREATE TABLE IF NOT EXISTS pcquest ( ";
sql += "Id INTEGER PRIMARY KEY AUTOINCREMENT, ";
sql += "MagzineName TEXT, ";
sql += "MagzineType TEXT, ";
sql += "price NUMERIC CHECK (price >= 0) DEFAULT 0";
sql += ")";
createStmt.text = sql;
createStmt.execute();
addData();
}
Once we have the table, we need to add data to it. This is accomplished by
using 'addData()' function with 'insertStmt' and 'insertStmt2'.
function addData()
{
insertStmt = new air.SQLStatement();
insertStmt.sqlConnection = conn;
var sql = "";
sql += "INSERT INTO pcquest (MagzineName, MagzineType, price) ";
sql += "VALUES ('PCQUest', 'Enterprise', 100)";
insertStmt.text = sql;
insertStmt2 = new air.SQLStatement();
insertStmt2.sqlConnection = conn;
var sql2 = "";
sql2 += "INSERT INTO pcquest (MagzineName, MagzineType, price) ";
sql2 += "VALUES ('Living Digital', 'Life style', 50)";
insertStmt2.text = sql2;
insertStmt.execute();
insertStmt2.execute();
}
Once we have the table with data, we have to retrieve this data and then
display it. To accomplish retrieval, first createan instance of 'SQLStatement()',
i.e. selectStmt and pass your query as text property of this instance.
function getData()
{
selectStmt = new air.SQLStatement();
selectStmt.sqlConnection = conn;
var sql = "SELECT Id, MagzineName, MagzineType, price FROM pcquest";
selectStmt.text = sql;
selectStmt.execute();
renderResult();
}
Finally retrieved data is rendered using 'renderResult()' function
unction renderResult()
{
var result = selectStmt.getResult();
var row;
var cell;
var tbl = document.getElementById("resultsGrid");
tbl.innerHTML = "";
var numRows = result.data.length;
for (var i = 0; i < numRows; i++)
{
if (i == 0)
{
row = document.createElement("tr");
for (col in result.data[i])
{
cell = document.createElement("th");
cell.innerText = col;
row.appendChild(cell)
}
tbl.appendChild(row);
}
row = document.createElement("tr");
for (col in result.data[i])
{
cell = document.createElement("td");
cell.innerText = result.data[i][col];
row.appendChild(cell);
}
tbl.appendChild(row);
}
}
</script>
</head>
<body onload="init()">
<div id="buttonbar"><input type="button" value="Load data" onclick="getData();"
/></div>
<table id="resultsGrid">
</table>
<div id="statusbar"><span id="statustext"></span></div>
</body>
</html>
To check the results of the created application, open command prompt and move
to the application folder and type in the window:
Adl pcquest.xml
Finally one can easily package AIR application in just two steps, start with
creating certificate and key pair for application as shown here:
adt -certificate -cn SelfSigned 1024-RSA testCert.pfx
password
To create .air installer file, simply run following command, it will prompt
for password :
adt -package -storetype pkcs12 -keystore testCert.pfx
PCQUest.air pcquest.xml pcquest.html
Now one can distribute this application using PCQUest.air file. This shows
how web developers having SQL knowledge can use their SQL skills in easily
developing applications in AIR. Page(s) 1
|
Print
Comment
Email
Digg
Del.icio.us
Reddit
Twitter
