Marcos Placona Blog

Programming, technology and the taming of the web.

SQLite databases and mobile applications – A caveat

Reading time: 2 – 4 minutes

As some of you might have noticed, I have been building some mobile applications lately on my spare time specifically for the Blackberry Playbook. They are mainly built in Adobe Air using Actionscript 3 and Blackberry’s Tablet SDK.

On my latest application, I have found the need for a database, as it needs to store lots of user input data. The first option that comes to mind is SQLite, as it’s very simple to integrate, and has native support.

I will show here an integration example, and focus on a caveat I found while trying to use it on a real device.

For starters, here are the classes you need to import on your main class:

import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.filesystem.File;

I then create a method to initialize my database connection:

private var dbConnection:SQLConnection = new SQLConnection;
private function initDB():void{
	var embededSessionDB:File = File.applicationDirectory.resolvePath("assets/db.sqlite");
	var writeSessionDB:File = File.applicationStorageDirectory.resolvePath("assets/db.sqlite");
	// If a writable DB doesn't exist, we then copy it into the app folder so it's writteable
	if (!writeSessionDB.exists) {
		embededSessionDB.copyTo(writeSessionDB);
	}
	var dbFile:File = writeSessionDB;
	dbConnection.open(dbFile);
}

What is important to mention here, is that most people (myself being one of them) will be completely tempted to skip the bit where it copies the local file (coming from the application itself) into the local storage. Funnily enough, if you skip this step, when you test it locally, it will all work wonderfully, but when you deploy it into a real device (or even a simulator) nothing happens at all.

After some research, I found out that you need to be using the database from the local storage, and just like this is that you will have read and write permissions.

After getting that small “detour” out of the way, I can go on and write my first query against that database.

private function getWords(intTabID:int):Array{
	var stmt:SQLStatement = new SQLStatement();
	stmt.sqlConnection = dbConnection;
	stmt.text = "SELECT key, word FROM WORDS WHERE category_id = (:wordID)";
	stmt.parameters[':wordID'] = intTabID;
	stmt.execute();
	var result:Array = stmt.getResult().data;
	return result;
}

And this will return a nice array with all my data from the SQLite database.

14 Comments

  1. Thanks very much

    I had same problem what you faced
    Now I solved problem ,as per article instruction

  2. Marcos Placona

    June 20, 2011 at 13:11

    Glad to be of help Barani. thanks for commenting :-)

  3. hello!. In your example the “db.sqlite” it´s created with SQLite Databasebrowser? my problem it´s not read nothing!! You can help me? :(

  4. Marcos Placona

    August 30, 2011 at 17:18

    I used an AIR application called Lita to create my database. Just google for it and you’ll find. it’s really good.

  5. How to retrieve data in mxml file which return from “getWords” function.

  6. Thank you!!! this was causing me some serious grief. So simple yet I never thought of this. :)

  7. Marcos Placona

    October 24, 2011 at 09:53

    Glad it helped you!

  8. Thank you very much for your code,
    Now i want to ask how i ship SQLite database with Flex application in app store??

  9. Marcos Placona

    September 15, 2012 at 22:35

    The database will be part of the bundle. On my applications, the database lives under src/db. Where db is a directory I created under src.

  10. thanx Marcos Placona

  11. but when i am change database field in th src/db file , it is not reflected in the application.
    plz help me out.
    THnx in advance.

  12. Marcos Placona

    October 10, 2012 at 08:11

    Glad to be helping :-)

  13. but when i am change database field in th src/db file , it is not reflected in the application.
    plz help me out.
    THnx in advance.

  14. I’m trying to do this but I found it difficult.

    var selectStmtBB:SQLStatement = new SQLStatement();
    selectStmtBB.sqlConnection = conn;

    conn.begin();
    for(var iB:int = 0; iB < newArray.length; iB++){
    selectStmtBB.text = "SELECT * FROM Contenido WHERE Id=@ID";
    selectStmtBB.parameters["@ID"] = newArray[iB];
    selectStmtBB.execute();}

    conn.commit();
    var resultB:SQLResult = selectStmtBB.getResult();
    var numResultsBB:int = resultB.data != null ? resultB.data.length : 0;

    for(var iA:int = 0; iA < numResultsBB; iA++){…. etc

    shows only the last record of array…. :(

1 Pingback

  1. learnSQL.99hosting.info » Blog Archive » SQLite databases and mobile applications – A caveat | Adobe …

Leave a Reply

Your email address will not be published.

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>