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
    conn.open(db, 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
    stmt.execute();

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">
    <thead>
         <tr>
             <th>Name</th>
                    <th>URL</th>
         </tr>    
    </thead>
    <tbody id="bloggerTableBody">
        
    </tbody>
            
</table>


    // 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 <= result.data.length; 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(result.data[i].name)
        var urlContent = document.createTextNode(result.data[i].url)
    
        // append the new text elems to their appropriate cells
        nameCell.appendChild(nameContent);
        urlCell.appendChild(urlContent);
            
        // append the cells to the row
        rowElem.appendChild(nameCell);
        rowElem.appendChild(urlCell);
                
        // append the row to the table
        table.appendChild(rowElem);
    }

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 result.data object
    $.each(result.data, function(i, val){
        // append the data to the table using jquery's .append()
        tableBody.append('<tr><td>' + val.name + '</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.

Result

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

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