3 Replies Latest reply on Jul 15, 2010 10:46 AM by diditin

    Flex, AIR and SQLite issue

    CrazyMerlin Level 1

      Hey gang,

       

      I'm creating a local db file to hold contact data for offline use in an AIR app but ran into an odd issue.

       

      When I run the app for the first time I create the database and a single table in it called Contact. No problems there. But then when I run the app again, and I attempt to load the table schema I get:

       

      "No schema objects with type 'table' in database 'infused' were found."

       

      So I loaded the database in an SQLite viewer and the table is indeed there. Now, if I put the loadSchema call into a try...catch, and in the catch allow the code to run into the create table function, I get:

       

      "SQLError: 'Error #3115: SQL Error.', details:'table 'Contact' already exists', operation:'execute', detailID:'2061'"

       

      Can you see how that makes no sense whatsoever?

       

      First it says there are no tables, then it says it cannot create the table because it already exists.

       

      Anyone have any ideas about this? For now I can just add an "IF NOT EXISTS" clause to the create table statement, but I don't see why I would need to.

       

      Thanks for any thoughts.

       

      Paul.

        • 1. Re: Flex, AIR and SQLite issue
          Stefan Horochovec

          Hello

           

          You can post your code here? Its more easy to help you

           

          Regards    

          • 2. Re: Flex, AIR and SQLite issue
            diditin

            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

            • 3. Re: Flex, AIR and SQLite issue
              diditin Level 1

              <?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 TIME,";      
                              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 ARV 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_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)";
                              //,arv_date, arv_depdate, arv_meetingtime,
                              //, '2010-07-19',  '2010-09-12', '00:12',
                              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>