Paul Robertson's words, punctuated

Thoughts on development, user-centered design, code, etc. by Paul Robertson

AIR embedded SQL database: What's new in beta 2

Now that the roar of MAX is over, and since AIR public beta 2 is now available on Adobe Labs, I thought I’d highlight what’s new in beta 2 specifically around the embedded SQL database in AIR:

Synchronous database operations

From the various public and internal feedback forums I’ve seen (discussion lists, mailing lists, etc.) this is definitely the most-requested feature from beta 1 (at least related to the embedded database). It’s in there now – when you instantiate a SQLConnection object, just add true as a constructor argument and all the operations (including SQL statements) that are executed through that SQLConnection will run synchronously.

The good news is it’s really very similar to the way things worked in beta 1, so if you’ve already been doing things asynchronously you won’t have to do much to get going with synchronous db operations. Note that a lot of people have referred to this as a “synchronous API” but in fact there isn’t a separate synchronous API – it’s the same API, with the exception of the single SQLConnection() argument.

To learn more, here’s the place where you should probably start: Developing AIR Applications > Working with files and data > Working with local SQL databases > Using synchronous and asynchronous database operations (Flex | HTML/JavaScript | Flash)

Schema API (database instrospection)

One limitation that developers pointed out in beta 1 was that there wasn’t any built-in way to get information about the structure of a database, tables, etc. In beta 2, there’s a new set of classes that can be used to get at that information. There isn’t any information in the developers guide about this yet, but in this case you can figure it out by looking in the API reference. The best place to start is probably with the SQLConnection.loadSchema() method (Flex | HTML/JavaScript | Flash).

There are also several new classes that are used for the various types of schema information you can get:

Additional data types

Several ActionScript/JavaScript data types can now be used as column data types in the CREATE TABLE statements. When you specify one of these data types for your column, you can pass an instance of that type into an SQL statement (using a parameter) and it will property store and retrieve the data as the ActionScript/JavaScript data type. The new supported data types are:

  • Boolean
  • Date
  • XML

More information about these data types can be found in the developers guide section “Working with database data types” (Flex | HTML/JavaScript | Flash) and in the language reference appendix “SQL support in local databases” (specifically the ”data type support” section). (Note that the link goes to the Flex language reference. I couldn’t find that appendix in the Flash or HTML/JS versions, although the content is identical for all of them since it’s really just talking about SQL.)

Other new documentation

Although I did take several weeks between beta 1 and beta 2 to move from Indiana to the San Francisco bay area, I did get some additional documentation written. If I remember right, these are the sections that are new for beta 2 (other than the ones I’ve already mentioned above):

  • Using parameters in statements (Flex | HTML/JavaScript | Flash)
  • Strategies for working with SQL databases (Flex | HTML/JavaScript | Flash) - new sections on improving performance and best practices
  • … and more! (improvements and corrections in various other sections)

Bug fixes

Need I say more? =)

(In seriousness, lots of bugs were fixed, so if there’s an issue you were running into, try it out in beta 2 and let us know if it’s still not working.)