BTA Flexing

My passion RIAs…………..Shardul Singh Bartwal

Adobe AIR and SQLite Connectivity

Posted by Shardul Singh Bartwal on April 14, 2008

Adobe AIR and SQLite Connectivity

 ===========================

  Abode AIR provides very easy mechanism to connect to the SQLite.I am doing the connectivity by coding. Here nothing different just create the connection and create the SQLConnection,open it, create SQLStatement and execute them, close connection.Upto my experience I think this is the same common way which we used in any database connectivity. So there is nothing special. I found this process very cool especially due the portable size of the SQLite and creating and deleting the database in just one mouse click. Apart form this you don’t require any thing else Flex Builder IDE with AIR. There is no software requirement for the SQLite.

 I am just putting some line of code for your better understanding. You can just copy and paste it. Although Its just a small code but hope it will give you the over all idea about dealing with the database in an AIR application.

 

*************************************************************

<?xml version=”1.0″ encoding=”utf-8″?>

<mx:WindowedApplication xmlns:mx=”http://www.adobe.com/2006/mxml

layout=”vertical” creationComplete=”init()”

width=”500” height=”400” backgroundColor=”#000000>

<mx:Script>

 

<![CDATA[

      import mx.controls.Alert;

      import mx.collections.ArrayCollection;

      import flash.data.*;

     

      private var ob:Array=new Array();

      public var mysqlConnection:SQLConnection;

      public var _createProdct:SQLStatement;

      public var _insertProduct:SQLStatement;

      public var _selectProduct:SQLStatement;

 

      private const _counterProduct:int = 1;

      private var databaseFile:File =

      File.applicationStorageDirectory.resolvePath(“data.db”);

      private const CREATE_PRODUCT_TABLE:String =

      “CREATE TABLE product (id INTEGER PRIMARY KEY,name TEXT,” +

      “department TEXT, title TEXT, description TEXT);”

     

      private var _id:int;

      private var _name:String;

      private var _department:String;

      private var _title:String;

      private var _description:String;

     

      private var INSERT_PRODUCT:String;

      private const SELECT_PRODUCT:String = “SELECT * FROM product”;

 

      public function createDatabase(databaseFile:File, productCount:int):void

      {

            if ((databaseFile != null) && (databaseFile.exists))

            {

                  databaseFile.deleteFile();

                  dg1.dataProvider=null;

                  Alert.show(“Database Created Successfully.”);

            }

     

            if (productCount < 1)

            {

                  productCount = 1;

            }

     

            mysqlConnection = new SQLConnection();

            mysqlConnection.open(databaseFile, SQLMode.CREATE);

            mysqlConnection.close();

            schemaGeneration();

      }

 

      public function deleteDatabase():void

      {

            if ((databaseFile != null) && (databaseFile.exists))

            {

                  databaseFile.deleteFile();

                  dg1.dataProvider=null;

                  Alert.show(“Database Deleted Successfully.”);

            }

            else

            {

                  Alert.show(“There is no database to Delete.”);

            }

      }

 

      private function schemaGeneration():void

      {

            var sqlStatement:SQLStatement = new SQLStatement();

            mysqlConnection=new SQLConnection();

            sqlStatement.sqlConnection = mysqlConnection;

            mysqlConnection.open(databaseFile, SQLMode.CREATE);

            sqlStatement.text = CREATE_PRODUCT_TABLE;

            sqlStatement.execute();

            mysqlConnection.close();

      }

 

      private function createInsertStatements():void

      {

            if((txt1.text !=“”)&&(txt2.text !=“”)&&(txt3.text !=“”)

            &&(txt4.text !=“”))

            {

                  _id=0;

                  _name=txt1.text;

                  _department=txt2.text;

                  _title=txt3.text;

                  _description=txt4.text;

                 

                  if((databaseFile != null) && (databaseFile.exists))

                  {

                        INSERT_PRODUCT=“INSERT INTO product (id, name, department, title, description)” +

                        ” VALUES (“+“((SELECT max(id) FROM product)+1)”+“,'”+_name+“‘,'”+_department+

                        “‘,'”+_title+“‘,'”+_description+“‘);”;

                        _insertProduct =new SQLStatement();

                        mysqlConnection=new SQLConnection();

                        _insertProduct.sqlConnection= mysqlConnection;

                        mysqlConnection.open(databaseFile, SQLMode.CREATE);

                        _insertProduct.text = INSERT_PRODUCT;

                        _insertProduct.execute();

                        mysqlConnection.close();

                        createSelectStatements();

                        clearAll();

                  }

                  else

                  {

                        Alert.show(“There is no database so please create it First.”);

                  }

            }

            else

            {    

                        Alert.show(“Please enter the proper values For all the relative fields.”);

            }

      }

 

      private function createSelectStatements():void

      {

                  _selectProduct =new SQLStatement();

                  mysqlConnection=new SQLConnection();

                  _selectProduct.sqlConnection = mysqlConnection;

                  mysqlConnection.open(databaseFile,SQLMode.READ);

                  _selectProduct.text = SELECT_PRODUCT;

                  _selectProduct.execute();

                  ob=_selectProduct.getResult().data;

                  mysqlConnection.close();

                  dg1.dataProvider=ob;

      }

 

      private function clearAll():void

      {

                  txt1.text=“”;

                  txt2.text=“”;

                  txt3.text=“”;

                  txt4.text=“”;

      }

]]>

 

</mx:Script>

 

<mx:VBox width=”100%” height=”100%” horizontalAlign=”center>

     

      <mx:HBox width=”80%” height=”30” horizontalAlign=”center>

     

            <mx:Label width=”120” textAlign=”right” text=”Name” color=”#ff0000/>

           

            <mx:TextInput id=”txt1” width=”120” height=”35/>

 

      </mx:HBox>

 

      <mx:HBox width=”80%” height=”30” horizontalAlign=”center>

     

            <mx:Label width=”120” textAlign=”right” text=”Department” color=”#ff0000/>

           

            <mx:TextInput id=”txt2” width=”120” height=”35/>

     

      </mx:HBox>

 

      <mx:HBox width=”80%” height=”30” horizontalAlign=”center>

     

            <mx:Label width=”120” textAlign=”right” text=”Title” color=”#ff0000/>

           

            <mx:TextInput id=”txt3” width=”120” height=”35/>

     

      </mx:HBox>

 

      <mx:HBox width=”80%” height=”30” horizontalAlign=”center>

     

            <mx:Label width=”120” textAlign=”right” text=”Description” color=”#ff0000/>

           

            <mx:TextInput id=”txt4” width=”120” height=”35/>

     

      </mx:HBox>

     

      <mx:HBox width=”80%” height=”100%” horizontalAlign=”center>

     

            <mx:DataGrid id=”dg1” width=”100%” height=”100%>

           

                  <mx:columns>

           

                        <mx:DataGridColumn id=”idCol” width=”50” dataField=”id” headerText=”Id” textAlign=”center/>

                       

                        <mx:DataGridColumn id=”nameCol” width=”100” dataField=”name

                              headerText=”Name” textAlign=”center/>

                       

                        <mx:DataGridColumn id=”departmentCol” width=”100” dataField=”department

                              headerText=”Department” textAlign=”center/>

                       

                        <mx:DataGridColumn id=”titleCol” width=”100” dataField=”title

                              headerText=”Title” textAlign=”center/>

                       

                        <mx:DataGridColumn id=”descriptionCol” width=”200” dataField=”description

                              headerText=”Description” textAlign=”center/>

           

                  </mx:columns>

           

            </mx:DataGrid>

     

      </mx:HBox>

     

      <mx:HBox width=”80%” height=”50” horizontalAlign=”center>

     

            <mx:Button id=”btnCreateDataBase” label=”Create DataBase” width=”130

                  click=”createDatabase(databaseFile,_counterProduct)”/>

           

            <mx:Button id=”btnInsertIntoDataBase” label=”Insert into DataBase” width=”150

                  click=”createInsertStatements()”/>

            <mx:Button id=”btnDeleteDataBase” label=”Delete DataBase” width=”130

                  click=”deleteDatabase()”/>

      </mx:HBox> 

</mx:VBox>

 </mx:WindowedApplication>

  

********************************************************** 

Hope you will enjoy this………………………………….. 

 

Advertisements

21 Responses to “Adobe AIR and SQLite Connectivity”

  1. Nielsen Tomazini said

    Hello,
    First of all, thank you very much for your code. It is very helpful for me who is learning Flex.
    I have copied the code and “created” the application in Flex, so I exported it and installed in my machine. And everything is working just fine.
    However I have a question that I hope you could give a reply.
    Well at the first time the application runs, the database has to be created, right? So, the file data.db should be created? That is my point, the database is there running, all the data I insert is there, but where is the database file? I can’t find it in my hard disk. It is not inside the program folder.
    This have been my great question about SQLite and Flex, I have seen documentations from Adobe but have not succeeded in making them run. I can’t see the database created. What am I missing?
    I have seen other tutorial even the source code of the SQLiteadm. Actualy using the SQLiteadm it creates the database file but I have not succeeded in adapting it for my need.
    Now your code is the best I have found but I am wondering about the database file.
    If you could give me a reply, I would really appreciate.
    Thank you again,
    Best regards!

  2. Nielsen Tomazini said

    Hey,

    I have just figured out what I was missing.
    I was not aware about how was working the pointing of file directory.
    I have changed the File.applicationStorageDirectory to File.documentsDirectory and now I see the database file created.
    Thank you!!!!

  3. Shardul Singh Bartwal said

    Hi,
    Its greate to know that u resolved the problem,and soory that I can not answer you at right time.

    Thanks

    Shardul Singh Bartwal

  4. sandeep said

    Actually where we can found the DB file

  5. sandeep said

    Actually where we can find the DB file.where i will be stored?

  6. sandeep said

    where it will be stored??

  7. Shardul singh Bartwal said

    Hi Sandeep,
    You can find the file under the path..
    C:\Documents and Settings\userProfileName\Application Data\ProjectName\Local Store
    There is the file namely ‘data’.

    Thnx….

  8. Salvi said

    Hello,

    Interesting learning.

    Where would i find the database in Vista?

    Thanks & Regards

    Salvi

    • shardul said

      Hi,
      I am not sure about the Vista,as I am not having that.But you can search for ‘Application Data’ or ‘Local Store’ folder in your pc and try to find your project name there.Try to compare it with ‘C:\Documents and Settings\userProfileName\Application Data\ProjectName\Local Store’.There is the file namely ‘data’.

      Hopefully you will find it.

      Thnx…..
      Shardul

  9. I personally like your post. It is very good to know that you don’t know. Fantastic post! Keep posting your good work.Thanks
    Regard

  10. Erthy said

    Hi! I am probably just asking a silly question, however I would really like the answer.
    Where is the file saved? (How can I make it work for an already existing database (.db) file?)

    Erthy (you helped me on the Adobe Forums)

    • Erthy said

      I am sorry, this was obviously answered already (in a way). Now the question I have is: How to change the directory, where the program is looking?

    • The path of the file will like this.
      ‘C:\Documents and Settings\userProfileName\Application Data\ProjectName\Local Store’.

      If you are already having an existing db file
      then you can check it by…….
      if((databaseFile != null) && (databaseFile.exists))
      {
      //Do your sql execution here………..
      }

      • Erthy said

        And can I change the directory? And set it for example to a location that is not in the Application Data (so the user couldn’t accidentaly clean it?). I am trying to make a program for testing vocabulary and I would like to store the dictionaries in the .db files, however, Application Data isn’t an ideal place for them in this case.

        Thank you for everything so far!

      • Erthy said

        And one more question: How can I see, what the database returns without uding a DataGrid (i would like to put the values into variables)?

  11. Erthainel said

    Any help on my last question?

  12. suresh kumar reddy said

    if anybody wanna see the database what the created use document directory insteadof application storage directory. coding like this File.documentsDirectory.resolvePath(“data.db”);

    In Vista(for salvi) you can find the database file at
    C:\Users\Administrator\Documents\data.db

    thats All, regards,
    suresh kumar reddy

  13. jase21 said

    Actually I meant to post it here.
    once again nice work dude.

  14. Great post!! 😉 Thank you so much

  15. I need to to thank you for this very good read!
    ! I certainly enjoyed every bit of it. I’ve got you book-marked to check out new things you post…

  16. LoL Arena said

    Hi to every single one, it’s actually a fastidious for me to pay a quick visit this site,
    it contains important Information.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: