Using the local database with Adobe AIR

Posted on July 23, 2010 by Leonardo França 4 Comments

We often need our system can store data locally without the need for a robust database as a MySQL or SQL Server, or even that data can be stored locally for later synchronization with a remote database. Adobe AIR offers the possibility of working with database locally, your bank is based on the SQLite database, the more used to this kind of need to be very fast and easy to work with. Was added transaction support in Adobe AIR 2.0.
Let's do a simple example, start with the SQLConnection class, she will be responsible for connecting with our local bank, it is also defined that the database will be used only in memory or whether it created a local file (extension. db).

PLAIN TEXT
ACTIONSCRIPT3:
  1. conn = new SQLConnection();
  2. try
  3.             {
  4.                 conn.open(dbFile);//conn.open(null) passe null para o banco ser criado somente em memoria
  5.             }
  6.             catch(err:Error)
  7.             {
  8.                 trace(ObjectUtil.toString(err));
  9.                 return;
  10.             }

In this case, I am creating a local file to the database. dbfiles is an instance of the File class:

PLAIN TEXT
ACTIONSCRIPT3:
  1. dbFile = File.applicationStorageDirectory.resolvePath("dbFile.db");

Having created our database, we create our table for storing data. We will use the class SQLStatement.

PLAIN TEXT
ACTIONSCRIPT3:
  1. createStmt = new SQLStatement();
  2.             createStmt.sqlConnection = conn;

We will pass by the SQL string to create the table then to be performed by AIR:

PLAIN TEXT
ACTIONSCRIPT3:
  1. var sql:String = "";
  2.             sql += "CREATE TABLE IF NOT EXISTS contato (";
  3.             sql += "    id  INTEGER PRIMARY KEY AUTOINCREMENT,";
  4.             sql += "    nome    TEXT,";
  5.             sql += "    email   TEXT";
  6.             sql += ")";
  7. createStmt.text = sql;
  8. try
  9.             {
  10.                 createStmt.execute();
  11.             }
  12.             catch(error:SQLError)
  13.             {
  14.                 trace("CREATE TABLE error:", error);
  15.                 trace("error.message:", error.message);
  16.                 trace("error.details:", error.details);
  17.                 return void;
  18.             }

We have our database and tables created, let's create a form to enter some data in the database:

PLAIN TEXT
ACTIONSCRIPT3:
  1. insertStmt = new SQLStatement();
  2.             insertStmt.sqlConnection = conn;
  3.             var sql:String = "";
  4.             sql += "INSERT INTO contatos (nome, email) ";
  5.             sql += "VALUES ('"+txtNome.text+"', '"+txtEmail.text+"')";
  6.             insertStmt.text = sql;
  7.            
  8.             try
  9.             {
  10.                 insertStmt.execute();
  11.             }
  12.             catch (error:SQLError)
  13.             {
  14.                 trace("INSERT error:", error);
  15.                 trace("error.message:", error.message);
  16.                 trace("error.details:", error.details);
  17.                 return void;
  18.             }

Now just create the method to execute the select

PLAIN TEXT
ACTIONSCRIPT3:
  1. selectStmt = new SQLStatement();
  2.             selectStmt.sqlConnection = conn;
  3.             var sql:String = "SELECT id, nome, email FROM contatos";
  4.             selectStmt.text = sql;
  5.            
  6.             try
  7.             {
  8.                 selectStmt.execute();
  9.             }
  10.             catch (error:SQLError)
  11.             {
  12.                 trace("SELECT error:", error);
  13.                 trace("error.message:", error.message);
  14.                 trace("error.details:", error.details);
  15.                 return;
  16.             }
  17.            
  18.             var result:SQLResult = selectStmt.getResult();
  19.             dg.dataProvider = result.data;

Following is the complete code:

PLAIN TEXT
MXML:
  1. <?xml version="1.0" encoding="utf-8"?>
  2. <s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009"
  3.                        xmlns:s="library://ns.adobe.com/flex/spark"
  4.                        xmlns:mx="library://ns.adobe.com/flex/mx">
  5.     <fx:Script>
  6.         <![CDATA[
  7.             import flash.data.SQLConnection;
  8.             import flash.data.SQLResult;
  9.             import flash.data.SQLStatement;
  10.             import flash.errors.SQLError;
  11.             import flash.events.MouseEvent;
  12.             import flash.filesystem.File;
  13.            
  14.             import mx.utils.ObjectUtil;
  15.            
  16.             private var conn:SQLConnection;
  17.             private var dbFile:File;
  18.             private var createStmt:SQLStatement;
  19.             private var createStatusStmt:SQLStatement;
  20.             private var insertStmt:SQLStatement;
  21.             private var selectStmt:SQLStatement;
  22.            
  23.             override protected function childrenCreated():void
  24.             {
  25.                 super.childrenCreated();
  26.                
  27.                 dbFile = File.applicationStorageDirectory.resolvePath("dbContatos.db");
  28.                 conn = new SQLConnection();
  29.                
  30.                 try
  31.                 {
  32.                     conn.open(dbFile);
  33.                 }
  34.                 catch(err:Error)
  35.                 {
  36.                     trace(ObjectUtil.toString(err));
  37.                     return;
  38.                 }
  39.                
  40.                 createTable();
  41.                
  42.                 btnInsert.addEventListener(MouseEvent.CLICK,addData);
  43.             }
  44.            
  45.             private function createTable():void
  46.             {
  47.                 createStmt = new SQLStatement();
  48.                 createStmt.sqlConnection = conn;
  49.                
  50.                 var sql:String = "";
  51.                 sql += "CREATE TABLE IF NOT EXISTS contatos (";
  52.                 sql += "    id  INTEGER PRIMARY KEY AUTOINCREMENT,";
  53.                 sql += "    nome    TEXT,";
  54.                 sql += "    email   TEXT";
  55.                 sql += ")";
  56.                 createStmt.text = sql;
  57.                
  58.                 try
  59.                 {
  60.                     createStmt.execute();
  61.                 }
  62.                 catch(error:SQLError)
  63.                 {
  64.                     trace("CREATE TABLE error:", error);
  65.                     trace("error.message:", error.message);
  66.                     trace("error.details:", error.details);
  67.                     return void;
  68.                 }
  69.                
  70.                 getData();
  71.             }
  72.            
  73.             private function addData(evt:MouseEvent=null):void
  74.             {
  75.                 insertStmt = new SQLStatement();
  76.                 insertStmt.sqlConnection = conn;
  77.                 var sql:String = "";
  78.                 sql += "INSERT INTO contatos (nome, email) ";
  79.                 sql += "VALUES ('"+txtNome.text+"', '"+txtEmail.text+"')";
  80.                 insertStmt.text = sql;
  81.                
  82.                 try
  83.                 {
  84.                     insertStmt.execute();
  85.                 }
  86.                 catch (error:SQLError)
  87.                 {
  88.                     trace("INSERT error:", error);
  89.                     trace("error.message:", error.message);
  90.                     trace("error.details:", error.details);
  91.                     return void;
  92.                 }
  93.                 getData();
  94.             }
  95.            
  96.             private function getData():void
  97.             {
  98.                 selectStmt = new SQLStatement();
  99.                 selectStmt.sqlConnection = conn;
  100.                 var sql:String = "SELECT id, nome, email FROM contatos";
  101.                 selectStmt.text = sql;
  102.                
  103.                 try
  104.                 {
  105.                     selectStmt.execute();
  106.                 }
  107.                 catch (error:SQLError)
  108.                 {
  109.                     trace("SELECT error:", error);
  110.                     trace("error.message:", error.message);
  111.                     trace("error.details:", error.details);
  112.                     return;
  113.                 }
  114.                
  115.                 var result:SQLResult = selectStmt.getResult();
  116.                 dg.dataProvider = result.data;
  117.             }
  118.  
  119.         ]]>
  120.     </fx:Script>
  121.     <fx:Declarations>
  122.         <!-- Place non-visual elements (e.g., services, value objects) here -->
  123.     </fx:Declarations>
  124.     <s:Group width="100%" height="100%">
  125.         <mx:ViewStack x="0" y="68" id="viewstack1" width="100%" height="198" creationPolicy="all">
  126.             <s:NavigatorContent label="Registros" width="100%" height="100%">
  127.                 <mx:DataGrid id="dg" x="18" y="30" width="100%" height="100%">
  128.                     <mx:columns>
  129.                         <mx:DataGridColumn headerText="ID" width="25" dataField="id"/>
  130.                         <mx:DataGridColumn headerText="Nome" width="200" dataField="nome"/>
  131.                         <mx:DataGridColumn headerText="Email" width="200" dataField="email"/>
  132.                     </mx:columns>
  133.                 </mx:DataGrid>
  134.             </s:NavigatorContent>
  135.             <s:NavigatorContent label="Inserir Registro" width="100%" height="100%">
  136.                 <s:Label x="10" y="10" text="Inserir Registro"/>
  137.                 <mx:Form x="10" y="34" width="100%" height="100%">
  138.                     <mx:FormItem label="Nome:">
  139.                         <s:TextInput id="txtNome"/>
  140.                     </mx:FormItem>
  141.                     <mx:FormItem label="Email:">
  142.                         <s:TextInput id="txtEmail"/>
  143.                     </mx:FormItem>
  144.                     <mx:FormItem>
  145.                         <s:Button id="btnInsert" label="Inserir"/>
  146.                     </mx:FormItem>
  147.                 </mx:Form>
  148.             </s:NavigatorContent>
  149.         </mx:ViewStack>
  150.         <mx:LinkBar x="0" y="35" dataProvider="{viewstack1}">
  151.         </mx:LinkBar>
  152.     </s:Group>
  153. </s:WindowedApplication>

more:
Quick Start: Working asynchronously with a local SQL database (Flex)
Quick Start: Working asynchronously with a local SQL database (Flash)
Quick Start: Working asynchronously with a local SQL database (HTML)
Quick Start: Working synchronously with a local SQL database (Flex)
Quick Start: Working synchronously with a local SQL database (Flash)
Quick Start: Working synchronously with a local SQL database (HTML)

Books:
Adobe AIR Bible (Bible (Wiley))
Adobe AIR in Action

Related Articles

ActionScript 3.0, Adobe AIR, Flex , ,

4 comments

  1. Pingback: Flex learner | Blog | Leonardo França » Using the local database with Adobe AIR

  2. Pingback: Protect your computer and personal information by using an anonymous proxy server « e377

  3. Pingback: » Stress-Free Zen Cart Product Import – Know-How

  4. starfall
    August 23, 2011 at 8:14 pm

    Thanks for sharing this very clear tutorial off Adobe AIR I Learn so much on this.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>