Learn Adobe AIR, Part II: Build a Customer Management App

Retrieving Our First Records

Now that we have an interface and a data source, it’s time to start reading records from our CRM database. The fun begins with the customers.html file, which is included in the skeleton code archive and contains some sample static output. We’ll use jQuery to remove this static data and replace it with real records from the database. Open your behaviour.js file and insert the following code:

$(document).ready(function(){  
 db = SetupDB();  
 read_customers();  
 $("#refresh").click(function(){ read_customers(); });  
});  
 
function read_customers() {  
 dbQuery = new air.SQLStatement();  
 dbQuery.sqlConnection = db;  
 dbQuery.text = "SELECT * FROM customers";  
   
 try { dbQuery.execute(); } catch (error) {  
   air.trace("Could not retrieve customers from DB.", error.message);  
 }  
   
 results = dbQuery.getResult();  
 $("#customerlist tbody").empty();  
 for (i = 0; i < results.data.length; i++) {  
   row = results.data[i];  
   $("#customerlist tbody").append('<tr><td>'+row.id+'</td><td>'  
     +row.name+'</td></tr>');  
 }  
 $("#customerlist tbody tr").each(function(){  
   $(this).click(function(){  
     view($(this).children("td:first").text())  
   })  
 });

The first part of this code is fairly self-explanatory:

1. Call the database connection function we defined earlier.


2. Read our list of customers.


3. For each record, instruct the click handler to invoke the view function.

Binding information to elements in the DOM like this is always a bit tricky, so we’ve used the td:first selector to grab the value of the first cell—the ID—and passed that as a parameter to the view function. This function will launch a new window and provide the user with greater detail about the selected record, including a phone number and some associated notes. Let’s define that function now; add the following code to the bottom of the behaviour.js file:

function view(id) {  
 current_id = id;  
 customerWindow = air.HTMLLoader.createRootWindow(true, null, true, new air.Rectangle(0,0,640,480));  
 customerWindow.addEventListener(air.Event.COMPLETE, passDbId);  
 customerWindow.load(new air.URLRequest("app:/view_customer.html"));  
}  
 
function create() {  
createWindow = air.HTMLLoader.createRootWindow(true, null, true, new air.Rectangle(0,0,640,480));  
createWindow.addEventListener(air.Event.COMPLETE, passDb);  
createWindow.load(new air.URLRequest("app:/new_customer.html"));  
}  
 
function passDbId(event) {  
event.target.window.receiveDb(db);  
event.target.window.display_customer(current_id);  
}  
 
function passDb(event) { event.target.window.receiveDb(db); }

In the code above, the view function (and the related create function), work with the passDb and passDbId functions; these then launch new windows that render an appropriate HTML page. Remember that we need to share our database connection across multiple pages, so we’ll define a receiveDb function within the target pages. However, as our custom JavaScript function for connecting to the database is called from within the page, we need to wait for the parent page to load first!

The HTML Loader APIs in AIR permit these kinds of window logistics quite easily. Let’s break it down:

As you can see in the code above, the createRootWindow constructor accepts a few parameters, including an air.Rectangle() object that represents the dimensions of the window to be created. This function provides us with a window object, to which we add a new event listener for the COMPLETE event. This event listener will be triggered whenever a new page has finished loading. You’ll note in the view method that we store the current ID temporarily before loading the page using a URL request. Once the page has loaded, the callback function passes along the database connection and instructs the new page to display the customer details.

You may recall from the previous article that we used a hash string in the URL to pass information around between pages (for example, view_customer.html#id/1). This is a reliable hack, given AIR is a controlled environment that does not make use of an address bar—at which point our magic would be revealed! That said, taking advantage of the HTML loader system—as we’ve done here—is a much more stable and useful approach, as it allows us to pass entire objects around as needed.

Viewing Records and Receiving Information

Right! We’ve set up a system to read records from the database, but now we need to view individual customer records. We already know we’ll need two functions—receiveDB and display_customer—both of which receive information from our main customers.html window. Our view_customer.html file is also populated with sample data, but once again we’ll use jQuery to replace this with real information from the database.

Edit your view_behaviour.js file so that it looks like this:

dbConn = null;  
function receiveDb(dbFromParent) {  
 dbConn = dbFromParent;  
}  
 
function display_customer(id) {  
 dbQuery = new air.SQLStatement();  
 dbQuery.sqlConnection = dbConn;  
 dbQuery.text = "SELECT * FROM customers WHERE id=:id";  
 dbQuery.parameters[":id"] = id;  
   
 try {  
   dbQuery.execute();  
   dbQuery.clearParameters();  
 }  
 catch (error) {  
   air.trace("Error retrieving notes from DB:", error);  
   air.trace(error.message);  
   return;  
 }  
   
 var customerData = dbQuery.getResult();  
 $("#cust_name").val(customerData.data[0].name);  
 $("#cust_phone").val(customerData.data[0].phone);  
 $("#cust_notes").text(customerData.data[0].notes);  
}

Here we’ve defined a receiveDb function, which simply accepts the handle to the database connection as a parameter and stores it in a local variable. Our display_customer function is where the real work happens, retrieving the record in question and outputting it to the page.

Note that we’re using a textarea for the notes information, as opposed to creating input fields for the individual name and phone fields. While I won’t cover how to add “edit” functionality in this tutorial, the logic is much the same as for the record retrieval we just wrote; the only difference, is you would obviously use an UPDATE statement instead of a SELECT statement.

Note: Limitations with AIR and SQL
If you ever find you have to do some serious information manipulating in local SQL databases, keep in mind that there are many constraints to the AIR implementation—the Adobe AIR Language Reference has more information.

The database logic is quite similar to our logic that retrieved all customer records, only this time we’re making use of parameterized statements—as demonstrated by the following two lines:

dbQuery.text = "SELECT * FROM customers WHERE id=:id";  
dbQuery.parameters[":id"] = id;  


This is fairly straightforward; first we define a named parameter, :id, then we supply the value of that parameter separately. While we could achieve the same thing using literal values in SQL queries, prepared statements like this are much more reliable, so I prefer them. While it may be slightly overkill for this tutorial, using a prepared statement is safer (it provides SQL injection-proofing out of the box) and faster (queries can be reused without having to recompile them each time). For further details, consult the AIR manual, specifically the section on using parameters in statements.