Data “paging” in AIR SQLite

Posted April 7, 2010 12:25 pm
Filed under: AIR, Articles by Paul, local SQL database, Projects, SQL, Tutorials

I got an interesting question a few days ago that I thought I’d share:

My application has thousands of records in one particular table that I need to populate the display with. I was wondering if I can implement paging to speed up the retrieval of those records?

In fact he specifically wanted to know if it was possible to do data paging with the SQLRunner class in my air-sqlite library. The answer is yes it works, without even needing any changes to the library. See below for how to do that.

The easiest way to implement data paging (in other words, getting only a subset of a query’s results at a time) in a SELECT statement is to use the LIMIT..OFFSET clause in the SELECT statement.

In summary, you can put a LIMIT clause at the end of a SELECT statement and the result set will only include the specified number of rows:

SELECT * FROM myTable
LIMIT 3

Then to get the next 3 rows, you add an OFFSET value:

SELECT * FROM myTable
LIMIT 3 OFFSET 3

Fortunately, you can use statement parameters for the LIMIT and OFFSET values (although the docs don’t mention this — shame on me!):

SELECT * FROM myTable
LIMIT :limit OFFSET :offset

Then you just specify values for those parameters and you’re good to go.

I tested this using the SQLRunner class by adding a new set of unit tests and it worked just fine without any changes to the library. (Hooray for unit tests and FlexUnit support in Flash Builder 4 — they made it nice and easy to test this out since I already had the infrastructure in place.)

Here is the SQL statement I used for the unit test using LIMIT..OFFSET with parameters:

SELECT colString,
colInt
FROM main.testTable
LIMIT :limit OFFSET :offset

And here is the line of code that calls the statement (modified slightly for readability). test_result is the result handler function. The parameters object specifies that I want 7 result rows, starting with row number 3 (i.e. skipping 2):

_sqlRunner.execute(SQL, {limit:7, offset:2}, test_result);

As Peter points out in the comments, another approach to do something similar to data paging with AIR is to specify a number of rows to retrieve as the first argument to the SQLStatement.execute() method, then call the SQLStatement.next() method to retrieve additional rows from the same statement. This technique is described in the documentation so I won’t go into more detail about it here except to say that it does have a couple of drawbacks (mentioned in my comment below) that make it less suitable for data paging (but still very useful).

You can leave a comment, or trackback from your own site.

http://www.peterelst.com Peter

Thats great Paul, might be interesting to point out you can also use the built in SQLStatement.execute(number of records) and SQLStatement.next() along with the SQLResult.complete property to do paging if not using your SQLRunner class.
http://probertson.com/ Paul Robertson

Thanks for the comment Peter. You’re definitely right about using execute() and next() to retrieve result sets in chunks — I definitely should have mentioned it. In case you’re wondering, I did actually have some reasons why I didn’t mention it:

- It isn’t (currently) compatible with my SQLRunner class, and the person who was asking specifically asked about a solution that would work with SQLRunner
- It’s already covered more extensively in the documentation, since it’s part of the AIR API rather than a SQL language feature
- It has a limitation that you can’t get random access to the data — only linear access — so in that regard it’s most suitable when you know you’ll want all the data (or at least a subset from the beginning).

(But as I said, I definitely should have at least mentioned it.)

And of course, if you’re not using my SQLRunner class you can actually use both of them together. =)