12 Replies Latest reply on Apr 8, 2008 9:18 PM by Tiggyboo1

    Sparse air/sqlite non-flex example?

    Tiggyboo1 Level 1
      I'm just starting to traverse the plethora of example code and tutorials for AIR, most of which seem to use Flex for accessing sqlite databases. The tutorials are very complete and impressive, but I'm feeling the need for a small bit of immediate gratification via examing some code that uses nothing but AIR in the sparsest terms possible, e.g., showing perhaps a "select * from mytable." I do have extjs at my disposal as well, so if a library along those lines is required for a db connection, then so be it - I just need to see the bare bones of what is required to get my largely ignorant juices flowing. I still plan to work my way through the example code, just for the record :-) So if anyone out there has such a chunk of code lying around, or can direct me to some, that would be great.
      Regards,
      Al
        • 1. Re: Sparse air/sqlite non-flex example?
          adobe_paul Adobe Employee
          There are two "quick start" articles linked from this page that are very "bare bones" examples using HTML/JS and SQLite:

          http://www.adobe.com/devnet/air/ajax/quickstart/

          The articles are called:
          - Working asynchronously with a local SQL database
          - Working synchronously with a local SQL database

          Is that the sort of thing you're looking for?
          • 2. Re: Sparse air/sqlite non-flex example?
            Tiggyboo1 Level 1
            Yeah, that looks like just what I'm looking for, thanks a bunch. I think I kept missing it because all of my searches involved sqlite :-). I assume that's what we're talking about here under the hood...

            Thanks again,
            Al
            • 3. Sparse air/sqlite non-flex example?
              Tiggyboo1 Level 1
              Hi Paul - the examples you suggested have been fun to work with, but when I try to substitute in my own database info, I get:

              SELECT error: SQLError: 'Error #3115: SQL Error.', details:'no such table: cftable', operation:'execute'
              error.message: Error #3115: SQL Error.
              error.details: no such table: cftable

              If I go in to sqlite through the admin console, the table cftable exists and returns dynamic results quite well.

              I'm connecting to an existing sqlite database (2.8.5) via:

              conn = new air.SQLConnection();

              statustext.innerText = "Creating and opening database";

              try
              {

              var dbFile = air.File.applicationStorageDirectory.resolvePath("qt.db");
              conn.open(dbFile);
              ...

              Then the query is like:

              function getData()
              {
              statustext.innerText = "Loading data";

              selectStmt = new air.SQLStatement();
              selectStmt.sqlConnection = conn;
              var sql = "SELECT last_name, first_name, middle_name, alt_identifier_1, alt_identifier_2, alt_identifier_3 FROM cftable";
              selectStmt.text = sql;

              try
              {
              selectStmt.execute();
              }
              catch (error)
              {
              statustext.innerText = "Error loading data";

              air.trace("SELECT error:", error);
              air.trace("error.message:", error.message);
              air.trace("error.details:", error.details);

              return;
              }

              renderResult();
              }

              Any ideas? As you can see the code is shamelessly lifted from the sample except that my db specifics are substituted. Before I forget, here's the application.xml file:

              <?xml version="1.0" encoding="utf-8" ?>
              <application xmlns=" http://ns.adobe.com/air/application/1.0"
              minimumPatchLevel="1047">
              <id>edu.mayo.ava</id>
              <version>0.5</version>
              <filename>QTTest</filename>
              <name>QT Test</name>
              <description>
              A quick proof of concept for retrieving Quarterly Directory information.
              </description>
              <copyright>Copyright (c) 2008 Mayo Clinic</copyright>
              <initialWindow>
              <title>QT Test</title>
              <content>
              dbtest.html
              </content>
              <systemChrome>none</systemChrome>
              <transparent>true</transparent>
              <visible>true</visible>
              <minimizable>true</minimizable>
              <maximizable>false</maximizable>
              <resizable>false</resizable>
              <width>640</width>
              <height>480</height>
              <minSize>320 240</minSize>
              <maxSize>1280 960</maxSize>
              </initialWindow>
              <installFolder> QTTest</installFolder>
              <customUpdateUI>true</customUpdateUI>
              <allowBrowserInvocation>true</allowBrowserInvocation>
              </application>

              • 4. Re: Sparse air/sqlite non-flex example?
                wmshelly
                I am having the same problem. Is there a very simple example with an external database?
                • 5. Re: Sparse air/sqlite non-flex example?
                  enorton@adobe Level 1
                  Hi,

                  So, there could be a couple of things going on here.
                  1) SQLite update to 3.x included some pretty big changes, and AIR uses a 3.x version (I don't have the specific version in available as I'm typing this)
                  2) AIR doesn't officially support working with databases not created with AIR.

                  -Erica
                  • 6. Re: Sparse air/sqlite non-flex example?
                    Tiggyboo1 Level 1
                    Thanks Erica - it was correcting a file path issue that did the trick for me.
                    Al
                    • 7. Re: Sparse air/sqlite non-flex example?
                      zacko_2 Level 1
                      So, I have a question: You can or can not work with a SQLlite DB built outside of AIR? I have to create the DB every time my app runs? What about information that is modified in there, is that persistent?

                      Also, if you can create and include a SQLlite db not created in AIR, what is a good app to use to do so? any suggestions? I am on a Mac when developing.

                      thanks.
                      • 8. Re: Sparse air/sqlite non-flex example?
                        Tiggyboo1 Level 1
                        I created a db at the sqlite3 command line on a Mac, but had trouble accessing it. It wouldn't error out until the sql was run against a table, when in fact it had no idea where the database was at all (did not throw an error when instantiating the db.) In my case, the difference was changing:

                        air.File.applicationStorageDirectory.resolvePath('mydb');

                        to

                        air.File.applicationStorageDirectory.resolvePath('/Library/WebServer/Documents/air/dbtest/ mydb');

                        This was the path obtained with the .database command from the sqlite3 command prompt.

                        HOWEVER - it appears that I can't move the app to another computer without it dying with a "Cannot find database!" message. I haven't heard back on this elsewhere in the forum yet, but I suspect it might be because I created the db outside of AIR. So far I've tried moving it to another OSX machine and an XP machine with no success. Hope this helps.

                        • 9. Re: Sparse air/sqlite non-flex example?
                          zacko_2 Level 1
                          Yikes, sounds like a lot of trouble to create a simple DB. I have an existing MySQL DB and want to be able to include the most recent version in my AIR app, without having to build it at every launch. I hope that it turns out that I can do this! Maybe I am misunderstanding how to work with the SQLlite functionality in AIR. Anyway, thanks Tiggyboo1 for the update.
                          • 10. Re: Sparse air/sqlite non-flex example?
                            Tiggyboo1 Level 1
                            Bear in mind I'm very much a newbie with sqlite/AIR - although not a newbie to databases in general. I'm hoping I can create an AIR utility that will build and populate a database - then perhaps that database can be reuseable by other AIR apps. If the db has to build with every launch of the app, it's useless for the things I want to do.
                            • 11. Re: Sparse air/sqlite non-flex example?
                              zacko_2 Level 1
                              Alright, I got this working so that I can include a local sqllite database in my included files and connect to it. I tested it in Preview and tested it as an AIR app.

                              So here is what I did:
                              First, I used the really cool FireFox add-on called SQLite Manager 0.2.34 to create and manage the local database. That created a database called 'dumpTest.sqlite'. I saved it to the AIR app directory and then included it in the 'included files' list in the AIR setting window.

                              Inside of my HTML / JS AIR app I used the following code to connect:

                              var conn = new air.SQLConnection();
                              conn.addEventListener(air.SQLEvent.OPEN, openHandler);
                              conn.addEventListener(air.SQLErrorEvent.ERROR, errorHandler);
                              var dbFile = air.File.applicationDirectory.resolvePath("dumpTest.sqlite");
                              conn.openAsync(dbFile, air.SQLMode.READ);
                              function openHandler(event){
                              alert("the database was opened successfully");
                              }
                              function errorHandler(event){
                              alert(event);
                              }


                              It then fires an alert message to let me know it connected or failed to connect.

                              I then set up an object to query it, like so:

                              var selectStmt = new air.SQLStatement();
                              selectStmt.sqlConnection = conn;
                              selectStmt.text = "SELECT * FROM categories WHERE type = :keywordType";
                              selectStmt.addEventListener(air.SQLEvent.RESULT, resultHandler);
                              selectStmt.addEventListener(air.SQLErrorEvent.ERROR, errorHandler);

                              function resultHandler(event){
                              // do something after the statement execution succeeds
                              alert('ran the SQL clean!');
                              }

                              function errorHandler(event){
                              // do something after the statement execution fails
                              alert('Failed to query');
                              }
                              selectStmt.parameters[":keywordType"] = 'sector';
                              selectStmt.execute();


                              That works too! And I did not include my code for outputting the results, but it does output the desired results. That's it! It worked and that's how I did it. The only weird issue I ran into, and I can't reproduce it 100% of the time, is that on Win XP when I installed the AIR app it would auto launch it after install and then gave me an 'Error #3104'. So, if I closed the app and then launched it like a normal windows app, it would work fine. Not sure what the deal is with that, but it's no longer doing it. I think it may have something to do with me not chaining the query after the connection is successful. So maybe putting my query stuff inside the 'openHandler' function of the connection call would fix that issue. I'm sure thats the case. So, make sure you are connected before you query!
                              • 12. Re: Sparse air/sqlite non-flex example?
                                Tiggyboo1 Level 1
                                Thanks for the great info - didn't know about the cool Firefox plugin either.