4 Replies Latest reply on Jul 10, 2008 4:07 PM by Dr. Fred Mbogo

    AIR + AS3 + SQLite = tons of questions

      Hi - first off, like many who end up posting I lean more to "designer" than "developer". Which probably has the programmer groaning :)

      Anyway what I want to do is develop an AIR application through Flash (AS3). Basically I want to have a desktop nerd database so I can catalog and look up the japanese vinyl figures I have.

      The layout is simple = 3 tabs (view fields and image, enter / modifiy / delete rows and god willing, a search function). The view area is basic - data display on the left, image on the right. I figured I'd do it with separate text areas however if using the datagrid component is easier then that'll also work.

      The enter tab will have matching fields to enter in the kaiju info along with adding a photo. And then there's the search...

      I saw the
      dev how-to for SQlite

      help here, which is very detailed however it doesn't really get me past the creation of the database. Now the one thing that I wasn't sure of was whether or not the database is physically created when I do a test compile in Flash, or only when I go through the whole process of packaging up the AIR app and then installing it.

      Now when I follow the directions on the how-to I can get the first part, the creation of the database, to show up in the "output" window. when I go to create a table, though, I get errors. and this is the same for both the code I cobbled together (with different fields) or the same as shown in the example. Here is the code I have:


      ////// start code
      import flash.display.NativeWindow;
      import flash.display.StageAlign;
      import flash.display.StageScaleMode;
      import flash.display.Loader;
      import flash.display.Sprite;

      import flash.desktop.NativeApplication;

      import flash.events.Event;
      import flash.events.SQLErrorEvent;
      import flash.events.SQLEvent;
      import flash.events.MouseEvent;

      import flash.data.SQLConnection;
      import flash.data.SQLStatement;

      import flash.filesystem.File;

      // tab buttons
      this.viewOffBtn.addEventListener(MouseEvent.MOUSE_DOWN, viewOffBtn_CLICK);
      function viewOffBtn_CLICK(e:MouseEvent):void
      {
      this.gotoAndStop(5);
      databaseOffBtn.gotoAndStop(1);
      searchOffBtn.gotoAndStop(1);
      }

      this.databaseOffBtn.addEventListener(MouseEvent.MOUSE_DOWN, databaseOffBtn_CLICK);
      function databaseOffBtn_CLICK(e:MouseEvent):void
      {
      this.gotoAndStop(5);
      viewOffBtn.gotoAndStop(1);
      searchOffBtn.gotoAndStop(1);
      }

      this.searchOffBtn.addEventListener(MouseEvent.MOUSE_DOWN, searchOffBtn_CLICK);
      function searchOffBtn_CLICK(e:MouseEvent):void
      {
      this.gotoAndStop(5);
      viewOffBtn.gotoAndStop(1);
      databaseOffBtn.gotoAndStop(1);
      }

      // window dressing
      var mainWindow:NativeWindow = this.stage.nativeWindow;
      //mainWindow.maximize();
      mainWindow.activate();

      var options:NativeWindowInitOptions = new NativeWindowInitOptions();
      options.systemChrome = NativeWindowSystemChrome.NONE;
      options.type = NativeWindowType.UTILITY
      options.transparent = true;
      options.resizable = false;
      options.maximizable = true;

      baseMC.addEventListener(MouseEvent.MOUSE_DOWN, baseMC_CLICK);
      function baseMC_CLICK(e:MouseEvent):void
      {
      stage.nativeWindow.startMove();
      }

      minimizeBtn.addEventListener(MouseEvent.CLICK, minimizeButton_CLICK);
      function minimizeButton_CLICK(e:MouseEvent):void
      {
      stage.nativeWindow.minimize();
      }
      maximizeBtn.addEventListener(MouseEvent.CLICK, maximizeBtn_CLICK);
      function maximizeBtn_CLICK(e:MouseEvent):void
      {
      stage.nativeWindow.maximize();
      }

      closeBtn.addEventListener(MouseEvent.CLICK,closeButton_CLICK);

      function closeButton_CLICK(e:MouseEvent):void
      {
      NativeApplication.nativeApplication.exit();
      }


      // database start
      var conn:SQLConnection = new SQLConnection();
      conn.addEventListener(SQLEvent.OPEN, openHandler);
      conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
      var dbFile:File = File.applicationStorageDirectory.resolvePath("DBSample.db");
      conn.openAsync(dbFile);
      function openHandler(event:SQLEvent):void
      {
      trace("the database was created successfully");
      }
      function errorHandler(event:SQLErrorEvent):void
      {
      trace("Error message:", event.error.message);
      trace("Details:", event.error.details);
      }

      var createStmt:SQLStatement = new SQLStatement();
      createStmt.sqlConnection = conn;
      var sql:String =
      "CREATE TABLE IF NOT EXISTS kaiju (" +
      " kaiId INTEGER PRIMARY KEY AUTOINCREMENT, " +
      " kaijuName TEXT, " +
      " companyName TEXT, " +
      " collectionSize NUMERIC CHECK (collectionSize > 0), " +
      " headerCard Text, " +
      " limitedEdition Text, " +
      " kaijuCondition NUMERIC CHECK (kaijuCondition > 0), " +
      " notes Text" +
      ")";

      createStmt.text = sql;
      createStmt.addEventListener(SQLEvent.RESULT, createResult);
      createStmt.addEventListener(SQLErrorEvent.ERROR, createError);
      createStmt.execute();
      function createResult(event:SQLEvent):void
      {
      trace("Table created");
      }
      function createError(event:SQLErrorEvent):void
      {
      trace("Error message:", event.error.message);
      trace("Details:", event.error.details);
      }


      /////////// end code

      which results in the following errors on test compile:

      Error: Error #3104: A SQLConnection must be open to perform this operation.
      at Error$/throwError()
      at flash.data::SQLStatement/checkAllowed()
      at flash.data::SQLStatement/checkReady()
      at flash.data::SQLStatement/execute()
      at kaiju_collector_fla::MainTimeline/frame1()

      and when I run the debugger, this is the output:
      Attemping to launch and connect to Player using URL /Users/xxx/Desktop/xxx/kaiju/kaiju_collector-app.xml
      [SWF] kaiju_collector.swf - 11056 bytes after decompression
      Error: Error #3104: A SQLConnection must be open to perform this operation.
      at Error$/throwError()
      at flash.data::SQLStatement/checkAllowed()
      at flash.data::SQLStatement/checkReady()
      at flash.data::SQLStatement/execute()
      at kaiju_collector_fla::MainTimeline/frame1()[kaiju_collector_fla.MainTimeline::frame1:113]
      Cannot display source code at this location.
      Cannot display source code at this location.
      Cannot display source code at this location.
      Cannot display source code at this location.

      So that's problem #1 is just getting this baby to write the database and table.

      The following questions pertain to the next step:

      + The first block of code for the database is to create the database. how will the script know not to create the same DB every time?

      + Upon successful writing of the DB / Table fields, then comes the fun task of entering in the info. I really haven't done anything with the datagrid component -- this doesn't seem to do more than display data rather than writing and displaying. So then the other option is to create the various input areas and have the submit button drop everything into the database?

      + to display the info I'll need to have a back / next option that refreshes the data, which I think should be fairly easy to work out however I didn't know if there was anything specific to keep an eye on with SQLite.

      + When creating the fields via SQL I read about setting one field for BLOB to contain the pictures of the kaiju although finding any further info on this pointed only to Flex.

      There seems to be much more coverage on this for Flex 3 rather than totally in Flash. I'm comfortable with Flash and have only monkeyed around with Flex a few times so I'm not too familiar with the environment. Would something like this be better to follow through with Flex (or even in Dreamweaver) rather than in Flash?

      Thank you for any and all help / pointers / suggestions!
        • 1. Re: AIR + AS3 + SQLite = tons of questions
          Joe ... Ward Level 4
          The database not open issue is because you are opening the database asynchronously, but you are accessing it immediately (before it has a chance to finish opening). To fix this, put the rest of the database setup code into the openHandler() function.

          If the database file already exists, AIR doesn't create a new one. If the table in the database already exists, then the IF NOT EXISTS clause prevents AIR from trying to create it again.

          Yes, you will have to create some sort of form to enter the data (unless you want to enter SQL statements by hand ;).

          You could store the pictiures in the database, or you could save them to the app-storage directoy and only save the path in the database. The latter is simpler to code and keeps the database smaller, but you lose portability since the data is no longer in a single file. Six of one, half dozen of the other.

          All the SQL classes are ActionScript, there's nothing Flex-centric about them. In general, Flex does have more support for building a data display GUI, though.

          1 person found this helpful
          • 2. Re: AIR + AS3 + SQLite = tons of questions
            Thanks for the help! That explained much and I'm going to take another crack at this.

            I had thought about storing just the path to the images, although there's always that issue of if I move the directory then the images will break and I'll still have to have a way of updating the path.

            After posting I played around with the data grid a bit more. The last time I used this component was years ago and it was more clunky than it is now. Plus I dug up on the Adobe support site how to have the data grid display images along with info, so that might work out too.
            • 3. Re: AIR + AS3 + SQLite = tons of questions
              So moving the AS to fall under the open statement worked!

              One final question is...where's the database? I thought the create statement would create a 'physical' db file. Or do I need to do this externally?

              Thanks!
              • 4. Re: AIR + AS3 + SQLite = tons of questions
                Dr. Fred Mbogo Level 1
                You told it to put the DB file in the application storage directory:

                var dbFile:File = File.applicationStorageDirectory.resolvePath("DBSample.db");

                So, that's where it is. The location of the application storage directory depends on the OS, and various details about your particular program. It's complex to avoid a risk of directory name conflicts. For more, RTFM:

                http://livedocs.adobe.com/flex/3/html/Filesystem_03.html