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.
Transactions is SQL statements are something that I have come to love. I'm sure you have too. But for those that don't know what transactional control in a database management system is, we'll start with a definition.
Usually, when doing multiple SQL statements in a row in an application, each SQL statement is handled as a atomic unit and is committed permanently to the database before the next one is run. This can be very problematic when those queries depend on each other to work properly to maintain data integrity.
On Wednesday, September 2nd, I will be presenting at the Twin Cities ColdFusion User Group meeting. At this meeting we are going to be trying something new (at least for me since I have been going to the CFUG). We are going to do some hands-on work with the technologies we love instead of just doing a lecture-style presentation.
The work we will be doing is with Adobe AIR, JavaScript, jQuery, and SQLite. Here is the description for the session:
In a previous post we looked at doing simple CRUD with Adobe AIR and SQLite and doing CREATE TABLE statements. But the examples we've looked at are VERY simple. In fact, we have not looked at any dynamically constructed queries.
Today I want to look at properly building dynamic queries in AIR using bind parameters.
So in case you you've been living under a rock for the last several years, you know that CRUD stands for Create, Read, Update and Delete. Which is what we are going to look at today, doing simple SQL statements with SQLite databases in Adobe AIR using JavaScript.
We saw in my last couple posts how do do simple CREATE statements using both synchronous and asynchronous connections. I will paste them here again so that we have the reference all on one page.
So in my last AIR and SQLite post we talked about Synchronous Database Connections in AIR.
In many cases, synchronous connections may be all you need. If your queries are fast and a slight applicaiton pause is not a concern, or if you have a need for rigid program flow control, then synchronous connections are great. But there may come a time when you do not want the program to pause during a query, or series of queries. You may want the user to be able to continue working while the queries take place in the background. This is where asynchronous queries come in.
Last night I got together, online, with Todd Raffery, Ben Nadel and Andy Matthews to talk about Adobe AIR and jQuery.
Last week, while twittering, we came up with the idea of having a "virtual classroom" setting, using Adobe Connect to get together and work through building an AIR application from scratch. So we did just that. This was a private session, so don't feel like you missed an announcement. I wanted to keep it small and informal so that we could feel OK about speaking freely, having fun and learning through questions and discussion.
So as we discussed last time, there are two ways to connect to a SQLite database with Adobe AIR. Today we are going to look at how to make a synchronous connection.
Just as a reminder, when we use a synchronous connection to connect to the database, the program will not move forward in processing until it is done processing a statement. With these small statements, that really shouldn't be noticeable.
If you are planning on building an Adobe AIR application, you need to be familiar with the concepts of Synchronous and Asynchronous requests, henceforth referred to as sync and async, respectiviely.
The terms sync and async are used all over the place. There are sync and async transmissions, sync and async learning, sync and async communications, and in Ajax and AIR we have sync and async requests and connections.
One of the best parts about working with Adobe AIR applications is the ability to have applications that work both online and offline. Since Adobe AIR applications are desktop applications that can run without the browser and without the need for an HTTP server or middleware server (ColdFusion, .NET, PHP, etc), they can be used without a connection to the internet. Of course we still need to write code to handle the "sometimes connected" abilities of our application and sometimes we need to be able to store data that we would normally receive from the server. This data can be stored in an embedded SQLite database.
