|
|
Adobe AIR includes an embedded SQLite database that AIR applications can leverage. SQLite is a compact open source database that supports ACID transactions, requires zero configuration, implements most of SQL92, and supports strings and BLOBs up to 2 GB in size. All database information is stored in a single file on disk, which you can freely share between machines, even if they have different byte orders.
NOTE
You can find more information about SQLite on the project web site, at http://www.sqlite.org.
Adobe AIR supports both synchronous and asynchronous database transactions. A synchronous transaction will block additional user interface interaction until the transaction has been completed, but can be substantially less effort to code. An asynchronous approach will allow additional interaction with the user interface while the transaction is processing, though it may require a substantial amount of code for event handlers. All of the following examples showcase an asynchronous approach.
You need to connect to a local database prior to working with the schema or altering data.
You can create and connect to a database using the single SQLConnection.open() method.
SQLite stores all database information in a single file on disk. This means that before an application can access a database, it must first have a reference to the file. A single application might choose to access any number of database files. Databases are managed through the SQLConnection data type.
You can obtain a reference to the database file through the File.resolvePath() method, which takes a single argument: the name of the file that will be referenced. Files that do not yet exist can have a reference, and the File.exists property returns a Boolean to determine that file's presence on disk:
var db = new air.SQLConnection(); var file = air.File.applicationStorageDirectory. resolvePath( 'mycrm.db' );
The extension to the database file is not specific and can be named as necessary for the application.
To operate using asynchronous database transactions, an application must first create and register a handler for the events in which it is interested. In the case of establishing a connection to a database, the SQLEvent.OPEN event will be monitored. Among various other properties, you can use the SQLEvent.type property to determine the status of the database.
db.addEventListener( air.SQLEvent.OPEN, doDbOpen );
function doDbOpen( event )
{
alert('Connected' );
}
The SQLConnection.open() method can take a number of different arguments. The most common arguments are the file reference to the database, and a String value indicating the mode in which the database should be opened. The default value of SQLMode.CREATE will create the database if it does not exist, and then will establish a connection to the database.
<html>
<head>
<title>Connecting to a Database</title>
<script type="text/javascript" src="airaliases.js"></script>
<script>
var db = new air.SQLConnection();
function doDbOpen( event )
{
alert( 'You are now connected to the database.' );
}
function doLoad()
{
var file = air.File.applicationDirectory.resolvePath(
'crm.db' );
db.addEventListener( air.SQLEvent.OPEN, doDbOpen );
db.open( file, air.SQLMode.READ );
}
</script>
</head>
<body onLoad="doLoad();">
</body>
</html>
An application has a specific schema it needs to provide for data storage.
You can create a database schema using the SQLStatement class, using SQL92 grammar.
Once a database file has been created and a connection to the database has been established, the next likely step will be to create any required schema. You can do this using SQL92 in conjunction with the SQLStatement class. The SQLStatement class executes commands against a specified database.
Using an asynchronous approach, the best place to check for any required schema—or to create it—is in the handler for the SQLEvent.OPEN event. At this point, the application can be assured a connection against which statements can be executed. Along the same lines, event handlers must also be registered on the SQLStatement instance:
var stmt = new air.SQLStatement(); stmt.addEventListener( air.SQLErrorEvent.ERROR, doStmtError ); stmt.addEventListener( air.SQLEvent.RESULT, doStmtResult );
When applied to a SQLStatement object, the SQLErrorEvent.ERROR event is called when an error has occurred while executing a SQLStatement.next() or SQLStatement.execute() method. Conversely, the SQLEvent.RESULT event is called when results are returned from the database. This usually indicates a successful execution:
function doStmtError( event )
{
alert( 'There has been a problem executing
the statement.' );
}
function doStmtResult( event )
{
alert( 'The database table has
been created.' );
}
To execute a SQL statement, a SQLConnection instance against which to execute must be established. You can assign a SQLConnection instance to the SQLStatement.sqlConnection property. The SQLStatement.text property is then assigned any SQL that needs to be executed. Finally, the SQLStatement.execute() method is called:
stmt.sqlConnection = db;
stmt.text = 'CREATE TABLE IF NOT EXISTS contact ( ' +
'id INTEGER PRIMARY KEY AUTOINCREMENT, ' +
'first TEXT, ' +
'last TEXT )';
stmt.execute();
In this case, a CREATE TABLE statement has been applied to the database. Additional types of SQL statements, such as SELECT, INSERT, UPDATE, and DELETE, are executed in the same manner. The SQLStatement.execute() method can take two optional arguments: the number of rows to prefetch, and a responder object to handle events.
The prefetch option defaults to -1, which indicates that all rows should be returned. The responder object can be a custom object designed to handle any status or result events that take place during execution. The default responder is null in this case, as event handlers have been registered with the SQLStatement object directly:
<html>
<head>
<title>Creating Database Tables</title>
<script type="text/javascript" src="airaliases.js"></script>
<script>
var db = null;
var stmt = null
function doDbOpen( event )
{
stmt = new air.SQLStatement();
stmt.addEventListener( air.SQLErrorEvent.ERROR,
doStmtError );
stmt.addEventListener( air.SQLEvent.RESULT, doStmtResult );
stmt.sqlConnection = db;
stmt.text = 'CREATE TABLE IF NOT EXISTS contact ( ' +
'id INTEGER PRIMARY KEY
AUTOINCREMENT, ' +
'first TEXT, ' +
'last TEXT )';
stmt.execute();
}
function doLoad()
{
var file = air.File.applicationDirectory.resolvePath(
'crm.db' );
db = new air.SQLConnection();
db.addEventListener( air.SQLEvent.OPEN, doDbOpen );
db.open( file, air.SQLMode.CREATE );
}
function doStmtResult( event )
{
alert( 'The database table has been created.' );
}
function doStmtError( event )
{
alert( 'There has been a problem executing
a statement:\n' + event.error.message );
}
</script>
</head>
<body onLoad="doLoad();">
</body>
</html>
An application needs to store user-provided data in a relational database on disk.
SQL92 INSERT statements can be created and executed using the SQLStatement class.
Given a valid database file with the appropriate schema created, SQL92 statements can be executed using the SQLStatement object. The same SQLStatement object can be reused to execute multiple statements. When reusing the same SQLStatement object, it is important to differentiate what type of statement has just been executed. You can listen for the different actions in various ways.
function doSave()
{
var first = document.getElementById( 'txtFirst' ).value;
var last = document.getElementById( 'txtLast' ).value;
stmt.text = 'INSERT INTO contact VALUES ( ' +
'NULL, ' +
'\'' + first + '\', ' +
'\'' + last + '\' ) ';
stmt.execute();
}
One approach is to assign different event handlers for the different statements that will be executed. (Do not forget to remove the old handlers.) Another approach is to specify different responder objects that have been created with the specific statement in mind. The approach used in this example is a basic state machine that tracks what type of statement has just been executed:
var NONE = - 1;
var CREATE_SCHEMA = 0;
var INSERT_DATA = 1;
var state = NONE;
var stmt = new air.SQLStatement();
// Other database creation and configuration
function doSave()
{
var first = document.getElementById( 'txtFirst' ).value;
var last = document.getElementById( 'txtLast' ).value;
stmt.text = 'INSERT INTO contact VALUES ( ' +
'NULL, ' +
'\' + first + '\', ' +
'\'' + last + '\' )';
// Track state
state = INSERT_DATA;
stmt.execute();
}
After successfully executing a database statement, the SQLResultEvent.RESULT event will be triggered. If an error occurs, the SQLStatusEvent.STATUS event will be raised. By tracking the state, the method designed to handle the result can determine the appropriate action(s) to take. In the case of inserting new data, this may be user notification and updating of the user interface:
<html>
<head>
<title>Storing Data in a Database</title>
<style type="text/css">
body {
font-family: Verdana, Helvetica, Arial, sans-serif;
font-size: 11px;
color: #0B333C;
}
</style>
<script type="text/javascript" src="airaliases.js"></script>
<script type="text/javascript">
var db = null;
var stmt = null
var NONE = -1;
var CREATE_SCHEMA = 0;
var INSERT_DATA = 1;
var state = NONE;
function doDbOpen( event )
{
stmt = new air.SQLStatement();
stmt.addEventListener( air.SQLErrorEvent.ERROR,
doStmtError );
stmt.addEventListener( air.SQLEvent.RESULT,
doStmtResult );
stmt.sqlConnection = db;
stmt.text = 'CREATE TABLE IF NOT EXISTS contact ( ' +
'id INTEGER PRIMARY KEY
AUTOINCREMENT, ' +
'first TEXT, ' +
'last TEXT )';
state = CREATE_SCHEMA;
stmt.execute();
}
function doLoad()
{
var file = air.File.applicationDirectory.resolvePath(
'crm.db' );
db = new air.SQLConnection();
db.addEventListener( air.SQLEvent.OPEN, doDbOpen );
db.open( file, air.SQLMode.CREATE );
document.getElementById( 'btnSave' ).addEventListener(
'click', doSave );
}
function doSave()
{
var first = document.getElementById( 'txtFirst' ).value;
var last = document.getElementById( 'txtLast' ).value;
stmt.text = 'INSERT INTO contact VALUES ( ' +
'NULL, ' +
'\'' + first + '\', ' +
'\'' + last + '\' )';
state = INSERT_DATA;
stmt.execute();
}
function doStmtResult( event )
{
switch( state )
{
case CREATE_SCHEMA:
alert( 'The database table has been created.' );
state = NONE;
break;
case INSERT_DATA:
document.getElementById( 'txtFirst' ).value = '';
document.getElementById( 'txtLast' ).value = '';
alert( 'A new record has been stored.' );
}
}
function doStmtError( event )
{
alert( 'There has been a problem executing a
statement:\n' + event.error.message );
}
</script>
</head>
<body onLoad="doLoad();">
<div>
First name: <input id="txtFirst" type="text" />
</div>
<div>
Last name: <input id="txtLast" type="text" />
</div>
<div>
<input id="btnSave" type="button" value="Save" />
</div>
</body>
</html>
You need to generate a tabular display of data from the embedded database.
Database data can be queried using SQL92 and the SQLStatement class.
You can run traditional SELECT statements using a SQLStatement object that has been referenced against an existing database. A successful execution of the SELECT statement invokes the registered SQLResultEvent.RESULT event handler. That event handler will get a SQLResultEvent object which contains the result data:
function doStmtResult( event )
{
var elem = null;
var results = stmt.getResult();
if( results.data != null )
{
for( var c = 0; c < results.data.length; c++ )
{
elem = document.createElement( 'div' );
elem.innerText = results.data[c].first + ' '
+ results.data[c].last;
document.body.appendChild( elem );
}
}
}
NOTE
This snippet forgoes much of the state management, event registration, and database connectivity covered in other sections. Please review that content, or the example at the end of this section, for complete coverage of the topic.
To get any result data, SQLStatement.getResult() is called, which returns a SQLResult object. The SQLResult.data property is an Array of the results, if any. SQLResult.data Array will contain Object instances whose properties match the names of the columns used in the query. This Array can be used to iterate over the results of a query.
If the database table that is being queried has no data, or the statement did not return any data, the SQLResult.data property will be null:
<html>
<head>
<title>Accessing Data in a Database</title>
<style type="text/css">
body {
font-family: Verdana, Helvetica, Arial, sans-serif;
font-size: 11px;
color: #0B333C;
}
</style>
<script type="text/javascript" src="airaliases.js"></script>
<script type="text/javascript">
var db = null;
var stmt = null
var NONE = -1;
var CREATE_SCHEMA = 0;
var SELECT_DATA = 1;
var state = NONE;
function doDbOpen( event )
{
stmt = new air.SQLStatement();
stmt.addEventListener( air.SQLErrorEvent.ERROR,
doStmtError );
stmt.addEventListener( air.SQLEvent.RESULT, doStmtResult );
stmt.sqlConnection = db;
stmt.text = 'CREATE TABLE IF NOT EXISTS contact ( ' +
'id INTEGER PRIMARY KEY
AUTOINCREMENT, ' +
'first TEXT, ' +
'last TEXT )';
state = CREATE_SCHEMA;
stmt.execute();
}
function doLoad()
{
var file = air.File.applicationDirectory.resolvePath(
'crm.db' );
db = new air.SQLConnection();
db.addEventListener( air.SQLEvent.OPEN, doDbOpen );
db.open( file, air.SQLMode.CREATE );
}
function doStmtResult( event )
{
var elem = null;
var result = null;
switch( state )
{
case CREATE_SCHEMA:
stmt.text = 'SELECT * FROM contact';
state = SELECT_DATA;
stmt.execute();
break;
case SELECT_DATA:
result = stmt.getResult();
if( result.data != null )
{
for( var c = 0; c < result.data.length;
c++ )
{
elem = document.createElement( 'div' );
elem.innerText = result.data[c].first +
' ' + result.data[c].last;
document.body.appendChild( elem );
}
}
state = NONE;
break;
default:
state = NONE;
break;
}
}
function doStmtError( event )
{
alert( 'There has been a problem executing
a statement:\n' + event.error.message );
}
</script>
</head>
<body onLoad="doLoad();">
</body>
</html>
|
|