Learn Adobe AIR, Part I: Build A Note Storage App

Working with Databases

Now that we’ve set up a database, we’re ready to start pulling existing notes from the database.

Just as an SQLConnection object maintains a connection to a database, an SQLStatement maintains a particular query to the database. These SQLStatement instances are linked to a particular connection through their SQLConnection property. SQLStatement objects support prepared statements, and a few different methods for retrieving the result of each query; they can also be reused. Check out the reference page for SQLStatement for further details.

Let’s create a GetNotes function to fetch our notes. Here’s the code, which should be added to the end of your notes.js file.

function GetNotes() {  
 dbQuery = new air.SQLStatement();  
 dbQuery.sqlConnection = db;  
 dbQuery.text = "SELECT id,title,created,data FROM notes";  
   
 try {  
   dbQuery.execute();  
 } catch (error) {  
   air.trace("Error retrieving notes from DB:", error);  
   air.trace(error.message);  
   return;  
 }  
   
 return dbQuery.getResult();  
}

This code is reasonably straightforward; we first create a new SQL statement and assign its SQLConnection property to our existing database connection. We then set the actual query text and run the query, catching any potential errors along the way. Finally, we return the result as a SQLResult object.

Let’s use the Introspector to examine the data type that this function returns. Run your AIR application (Run > Run…), then hit F12 to launch the Introspector, and execute “GetNotes()” from the JavaScript console. Here’s the output:

The number that’s important to us here is the value “0” that I’ve highlighted in the figure above—it represents the first item in our database table. The data property, in fact, contains all of the rows returned by a database query, beginning at index number 0. Each query also provides us with a length property, which tells us how many rows were returned. Knowing this information, we can now construct a simple routine to retrieve each note from the database, and print it to the page:

var notes = GetNotes();  
$("#notes").empty();  
 
var numRecords = notes.data.length;  
 
for (i=0;i<numRecords;i++) {  
 dateObj = new Date(notes.data[i].created);  
 time = dateObj.getFullYear()+"-"+  
      String("0"+dateObj.getMonth()).slice(-2)+"-"+  
      String("0"+dateObj.getDate()).slice(-2)+" "+  
      String("0"+dateObj.getHours()).slice(-2)+":"+  
      String("0"+dateObj.getMinutes()).slice(-2);  
 $("<li/>").append('<span class="note_time">'+time+  
           '<a href="#del/'+notes.data[i].id+'">'+  
           '<img src="icons/delete.png"/></a></span>')  
       .append('<span class="note_title">'+notes.data[i].title)    
       .append('<pre>'+notes.data[i].data.'</pre>')  
       .appendTo("#notes");  
}

This routine inspects each record, checking when the note was created and performing some basic formatting in preparation for displaying the note. We create a new list item for each record, and place each note at the end of the list. We’re achieving all this by relying fairly heavily on jQuery here, but this could well have been achieved using standard DOM calls.

Deleting Notes

OK, so we can display our notes, but we’re still missing some functionality—namely, adding and deleting notes. Let’s enable that pretty, red Delete button!

We need to bind a new function to each of the Delete buttons. Note that the actual Delete images will be surrounded by links, each of which has its own unique identifier. We can leverage this fact to determine which note we want to delete. Once we’ve written the code to do this, the code is almost identical to our GetNotes Function.

It’s time to add a new method, ListNotes, to the bottom of your notes.js file:

function ListNotes() {  
 var notes = GetNotes();  
 $("#notes").empty();  
   
 var numRecords = notes.data.length;  
   
 for (i=0;i<numRecords;i++) {  
   dateObj = new Date(notes.data[i].created);  
   time = dateObj.getFullYear()+"-"+  
        String("0"+dateObj.getMonth()).slice(-2)+"-"+  
        String("0"+dateObj.getDate()).slice(-2)+" "+  
        String("0"+dateObj.getHours()).slice(-2)+":"+  
        String("0"+dateObj.getMinutes()).slice(-2);  
   $("<li/>").append('<span class="note_time">'+time+  
             '<a href="#del/'+notes.data[i].id+'">'+  
             '<img src="icons/delete.png"/></a></span>')  
         .append('<span class="note_title">'+unescape(notes.data[i].title))  
         .append('<pre>'+unescape(notes.data[i].data)+'</pre>')  
         .appendTo("#notes");  
 }  
   
 $(".note_time a").click(function(){  
   var currHash = $(this).attr("href").split('/');  
   var id = currHash[1];  
     
   var dbQuery = new air.SQLStatement();  
   dbQuery.sqlConnection = db;  
   dbQuery.text = "DELETE FROM notes WHERE id=" + id;  
     
   try {  
     dbQuery.execute();  
   } catch (error) {  
     air.trace("Error deleting note from DB:", error);  
     air.trace(error.message);  
     return;  
   }  
     
   ListNotes();  
 });  
}

We also need to call ListNotes from our $(document).ready method at the start of our notes.js file.

Adding New Notes

We have just one more database operation to flesh out: allowing our users to add new notes. This functionality is quite similar to both of the previous database operations, with two exceptions:

1. We need to fetch the values from the New Note form (and work out the current time in UNIX time), and


2. We need to regenerate the list of notes, once we’re done.

Here’s the code, ready to copy into the end of our notes.js file:

function AddNote() {  
 var title = escape($("#title").val());  
 var now = new Date();  
 var created = Date.parse(now.toString());  
 var data = escape($("#data").val());  
   
 dbQuery = new air.SQLStatement();  
 dbQuery.sqlConnection = db;  
 dbQuery.text  = "INSERT INTO notes (title,created,data)";  
 dbQuery.text += "VALUES ('"+title+"',"+created+",'"+data+"')";  
   
 try {  
   dbQuery.execute();  
 } catch (error) {  
   air.trace("Error inserting new record into database:", error);  
   air.trace(error.message);  
 }  
   
 HideNewNote();  
 ListNotes();  
}

The first few lines of this code fetch the values from each field. Date.parse (a static date function) will convert a date string into a UNIX time value. When we construct our database query, we add the user data from these form fields. There’s just one minor limitation—we need to escape our string. Failing to do so could potentially result in a malformed query string, hence the escape calls that you see there.

We also need to bind this new function to the Create Note button. This is very easy using jQuery—just add the following line, shown in bold, to your BindEvents function:

function BindEvents() {  
 $("#new_note_form").hide();  
 $("a.notes_list").click(HideNewNote);  
 $("#new_note_btn").click(AddNote);  
}