Using SQLite Databases with AIR - Part 7 - Looping over Query results (also with jQuery)

I think that I have neglected to cover one of the most important parts of working with SQLite databases in Adobe AIR, and that is how to get the results out of the query. We talked about making queries, parameterizing queries, using transactions with queries. But I don't think we have covered getting the data out of the queries.

So let's do that.

Looping over the Query Result (The Hard Way)

We'll start with a very simply query that asks for the NAME and URL of the bloggers in my BLOGGER table. The database I am using is attached to this blog post if you wish to try it out.

    // Create the connection, db file reference and a query statement
    var conn = new air.SQLConnection();
    var db = air.File.applicationStorageDirectory.resolvePath('bloggers.db');
    var stmt = new air.SQLStatement();
    // Open a synchronous connection, air.SQLMode.CREATE);
    // Provide the SQLStatement object with the SQL statement and the connection it needs to use            
    stmt.text = 'SELECT name, url FROM bloggers;';
                stmt.sqlConnection = conn;
    // Execute the query

Now to get the results and loop over them. We will insert the result into a table tag with id="bloggerTableBody".

Here is the table we'll be using

<table id="bloggerTable">
    <tbody id="bloggerTableBody">

    // Put the Query result into a variable
    var result = stmt.getResult();

    // Get the TBODY reference that we'll be sticking these into
    var table = document.getElementById('bloggerTableBody');
    var row = ""
    // Loop over the result
    for (i=0; i <=; i++) {
        // Create a ton of elements to construct the table
        var rowElem = document.createElement('tr');
        var nameCell = document.createElement('td');
        var urlCell = document.createElement('td');
        var nameContent = document.createTextNode([i].name)
        var urlContent = document.createTextNode([i].url)
        // append the new text elems to their appropriate cells
        // append the cells to the row
        // append the row to the table

Wow, I forgot how hard this is without jQuery.

The SQLResult object that is returned from the getResult() method of the SQLStatement object contains a "data" array that contains the actual records. Each record is an object of its own with associative keys named for the column names.

Looping over the Query Result (The jQuery Way)

// Place the results into a variable
    var result = stmt.getResult();
    // get a reference to the table TBODY
    var tableBody = $('#bloggerTableBody');
    // Use jQuery's $.each(0 funciton to loop over the object
    $.each(, function(i, val){
        // append the data to the table using jquery's .append()
        tableBody.append('<tr><td>' + + '</td>' + '<td>' + val.url + '</td></tr>');

As you can see, the jQuery way is much cleaner and easier to look at. We are still performing the same actions, but jQuery's utility functions make this A LOT easier. We are still looping over the SQLResult's "data" property which contains the array of records, but we are able to use a simple closure to process each record and then we can use the append() method of the tableBody object to simplly append a string that constructs the table rows instead of needing to deal with all of those createElement() and createTextElement() DOM functions.


The results of each of these method are identical. Here is a screen capture of the completed table.

BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner