could no be able to get data from sqlite database in flex

Question

I'm making a mobile app in flex 4.5, I want to get name of person and age of person then save to sqlite database done my code looks like :

public var personNamesDB:File;
        public var dbConnection:SQLConnection;

        protected function createDatabase(event:FlexEvent):void
        {
            // TODO Auto-generated method stub
            personNamesDB = File.applicationStorageDirectory.resolvePath("person.db");
            dbConnection = new SQLConnection();
            dbConnection.openAsync(personNamesDB, SQLMode.CREATE);
            dbConnection.addEventListener(SQLEvent.OPEN, onDatabaseOpened);
            dbConnection.addEventListener(SQLEvent.CLOSE, onDatabaseClosed);



        }// end createDatabase method

protected function onDatabaseOpened(arshayEvent:SQLEvent):void
        {
            trace("DB Opened");
            var statement:SQLStatement = new SQLStatement();
            statement.sqlConnection = dbConnection;
            statement.text = "CREATE TABLE IF NOT EXISTS personinfo(id INTEGER PRIMARY KEY AUTOINCREMENT, nameofperson TEXT, ageofperson TEXT)";

            statement.execute();
            // for showing saved city names in list on App start up
            showSavedNames();
            trace("table created");
        }

Now for Inserting data code is :

///////////////////////////////////
        public var insertData:SQLStatement
        protected function saveName():void
        {
            // SQLite Usage
            insertData = new SQLStatement();
            insertData.sqlConnection = dbConnection;
            insertData.text = "INSERT INTO personinfo(nameofcity, ageofperson) VALUES(:nameofcity, :ageofperson)";
            insertData.parameters[":nameofcity"] =nameInput.text;
            insertData.parameters[":ageofperson"] = ageInput.text;
            insertData.addEventListener(SQLEvent.RESULT, dataInsertedSuccess);
            trace("Name " + nameInput.text);
            insertData.execute();

            showSavedNames();

        }

        protected function dataInsertedSuccess(event:SQLEvent):void
        {
            trace(insertData.getResult().data);
        }
        //////////////////////////////////////////////
        public var selectQuery:SQLStatement;
        protected function showSavedNames():void
        {
            selectQuery = new SQLStatement();
            selectQuery.sqlConnection = dbConnection;
            selectQuery.text = "SELECT * FROM personinfo ORDER BY nameofperson ASC";
            selectQuery.addEventListener(SQLEvent.RESULT, showNamesInList);

            selectQuery.execute();
        }

        protected function showNamesInList(event:SQLEvent):void
        {
            listOfAddedNames.dataProvider = new ArrayCollection(selectQuery.getResult().data);
        }

mxml code for list control is :

<s:List id="listOfAddedNames" width="345" height="100%" labelField="nameofperson"
            click="get_names_data(event)"/>

Now get_names_data method is like :

public var selectNameQuery:SQLStatement;

        protected function get_names_data(event:MouseEvent):void
        {
            // TODO Auto-generated method stub

            var currentName:String = listOfAddedNames.selectedItem.nameofperson;

            selectNameQuery = new SQLStatement();
            selectNameQuery.sqlConnection =dbConnection;
            selectNameQuery.text = "SELECT ageofperson FROM personinfo WHERE (nameofperson = '" + currentName + "')";
            selectNameQuery.addEventListener(SQLEvent.RESULT, nowGotAge);

            selectNameQuery.execute();
            trace("current selected   >> "+currentName);
        }

        protected function nowGotAge(event:SQLEvent):void
        {

            trace("Age of selected Name is >> "+selectNameQuery.getResult().data.ageofperson);
        }

On this line :

trace("Age of selected Name is >> "+selectNameQuery.getResult().data.ageofperson);

No data is fetched from database and trce is displaying "undefined" please solve this for me and tell me how to get data from ageofperson column according to selected name in list of person names-- Thanks in advance

Answer 1

First problem with need to understand async execution model because you opened sqlite database async mode. Second problem with column name 'nameofcity' that person table doesn't have any column like you declared.so i modify here as 'nameofperson' in saveName().

Where you call saveName() please make sure that you have called 'saveName()'.

In dataInsertedSuccess() In sqlquery return no of affected row only when run againt INSERT/UPDATE sql query ie, only return integer value. So always insertData.getResult().data is null/undefined.it will contain(s) data if you run SELECT sql query.

            public var personNamesDB:File;
        public var dbConnection:SQLConnection;

        protected function createDatabase(event:FlexEvent):void
        {
            // TODO Auto-generated method stub
            personNamesDB = File.applicationStorageDirectory.resolvePath("person.db");
            dbConnection = new SQLConnection();
            dbConnection.openAsync(personNamesDB, SQLMode.CREATE);
            dbConnection.addEventListener(SQLEvent.OPEN, onDatabaseOpened);
            dbConnection.addEventListener(SQLEvent.CLOSE, onDatabaseClosed);
        }// end createDatabase method

        protected function onDatabaseOpened(arshayEvent:SQLEvent):void
        {
            trace("DB Opened");
            var statement:SQLStatement = new SQLStatement();
            statement.sqlConnection = dbConnection;
            statement.text = "CREATE TABLE IF NOT EXISTS personinfo(id INTEGER PRIMARY KEY AUTOINCREMENT, nameofperson TEXT, ageofperson TEXT)";
            statement.addEventListener(SQLEvent.RESULT ,function(event:SQLEvent):void
            {
                trace("table created");
                // for showing saved city names in list on App start up
                showSavedNames(); **//Need to call after get success event**
            });
            statement.execute();
        }

        public var insertData:SQLStatement
        protected function saveName():void
        {
            // SQLite Usage
            insertData = new SQLStatement();
            insertData.sqlConnection = dbConnection;
            insertData.text = "INSERT INTO personinfo(nameofperson, ageofperson) VALUES(:nameofperson, :ageofperson)";
            insertData.parameters[":nameofperson"] = "Xyz";
            insertData.parameters[":ageofperson"] = "27"
            insertData.addEventListener(SQLEvent.RESULT, dataInsertedSuccess);
            insertData.addEventListener(SQLErrorEvent.ERROR, function(event:SQLErrorEvent):void
            {
                //details   "table 'personinfo' has no column named 'nameofcity'"   
                trace(event.error.message.toString());
                //                  showSavedNames();
            });
            insertData.execute();
        }

        protected function dataInsertedSuccess(event:SQLEvent):void
        {
            trace("Success :: " + insertData.getResult().rowsAffected);
            showSavedNames();
        }
        //////////////////////////////////////////////
        public var selectQuery:SQLStatement;
        protected function showSavedNames():void
        {
            selectQuery = new SQLStatement();
            selectQuery.sqlConnection = dbConnection;
            selectQuery.text = "SELECT * FROM personinfo ORDER BY nameofperson ASC";
            selectQuery.addEventListener(SQLEvent.RESULT, showNamesInList);
            selectQuery.execute();
        }

        protected function showNamesInList(event:SQLEvent):void
        {
            listOfAddedNames.dataProvider = new ArrayCollection(selectQuery.getResult().data);
        }

        public var selectNameQuery:SQLStatement;
protected function get_names_data(event:MouseEvent):void
        {
            //Need not to get ageofperson from db
            Alert.show(listOfAddedNames.selectedItem.ageofperson);

            //Any way continue your way
            var currentName:String = listOfAddedNames.selectedItem.nameofperson;

            selectNameQuery = new SQLStatement();
            selectNameQuery.sqlConnection =dbConnection;
            selectNameQuery.text = "SELECT ageofperson FROM personinfo WHERE nameofperson = '" + currentName + "'";
            selectNameQuery.addEventListener(SQLEvent.RESULT, nowGotAge);
            selectNameQuery.execute();
            trace("current selected   >> "+currentName);
        }

        protected function nowGotAge(event:SQLEvent):void
        {
            trace("Age of selected Name is >> "+selectNameQuery.getResult().data[0].ageofperson);
        }