Adobe Air/Flex + SQLite database problem

Question

I'm still a newbie to Adobe Air/Flex, and still fairly new with SQL.

I've downloaded this (http://coenraets.org/blog/2008/11/using-the-sqlite-database-access-api-in-air…-part-1/) code and have been looking over it and I'm trying to implement the same idea.

I think it's just something stupid. I'm using Flex Builder. I made a new desktop application project, didn't import anything.

I added a DataGrid object and bound it to an ArrayCollection:

I'm trying to make it so when the program initializes it will load data from a database if it exists, otherwise it'll create a new one.

The problem is, when the application runs, the datagrid is empty. No column headers, no data, nothing. I've tried changing a whole bunch of stuff, I've used the debugger to make sure all the functions are being called like they're supposed to. I don't know what I'm doing wrong. I've compared my code to the before mentioned code, I've looked for tutorials on Google. Anyone know what I'm doing wrong?

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" width="672" height="446"
 applicationComplete="onFormLoaded()"
 title="iRecipes">

 <mx:Script>
  <![CDATA[
   import mx.collections.ArrayCollection;

   private var sqlConnection:SQLConnection;
   [Bindable] private var recipeList:ArrayCollection;

   private function onFormLoaded():void
   {
    sqlConnection = new SQLConnection();
    openDataBase();
   }

   private function openDataBase():void
   {
    var file:File = File.userDirectory.resolvePath("recipes.db");

    sqlConnection.open(file, SQLMode.CREATE);

    if(!file.exists)
    {
     createDatabase();
    }   

    populateRecipeList()
   }

   private function createDatabase():void
   {
    var statement:SQLStatement = new SQLStatement();
    statement.sqlConnection = sqlConnection;
    statement.text = "CREATE TABLE Recipes (recipeId INTEGER PRIMARY KEY AUTOINCREMENT, recipeName TEXT, authorName TEXT)";
    statement.execute();

    statement.text = "INSERT INTO Recipes (recipeName, authorName) VALUES (:recipeName, :authorName)";

    statement.parameters[":recipeName"] = "Soup";
    statement.parameters[":authorName"] = "Joel Johnson";
    statement.execute();

    statement.parameters[":recipeName"] = "Garbage";
    statement.parameters[":authorName"] = "Bob Vila";
    statement.execute();
   }

   private function populateRecipeList():void
   {
    var statement:SQLStatement = new SQLStatement();
    statement.sqlConnection = sqlConnection;

    statement.text = "SELECT * FROM Recipes";
    statement.execute();
    recipeList = new ArrayCollection(statement.getResult().data);
   }
  ]]>
 </mx:Script>

 <mx:DataGrid dataProvider="{recipeList}">

 </mx:DataGrid>
</mx:WindowedApplication>

Answer 1

I just tried this out using your code. I made a change and removed the condition as I was getting errors about the table not existing.

 //if(!file.exists)
 //{
   createDatabase();
 //}

This got the datagrid showing the correct info. I think that there is something wrong with the way you are initialising the database file. I'm having a look into it at the moment.

Try using

sqlConnection.open(file, SQLMode.CREATE);

instead, for opening the database.

Answer 2

Thanks Feet. With your suggestion, I believe I have figured it out. I changed the if statement to this:

   if(!file.exists)
   {
    sqlConnection.open(file, SQLMode.CREATE);
    createDatabase();
   }
   else   
   {
    sqlConnection.open(file, SQLMode.UPDATE);
   }

And it works great. Thanks for your help.