Working AIR / SQLite DataGrid example
!!!August 24 - The following code is for example only - DON’T copy and paste… the lovely little syntax highlighter screwed up casing and tag names. Just pay attention to the methods noted above and general structure.. I’ll post up the correct files later on
Okay so in my excitement to check out AIR/Apollo I tried to get the SQLite connection working. The sample code in the livedocs on one of the initial Flex Builder builds (the online docs have since been corrected), had a few errors in it … So I reworked an example from start to finish to:
1. Open a connection
2. Create a table
3. Populate the table
4. Fetch from the table
5. Put results into a dataprovider.
A couple of errors that will set you wrong in the docs, I’ve highlighted them below **. (Adobe … feel free to put this into your examples and tidy it accordingly)
1. The example accesses SQLDBStatement, which doesn’t exist - it’s called SQLStatement
2. Instead of the method .db on SQLStatement it’s .sqlConnection. Which will get you a working connection.
Application File - test.mxml
<mx:windowedapplication xmlns:mx="http://www.adobe.com/2006/mxml" xmlns:rh="com.rowanh.components.*" layout="absolute"> <rh:customers width="100%" height="100%"> </rh:customers> </mx:windowedapplication>
Datagrid component - com/rowanh/components/Customers.mxml
(note watchout for the cdata tags when you copy and paste… syntax highlighting isn’t perfect)
<mx:DataGrid xmlns:mx="http://www.adobe.com/2006/mxml" creationcomplete="initConnection()" dataprovider="{customerCollection}"> <mx:script> <!--[CDATA[ import flash.net.Responder; import mx.collections.ICollectionView; import flash.data.* import flash.filesystem.File; import flash.net.Responder; private var db:SQLConnection = new SQLConnection(); private var dbStatement:SQLStatement = new SQLStatement(); //*** Was SQLDBStatement in docs private var customerResponder:Responder = new Responder(customersResult); [Bindable] private var customerCollection:Array; private function initConnection():void { var dbFile:File = new File(File.separator + "customers.db"); db.addEventListener(SQLEvent.OPEN, dbOpenHandler); db.addEventListener(SQLErrorEvent.ERROR, errorHandler); dbStatement.sqlConnection = db; //*** method was db not sqlConnection in docs. db.open(dbFile); } private function dbOpenHandler(event:SQLEvent):void { initTable(); } private function initTable():void { dbStatement.text = "CREATE TABLE IF NOT EXISTS customers ( id INTEGER PRIMARY KEY, name TEXT, description TEXT )"; dbStatement.addEventListener(SQLEvent.RESULT, insertCustomers); dbStatement.execute(); } private function insertCustomers(event:SQLEvent):void { dbStatement.removeEventListener(SQLEvent.RESULT, insertCustomers); dbStatement.addEventListener(SQLEvent.RESULT, getCustomers); dbStatement.text = "INSERT INTO customers (name, description) VALUES ('Harry', 'Manager')"; dbStatement.execute(); } private function getCustomers(event:SQLEvent):void { dbStatement.removeEventListener(SQLEvent.RESULT, getCustomers); dbStatement.text = "SELECT * FROM CUSTOMERS" dbStatement.execute(-1, customerResponder); // For the love of ... I could not add an SQLEvent.Result handler and get CustomersResult to handle it, // I had to go through the customerRepsonder which just seems wrong. } private function customersResult(result:SQLResult):void { customerCollection = result.data; } private function errorHandler(error:SQLError):void { trace("Failedure."); trace("code: " + error.code); trace("operation:" + error.operation); trace("details:" + error.message); } ]]--> <mx:DataGridColumn datafield="id"> <mx:DataGridColumn datafield="name"> <mx:DataGridColumn datafield="description"> </mx:DataGrid>
13 Comments, Comment or Ping
Jit
hi,
I’m pretty new to air + sqlite.
When I run the above code i’m getting the error:
Could not resolve to a component implementation.
Is there any chance you could email me a zip of your working version.
Thanks
jit
Jun 27th, 2007
Jit
Got it working now.
Case Sensitive
Jun 27th, 2007
jayan
Hi,
I too am trying out the SQLite API in AIR. I get the same error.
Could not resolve to a component implementation.
Is case-sensitivity the problem? how do i give then? Any help is appreciated
Jul 5th, 2007
Riley
This code is full of errors. Did you bother testing it before posting to your blog? There is no such thing as a tag in mxml.
Aug 24th, 2007
Riley
my tag got stripped from last comment datagrid should be DataGrid
Aug 24th, 2007
admin
I did have it working - evident that the syntax highlighter when I posted screwed up some of the tags (somehow changed casing and tag terminations so it was all over the place). Just looked at it in my new layout and it’s truncating long lines (great). Thanks for bringing it to my attention - fixing it up now…
Aug 24th, 2007
Jason Graves
This code makes the most sense to me, and I have everything cleaned up, except for 2-errors… Any chance I can get someone to look at the code? I posted it up here - http://www.z2tech.com/codesample.cfm
Here are the errors I’m getting:
Error 1:
Access of Undefined property customerCollection (Line 1: Customers.mxml)
Error 2:
Call to a possibly undefined method initConnection (Line 1: Customers.mxml)
Aug 31st, 2007
Jason
Hey Rowan, This looks great but man you are killing me with the source source code all chopped off and messed up! Can you just post a text file or something?
Nov 7th, 2007
Mike
Hi,
i am trying out AIR + SQLite. I have been lucky so far. But I got few questions, hope anyone can help me out, pls!
1) my db was successfully created, but i was wondering how to find out the actual file location(my db name is account.db).
2) further thinking, if i need to reinstall my app, but dont want to lose all the data i entered. how could i do that?
thanks in advance…… really appreciate!
Mar 6th, 2008
Jonah
Hello
My name is Jonah Huggins, and I am a programmer trying to figure out a simple syntax problem that I am having with an AIR app that I am writing.
So here is what I am trying to do…
I am trying to send the text that is stored in a variable to my SQLite db. I have no idea what the syntax is but I know that it is simple…
Here is a bit of my code where the problem occurs.
private function postComment():void {
var commentVAR:String = commentText.text;
//Alert.show(commentVAR);
var q:SQLStatement = new SQLStatement();
q.text = “UPDATE data SET comment=commentVAR WHERE prim = 1″;
q.sqlConnection = conn;
q.addEventListener( SQLErrorEvent.ERROR, queryError );
q.execute();
//Alert.show(q.text);
}
As you can see I am trying to update a field by sending it the value of the variable “commentVAR”… We have tried countless syntaxes and researched for hours with no luck or example of what the syntax is for doing this simple thing, we have even tried just updating with commentText.text but “commentText.text” is what gets stored…
Ps. Everything else works great, but when we try to get the variable’s data stored in the db it just stores “commentVAR” instead.
If anyone could help me out on this it sure would be appreciated.
Thank you so much :
Jonah Huggins
Jun 3rd, 2008
Jacob
For jonah:
I used the following in the q.text:
UPDATE data SET comment=’”+commentVAR+”‘, secondrecord=’”+secondvalue+”‘
so this should do the trick.
Greets, J.
Aug 11th, 2008
Jacob
I have a question also,
Since you’ve opened a connection with the .db file.
like this: db.open(dbFile);
How do you close that file?
What i am trying to do is create a backup system for my .db file and save it in a zip.
When i try to write it to the zip i get the error;
Error: Error #3013: File or directory is in use.
so i did set the dbFile to null.. but is this enough?
And do i need to do a db.open(dbFile) each time i need to get some data from the db?
Please let me know.
Aug 11th, 2008
Reply to “Working AIR / SQLite DataGrid example”