Using browser SQLite’s LIMIT/OFFSET to page results with AJAX

Another post mostly for me.

I spent some time tonight trying to solve a problem in a mobile/HTML 5 app I am building. I have two lists that pull data from a client-side database that could possibly contain many records. Rather than pull all the results on page load and dynamically build a huge list I decided to look into doing some sort of paged results. I wanted my results to look something like this:

Luckily SQLite’s SELECT statement supports LIMIT and OFFSET statements that are ideal for supporting AJAX-style paging. Following is psuedocode for the implementation:

function getAccounts(pageSize, pageIndex) {
  // setup the db transaction
  var sql = 'SELECT * FROM accounts ORDER BY name LIMIT ' \+ pageIndex + ',' \+ pageSize + ';';

  /// execute the sql statement

  if (result == null || result.rows.length == 0) {
    // hide the "More" button
  } else {
    for (var i = 0; i < result.rows.length; i++) {
      // dynamically add the item to the list
    }
  }
}