This content has been marked as final. Show 12 replies
There are two "quick start" articles linked from this page that are very "bare bones" examples using HTML/JS and SQLite:
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?
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...
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";
var dbFile = air.File.applicationStorageDirectory.resolvePath("qt.db");
Then the query is like:
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;
statustext.innerText = "Error loading data";
air.trace("SELECT error:", error);
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"
A quick proof of concept for retrieving Quarterly Directory information.
<copyright>Copyright (c) 2008 Mayo Clinic</copyright>
I am having the same problem. Is there a very simple example with an external database?
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.
Thanks Erica - it was correcting a file path issue that did the trick for me.
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.
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:
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.
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.
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.
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();
var dbFile = air.File.applicationDirectory.resolvePath("dumpTest.sqlite");
alert("the database was opened successfully");
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";
// do something after the statement execution succeeds
alert('ran the SQL clean!');
// do something after the statement execution fails
alert('Failed to query');
selectStmt.parameters[":keywordType"] = 'sector';
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!
Thanks for the great info - didn't know about the cool Firefox plugin either.