SQLite Example for Adobe AIR–Working with local SQL databases(with source code)

Windows-Table-256x256 Adobe AIR includes the capability of creating and working with local SQL databases. Many stand SQL features are supported in the runtime, open source SQLite system can be used for storing local, persistent data.

The flollowing is a simplistic example that create a sqlite database, add, get, update and remove records from the “user” table.

Notice:
You might have wondered about this line:

sqlConnection.openAsync(dbFile);

.Asychnronous means that your code will have an event listener on the SQLConnection and an event handler for the response.

.Synchronous means that your application will make an “inline” call to SQLite where it performs the operation and then moves on as if it were any other line of actionscript code.This tutorial is using the asynchronous method. :)

<?xml version=”1.0″ encoding=”utf-8″?>
<mx:WindowedApplication xmlns:mx=”http://www.adobe.com/2006/mxml” layout=”absolute”
        preinitialize=”openDatabaseConnection()”
        fontSize=”12″ backgroundColor=”#FFFFFF” width=”600″ height=”700″>
        <mx:Script>
                <![CDATA[
                        import flash.data.SQLConnection;
                        import flash.events.SQLErrorEvent;
                        import flash.events.SQLEvent;
                        import flash.filesystem.File;
                        private var conn:SQLConnection;
                        private var initComplete:Boolean = false;
                        private var sqlStat:SQLStatement;

                        public function openDatabaseConnection():void{

                        // create new sqlConnection
                        sqlConnection = new SQLConnection();
                        sqlConnection.addEventListener(SQLEvent.OPEN, onDatabaseOpen);
                        sqlConnection.addEventListener(SQLErrorEvent.ERROR, errorHandler);

                        // get currently dir
                        var dbFile:File = File.applicationStorageDirectory.resolvePath("sampleDB.db");

                        // open database,If the file doesn't exist yet, it will be created
                        sqlConnection.openAsync(dbFile);
                        }

                        // connect and init database/table
                        private function onDatabaseOpen(event:SQLEvent):void
                        {
                                // init sqlStatement object
                            sqlStat = new SQLStatement();
                                sqlStat.sqlConnection = conn;
                                var sql:String =         "CREATE TABLE IF NOT EXISTS user (" +
                                                                    "    id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                                                    "    name TEXT, " +
                                                                    "    password TEXT" +
                                                                    ")";
                                sqlStat.text = sql;
                                sqlStat.addEventListener(SQLEvent.RESULT, statResult);
                                sqlStat.addEventListener(SQLErrorEvent.ERROR, createError);
                                sqlStat.execute();
                        }
                        private function statResult(event:SQLEvent):void
                        {
                                // refresh data
                            var sqlresult:SQLResult = sqlStat.getResult();
                                if(sqlresult.data == null){
                                        getResult();
                                        return;
                                }
                                datafiled.dataProvider = sqlresult.data;
                        }
                        // get data
                        private function getResult():void{
                                var sqlquery:String = "SELECT * FROM user"
                                excuseUpdate(sqlquery);
                        }
                        private function createError(event:SQLErrorEvent):void
                        {
                            trace("Error code:", event.error.code);
                            trace("Details:", event.error.message);
                        }
                        private function errorHandler(event:SQLErrorEvent):void
                        {
                            trace("Error code:", event.error.code);
                            trace("Details:", event.error.message);
                        }
                        // update
                        private function excuseUpdate(sql:String):void{
                                sqlStat.text = sql;
                                sqlStat.execute();
                        }
                        // insert
                        private function insertemp():void{
                                var sqlupdate:String = "Insert into user(id,name,password) values('" +
                                                name.text +
                                                "','" +
                                                password.text  +
                                                "')";
                        debug.text+=sqlupdate+"\n"
                                excuseUpdate(sqlupdate)
                        }
                        // delete
                        private function deleteemp():void{
                                var sqldelete:String = "delete from user where id='" +
                                                datafiled.selectedItem.id +
                                                "'";
                                excuseUpdate(sqldelete);
                                debug.text+=sqldelete+"\n"
                        }
                ]]>
        </mx:Script>
        <mx:TextArea x=”21″ y=”10″ width=”402″ height=”179″ id=”debug”/>
        <mx:DataGrid x=”21″ y=”197″ id=”datafiled”>
                <mx:columns>
                        <mx:DataGridColumn headerText=”ID” dataField=”id”/>
                        <mx:DataGridColumn headerText=”name” dataField=”name”/>
                        <mx:DataGridColumn headerText=”password” dataField=”password”/>
                </mx:columns>
        </mx:DataGrid>
        <mx:Form x=”21″ y=”471″>
                <mx:FormItem label=”name”>
                        <mx:TextInput id=”name”/>
                </mx:FormItem>
                <mx:FormItem label=”password”>
                        <mx:TextInput id=”password”/>
                </mx:FormItem>
        </mx:Form>
        <mx:Button x=”300″ y=”503″ label=”add” click=”insertemp()”/>
        <mx:Button x=”300″ y=”533″ label=”delete” click=”deleteemp()”/>
</mx:WindowedApplication>
Share and Enjoy:
  • Digg
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • DZone
  • Reddit
  • Technorati
  • StumbleUpon
  • Twitter
RSS Enjoy this Post? Subscribe to Ntt.cc

RSS Feed   RSS Feed     Email Feed  Email Feed Follow us Follow us
You can leave a response, or trackback from your own site.

24 Responses to “SQLite Example for Adobe AIR–Working with local SQL databases(with source code)”

  1. pault107 says:

    I’ve been looking for something like this. Thank you – I’ll give this a go soon.

  2. Barry says:

    I havent tried the code yet but looking at it shouldnt

    private function openHandler(event:SQLEvent) be private function onDatabaseOpen(event:SQLEvent)

    or

    sqlConnection.addEventListener(SQLEvent.OPEN, onDatabaseOpen); be sqlConnection.addEventListener(SQLEvent.OPEN, openHandler);

    ?

    Good job though and very much appreciated.

  3. minidxer says:

    a stupid copy mistake when i actived the plugin and re-edited it for source code highlight , thanks, Barry. :)

  4. Barry says:

    No problem just hought id mention it as its a great example. Something ive been looking for, for a while.

  5. [...] SQLite Example for Adobe AIR–Working with local SQL databases(with source code) – Ntt.cc – Adobe AIR includes the capability of creating and working with local SQL databases. Many stand SQL features are supported in the runtime, open source SQLite system can be used for storing local, persistent data. [...]

  6. Henrik says:

    Well, I am copying this code inside flex. But I get errors.

    Do I need to install something SQLite or ? And how does it work?

  7. Earnestine says:

    yep you are jack our maniac in my chat box and your mental here,

  8. kamui says:

    Hello,

    I`ve got a problem with the sqlStat.getResult() in the statResult-method!

    this error occurs:

    “TypeError: Error #1009: Cannot access a property or method of a null object reference.”

    What can i do to fix it?

    Thanks from Germany,
    kamui

  9. Simon says:

    Hi,
    Im getting the following error message, any thoughts please guys?
    Error #3109: Operation is not permitted when the SQLStatement.sqlConnection property is not set.

    any help is appreciated.

    Cheers

    Simon

  10. Melyssa says:

    This is good news, I

  11. amar says:

    its totally a crap peice of code .wasted a lot of time . its not workign at all .lots of bugs .

  12. burgen says:

    I really liked the way they came off

  13. Sam says:

    Hi,

    It worked for me – but had to tweak a lot,
    like you declared the connection variable as “conn” but in the preintialize handler used “sqlConnection”.
    Also the code highlighter has messed up with the formatting.
    And then in the insert statement, you can give only two coumns – INSERT INTO user (name, password) … as the id is Autoincrement.
    But still it is good article.

  14. darida says:

    code without bugs :P

  15. edster says:

    Hello every one, nice post.

    Is it possible for one to create “user-defined functions” in the AIR runtime Embedded Database system ( SQLlite so far )? If yes… How?

  16. nancy_botwin says:

    Quick question:

    If private var conn is the SQLConnection then how come you say sqlConnection = new SQLConnection() in openDatabaseConnection function? where is var sqlConnection declared?

    Sorry if Im missing something, Im new to this.

  17. amar says:

    hello nancy,

    you are right . instead of using conn:SQLConnection he used sqlConnection and while assigning he assigned sqlStat.sqlConnection = conn;
    it should be sqlStat.sqlConnection = sqlConnection;

  18. toto says:

    This script is working but I add some field so delete them and it will be good

    public var sqlConnection:SQLConnection;
    private var sqlStat:SQLStatement;

    public function openDataBaseConnection():void{
    // create new sqlConnection
    sqlConnection = new SQLConnection();
    sqlConnection.addEventListener(SQLEvent.OPEN, onDataBaseOpen);
    sqlConnection.addEventListener(SQLErrorEvent.ERROR, errorHandler);

    // get currently dir
    var dbFile:File = File.applicationStorageDirectory.resolvePath(“bddtest.db”);

    // open database,If the file doesn’t exist yet, it will be created
    sqlConnection.openAsync(dbFile);
    }

    // connect and init database/table
    public function onDataBaseOpen(e:Event):void{
    // init sqlStatement object
    sqlStat = new SQLStatement();
    sqlStat.sqlConnection = sqlConnection;

    var sql:String = “CREATE TABLE IF NOT EXISTS clients(“+
    ” idclient INTEGER PRIMARY KEY AUTOINCREMENT,”+
    ” nom TEXT,”+
    ” prenom TEXT,”+
    ” adresse TEXT,”+
    ” tel TEXT,”+
    ” fax TEXT,”+
    ” mail TEXT,”+
    ” infoPrivees TEXT,”+
    ” codeClient TEXT”+
    “)”;
    sqlStat.text = sql;
    sqlStat.addEventListener(SQLEvent.RESULT, statResult);
    sqlStat.addEventListener(SQLErrorEvent.ERROR, createError);
    sqlStat.execute();

    }
    private function statResult(event:SQLEvent):void{
    //refresh data
    var sqlresult:SQLResult = sqlStat.getResult();
    if(sqlresult.data == null){
    getResult();
    return;
    }
    datafiled.dataProvider = sqlresult.data;
    trace(sqlresult.data);
    }

    // get data
    private function getResult():void{
    var sqlquery:String = “SELECT * FROM ‘clients’”;
    excuseUpdate(sqlquery);
    }
    private function errorHandler(event:SQLErrorEvent):void{
    //trace(“Error code : “+event.error.code);
    trace(“Details : “+event.error.message);
    }
    private function createError(event:SQLErrorEvent):void{
    trace(“Error code : “+event.error.details);
    trace(“Details : “+event.error.message);
    }
    //update
    private function excuseUpdate(sql:String):void{
    sqlStat.text = sql;
    sqlStat.execute();
    //
    }
    //insert
    private function insertemp():void{
    var sqlupdate:String = “Insert into clients(nom,prenom,adresse,tel,fax,mail,infoprivees)values(“+
    “‘”+nom.text+”‘”+
    “,”+
    “‘”+prenom.text+”‘”+
    “,”+
    “‘”+adresse.text+”‘”+
    “,”+
    “‘”+tel.text+”‘”+
    “,”+
    “‘”+fax.text+”‘”+
    “,”+
    “‘”+mail.text+”‘”+
    “,”+
    “‘”+infoprivees.text+”‘)”;
    debug.text += sqlupdate+”\n”;
    excuseUpdate(sqlupdate);
    }
    //delete
    private function deleteemp():void{
    var sqldelete:String = “delete from client where id=”+
    “‘”+datafiled.selectedItem.id+”‘”;
    excuseUpdate(sqldelete);
    debug.text+=sqldelete+”\n”;
    }

  19. serge says:

    Hi,

    @amar : I find it’s really stupid beeing so negative, it’s unefficient and injurious
    - The bugs were not numerous,
    - The bugs were really easy to correct.

    If you were so efficient :
    - you would rapidly have found them,
    - you would post, like toto, some correct sample.
    But I’m sure on your website, there are lots of high-quality samples !!!!

    Thanks to the author for this post (and also to toto)

    - A small suggestion :
    May be, it would have been preferable to advertise the user when the database is created (and ask for confirm), not to create it silently.
    Why ?
    - When the user launch the app for the first time, it would not be surprised
    - If he gets the advertising an other time, it woud be informed that a problem has occured

    (sorry, my english is really poor)

  20. Wenderson says:

    It has an interesting story about this, in this site http://migre.me/9FGUI , I found this site an AIR API that works in SQL Server.

  21. Anas says:

    Hi,
    I am working in a sqlite+flex project using as3. I was wondering if there is any way to avoid “Data Type Mismatch Error” automatically ? I can always check for the valid data type. But i would prefer using some library or pre-built validation library so that it saves me time. Thanks in advance for any kind of help/suggestion.

Leave a Reply