4 Replies Latest reply on Jul 24, 2010 3:43 AM by diditin

    Why my app database starts over again?

    diditin

      Hi,  I am really new to AIR, but so far, i though that creating an AIR App using a database, creating a table and inserting data into it will be stored permanently in the database. Is that right?

       

      I tried some examples where i create a table then inserted data, closes the application. Open it again, load the data, but nothing as if the table is created again (Proof when inserting, index starting at 1 again. Previous entries are lost.)

       

      I want to have a permanent database on my AIR app. how do i do?

       

      Any help pls?

       

      Thanks

       

      David

        • 1. Re: Why my app database starts over again?
          thilgen Level 4

          What you describe should work....

           

          could include an example of your code that appears to be working incorrectly?

           

          Thanks,

           

          Chris Thilgen

          AIR Engineering

          1 person found this helpful
          • 2. Re: Why my app database starts over again?
            diditin Level 1

            Thanks for your reply, i am sorry for my late response, i mistakenly drop the reply into  another post.

             

            Simple Connection

            [CODE]

                            conn = new SQLConnection();
                              conn.addEventListener(SQLEvent.OPEN, openSuccess);
                              conn.addEventListener(SQLErrorEvent.ERROR, openFailure);
                             
                             status = "Creating and opening database";
                             
                             // Use these two lines for an on-disk database
                              // but be aware that the second time you run the app  you'll get errors  from
                             // creating duplicate records.
                              var dbFile:File =   File.applicationStorageDirectory.resolvePath("DBSample.db");
                              conn.openAsync(dbFile);

                             // Use this line for  an in-memory database
                             conn.openAsync(null);

            [/CODE]

             

            SQL  creation Table arrival

            [CODE]

            sql += "CREATE TABLE IF NOT  EXISTS arrival (";                    
                            sql +=  "arv_id INTEGER PRIMARY KEY AUTOINCREMENT,"; 
                            sql +=  "arv_repid INTEGER,";         
                            sql +=  "arv_clientname TEXT,";                    
                            sql +=  "arv_docno INTEGER,";         
                            sql += "arv_roomno  TEXT,";                    
                            sql += "arv_agencyName  TEXT,";                    
                            sql += "arv_flight  TEXT,";                    
                            sql += "arv_date  DATE,";             
                            sql += "arv_depflight  TEXT,";                 
                            sql += "arv_depdate  DATE,";          
                            sql += "arv_meetingtime  TIME,";      
                            sql += "arv_meetingok INTEGER,";       
                            sql += "arv_resaremarks TEXT,";                   
                             sql += "arv_repremarks TEXT,";                    
                             sql += "arv_active INTEGER";             
                            sql +=  ")";

             

            [/CODE]

             

             

            Insertion  into table

             

            [CODE]

                 sql += "INSERT INTO arrival (arv_repid, arv_clientname, arv_docno,  arv_roomno, arv_agencyName, arv_flight,arv_depflight,arv_meetingok,  arv_resaremarks, arv_repremarks, arv_active ) ";
                            sql  += "VALUES (1,'david tin',223442,'A123', 'Hotel plan',  'EK984','EK985',1, 'resaremarks', 'rep remarks',1)";

            [/CODE]

             

            retrieval

             

            [CODE]

                             var sql:String = "SELECT * FROM arrival";

            [/CODE]

             

             

            I don't have  anything more that those. Does the code helps? or do you want the  whole?

             

            David

             

             

             

            FULL CODE DOWN:

             

            <?xml version="1.0" encoding="utf-8"?>
            <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute"
                preinitialize="openDatabaseConnection();">
                <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 createStmt:SQLStatement;
                        private var insertStmt:SQLStatement;
                        private var selectStmt:SQLStatement;

             

                        public function openDatabaseConnection():void
                        {
                            conn = new SQLConnection();
                            conn.addEventListener(SQLEvent.OPEN, openSuccess);
                            conn.addEventListener(SQLErrorEvent.ERROR, openFailure);
                           
                            status = "Creating and opening database";
                           
                            // Use these two lines for an on-disk database
                            // but be aware that the second time you run the app you'll get errors from
                            // creating duplicate records.
                            var dbFile:File = File.applicationStorageDirectory.resolvePath("DBSample.db");
                            conn.openAsync(dbFile);

             

                            // Use this line for an in-memory database
                            conn.openAsync(null);
                        }
                       
                        private function openSuccess(event:SQLEvent):void
                        {
                            conn.removeEventListener(SQLEvent.OPEN, openSuccess);
                            conn.removeEventListener(SQLErrorEvent.ERROR, openFailure);
                            createTable();
                        }
                       
                        private function openFailure(event:SQLErrorEvent):void
                        {
                            conn.removeEventListener(SQLEvent.OPEN, openSuccess);
                            conn.removeEventListener(SQLErrorEvent.ERROR, openFailure);
                           
                            status = "Error opening database";
                           
                            trace("event.error.message:", event.error.message);
                            trace("event.error.details:", event.error.details);
                        }
                       
                       
                        private function createTable():void
                        {
                            status = "Creating table";
                           
                            createStmt = new SQLStatement();
                   
                            createStmt.sqlConnection = conn;
                            var sql:String = "";
                           
                            sql += "CREATE TABLE IF NOT EXISTS arrival (";                    
                            sql += "arv_id INTEGER PRIMARY KEY AUTOINCREMENT,"; 
                            sql += "arv_repid INTEGER,";         
                            sql += "arv_clientname TEXT,";                    
                            sql += "arv_docno INTEGER,";         
                            sql += "arv_roomno TEXT,";                    
                            sql += "arv_agencyName TEXT,";                    
                            sql += "arv_flight TEXT,";                    
                            sql += "arv_date DATE,";             
                            sql += "arv_depflight TEXT,";                 
                            sql += "arv_depdate DATE,";          
                            sql += "arv_meetingtime DATE,";      
                            sql += "arv_meetingok INTEGER,";      
                            sql += "arv_resaremarks TEXT,";                   
                            sql += "arv_repremarks TEXT,";                    
                            sql += "arv_active INTEGER";             
                            sql += ")";  
             
                            createStmt.text = sql;
                           
                            createStmt.addEventListener(SQLEvent.RESULT, createResult);
                            createStmt.addEventListener(SQLErrorEvent.ERROR, createError);
                           
                            createStmt.execute();
                        }
                       
                       
                       
                        private function createResult(event:SQLEvent):void
                        {
                            status = "Table created";

             

                            createStmt.removeEventListener(SQLEvent.RESULT, createResult);
                            createStmt.removeEventListener(SQLErrorEvent.ERROR, createError);
                           
            //                addData();
                        }

             

                        private function createError(event:SQLErrorEvent):void
                        {
                            status = "Error creating table";
                           
                            createStmt.removeEventListener(SQLEvent.RESULT, createResult);
                            createStmt.removeEventListener(SQLErrorEvent.ERROR, createError);
                           
                            trace("CREATE TABLE error:", event.error);
                            trace("event.error.message:", event.error.message);
                            trace("event.error.details:", event.error.details);
                        }
                       
                        private function addData():void
                        {
                            status = "Adding data to table";
                           
                            insertStmt = new SQLStatement();
                            insertStmt.sqlConnection = conn;
                            var sql:String = "";
                           
                            sql += "INSERT INTO arrival (arv_meetingtime, arv_depdate, arv_date, arv_repid, arv_clientname, arv_docno, arv_roomno, arv_agencyName, arv_flight,arv_depflight,arv_meetingok, arv_resaremarks, arv_repremarks, arv_active ) ";
                            sql += "VALUES ('00:12', '2010-09-12', '2010-07-19',1,'david tin',223442,'A123', 'Hotel plan', 'EK984','EK985',1, 'resaremarks', 'rep remarks',1)";
                            //, 
                            //,     
                            insertStmt.text = sql;
                           
                            insertStmt.addEventListener(SQLEvent.RESULT, insertResult);
                            insertStmt.addEventListener(SQLErrorEvent.ERROR, insertError);
                           
                            insertStmt.execute();
                           
            //                insertStmt2 = new SQLStatement();
            //                insertStmt2.sqlConnection = conn;
            //                var sql2:String = "";
            //                sql2 += "INSERT INTO employees (firstName, lastName, salary) ";
            //                sql2 += "VALUES ('John', 'Jones', 8200)";
            //                insertStmt2.text = sql2;
            //               
            //                insertStmt2.addEventListener(SQLEvent.RESULT, insertResult);
            //                insertStmt2.addEventListener(SQLErrorEvent.ERROR, insertError);
            //               
            //                insertStmt2.execute();
                        }       
                       
                        private function insertResult(event:SQLEvent):void
                        {
                            var stmt:SQLStatement = event.target as SQLStatement;
                            stmt.removeEventListener(SQLEvent.RESULT, insertResult);
                            stmt.removeEventListener(SQLErrorEvent.ERROR, insertError);
                           
                            getData();
                        }
                       
                        private function insertError(event:SQLErrorEvent):void
                        {
                            status = "Error inserting data";
                           
                            insertStmt.removeEventListener(SQLEvent.RESULT, insertResult);
                            insertStmt.removeEventListener(SQLErrorEvent.ERROR, insertError);
                           
                            trace("INSERT error:", event.error);
                            trace("event.error.message:", event.error.message);
                            trace("event.error.details:", event.error.details);
                        }
                       
                        private function getData():void
                        {
                            status = "Loading data";
                           
                            selectStmt = new SQLStatement();
                            selectStmt.sqlConnection = conn;
                            var sql:String = "SELECT * FROM arrival";
                            selectStmt.text = sql;
                           
                            selectStmt.addEventListener(SQLEvent.RESULT, selectResult);
                            selectStmt.addEventListener(SQLErrorEvent.ERROR, selectError);
                           
                            selectStmt.execute();
                        }
                       
                        private function selectResult(event:SQLEvent):void
                        {
                            status = "Data loaded";
                           
                            selectStmt.removeEventListener(SQLEvent.RESULT, selectResult);
                            selectStmt.removeEventListener(SQLErrorEvent.ERROR, selectError);
                           
                            var result:SQLResult = selectStmt.getResult();
                           
                            resultsGrid.dataProvider = result.data;
                           
            //                var numRows:int = result.data.length;
            //                for (var i:int = 0; i < numRows; i++)
            //                {
            //                    var output:String = "";
            //                    for (var prop:String in result.data[i])
            //                    {
            //                        output += prop + ": " + result.data[i][prop] + "; ";
            //                    }
            //                    trace("row[" + i.toString() + "]\t", output);
            //                }               
                        }
                       
                        private function selectError(event:SQLErrorEvent):void
                        {
                            status = "Error loading data";
                           
                            selectStmt.removeEventListener(SQLEvent.RESULT, selectResult);
                            selectStmt.removeEventListener(SQLErrorEvent.ERROR, selectError);
                           
                            trace("SELECT error:", event.error);
                            trace("event.error.message:", event.error.message);
                            trace("event.error.details:", event.error.details);
                        }
                       
                       
                    ]]>
                </mx:Script>
               
                <mx:HBox x="324"><mx:Button label="Load data" click="getData();"/><mx:Button label="Add data" click="addData();"/></mx:HBox>
                <mx:DataGrid id="resultsGrid" height="560" width="100%" y="30"/> 
               
            </mx:WindowedApplication>

             

             

            • 3. Re: Why my app database starts over again?
              diditin Level 1

              Hi thilgen, any news why its like that?

               

              thanks

               

              David

              • 4. Re: Why my app database starts over again?
                diditin Level 1

                OK got the answer just quote the conn.asyn(null). and it will use the database file to store and retrieve data.

                 

                Fiouff...