Use Flex to connect to the SQLite database (1) in Adobe Air (create databases and tables, and synchronous and asynchronous execution modes)

Series article Navigation

1. Connect to SQLite Database (1) using Flex in Adobe Air (CREATE DATABASE and table)
2. Use Flex in Adobe air to connect to the SQLite database (2) (Add, delete, modify, and statement parameters)
3. Connect to SQLite database using Flex in Adobe Air (3) (query)
4. Connect to the SQLite database using Flex in Adobe Air (4) (transactional)
5. FLEX,FMS3 related articles Index
6. FMS3 and Flex create online multiplayer video conferencing and video chats (with original code)
7. Free Beauty video chat, multi-person video conferencing feature enhanced version (FMS3 and Flex development (with Source))

This chapter summarizes the relational database engine (SQLite), synchronous and asynchronous execution patterns, creating databases and tables

Adobe AIR includes a SQL-based relational database engine (SQLite) that runs at run time and is stored locally in a database file on the computer where the AIR application is running (for example, on a computer's hard disk drive). Because the database is running and the data files are stored locally, the AIR application can use the database regardless of whether the network connection is available.
A single Adobe AIR local SQL database is stored as a single file in the computer's file system. The runtime includes the SQL database engine, which manages the creation and structuring of database files, as well as manipulating and retrieving data from database files. The runtime does not specify how or where database data is stored on the file system, whereas each database is completely stored in a single file. You specify the location where the database files are stored in the file system. A single AIR application can access one or more separate databases (that is, separate database files). Because the runtime stores each database on the file system as a single file, you can find your database as needed, based on the design of your application and the file access constraints of your operating system. Each user can have a separate database file for their specific data, or the database file can be accessed by all application users who share data on a single computer. Because the data is local to a single computer, data is not automatically shared between users on different computers. The local SQL database engine does not provide any functionality to execute SQL statements against a remote database or server-based database.

About SQLite
About the features of SQLite:
1. Acid Transaction
2.0 Configuration – No installation and management configuration required
3. A complete database stored in a single disk file
4. Database files can be freely shared between machines of different byte order
5. Support database size up to 2TB
6. Small enough, roughly 30,000 lines C code, 250K
7. Faster than some popular databases in most common database operations
8. Simple, Easy API
9. Include TCL bindings while supporting other language bindings via wrapper
10. Good annotated source code, and has more than 90% test coverage
11. Independence: No additional reliance
Source completely open, you can use it for any purpose, including selling it
13. Support multiple development languages, C, PHP, Perl, Java, Asp.net,python

For more information, refer to the following sites:
http://www.sqlite.org/
http://www.sqlite.com.cn/

Purpose of local SQL database
The AIR local SQL database feature can be used to store application data on the user's local computer for any purpose. Adobe AIR includes several mechanisms for storing data locally, with different advantages for each mechanism. The following are some possible uses of local SQL databases in AIR applications:
1. For data-oriented applications such as address books, the database can be used to store the master application data.
2. For document-oriented applications where users create documents to save and possibly share, you can save each document as a database file in a user-specified location. (However, note that any AIR application can open the database file, so a separate encryption mechanism is recommended for potentially sensitive documents.) ）
3. For network-enabled applications, the database can be used to store the local cache of application data or to temporarily store data when a network connection is unavailable. You can create a mechanism to synchronize a local database with a network data store.
4. For any application, the database can be used to store an individual user's application settings, such as user options or application information such as window size and location.

About synchronous and asynchronous execution modes
1. When writing code to handle a local SQL database, it is specified to perform database operations in one of two execution modes: asynchronous or synchronous execution mode. Typically, the code example shows how to perform each operation in both ways, so that you can use the example that best suits your needs.
2. In asynchronous execution mode, a directive is provided to the runtime, which dispatches the event when the requested operation completes or fails. First, inform the database engine to perform the operation. The database engine works in the background while the application continues to run. Finally, when the operation is completed (or it fails), the database engine dispatches the event. The code that is triggered by the event performs subsequent operations. This approach has an important advantage: the runtime performs database operations in the background while the main application code continues to execute. If the database operation takes a significant amount of time, the application continues to run. Most importantly, users can continue interacting with them, and the screen will not freeze. However, writing asynchronous operation code can be more complex than other code. This complexity typically occurs when multiple related operations must be assigned to individual event listener methods.
3. Conceptually, it is simpler to encode an operation as a single step sequence (a set of synchronous operations, rather than a set of operations in several event listener methods). In addition to asynchronous database operations, Adobe AIR allows you to perform database operations synchronously. In synchronous execution mode, the operation does not run in the background. Instead, they run in the same execution sequence as all other application code. Notifies the database engine to perform an action. Then, the code pauses while the database engine is working. After you complete the operation, continue with the next line of code.
4. Asynchronous or synchronous execution is set at the SQLConnection level. With a single database connection, some operations or statements cannot be executed synchronously, while other operations or statements are executed asynchronously. By calling the SQLConnection method to open the database, you can specify whether SQLConnection is operating in synchronous or asynchronous execution mode. If Sqlconnection.open () is called, the connection operates in synchronous execution mode, and if Sqlconnection.openasync () is called, the connection operates in asynchronous execution mode. After the SQLConnection instance is connected to the database using open () or OpenAsync (), the instance is pinned to synchronous or asynchronous execution mode unless the connection to the database is closed and reopened.

Create a database
To create a database file, you first create a SQLConnection instance. Call its open () method to turn it on in synchronous execution mode, or call its OpenAsync () method to open it in asynchronous execution mode. The open () and OpenAsync () methods are used to open a connection to the database. If the file instance that is passed references the reference parameter (the first parameter) that does not exist, the open () or OpenAsync () method creates a database file at that file location and opens a connection to the newly created database.
Whether you call the open () method or the OpenAsync () method to create the database, the name of the database file can be any valid file name with any file extension. If you call the open () or OpenAsync () method with the reference parameter null, the new in-memory database will be created instead of the database file created on disk.

The following code listing illustrates the process of creating a database file (a new database) using asynchronous execution mode, where the database file is stored in the application's storage directory with the file name "Mytestdb.db":
Import Mx.controls.Alert;

private Var con:sqlconnection;

Private Function Initapp (): void
{
var file:file = File.applicationStorageDirectory.resolvePath ("mytestdb.db")

con = new SQLConnection ();
Dispatched when the OpenAsync () method invocation operation completes successfully
Con.addeventlistener (Sqlevent.open,openhandler);
Dispatch when an asynchronous operation of an SQLConnection object causes an error
Con.addeventlistener (Sqlerrorevent.error,errorhandler);

Con.openasync (file);
}

Private Function Openhandler (evt:sqlevent): void
{
Alert.show ("successful completion");
}

Private Function ErrorHandler (evt:sqlerrorevent): void
{
Alert.show ("failure");
Alert.show (Evt.error.message);
Alert.show (evt.error.details);
}

To perform the operation synchronously, call the open () method when you open the database connection by using an SQLConnection instance. The following code shows how to create and open a SQLConnection instance that synchronizes its operations:
Import Mx.controls.Alert;

private Var con:sqlconnection;

Private Function Initapp (): void
{
var file:file = File.applicationStorageDirectory.resolvePath ("mytestdb.db")

con = new SQLConnection ();

Try
{
Con.open (file);
Alert.show ("successful completion");
}
catch (Error:sqlerror)
{
Alert.show (Error.message);
Alert.show (error.details);
}
}

Create a database table
The following uses asynchronous execution mode to create a table named "EMP" in an existing database file.
Import Mx.controls.Alert;

private Var con:sqlconnection;
private Var createstmt:sqlstatement;

Private Function Initapp (): void
{
var file:file = File.applicationStorageDirectory.resolvePath ("mytestdb.db")

con = new SQLConnection ();
Dispatched when the OpenAsync () method invocation operation completes successfully
Con.addeventlistener (Sqlevent.open,openhandler);
Dispatch when an asynchronous operation of an SQLConnection object causes an error
Con.addeventlistener (Sqlerrorevent.error,errorhandler);

Con.openasync (file);
}

Private Function Createresult (event:sqlevent): void
{
Alert.show ("Table creation");
}

Private Function Createerror (event:sqlerrorevent): void
{
Alert.show ("Error message:", event.error.message);
Alert.show ("Details:", event.error.details);
}

Private Function Openhandler (evt:sqlevent): void
{
 alert.show ("completed successfully");
 
 createstmt = new SQLStatement ();  
 createstmt.sqlconnection = con; 
 var Sql:string =  
    "CREATE TABLE IF not EXISTS emp (" +  
    "& nbsp;   empId INTEGER PRIMARY KEY autoincrement, "+  
   "     FirstName text, "+  
   "     lastName text, "+  
         Salary NUMERIC CHECK (Salary > 0) +  
    ")"; 
    
 createstmt.text = SQL;
 createstmt.addeventlistener (Sqlevent.result, createresult);  
 createstmt.addeventlistener ( Sqlerrorevent.error, createerror);  
 createstmt.execute ();   
}

Private Function ErrorHandler (evt:sqlerrorevent): void
{
Alert.show ("failure");
Alert.show (Evt.error.message);
Alert.show (evt.error.details);
}

The following code uses synchronous execution mode to create a table named "EMP" in an existing database file
Import Mx.controls.Alert;

private Var con:sqlconnection;
private Var createstmt:sqlstatement;

Private Function Initapp (): void
{
var file:file = File.applicationStorageDirectory.resolvePath ("mytestdb.db")

con = new SQLConnection ();
createstmt = new SQLStatement ();

Try
{
Con.open (file);


Createstmt.sqlconnection = con;
var sql:string =
"CREATE TABLE IF not EXISTS emp (" +
"EmpId INTEGER PRIMARY KEY autoincrement," +
"FirstName TEXT," +
"LastName TEXT," +
"Salary NUMERIC CHECK (Salary > 0)" +
")";

Createstmt.text = SQL;
Createstmt.execute ();

Alert.show ("successful completion");
}
catch (Error:sqlerror)
{
Alert.show (Error.message);
Alert.show (error.details);
}
}

Code Download:
Http://files.cnblogs.com/aierong/Air_Test_SQLite.rar

Use Flex to connect to the SQLite database (1) in Adobe Air (create databases and tables, and synchronous and asynchronous execution modes)