Retrieving data from a SQLite database

The ability to create a table and insert data into it is not of much use if we don't know how to retrieve that data and present it in a useful way to the user! We'll now introduce the concept of resultSet (or recordSet, if you prefer) in SQLite and see how to retrieve data via this resultSet object, which can be collected and returned in an array format suitable for use within a TableView.

How to do it...

In your database.js file, add the following function under the db.deleteFavorite function:

db.getFavorites = function() {
  var sql = "SELECT * FROM favorites ORDER BY title ASC";

  var results = [];
  var resultSet = db.database.execute(sql);

    while (resultSet.isValidRow()) {
      results.push({
          id: resultSet.fieldByName('id'),
         title: resultSet.fieldByName('title'),
         data: {
                title: resultSet.fieldByName('title'),
                description: resultSet.fieldByName('description'),
                link: resultSet.fieldByName('link'),
color: "#000", // sets the title color for Android
                height: 40 // sets the row height for Android
                }
          //iterates to the next record
        resultSet.next();
    }
  
    //you must close the resultset
    resultSet.close();  
    //finally, return our array of records!
  return results;
}

Now, open the favorites.js file for the first time, and replace its contents with the following code. Much of this code should be pretty familiar to you by now, including defining and adding TableView to your Window, plus requiring the database.js file as a CommonJS module called db:

var db = require('database');

//create an instance of a window
module.exports = (function() {

  var win = Ti.UI.createWindow({
    title : 'Favorites',
  backgroundColor : "#fff"
  });

  var tblFavorites = Ti.UI.createTableView();
  
  win.add(tblFavorites);

  function loadFavorites() {
    data = [];
    //set our data object to empty
    data = db.getFavorites();
    tblFavorites.data = data;
  }

  //the focus event listener will ensure that the list
  //is refreshed whenever the tab is changed
  win.addEventListener('focus', loadFavorites);

  return win;

})();

How it works...

What we are doing in the first block of code is actually just an extension of our previous recipe, but instead of creating or removing records, we are selecting them in a database recordset called resultSet. Then we loop through this resultSet object, adding the data that we require from each record to our results array.

The results array can then be added to our TableView's data property just like any other data source, such as the one you obtained at the start of this chapter from an external XML feed. One thing to note is that you must always iterate to the new record in resultSet using resultSet.next(), and, when finished, always close resultSet using resultSet.close(). Failure to do either of these actions can cause your application to record invalid data, leak memory badly, and, in the worst case scenario, fatally crash!

An important difference between the favorites screen and the recipe screen is that we do not explicitly create custom TableViewRow objects as we did before. Instead, we just create an array and populate the TableView.data property directly, because we've specified a title property, which is used automatically as the default text in the row. Therefore, it's really easy to create a simple table!

The preceding screenshot shows the TableView in our Favorites tab, displaying the records that we have added as favorites into our local SQLite database.