I've mentioned that I was curious about using AIR in conjunction with Spatialite in a previous post. Unfortunately this isn't possible, but you can use the overarching SQLite database in AIR to create a compact database for your applications. There are a number of good examples out there on how to connect to SQLite in AIR. Two things that took me a little while to figure out: 1) SQLite is not available in just FLEX, you need AIR, 2) What is the SQLResult? Most of the tutorials just stop with telling you how to send a query, parameterized or otherwise, and set up an event listener to catch the result. But what is the result?
The result comes as part of a class called SQLResult. The table that you requested from the database is stored in the Data property of the SQLResult class, SQLResult.Data. This is actually just an Array. BUT! That array stores an object, where the properties of that object are the Column names (or aliases) and the values returned by that property is the row value. So what does that mean? Here is an example, presuming you've gotten as far as setting all this up.
var q:SQLStatement = new SQLStatement;
q.sqlConnection = yourConnection;
q..text = "SELECT Venue.Venue_Name AS Name, DATE(revenue_data.Rev_Date) AS Date FROM revenue_data INNER JOIN Venue ON revenue_data.Venue_ID = Venue.Venue_ID"
q.addEventListener(SQLEvent.RESULT, onResult);
q.addEventListener(SQLErrorEvent.ERROR, onError);
q.execute();
In this example we are using a SELECT statement and an INNER JOIN to return the name and date from these two tables. I've also assigned aliases to the columns, which I'll explain why in a minute.
var result:SQLResult = SQLStatement(e.target).getResult();
var darray:Array = results.Data;
trace(String(darray[0].Name));
In this next part I've gottent he results and assigned the data to an Array. Then with a simple trace I've pulled the first Row (0) with the Column name (Name). That provides me with direct access to the data. Now you might actually want to look at this in a Table. So you can use a DataGrid component for this, and simply set the DataProvider property equal to your Array, darray in the above example. No problemo. The problem I had was setting up the columns, and my work around was to change the Column names to an alias in the query, that way I could simplify the process putting more emphasis on building the query. This really is pretty inelegant in my opinion...it would be nice to be able to retrieve the schema of the query results. One other problem I have not had a work around for at the moment is the column order in the datagrid is automatically set up in alphabetic order not the returned order of the query. I'll have to look into that one further.